Tag Archives: SQLcl

Generating Table DDL in Oracle Database

Generating table DDL is a common requirement. Unfortunately it’s not quite common enough for me to remember the syntax by heart, so this post serves as a reference to myself how to do this. Hopefully it saves you a few minutes, too.

I used Oracle SQLDeveloper Command-Line (SQLcl) version: 22.1.1.0 build: 22.1.1.131.0820 for this post, connecting to an Oracle 19c database running on Linux. The DDL command you are reading about later is not new to version 22.1, that one just so happens to be the most current version at the time of writing.

Here are the links for downloading SQLcl:

SQLcl 22.1 comes with a lot more cool stuff, Jeff Smith wrote about the details here.

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 database for quite some time and is documented in the PL/SQL Packages and Types Guide. In cases where SQLcl is unavailable it’s still a more than viable option to combine calls to DBMS_METADATA.set_transform_param() with DBMS_METADATA.get_ddl(). If you are in this position you might find the following sections in the PL/SQL Packages and Types Guide useful:

Tables 107-23 and 107-25 respectively (referenced above) are key to when it comes to understanding the options SQLcl offers. More on that later.

SQLcl is a LOT easier to use than DBMS_METADATA

SQLcl provides a shortcut to using the package: rather than calling DBMS_METADATA.GET_DDL() you can make use of the ddl command instead:

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>]]

Let’s use the command with the ORDERS table Swingbench provides:

SQL> show user
USER is "SOE"
SQL> ddl orders

  CREATE TABLE "SOE"."ORDERS" 
   (	"ORDER_ID" NUMBER(12,0) CONSTRAINT "ORDER_ORDER_ID_NN" NOT NULL ENABLE, 
	"ORDER_DATE" TIMESTAMP (6) WITH LOCAL TIME ZONE CONSTRAINT "ORDER_DATE_NN" NOT NULL ENABLE, 
	"ORDER_MODE" VARCHAR2(8), 
	"CUSTOMER_ID" NUMBER(12,0) CONSTRAINT "ORDER_CUSTOMER_ID_NN" NOT NULL ENABLE, 
	"ORDER_STATUS" NUMBER(2,0), 
	"ORDER_TOTAL" NUMBER(8,2), 
	"SALES_REP_ID" NUMBER(6,0), 
	"PROMOTION_ID" NUMBER(6,0), 
	"WAREHOUSE_ID" NUMBER(6,0), 
	"DELIVERY_TYPE" VARCHAR2(15), 
	"COST_OF_DELIVERY" NUMBER(6,0), 
	"WAIT_TILL_ALL_AVAILABLE" VARCHAR2(15), 
	"DELIVERY_ADDRESS_ID" NUMBER(12,0), 
	"CUSTOMER_CLASS" VARCHAR2(30), 
	"CARD_ID" NUMBER(12,0), 
	"INVOICE_ADDRESS_ID" NUMBER(12,0), 
	 CONSTRAINT "ORDER_MODE_LOV" CHECK (order_mode in ('direct','online')) DEFERRABLE ENABLE NOVALIDATE, 
	 CONSTRAINT "ORDER_TOTAL_MIN" CHECK (order_total >= 0) DEFERRABLE ENABLE NOVALIDATE, 
	 CONSTRAINT "ORDERS_CUSTOMER_ID_FK" FOREIGN KEY ("CUSTOMER_ID")
	  REFERENCES "SOE"."CUSTOMERS" ("CUSTOMER_ID") ON DELETE SET NULL ENABLE NOVALIDATE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 16 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 8388608 NEXT 8388608 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOE_TBS" ;
  CREATE UNIQUE INDEX "SOE"."ORDER_PK" ON "SOE"."ORDERS" ("ORDER_ID") REVERSE 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOE_TBS" ;
ALTER TABLE "SOE"."ORDERS" ADD CONSTRAINT "ORDER_PK" PRIMARY KEY ("ORDER_ID")
  USING INDEX "SOE"."ORDER_PK"  ENABLE NOVALIDATE;

  CREATE INDEX "SOE"."ORD_CUSTOMER_IX" ON "SOE"."ORDERS" ("CUSTOMER_ID") REVERSE 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOE_TBS" ;

  CREATE INDEX "SOE"."ORD_ORDER_DATE_IX" ON "SOE"."ORDERS" ("ORDER_DATE") REVERSE 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOE_TBS" ;

  CREATE INDEX "SOE"."ORD_SALES_REP_IX" ON "SOE"."ORDERS" ("SALES_REP_ID") REVERSE 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOE_TBS" ;

  CREATE INDEX "SOE"."ORD_WAREHOUSE_IX" ON "SOE"."ORDERS" ("WAREHOUSE_ID", "ORDER_STATUS") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOE_TBS" ;

Not only does the ddl command print the table DDL, it also includes the primary key as well as the table’s indexes! If you just want to recreate the table in a different (pluggable) database/same schema, then you’re done here. Optionally store the DDL in a file and put it into version control.

Customising Output

Sometimes however you might want to customise the output. That’s possible with SQLcl, too. The set ddl command can be used to that 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 ]

Table 107-23 in the DBMS_METADATA package documentation describe the meaning of each of these. SQLTERMINATOR for example defines whether a SQL terminator such as the semi-colon or slash should be added after each statement. This defaults to FALSE in DBMS_METADATA, and TRUE in SQLcl. The current settings can be viewed using 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

I don’t need the storage attributes, don’t want SQLcl to emit the schema and neither do I need the segment attributes. Although turning off SEGMENT_ATTRIBUTES disables STORAGE, too, I set this flag explicitly as a note to self. 

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

With the defaults changed to my use case the output is reduced quite a bit:

SQL> ddl orders

  CREATE TABLE "ORDERS" 
   (	"ORDER_ID" NUMBER(12,0) CONSTRAINT "ORDER_ORDER_ID_NN" NOT NULL ENABLE, 
	"ORDER_DATE" TIMESTAMP (6) WITH LOCAL TIME ZONE CONSTRAINT "ORDER_DATE_NN" NOT NULL ENABLE, 
	"ORDER_MODE" VARCHAR2(8), 
	"CUSTOMER_ID" NUMBER(12,0) CONSTRAINT "ORDER_CUSTOMER_ID_NN" NOT NULL ENABLE, 
	"ORDER_STATUS" NUMBER(2,0), 
	"ORDER_TOTAL" NUMBER(8,2), 
	"SALES_REP_ID" NUMBER(6,0), 
	"PROMOTION_ID" NUMBER(6,0), 
	"WAREHOUSE_ID" NUMBER(6,0), 
	"DELIVERY_TYPE" VARCHAR2(15), 
	"COST_OF_DELIVERY" NUMBER(6,0), 
	"WAIT_TILL_ALL_AVAILABLE" VARCHAR2(15), 
	"DELIVERY_ADDRESS_ID" NUMBER(12,0), 
	"CUSTOMER_CLASS" VARCHAR2(30), 
	"CARD_ID" NUMBER(12,0), 
	"INVOICE_ADDRESS_ID" NUMBER(12,0), 
	 CONSTRAINT "ORDER_MODE_LOV" CHECK (order_mode in ('direct','online')) DEFERRABLE ENABLE NOVALIDATE, 
	 CONSTRAINT "ORDER_TOTAL_MIN" CHECK (order_total >= 0) DEFERRABLE ENABLE NOVALIDATE, 
	 CONSTRAINT "ORDERS_CUSTOMER_ID_FK" FOREIGN KEY ("CUSTOMER_ID")
	  REFERENCES "CUSTOMERS" ("CUSTOMER_ID") ON DELETE SET NULL ENABLE NOVALIDATE
   ) ;
  CREATE UNIQUE INDEX "ORDER_PK" ON "ORDERS" ("ORDER_ID") REVERSE 
  ;
ALTER TABLE "ORDERS" ADD CONSTRAINT "ORDER_PK" PRIMARY KEY ("ORDER_ID")
  USING INDEX "ORDER_PK"  ENABLE NOVALIDATE;

  CREATE INDEX "ORD_CUSTOMER_IX" ON "ORDERS" ("CUSTOMER_ID") REVERSE 
  ;

  CREATE INDEX "ORD_ORDER_DATE_IX" ON "ORDERS" ("ORDER_DATE") REVERSE 
  ;

  CREATE INDEX "ORD_SALES_REP_IX" ON "ORDERS" ("SALES_REP_ID") REVERSE 
  ;

  CREATE INDEX "ORD_WAREHOUSE_IX" ON "ORDERS" ("WAREHOUSE_ID", "ORDER_STATUS") 
  ;
SQL>

And this is exactly what I wanted ;) Instead of listing all the specifics in the storage clause and segment management I can run this script on a different system with different tablespaces and other settings. The downside is that I’m now responsible for providing useful defaults.

Summary

SQLcl provides as super convenient shortcut to using DBMS_METADATA. 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.

Advertisement