Schema Creation Examples

The following examples show the steps you would take to create a schema for an Oracle Globally Distributed Database using the system-managed, user-defined, and composite sharding methods.

• Schema for System-Managed Data Distribution

• Schema for User-Defined Data Distribution

• Schema for Composite Data Distribution

Schema for System-Managed Data Distribution

Create the tablespace set, sharded tables, and duplicated tables for an Oracle Globally Distributed Database that uses the system-managed data distribution 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 data distribution.

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;

Next you should monitor the DDL processing and verify that the tablespace sets, tables, and chunks were correctly created on all of the shards.

Schema for User-Defined Data Distribution

Create the schema user, tablespace set, sharded tables, and duplicated tables for an Oracle Globally Distributed Database that uses the user-defined data distribution 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;

Next you should monitor the DDL processing and verify that the tablespace sets, tables, and chunks were correctly created on all of the shards.

Schema for Composite Data Distribution

Create the schema user, tablespace set, sharded tables, and duplicated tables for an Oracle Globally Distributed Database that uses the composite data distribution 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 data distribution.

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;

Next you should monitor the DDL processing and verify that the tablespace sets, tables, and chunks were correctly created on all of the shards.