This statement creates a tablespace set that can be used as a logical storage unit for one or more sharded tables and indexes. A tablespace set consists of multiple Oracle tablespaces distributed across shards in a shardspace.

The CREATE TABLESPACE SET statement is intended specifically for distributed databases. Its syntax is similar to CREATE TABLESPACE.

CREATE TABLESPACE SET tablespace_set 
	   [IN SHARDSPACE shardspace]
      		[USING TEMPLATE (
    { MINIMUM EXTENT size_clause
    | BLOCKSIZE integer [ K ]
    | logging_clause
    | FORCE LOGGING
    | ENCRYPTION tablespace_encryption_spec
    | DEFAULT [ table_compression ] storage_clause
    | { ONLINE | OFFLINE }
    | extent_management_clause
    | segment_management_clause
    | flashback_mode_clause
    }...
   )];

Note that in system-managed sharding there is only one default shardspace in the distributed database. The number of tablespaces in a tablespace set is determined automatically and is equal to the number of chunks in the corresponding shardspace.

All tablespaces in a tablespace set are bigfile tablespaces and have the same properties. The properties are specified in the USING TEMPLATE clause and they describe the properties of one single tablespace in the tablespace set. This clause is the same as permanent_tablespace_clause for a typical tablespace, with the exception that a data file name cannot be specified in the datafile_tempfile_spec clause. The data file name for each tablespace in a tablespace set is generated automatically.

Note that a tablespace set can only consist of permanent tablespaces, there is no system, undo, or temporary tablespace set. Also, note that in the example below the total data file size of the tablespace set is 100mxN (where N is the number of tablespaces in the tablespace set).

Example

CREATE TABLESPACE SET TSP_SET_1 IN SHARDSPACE sgr1 
USING TEMPLATE
( DATAFILE SIZE 100m
  EXTEND MANAGEMENT LOCAL
  SEGMENT SPACE MANAGEMENT AUTO
);

This statement alters a tablespace set that can be used as a logical storage unit for one or more sharded tables and indexes.

The SHARDSPACE property of a tablespace set cannot be modified. All other attributes of a tablespace set can be altered just as for a regular permanent tablespace. Because tablespaces in a tablespace set are bigfile, the ADD DATAFILE and DROP DATAFILE clauses are not supported.

These statements drop or purge a tablespace set, which can be used as a logical storage unit for one or more sharded tables and indexes.

The syntax and semantics for these statements are similar to DROP and PURGE TABLESPACE statements.

The CREATE TABLE statement has been extended to create sharded and duplicated tables, and specify a table family.

Syntax

CREATE [ { GLOBAL TEMPORARY | SHARDED | DUPLICATED} ] 
	     TABLE [ schema. ] table
      { relational_table | object_table | XMLType_table }
      	[ PARENT [ schema. ] table ] ;

The following parts of the CREATE TABLE statement are intended to support distributed databases

• The SHARDED and DUPLICATED keywords indicate that the table content is either partitioned across shards or duplicated on all shards respectively. The DUPLICATED keyword is the only syntax change to create duplicated tables. All other changes described below apply only to sharded tables.

• The PARENT clause links a sharded table to the root table of its table family.

• In system and composite sharding, to create a sharded table, TABLESPACE SET is used instead of TABLESPACE. All clauses that contain TABLESPACE are extended to contain TABLESPACE SET.

• Three clauses: consistent_hash_partitions, consistent_hash_with_subpartitions, and partition_set_clause in the table_partitioning_clauses.

  table_partitioning_clauses ::=
  {range_partitions
  | hash_partitions
  | list_partitions
  | composite_range_partitions
  | composite_hash_partitions
  | composite_list_partitions
  | reference_partitioning
  | system_partitioning
  | consistent_hash_partitions
  | consistent_hash_with_subpartitions
  | partition_set_clause
  }

Example

CREATE SHARDED TABLE customers 
( cust_id     NUMBER NOT NULL
, name        VARCHAR2(50)
, address     VARCHAR2(250) 
, location_id VARCHAR2(20)
, class       VARCHAR2(3)
, signup_date DATE
,
CONSTRAINT cust_pk PRIMARY KEY(cust_id, class)
)
PARTITIONSET BY LIST (class)
PARTITION BY CONSISTENT HASH (cust_id)
PARTITIONS AUTO
(PARTITIONSET gold   VALUES (‘gld’) TABLESPACE SET ts2,
 PARTITIONSET silver VALUES (‘slv’) TABLESPACE SET ts1)
;

Example of consistent_hash_with_subpartitions

CREATE SHARDED TABLE Customers
       ( "custi_id" NUMBER NOT NULL
       , name VARCHAR2(50)
       , class VARCHAR2(3) NOT NULL
       , signup_date DATE
       ,
       CONSTRAINT cust_pk PRIMARY KEY("custi_id",name,signup_date,class)
       )
       PARTITIONSET BY LIST (class)
       PARTITION BY CONSISTENT HASH ("custi_id",name)
       SUBPARTITION BY RANGE (signup_date)
       SUBPARTITION TEMPLATE
          ( SUBPARTITION per1 VALUES LESS THAN (TO_DATE('01/01/2000','DD/MM/YYYY'))
          , SUBPARTITION per2 VALUES LESS THAN (TO_DATE('01/01/2010','DD/MM/YYYY'))
          , SUBPARTITION per3 VALUES LESS THAN (TO_DATE('01/01/2020','DD/MM/YYYY'))
          , SUBPARTITION future VALUES LESS THAN (MAXVALUE))
       PARTITIONS AUTO
       (
         PARTITIONSET "gold" VALUES ('Gld','BRZ') TABLESPACE SET ts1 SUBPARTITIONS STORE IN(TBS1,TBS2,TBS3,TBS4)
       , PARTITIONSET "silver" VALUES ('Slv','OTH') TABLESPACE SET ts2 SUBPARTITIONS STORE IN(TBS5,TBS6,TBS7,TBS8)
       ) ;

Limitations

Limitations for sharded tables in the current release:

• There is no default tablespace set for sharded tables.

• A temporary table cannot be sharded or duplicated.

• Index-organized sharded tables are not supported.

• A sharded table cannot contain a nested table column or an identity column.

• A primary key constraint defined on a sharded table must contain the sharding column(s). A foreign key constraint on a column of a sharded table referencing a duplicated table column is not supported.

• System partitioning and interval range partitioning are not supported for sharded tables. Specification of individual hash partitions is not supported for partitioning by consistent hash.

• A column in a sharded table used in PARTITION BY or PARTITIONSET BY clauses cannot be a virtual column.

Duplicated tables in the current release are not supported with the following:

• System and reference partitioned tables

• Non-final abstract types

• Maximum number of columns without primary key is 999

• The nologging option

• XMLType column in a duplicated table cannot be used in non-ASSM tablespace

See CREATE TABLE for more information about using the clauses supporting distributed databases.

The ALTER TABLE statement is extended to modify sharded and duplicated tables.

There are limitations on using ALTER TABLE with a distributed database.

The following options are not supported for a sharded table in a system-managed or composite distributed database:

• Rename

• All operations on individual partitions and subpartitions

• All partition-related operations on the shard, except TRUNCATE partition, UNUSABLE LOCAL INDEXES, and REBUILD UNUSABLE LOCAL INDEXES

The following are not supported for duplicated tables:

• Data types: Non-final abstract types

• Column options: vector encode, invisible column, nested tables

• Clustered table

• External table

• ILM policy

• PARENT clause

• Flashback table operation

• System and Reference partitioning

• Enable NOLOGGING option

• Drop duplicated table materialized view log

• Drop duplicated table materialized views on shards

• Alter materialized views (of duplicated tables) on shards

The ALTER SESSION statement is extended to support distributed databases.

The session-level SHARD DDL parameter sets the scope for DDLs issued against the shard catalog database.

ALTER SESSION { ENABLE | DISABLE } SHARD DDL;

When SHARD DDL is enabled, all DDLs issued in the session are executed on the shard catalog and all shards. When SHARD DDL is disabled, a DDL is executed only against the shard catalog database. SHARD DDL is enabled by default for a distributed database user (the user that exists on all shards and the catalog). To create a distributed database user, the SHARD DDL parameter must be enabled before running CREATE USER.