This post was put together using SQLcl 126.96.36.199 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 188.8.131.52 by the way.
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_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
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:
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:
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.
Sometimes it is necessary to perform transformations of the output. Remember that with
DBMS_METADATA you'd use
set_remap_param() calls. SQLcl offers the
set ddl command to achieve the same effect.
If you are unsure about the meaining of these paramters, feel free to head over to the documentation:
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:
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:
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.
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.
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 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.