here are times when database data manipulation requires extracting DDL (Data Definition Language) commands for various objects, such as database tables, indexes, constraints, triggers, etc.

Recently I was charged with the task of writing a set of database packages to perform a high-performance massive DELETE operation. While my high-performance massive DELETE operation will be covered in my next solution, this month’s solution explores the related technique of extracting and storing database objects’ DDL.

The method for extracting and storing database objects’ DDL is as follows:

• Create a table with identical structure but with no primary key, alternate keys(s), and foreign keys(s) constraints.
• For instance, use MyTable_X, where MyTable is the target table for deletion.
• Insert data that needs to be kept into a newly created table (MyTable_X).
• Create indexes on your new table using the NOLOGGING PARALLEL options.
• Create constraints on your new table.
• Swap the MyTable and MyTable_X tables. Rename your main table to MyTable_T and MyTable_X to MyTable.
• Verify the results and drop the MyTable_T table.

Obviously, in order to write this code, you need to extract the database objects’ metadata (definitions and selected attributes) and store it in the memory, so it will be available while executing the above steps.

There are numerous scripts available on the Web that extract database objects’ metadata from the various Oracle dictionaries (user_tables, user_indexes, user_ind_columns, user_constraints, user_cons_columns, etc.), and then construct a DDL command for the object specified. One problem with these scripts is that they’re usually SQL*Plus scripts that produce client-side text file, which is not accessible for database-side code. The main problem is that they are usually:

• Incomplete: not all of the numerous options are being extracted and incorporated into the DLL statement.
• Out-of-date: scripts usually do not support the latest Oracle database features?partitioning, function-based indexes, automatic segment space management (ASSM), etc. These scripts either crash or generate incorrect DDL statements.

Though there are plenty of scripts available extract database objects’ metadata from the various Oracle dictionaries, most of them are either incomplete or out-of-date.

Learn how to perform the above task in the most optimal, error-free, and maintenance-free way using the DBMS_METADATA package.Using Oracle Native APIs: The DBMS_METADATA Package
Oracle databases provide a rich set of prepackaged APIs in the form of Supplied PL/SQL Packages. One of the packages introduced in Oracle 9.2 version is exactly what you need: the DBMS_METADATA package. It contains APIs for retrieving database objects’ definitions. Click here for the reference page for DBMS_METADATA.

The main API you’ll be using is the DBMS_METADATA.GET_DDL function. This function returns an object definition SQL string as a CLOB. It takes the following IN parameters:

• object_type VARCHAR2
• name VARCHAR2
• schema VARCHAR2 DEFAULT NULL
• version VARCHAR2 DEFAULT ‘COMPATIBLE’
• model VARCHAR2 DEFAULT ‘ORACLE’,
• transform VARCHAR2 DEFAULT ‘DDL’

The code below creates a table EmpTest with indexes and constraints that you’ll be using to test the code:

create table EmpTest(   empNo     integer      not null,  lastName  varchar2(30) not null,  firstName varchar2(20) not null,  job       varchar2(9)          '  hireDate  date                 '  isActive  number(1)  constraint EmpTest_CK1  check (isActive in (0,1))      ,  salary    number(9,2)          ,  commision number(9,2)         ,  deptNo    number(2)            ,  constraint EmpTest_PK  primary key (empNo),  constraint EmpTest_AK1  unique (lastName, firstName));create index EmpTest_HireDate_Salaryon EmpTest(   salary,   hireDate);

After running the above script, EmpTest table has been created with three indexes (two unique and one nonunique):

select index_name,       index_type,       uniqueness  from user_indexes where table_name = ‘EMPTEST’;INDEX_NAME	                 INDEX_TYPE	 UNIQUENESSEMPTEST_AK1	                 NORMAL        UNIQUEEMPTEST_HIREDATE_SALARY	 NORMAL        NONUNIQUEEMPTEST_PK                              NORMAL        UNIQUE

EmpTest table also includes six constraints:

• one primary key?EmpTest_PK
• one alternate key?EmpTest_AK
• one check constraint?EmpTest_CK1
• three NOT NULL constraints with system-generated (SYS_*) names:

CONSTRAINT_NAME   CONSTRAINT_TYPE INDEX_NAMESYS_C002144065    C	SYS_C002144066    C	SYS_C002144067    C	EMPTEST_CK1       C	EMPTEST_PK        P                EMPTEST_PKEMPTEST_AK1       U                EMPTEST_AK1

Now, execute an anonymous PL/SQL code block that calls the DBMS_METADATA.GET_DDL function to retrieve EmpTest table definition.

The DBMS_OUTPUT package can only output strings up to 255 characters long, which is a problem because it’s easy to exceed such a limit while working with tables’ DDL strings. To overcome this limitation, the local procedure Show() is used (Listing 1).

Listing 1 produces the following output:

DDL length: 461  CREATE TABLE "BORIS"."EMPTEST"    (	"EMPNO" NUMBER(*,0) NOT NULL ENABLE, 	"LASTNAME" VARCHAR2(30) NOT NULL ENABLE, 	"FIRSTNAME" VARCHAR2(20) NOT NULL ENABLE, 	"JOB" VARCHAR2(9), 	"HIREDATE" DATE, 	"ISACTIVE" NUMBER(1,0), 	"SALARY" NUMBER(9,2), 	"COMMISION" NUMBER(9,2), 	"DEPTNO" NUMBER(2,0), 	 CONSTRAINT "EMPTEST_CK1" CHECK (isActive in (0,1)) ENABLE, 	 CONSTRAINT "EMPTEST_PK" PRIMARY KEY ("EMPNO")  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)  TABLESPACE "TOOLS"  ENABLE,	 CONSTRAINT "EMPTEST_AK1" UNIQUE ("LASTNAME", "FIRSTNAME")  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)  TABLESPACE "TOOLS"  ENABLE   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)  TABLESPACE "TOOLS"

So far so good, though it returned the table’s DDL string with the primary key EmpTest_PK, the alternate key EmpTest_AK1, and checked constraint EmpTest_CK1 altogether. Two unique indexes are created to support the primary and alternate key constraints. This isn’t exactly what you want: you want a table without constraints and indexes to speed up the data load. Only when the data load is completed do you create indexes and constraints. Another reason to keep each object definition separate is flexibility: you may need to change the order of the objects’ creation.

Now it’s time to design a structure for storing all of your objects’ metadata.

The Metadata Memory Repository: MetaDataPkg Package Specification
First, you need to create a record type to store all of the necessary information for individual objects, like tables, indexes, etc:

subtype tString       is varchar2(30);subtype tDBString     is varchar2(255);subtype tDBLongString is varchar2(4000);subtype tLongString   is varchar2(32767);type tArrayLongString is table of tLongStringindex by pls_integer;type tMetaObject is record(   aName         tString,   aType         tString,   aLogging      tString,   aParallel     tString,   aStatus       tString,   aValidated    tString,   aRely         tString,   aDDLString    tLongString);

tMetaObject attributes hold the following information:

• aName: object’s name, i.e. EMPTEST_PK1.
• aType: object’s type, i.e. ‘YES’ (partitioned)/’NO’ (partitioned) (for tables),’UNIQUE’/’NONUNIQUE’ (for indexes), constraint type ‘P’/’U’/’C’/’R’ (for constraints)
• aLogging: object’s logging option, i.e. ‘LOGGING’/ ‘NOLOGGING’ (for tables and indexes).
• aParallel: object’s degree of parallelism (for tables and indexes).
• aStatus: object’s status, i.e. ‘VALID’/’UNUSABLE’ for indexes, ‘Y’ (backed-up)/’N’ (nonbacked-up) for tables.
• aValidated: object’s validate option, i.e. ‘VALIDATED’/’NOT VALIDATED’ (for constraints).
• aRely: object’s rely option, i.e. ‘RELY’/’NORELY’ (for constraints).
• aDDLString: object’s definition SQL string.

Now you need to define the associative array type to be able to instantiate the object of that type so it holds multiple objects of the tMetaObject type, i.e. all EmpTest indexes:

type tArrayMetaObject is table of tMetaObjectindex by pls_integer;

Next, you need to create a record type that consists of one tMetaObject attribute for the table itself (aTable) and three tArrayMetaObject attributes: one for indexes (aIndexes), one for constraints (aConstraints), and one for triggers (aTriggers):

type tFullMetaObject is record(   aTable       tMetaObject,   aIndexes     tArrayMetaObject,   aConstraints tArrayMetaObject,   aTriggers    tArrayMetaObject);

The object of type tFullMetaObject holds all the objects’ metadata for a single table. Finally, the top level type is the associative array of tFullMetaObjecttype tArrayFullMetaObjectByString is table of tFullMetaObjectindex by varchar2(30);

The above type’s object serves as a metadata memory repository and holds the complete set of metadata information for multiple tables.

All of the above types are contained in the MetaDataPkg package specification (Listing 2). I’ve also introduced the following API’s:

• MetaDataPkg.Load() Procedure: Loads metadata information into the memory repository of a specified table.
• MetaDataPkg.GetMeta() Function: This retrieves the tFullMetaObject type’s object from the memory repository.
• MetaDataPkg.SetMeta() Procedure (overloaded): Stores the object’s metadata in the memory repository.
• MetaDataPkg.Reset Procedure (overloaded): Resets the memory repository.
• MetaDataPkg.Show Procedure (overloaded): Displays the content of the memory repository.

Code that Does All of the Work
Some comments about MetaDataPkg package body code (Listing 3). The private procedure SetEnvironment() contains all the environment setup code. The procedure is called from the package initialization section. Therefore, it only executes once per session which is all you need. You want to set it up once at the very beginning. The DBMS_METADATA package provides the API for setting the environment parameters: DBMS_METADATA.SET_TRANSFORM_PARAM() procedure.

The following code prevents the output from formatting with indentation and line feeds:

dbms_metadata.set_transform_param(   dbms_metadata.session_transform, 'PRETTY', false);

The next three lines emit segment attributes (physical attributes, storage attributes, tablespace, logging, etc.), storage and tablespace clauses for tables, and indexes’ object definitions:

dbms_metadata.set_transform_param(   dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', true);dbms_metadata.set_transform_param(   dbms_metadata.session_transform, 'STORAGE', true);dbms_metadata.set_transform_param(   dbms_metadata.session_transform, 'TABLESPACE', true);

It’s important to specify all of the physical, storage, and logging attributes explicitly?otherwise, they’ll be set to defaults that may differ from the originally set values.

The last three lines of the SetEnvironment() procedure prevent all of the non-referential and referential constraints from being included in the table’s DDL. It also suppresses emitting table constraints as separate ALTER TABLE (and, if necessary, CREATE INDEX) statements:

dbms_metadata.set_transform_param(   dbms_metadata.session_transform, 'CONSTRAINTS', false);dbms_metadata.set_transform_param(   dbms_metadata.session_transform, 'REF_CONSTRAINTS', false);dbms_metadata.set_transform_param(   dbms_metadata.session_transform, 'CONSTRAINTS_AS_ALTER', false);

For maximum flexibility, extract object definitions for tables, indexes, and constraints separately and keep them separate. That way you control the order in which they are created.

MetaDataPkg package’s main workhorse is the MetaDataPkg.GetDDL() function. MetaDataPkg.GetDDL() contains an extended version of the code from the Listing 1. What’s been added is the ability to extract DDL strings longer than 32767 characters. This helps to handle partitioned table definitions?which can get rather long when the number of partitions grows. That’s why the GetDDL() code parses and loads the DDL string into the vLongStrings array of strings up to 32767 characters each. The current code version only returns the first array’s element, so you need to modify the code and make the array an attribute of tMetaObject record type. This allows it to handle DDL strings longer than 32767 characters, which is rather rare.

Use the MetaDataPkg.GetMeta() API to get complete metadata objects per specifiedtable. This API accepts two parameters: pTable, which is the table name, and pForce, the Boolean flag. When the pForce flag is set to TRUE, it forces metadata retrieval from Oracle dictionaries and then loads the metadata into the memory repository?whether or not it’s there already. However, the default value is FALSE, so the first call loads metadata into the memory repository and returns the object of tFullMetaObject type. Any subsequent GetMeta() calls simply retrieve the metadata from the repository.

Using the MetaDataPkg Package
To illustrate how to use the MetaDataPkg package, I created a small anonymous block code. It loads the medatada of EmpTest table in to the metadata repository and then prints it’s content.

This is the anonymous PL/SQL block:

declare   vTable        MetaDataPkg.tString := 'EmpTest';   vRunStartTime number;begin   vRunStartTime := dbms_utility.get_time;   MetaDataPkg.Load(vTable, true);   MetaDataPkg.Show();   dbms_output.put_line('Time Elapsed: ' ||    to_char((dbms_utility.get_time - vRunStartTime) / 100) || ' sec.');end;/

Listing 4 shows the output produced by the above code.

As you can see, this code loads complete metadata information for the EmpTest table and its indexes and constraints into the memory repository and retrieves it in less than a second. You now have the API’s to develop an automated solution for any data manipulation that involves renaming, swapping, and dropping database objects. In my next article, I’ll talk about using the MetaDataPkg package for a high-performance, massive DELETE operation.