Generating Table DDL in Oracle Database

May 23, 2022 | 6 minute read
Text Size 100%:

This post was put together using SQLcl 22.1.1.0 connecting to a 19c Autonomous Transaction Processing/shared (ATP) database. Please note that SQLcl 22.1 switched from Java 8 to 11 (or 17 respectively), which might require an update of your Java Development Kit/Java Runtime Environment. There are many interesting new features in SQLcl 22.1: this blog post by Jeff Smith is a great introduction. Creating metadata has been a longstanding feature in SQLcl, it's not related to version 22.1.1.0 by the way.

DBMS_METADATA

Table DDL (and a lot of other DDL for that matter) can be generated using DBMS_METADATA. This package has been part of the Oracle database for a long time now and it is documented in the PL/SQL Packages and Types Guide. In cases where SQLcl is unavailable or automation via SQL scripts is required it's still more than a viable option for generating DDL. DBMS_METADATA provides a rich API; GET_DDL() and GET_DEPENDENT_DDL() might be the most commonly used functions for generating DDL scripts. In addition to generating the DDL for a schema object as it is, a rich transformation API allows you to customise output. Output transformation is possible by calling set_transform_param() whereas remapping of tablespaces, users etc is done via set_remap_param().

SQLcl is more convenient than calling DBMS_METADATA directly

Although DBMS_METADTA is very useful, its use requires a bit of boilerplate code. SQLcl provides a shortcut to using the DBMS_METADATA package: rather than invoking the PL/SQL API you can make use of the ddl command instead which is a lot less typing:

SQL> help ddl
DDL
---

DDL generates the code to reconstruct the object listed.  Use the type option
for materialized views. Use the save options to save the DDL to a file.

DDL [<object_name> [<type>] [SAVE <filename>]]

Without changing any defaults, the command prints a schema object's DDL. Here is an example of the SH.SALES table, part of the database sample dataset on my ATP instance:

SQL> ddl sh.sales

  CREATE TABLE "SH"."SALES"
   (    "PROD_ID" NUMBER NOT NULL ENABLE,
        "CUST_ID" NUMBER NOT NULL ENABLE,
        "TIME_ID" DATE NOT NULL ENABLE,
        "CHANNEL_ID" NUMBER NOT NULL ENABLE,
        "PROMO_ID" NUMBER NOT NULL ENABLE,
        "QUANTITY_SOLD" NUMBER(10,2) NOT NULL ENABLE,
        "AMOUNT_SOLD" NUMBER(10,2) NOT NULL ENABLE,
         CONSTRAINT "SALES_PROMO_FK" FOREIGN KEY ("PROMO_ID")
          REFERENCES "SH"."PROMOTIONS" ("PROMO_ID") RELY DISABLE,
         CONSTRAINT "SALES_PRODUCT_FK" FOREIGN KEY ("PROD_ID")
          REFERENCES "SH"."PRODUCTS" ("PROD_ID") RELY DISABLE,
         CONSTRAINT "SALES_CUSTOMER_FK" FOREIGN KEY ("CUST_ID")
          REFERENCES "SH"."CUSTOMERS" ("CUST_ID") RELY DISABLE,
         CONSTRAINT "SALES_CHANNEL_FK" FOREIGN KEY ("CHANNEL_ID")
          REFERENCES "SH"."CHANNELS" ("CHANNEL_ID") RELY DISABLE,
         CONSTRAINT "SALES_TIME_FK" FOREIGN KEY ("TIME_ID")
          REFERENCES "SH"."TIMES" ("TIME_ID") RELY DISABLE
   )  DEFAULT COLLATION "USING_NLS_COMP" SEGMENT CREATION IMMEDIATE
  PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255
 COLUMN STORE COMPRESS FOR QUERY HIGH NO ROW LEVEL LOCKING LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SAMPLESCHEMA" ;

   COMMENT ON COLUMN "SH"."SALES"."PROD_ID" IS 'FK to the products dimension table';
   COMMENT ON COLUMN "SH"."SALES"."CUST_ID" IS 'FK to the customers dimension table';
   COMMENT ON COLUMN "SH"."SALES"."TIME_ID" IS 'FK to the times dimension table';
   COMMENT ON COLUMN "SH"."SALES"."CHANNEL_ID" IS 'FK to the channels dimension table';
   COMMENT ON COLUMN "SH"."SALES"."PROMO_ID" IS 'promotion identifier, without FK constraint (intentionally) to show outer join optimization';
   COMMENT ON COLUMN "SH"."SALES"."QUANTITY_SOLD" IS 'product quantity sold with the transaction';
   COMMENT ON COLUMN "SH"."SALES"."AMOUNT_SOLD" IS 'invoiced amount to the customer';
   COMMENT ON TABLE "SH"."SALES"  IS 'facts table, without a primary key; all rows are uniquely identified by the combination of all foreign keys';

As you can see from the output above, the DDL command provides the table definition including constraints and comments.  Note that the COLUMN STORE clause instructs the engine to store the table using Exadata's Hybrid Columnar Compression (HCC) format. I would like to point out that this table isn't a production table: its purpose is to provide a little bit of sample data for users new to Autonomous Database as you can see in the comments section.

Indexed tables (including primary keys) would have seen the indexes' DDL printed after the table.

Customising Output

Sometimes it is necessary to perform transformations of the output. Remember that with DBMS_METADATA you'd use set_transform_param() and set_remap_param() calls. SQLcl offers the set ddl command to achieve the same effect.

SQL> help set ddl
SET DDL
  SET DDL [[ PRETTY | SQLTERMINATOR | CONSTRAINTS | REF_CONSTRAINTS |
          CONSTRAINTS_AS_ALTER|OID | SIZE_BYTE_KEYWORD | PARTITIONING |
          SEGMENT_ATTRIBUTES | STORAGE | TABLESPACE | SPECIFICATION |
          BODY | FORCE | INSERT | |INHERIT | RESET] {on|off}
         ] | ON | OFF ]

If you are unsure about the meaining of these paramters, feel free to head over to the documentation:

  • table 107-23 in the Packages and Types Guide lists all available transformation parameters
  • table 107-25 lists all the remap parameters

The SQLTERMINATOR property for example defines whether a SQL terminator such as a ";" or a "/" should be added after each statement. The setting defaults to FALSE with DBMS_METADATA, the default in SQLcl is TRUE. The current settings are available via the show ddl command:

SQL> show ddl
STORAGE : ON
INHERIT : ON
EMIT_SCHEMA : ON
SQLTERMINATOR : ON
OID : ON
SPECIFICATION : ON
TABLESPACE : ON
SIZE_BYTE_KEYWORD : ON
PRETTY : ON
REF_CONSTRAINTS : ON
FORCE : ON
PARTITIONING : ON
CONSTRAINTS : ON
INSERT : ON
BODY : ON
CONSTRAINTS_AS_ALTER : ON
SEGMENT_ATTRIBUTES : ON

For the sake of this demonstration let's assume the table DDL needs to be run against a non-Exadata Oracle database, and the developer is conscious that removing HCC compression will result in a potentially significant increase in storage requirements for the table. Copying and pasting the above DDL command might fail in this case since non-Exadata systems typically don't have HCC compression available to them. Furthermore, the schema prefix ("SH") is not required as this table should be created under a different account.

With that in mind, the following settings could be implemented:

SQL> set ddl storage off
DDL Option STORAGE off
SQL> set ddl emit_schema off
DDL Option EMIT_SCHEMA off
SQL> set ddl segment_attributes off
DDL Option SEGMENT_ATTRIBUTES off

Although turning off segment attributes should also disable the output of storage metadata, the setting has been made deliberately as a note-to-self. With the new settings in place the output is reduced to only contain the essential information requested. Anything not explicitly specified will use the executing user's defaults such as tablespace names and other storage attributes.

SQL> ddl sh.sales

  CREATE TABLE "SALES"
   (    "PROD_ID" NUMBER NOT NULL ENABLE,
        "CUST_ID" NUMBER NOT NULL ENABLE,
        "TIME_ID" DATE NOT NULL ENABLE,
        "CHANNEL_ID" NUMBER NOT NULL ENABLE,
        "PROMO_ID" NUMBER NOT NULL ENABLE,
        "QUANTITY_SOLD" NUMBER(10,2) NOT NULL ENABLE,
        "AMOUNT_SOLD" NUMBER(10,2) NOT NULL ENABLE,
         CONSTRAINT "SALES_PROMO_FK" FOREIGN KEY ("PROMO_ID")
          REFERENCES "PROMOTIONS" ("PROMO_ID") RELY DISABLE,
         CONSTRAINT "SALES_PRODUCT_FK" FOREIGN KEY ("PROD_ID")
          REFERENCES "PRODUCTS" ("PROD_ID") RELY DISABLE,
         CONSTRAINT "SALES_CUSTOMER_FK" FOREIGN KEY ("CUST_ID")
          REFERENCES "CUSTOMERS" ("CUST_ID") RELY DISABLE,
         CONSTRAINT "SALES_CHANNEL_FK" FOREIGN KEY ("CHANNEL_ID")
          REFERENCES "CHANNELS" ("CHANNEL_ID") RELY DISABLE,
         CONSTRAINT "SALES_TIME_FK" FOREIGN KEY ("TIME_ID")
          REFERENCES "TIMES" ("TIME_ID") RELY DISABLE
   )  DEFAULT COLLATION "USING_NLS_COMP" ;

   COMMENT ON COLUMN "SALES"."PROD_ID" IS 'FK to the products dimension table';
   COMMENT ON COLUMN "SALES"."CUST_ID" IS 'FK to the customers dimension table';
   COMMENT ON COLUMN "SALES"."TIME_ID" IS 'FK to the times dimension table';
   COMMENT ON COLUMN "SALES"."CHANNEL_ID" IS 'FK to the channels dimension table';
   COMMENT ON COLUMN "SALES"."PROMO_ID" IS 'promotion identifier, without FK constraint (intentionally) to show outer join optimization';
   COMMENT ON COLUMN "SALES"."QUANTITY_SOLD" IS 'product quantity sold with the transaction';
   COMMENT ON COLUMN "SALES"."AMOUNT_SOLD" IS 'invoiced amount to the customer';
   COMMENT ON TABLE "SALES"  IS 'facts table, without a primary key; all rows are uniquely identified by the combination of all foreign keys';

The output has been reduced to those properties not excluded via the settings implemented using set ddl. The HCC clause has been removed, too, allowing this script to be executed on any Oracle database at the expense of storage savings.

Summary

SQLcl provides as very convenient shortcut to using DBMS_METADATA when it comes to generating DDL scripts. There are many use cases for this function, from creating DDL for use with version control systems to data migrations the possibilities are almost endless.

Martin Bach

Martin is a product manager at Oracle helping customers in Europe and around the world address their IT related problems. He is most interested in cloud technology, DevOps and how these can be used best with Oracle technology.


Previous Post

Success With Containers in a Multi-Architecture World

Tim Clegg | 6 min read

Next Post


Ubuntu 22.04 now available on OCI

Erin Dawson | 2 min read