Objects created using GDSCTL creates global, sharded database objects; however, you can create local or global objects by connecting to the shard catalog with SQL*Plus.

When a DDL to create an object is issued using the GDSCTL sql command, the object is created on all of the shards. A master copy of the object is also created in the shard catalog database. An object that exists on all shards, and the shard catalog database, is called a sharded database object.

When connecting to the shard catalog using SQL*Plus, two types of objects can be created: sharded database objects and local objects. Local objects are traditional objects that exist only in the shard catalog. Local objects can be used for administrative purposes, or they can be used by multi-shard queries originated from the shard catalog database, to generate and store a report, for example.

Sharded objects cannot have any dependency on local objects. For example, you cannot create an all-shard view on a local table.

The type of object (sharded database or local) that is created in a SQL*Plus session depends on whether the SHARD DDL mode is enabled in the session. This mode is enabled by default on the shard catalog database for the all-shards user, which is a user that exists on all of the shards and the shard catalog database. All of the objects created while SHARD DDL is enabled in a session are sharded database objects.

To enable SHARD DDL in the session, the all-shards user must run

ALTER SESSION ENABLE SHARD DDL

All of the objects created while SHARD DDL is disabled are local objects. To create a local object, the all-shards user must first run

ALTER SESSION DISABLE SHARD DDL

See ALTER SESSION for more information about the SHARD DDL session parameter.

Oracle Sharding includes SQL DDL statements with syntax that can only be run against a sharded database.

Changes to query and DML statements are not required to support Oracle Sharding, and the changes to the DDL statements are very limited. Most existing DDL statements will work the same way on a sharded database, with the same syntax and semantics, as they do on a non-sharded database.

• CREATE TABLESPACE SET
  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.
• ALTER TABLESPACE SET
  This statement alters a tablespace set that can be used as a logical storage unit for one or more sharded tables and indexes.
• DROP TABLESPACE SET and PURGE TABLESPACE SET
  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.
• CREATE TABLE
  The CREATE TABLE statement has been extended to create sharded and duplicated tables, and specify a table family.
• ALTER TABLE
  The ALTER TABLE statement is extended to modify sharded and duplicated tables.
• ALTER SESSION
  The ALTER SESSION statement is extended to support sharded databases.

Local users that only exist in the shard catalog database do not have the privileges to create schema objects in the sharded database. The first step of creating the sharded database schema is to create an all-shards user.

Create an all-shards user by connecting to the shard catalog database as SYSDBA, enabling SHARD DDL, and executing the CREATE USER command. When the all-shards user connects to the shard catalog database, the SHARD DDL mode is enabled by default.

Create a sharded table family with the SQL CREATE TABLE statement. You can specify parent-child relationships between tables using reference partitioning or equi-partitioning.

Use Reference Partitioning to Specify Parent-Child Relationships Between Tables

The recommended way to create a sharded table family is to specify parent-child relationships between tables using reference partitioning.

Partitioning by reference simplifies the syntax since the partitioning scheme is only specified for the root table. Also, partition management operations that are performed on the root table are automatically propagated to its descendents. For example, when adding a partition to the root table, a new partition is created on all its descendents.

The appropriate CREATE TABLE statements for Customers–Orders–LineItems schema using a system-managed sharding methodology are shown below. The first statement creates the root table of the table family, Customers.

CREATE SHARDED TABLE Customers 
( CustNo      NUMBER NOT NULL
, Name        VARCHAR2(50)
, Address     VARCHAR2(250) 
, CONSTRAINT RootPK PRIMARY KEY(CustNo)
)
PARTITION BY CONSISTENT HASH (CustNo)
PARTITIONS AUTO
TABLESPACE SET ts1
;

The following two statements create the Orders and LineItems tables, which are a child and grandchild of the Customers table.

CREATE SHARDED TABLE Orders 
( OrderNo   NUMBER NOT NULL
, CustNo    NUMBER NOT NULL
, OrderDate DATE
, CONSTRAINT OrderPK PRIMARY KEY (CustNo, OrderNo)
, CONSTRAINT CustFK  FOREIGN KEY (CustNo) REFERENCES Customers(CustNo) 
)
PARTITION BY REFERENCE (CustFK)
;

CREATE SHARDED TABLE LineItems 
( CustNo    NUMBER NOT NULL
, LineNo    NUMBER(2) NOT NULL
, OrderNo   NUMBER(5) NOT NULL
, StockNo   NUMBER(4)
, Quantity  NUMBER(2)
, CONSTRAINT LinePK  PRIMARY KEY (CustNo, OrderNo, LineNo)
, CONSTRAINT LineFK  FOREIGN KEY (CustNo, OrderNo) REFERENCES Orders(CustNo, OrderNo)
)
PARTITION BY REFERENCE (LineFK)
;

In the example statements above, corresponding partitions of all tables in the family are stored in the same tablespace set, TS1. However, it is possible to specify separate tablespace sets for each table.

Note that in the example statements above, the partitioning column CustNo used as the sharding key is present in all three tables. This is despite the fact that reference partitioning, in general, allows a child table to be equi-partitioned with the parent table without having to duplicate the key columns in the child table. The reason for this is that reference partitioning requires a primary key in a parent table because the primary key must be specified in the foreign key constraint of a child table used to link the child to its parent. However, a primary key on a sharded table must be the same as, or contain, the sharding key. This makes it possible to enforce global uniqueness of a primary key without coordination with other shards, a critical requirement for linear scalability.

To summarize, the use of reference-partitioned tables in a sharded database requires adhering to the following rules:

• A primary key on a sharded table must either be the same as the sharding key, or contain the sharding key. This is required to enforce global uniqueness of a primary key without coordination with other shards.

• Reference partitioning requires a primary key in a parent table, because the primary key must be specified in the foreign key constraint of a child table to link the child to its parent. It is also possible to have a foreign key constraint when the parent table has just UNIQUE constraint, but no PRIMARY KEY. The sharding key must also be NOT NULL.

  For example, to link the LineItems (child) table to the Orders (parent) table, you need a primary key in the Orders table. The second rule implies that the primary key in the Orders table contains the CustNo value. (This is an existing partitioning rule not specific to Oracle Sharding.)

Use Equi-Partitioning to Specify Parent-Child Relationships Between Tables

In some cases it is impossible or undesirable to create primary and foreign key constraints that are required for reference partitioning. For such cases, specifying parent-child relationships in a table family requires that all tables are explicitly equi-partitioned. Each child table is created with the PARENT clause in CREATE SHARDED TABLE that contains the name of its parent. An example of the syntax is shown below.

 CREATE SHARDED TABLE Customers 
( CustNo      NUMBER NOT NULL
, Name        VARCHAR2(50)
, Address     VARCHAR2(250) 
, region      VARCHAR2(20)
, class       VARCHAR2(3)
, signup      DATE
)
PARTITION BY CONSISTENT HASH (CustNo)
PARTITIONS AUTO
TABLESPACE SET ts1
;

CREATE SHARDED TABLE Orders 
( OrderNo   NUMBER 
, CustNo    NUMBER NOT NULL
, OrderDate DATE
)
PARENT Customers
PARTITION BY CONSISTENT HASH (CustNo)
PARTITIONS AUTO
TABLESPACE SET ts1
;

CREATE SHARDED TABLE LineItems 
( LineNo    NUMBER
, OrderNo   NUMBER
, CustNo    NUMBER NOT NULL
, StockNo   NUMBER
, Quantity  NUMBER
)
PARENT Customers
PARTITION BY CONSISTENT HASH (CustNo)
PARTITIONS AUTO
TABLESPACE SET ts1
;

Because the partitioning scheme is fully specified in all of the CREATE SHARDED TABLE statements, any table can be independently subpartitioned. This is not permitted with reference partitioning where subpartitions can only be specified for the root table and the subpartitioning scheme is the same for all tables in a table family.

Note that this method only supports two-level table families, that is, all children must have the same parent and grandchildren cannot exist. This is not a limitation as long as the partitioning column from the parent table exists in all of the child tables.

A sharded table is a table that is partitioned into smaller and more manageable pieces among multiple databases, called shards.

Tablespace Set Sizing

In the system-managed and composite sharding methods, when you create a tablespace set on the shard catalog, you must make sure you have enough space for all of the tablespaces created on the shard catalog and on each of the shards. This is especially important in a metered usage environment.

For example, with a shard catalog and three shards in the configuration, you issue the following statements.

ALTER SESSION ENABLE SHARD DDL;
CREATE TABLESPACE SET TSP_SET_1 IN SHARDSPACE SHSPC_1 USING TEMPLATE
 (DATAFILE SIZE 100M  AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED);

Assuming a default of 120 chunks per shard, the command creates 360 tablespaces of an initial tables space 100M each on the shard catalog and on each shard. While that doesn't sound like a lot of storage, when the database administrator allots 100G initially, they are not expecting 3.6TB per shard. If that amount of storage is not planned for, this may lead to a failed DDL, and will require significant effort to recover from.

Creating Sharded Tables in a System-Managed Sharded Database

In a system-managed sharded database, data is automatically distributed across the shards using partitioning by consistent hash.

Before creating a sharded table, create a tablespace set with CREATE TABLESPACE SET to store the table partitions.

CREATE TABLESPACE SET ts1;

If you need to customize the tablespace attributes, add the USING TEMPLATE clause to CREATE TABLESPACE SET as shown in this example.

CREATE TABLESPACE SET ts1
USING TEMPLATE
( DATAFILE SIZE 10M
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
  SEGMENT SPACE MANAGEMENT AUTO
  ONLINE
)
;

You create a sharded table with CREATE SHARDED TABLE, horizontally partitioning the table across the shards based on the sharding key cust_id.

CREATE SHARDED TABLE customers 
( cust_id     NUMBER NOT NULL
, name        VARCHAR2(50)
, address     VARCHAR2(250)
, region      VARCHAR2(20)
, class       VARCHAR2(3)
, signup      DATE
CONSTRAINT cust_pk PRIMARY KEY(cust_id)
)
PARTITION BY CONSISTENT HASH (cust_id)
PARTITIONS AUTO
TABLESPACE SET ts1
;

A system-managed sharded table is partitioned by consistent hash, by specifying PARTITION BY CONSISTENT HASH (primary_key_column).

The PARTITIONS AUTO clause specifies that the number of partitions is automatically set to the number of tablespaces in the tablespace set ts1, and each partition is stored in a separate tablespace.

Creating Sharded Tables in a User-Defined Sharded Database

In a user-defined sharded database, you explicitly map data to individual shards. A sharded table in a user-defined sharded database can be partitioned by range or list.

You do not create tablespace sets for user-defined sharded tables; however, you must create each tablespace individually and explicitly associate it with a shardspace deployed in the sharded database configuration, as shown here.

CREATE TABLESPACE tbs1 IN SHARDSPACE west;
CREATE TABLESPACE tbs2 IN SHARDSPACE west;

CREATE TABLESPACE tbs3 IN SHARDSPACE central;
CREATE TABLESPACE tbs4 IN SHARDSPACE central;

CREATE TABLESPACE tbs5 IN SHARDSPACE east;
CREATE TABLESPACE tbs6 IN SHARDSPACE east;

When you create the sharded table, you define the partitions with the ranges or lists of data to be stored in each tablespace, as shown in the following example.

CREATE SHARDED TABLE accounts
( id             NUMBER
, account_number NUMBER
, customer_id    NUMBER
, branch_id      NUMBER
, state          VARCHAR(2) NOT NULL
, status         VARCHAR2(1)
)
PARTITION BY LIST (state)
( PARTITION p_northwest VALUES ('OR', 'WA') TABLESPACE ts1
, PARTITION p_southwest VALUES ('AZ', 'UT', 'NM') TABLESPACE ts2
, PARTITION p_northcentral VALUES ('SD', 'WI') TABLESPACE ts3
, PARTITION p_southcentral VALUES ('OK', 'TX') TABLESPACE ts4
, PARTITION p_northeast VALUES ('NY', 'VM', 'NJ') TABLESPACE ts5
, PARTITION p_southeast VALUES ('FL', 'GA') TABLESPACE ts6
)
;

Creating Sharded Tables in a Composite Sharded Database

The sharded database using the composite sharding method allows you to partition subsets of data that correspond to a range or list of key values in a table partitioned by consistent hash.

With composite sharding, as with the other sharding methods, tablespaces are used to specify the mapping of partitions to shards. To partition subsets of data in a sharded table, a separate tablespace set must be created for each shardspace deployed in the sharded database configuration as shown in the following example.

CREATE TABLESPACE SET tbs1 IN SHARDSPACE shspace1;
CREATE TABLESPACE SET tbs2 IN SHARDSPACE shspace2;

The statement in the following example partitions a sharded table into two partition sets: gold and silver, based on class of service. Each partition set is stored in a separate tablespace. Then data in each partition set is further partitioned by consistent hash on customer ID.

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 tbs1,
 PARTITIONSET silver VALUES (‘slv’) TABLESPACE SET tbs2)
;

The number of database requests handled by a single shard can be maximized by duplicating read-only or read-mostly tables across all shards. This strategy is a good choice for relatively small tables that are not updated frequently, and that are often accessed together with sharded tables.

CREATE DUPLICATED TABLE Products 
( StockNo     NUMBER PRIMARY KEY
, Description VARCHAR2(20)
, Price       NUMBER(6,2))
;

Oracle Sharding synchronizes the contents of duplicated tables using Materialized View Replication.

Create the tablespace set, sharded tables, and duplicated tables for a sharded database that uses the system-managed sharding method.

1. Connect to the shard catalog database, create the application schema user, and grant privileges and roles to the user.

   In this example, the application schema user is called app_schema.

   $ sqlplus / as sysdba
   
   SQL> alter session enable shard ddl;
   SQL> create user app_schema identified by app_schema_password;
   SQL> grant all privileges to app_schema;
   SQL> grant gsmadmin_role to app_schema;
   SQL> grant select_catalog_role to app_schema;
   SQL> grant connect, resource to app_schema;
   SQL> grant dba to app_schema;
   SQL> grant execute on dbms_crypto to app_schema;

2. Create a tablespace set for the sharded tables.

   SQL> CREATE TABLESPACE SET TSP_SET_1 using template
    (datafile size 100m autoextend on next 10M maxsize unlimited
     extent management local segment space management auto);
   

3. If you use LOBs in a column, you can specify a tablespace set for the LOBs.

   SQL> CREATE TABLESPACE SET LOBTS1;

   Note:

   Tablespace sets for LOBS cannot be specified at the subpartitition level in system-managed sharding.

4. Create a tablespace for the duplicated tables.

   In this example the duplicated table is the Products table in the sample Customers-Orders-Products schema.

   SQL> CREATE TABLESPACE products_tsp datafile size 100m
    autoextend on next 10M maxsize unlimited
    extent management local uniform size 1m; 
   

5. Create a sharded table for the root table.

   In this example, the root table is the Customers table in the sample Customers-Orders-Products schema.

   SQL> CONNECT app_schema/app_schema_password
   SQL> CREATE SHARDED TABLE Customers
     (
       CustId      VARCHAR2(60) NOT NULL,
       FirstName   VARCHAR2(60),
       LastName    VARCHAR2(60),
       Class       VARCHAR2(10),
       Geo         VARCHAR2(8),
       CustProfile VARCHAR2(4000),
       Passwd      RAW(60),
       CONSTRAINT pk_customers PRIMARY KEY (CustId),
       CONSTRAINT json_customers CHECK (CustProfile IS JSON)
     ) TABLESPACE SET TSP_SET_1
     PARTITION BY CONSISTENT HASH (CustId) PARTITIONS AUTO;

   Note:

   If any columns contain LOBs, you can include the tablespace set in the parent table creation statement, as shown here.

   SQL> CREATE SHARDED TABLE Customers
     (
       CustId      VARCHAR2(60) NOT NULL,
       FirstName   VARCHAR2(60),
       LastName    VARCHAR2(60),
       Class       VARCHAR2(10),
       Geo         VARCHAR2(8),
       CustProfile VARCHAR2(4000),
       Passwd      RAW(60),
       image       BLOB,
       CONSTRAINT pk_customers PRIMARY KEY (CustId),
       CONSTRAINT json_customers CHECK (CustProfile IS JSON)
     ) TABLESPACE SET TSP_SET_1
       LOB(image) store as (TABLESPACE SET LOBTS1) 
     PARTITION BY CONSISTENT HASH (CustId) PARTITIONS AUTO;

6. Create a sharded table for the other tables in the table family.

   In this example, sharded tables are created for the Orders and LineItems tables in the sample Customers-Orders-Products schema.

   The Orders sharded table is created first:

   SQL> CREATE SHARDED TABLE Orders
     (
       OrderId     INTEGER NOT NULL,
       CustId      VARCHAR2(60) NOT NULL,
       OrderDate   TIMESTAMP NOT NULL,
       SumTotal    NUMBER(19,4),
       Status      CHAR(4),
       CONSTRAINT  pk_orders PRIMARY KEY (CustId, OrderId),
       CONSTRAINT  fk_orders_parent FOREIGN KEY (CustId) 
       REFERENCES Customers ON DELETE CASCADE
     ) PARTITION BY REFERENCE (fk_orders_parent);
   

   Create the sequence used for the OrderId column.

   SQL> CREATE SEQUENCE Orders_Seq;

   Create a sharded table for LineItems

   SQL> CREATE SHARDED TABLE LineItems
     (
       OrderId     INTEGER NOT NULL,
       CustId      VARCHAR2(60) NOT NULL,
       ProductId   INTEGER NOT NULL,
       Price       NUMBER(19,4),
       Qty         NUMBER,
       CONSTRAINT  pk_items PRIMARY KEY (CustId, OrderId, ProductId),
       CONSTRAINT  fk_items_parent FOREIGN KEY (CustId, OrderId)
       REFERENCES Orders ON DELETE CASCADE
     ) PARTITION BY REFERENCE (fk_items_parent);

7. Create any required duplicated tables.

   In this example, the Products table is a duplicated object.

   SQL> CREATE DUPLICATED TABLE Products
     (
       ProductId  INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
       Name       VARCHAR2(128),
       DescrUri   VARCHAR2(128),
       LastPrice  NUMBER(19,4)
     ) TABLESPACE products_tsp;

Create the schema user, tablespace set, sharded tables, and duplicated tables for a sharded database that uses the user-defined sharding method.

1. Connect to the shard catalog database, create the application schema user, and grant privileges and roles to the user.

   In this example, the application schema user is called app_schema.

   $ sqlplus / as sysdba
   
   SQL> alter session enable shard ddl;
   SQL> create user app_schema identified by app_schema_password;
   SQL> grant all privileges to app_schema;
   SQL> grant gsmadmin_role to app_schema;
   SQL> grant select_catalog_role to app_schema;
   SQL> grant connect, resource to app_schema;
   SQL> grant dba to app_schema;
   SQL> grant execute on dbms_crypto to app_schema;

2. Create tablespaces for the sharded tables.

   SQL> CREATE TABLESPACE ck1_tsp DATAFILE SIZE 100M autoextend on next 10M maxsize 
   unlimited extent management local segment space management auto in
    shardspace shspace1;
   
   SQL> CREATE TABLESPACE ck2_tsp DATAFILE SIZE 100M autoextend on next 10M maxsize 
   unlimited extent management local segment space management auto in
    shardspace shspace2;

3. If you use LOBs in any columns, you can specify tablespaces for the LOBs.

   SQL> CREATE TABLESPACE lobts1 ... in shardspace shspace1;
   
   SQL> CREATE TABLESPACE lobts2 ... in shardspace shspace2;

4. Create a tablespace for the duplicated tables.

   In this example the duplicated table is the Products table in the sample Customers-Orders-Products schema.

   SQL> CREATE TABLESPACE products_tsp datafile size 100m autoextend
    on next 10M maxsize unlimited extent management local uniform size 1m; 
   

5. Create a sharded table for the root table.

   In this example, the root table is the Customers table in the sample Customers-Orders-Products schema.

   SQL> CONNECT app_schema/app_schema_password
   
   SQL> ALTER SESSION ENABLE SHARD DDL;
   
   SQL> CREATE SHARDED TABLE Customers
     (
       CustId      VARCHAR2(60) NOT NULL,
       CustProfile VARCHAR2(4000),
       Passwd      RAW(60),
       CONSTRAINT pk_customers PRIMARY KEY (CustId),
       CONSTRAINT json_customers CHECK (CustProfile IS JSON)
     ) PARTITION BY RANGE (CustId)
     ( PARTITION ck1 values less than ('m') tablespace ck1_tsp,
       PARTITION ck2 values less than (MAXVALUE) tablespace ck2_tsp
     );

   Note:

   If any columns in the sharded tables contain LOBs, the CREATE SHARDED TABLE statement can include the LOB tablespaces, as shown here.

   SQL> CREATE SHARDED TABLE Customers
     (
       CustId      VARCHAR2(60) NOT NULL,
       CustProfile VARCHAR2(4000),
       Passwd      RAW(60),
       image       BLOB,
       CONSTRAINT pk_customers PRIMARY KEY (CustId),
       CONSTRAINT json_customers CHECK (CustProfile IS JSON)
     ) PARTITION BY RANGE (CustId)
     ( PARTITION ck1 values less than ('m') tablespace ck1_tsp
        lob(image) store as (tablespace lobts1),
       PARTITION ck2 values less than (MAXVALUE) tablespace ck2_tsp
        lob(image) store as (tablespace lobts2)
     );

6. Create a sharded table for the other tables in the table family.

   In this example, sharded tables are created for the Orders and LineItems tables in the sample Customers-Orders-Products schema.

   The Orders sharded table is created first:

   SQL> CREATE SHARDED TABLE Orders
     (
       OrderId     INTEGER NOT NULL,
       CustId      VARCHAR2(60) NOT NULL,
       OrderDate   TIMESTAMP NOT NULL,
       SumTotal    NUMBER(19,4),
       Status      CHAR(4),
       CONSTRAINT  pk_orders PRIMARY KEY (CustId, OrderId),
       CONSTRAINT  fk_orders_parent FOREIGN KEY (CustId) 
       REFERENCES Customers ON DELETE CASCADE
     ) PARTITION BY REFERENCE (fk_orders_parent);
   

   Create the sequence used for the OrderId column.

   SQL> CREATE SEQUENCE Orders_Seq;

   Create a sharded table for LineItems

   SQL> CREATE SHARDED TABLE LineItems
     (
       OrderId     INTEGER NOT NULL,
       CustId      VARCHAR2(60) NOT NULL,
       ProductId   INTEGER NOT NULL,
       Price       NUMBER(19,4),
       Qty         NUMBER,
       CONSTRAINT  pk_items PRIMARY KEY (CustId, OrderId, ProductId),
       CONSTRAINT  fk_items_parent FOREIGN KEY (CustId, OrderId)
       REFERENCES Orders ON DELETE CASCADE
     ) PARTITION BY REFERENCE (fk_items_parent);

7. Create any required duplicated tables.

   In this example, the Products table is a duplicated object.

   SQL> CREATE DUPLICATED TABLE Products
     (
       ProductId  INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
       Name       VARCHAR2(128),
       DescrUri   VARCHAR2(128),
       LastPrice  NUMBER(19,4)
     ) TABLESPACE products_tsp;

Create the schema user, tablespace set, sharded tables, and duplicated tables for a sharded database that uses the composite sharding method.

1. Connect to the shard catalog host, and set the ORACLE_SID to the shard catalog name.
2. Connect to the shard catalog database, create the application schema user, and grant privileges and roles to the user.

   In this example, the application schema user is called app_schema.

   $ sqlplus / as sysdba
   
   SQL> connect / as sysdba
   SQL> alter session enable shard ddl;
   SQL> create user app_schema identified by app_schema_password;
   SQL> grant connect, resource, alter session to app_schema;
   SQL> grant execute on dbms_crypto to app_schema;
   SQL> grant create table, create procedure, create tablespace,
    create materialized view to app_schema;
   SQL> grant unlimited tablespace to app_schema;
   SQL> grant select_catalog_role to app_schema;
   SQL> grant all privileges to app_schema;
   SQL> grant gsmadmin_role to app_schema;
   SQL> grant dba to app_schema;
   

3. Create tablespace sets for the sharded tables.

   SQL> CREATE TABLESPACE SET  
     TSP_SET_1 in shardspace cust_america using template
     (datafile size 100m autoextend on next 10M maxsize
      unlimited extent management
      local segment space management auto );
   
   SQL> CREATE TABLESPACE SET
     TSP_SET_2 in shardspace cust_europe using template
     (datafile size 100m autoextend on next 10M maxsize
      unlimited extent management
      local segment space management auto );

4. If you use LOBs in any columns, you can specify tablespace sets for the LOBs.

   SQL> CREATE TABLESPACE SET LOBTS1 in shardspace cust_america ... ;
   
   SQL> CREATE TABLESPACE SET LOBTS2 in shardspace cust_europe ... ;

   Note:

   Tablespace sets for LOBs cannot be specified at the subpartitition level in composite sharding.

5. Create a tablespace for the duplicated tables.

   In this example the duplicated table is the Products table in the sample Customers-Orders-Products schema.

   CREATE TABLESPACE products_tsp datafile size 100m autoextend on next 10M
    maxsize unlimited extent management local uniform size 1m;

6. Create a sharded table for the root table.

   In this example, the root table is the Customers table in the sample Customers-Orders-Products schema.

   connect app_schema/app_schema_password
   alter session enable shard ddl;
   
   CREATE SHARDED TABLE Customers
   (
     CustId      VARCHAR2(60) NOT NULL,
     FirstName   VARCHAR2(60),
     LastName    VARCHAR2(60),
     Class       VARCHAR2(10),
     Geo         VARCHAR2(8),
     CustProfile VARCHAR2(4000),
     Passwd      RAW(60),
     CONSTRAINT pk_customers PRIMARY KEY (CustId),
     CONSTRAINT json_customers CHECK (CustProfile IS JSON)
   ) partitionset by list(GEO)
   partition by consistent hash(CustId)
   partitions auto
   (partitionset america values ('AMERICA') tablespace set tsp_set_1,
   partitionset europe values ('EUROPE') tablespace set tsp_set_2
   );
   

   Note:

   If any columns in the sharded tables contain LOBs, the CREATE SHARDED TABLE statement can include the LOB tablespace set, as shown here.

   CREATE SHARDED TABLE Customers
   (
     CustId      VARCHAR2(60)  NOT NULL,
     FirstName   VARCHAR2(60),
     LastName    VARCHAR2(60),
     Class       VARCHAR2(10),
     Geo         VARCHAR2(8)   NOT NULL,
     CustProfile VARCHAR2(4000),
     Passwd      RAW(60),
     image       BLOB,
     CONSTRAINT pk_customers PRIMARY KEY (CustId),
     CONSTRAINT json_customers CHECK (CustProfile IS JSON)
   ) partitionset by list(GEO)
   partition by consistent hash(CustId)
   partitions auto
   (partitionset america values ('AMERICA') tablespace set tsp_set_1
    lob(image) store as (tablespace set lobts1),
   partitionset europe values ('EUROPE') tablespace set tsp_set_2
    lob(image) store as (tablespace set lobts2));
   

7. Create a sharded table for the other tables in the table family.

   In this example, sharded tables are created for the Orders and LineItems tables in the sample Customers-Orders-Products schema.

   Create the sequence used for the OrderId column.

   CREATE SEQUENCE Orders_Seq;
   

   The Orders sharded table is created first:

   CREATE SHARDED TABLE Orders
   (
     OrderId     INTEGER NOT NULL,
     CustId      VARCHAR2(60) NOT NULL,
     OrderDate   TIMESTAMP NOT NULL,
     SumTotal    NUMBER(19,4),
     Status      CHAR(4),
     constraint  pk_orders primary key (CustId, OrderId),
     constraint  fk_orders_parent foreign key (CustId) 
       references Customers on delete cascade
   ) partition by reference (fk_orders_parent);
   

   Create a sharded table for LineItems

   CREATE SHARDED TABLE LineItems
   (
     OrderId     INTEGER NOT NULL,
     CustId      VARCHAR2(60) NOT NULL,
     ProductId   INTEGER NOT NULL,
     Price       NUMBER(19,4),
     Qty         NUMBER,
     constraint  pk_items primary key (CustId, OrderId, ProductId),
     constraint  fk_items_parent foreign key (CustId, OrderId)
       references Orders on delete cascade
   ) partition by reference (fk_items_parent);

8. Create any required duplicated tables.

   In this example, the Products table is a duplicated object.

   CREATE DUPLICATED TABLE Products
   (
     ProductId  INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
     Name       VARCHAR2(128),
     DescrUri   VARCHAR2(128),
     LastPrice  NUMBER(19,4)
   ) tablespace products_tsp;