The Joy of DBMS_METADATA or How To Extract Your DDL From Oracle

Oracle delivered a great tool to us in the form of the DBMS_METADATA package. This package is documented here and allows us to extract the DDL needed to create an object from SQL PLUS quickly and easily.

Recently a colleague and I were performing an Ingrian i426 DataSecure installation on a production database. This involves among other things dropping foreign key indexes on the columns that are going to be encrypted, creating views and INSTEAD OF triggers, and then recreating the foreign key indexes.

Being a production database we of course made sure backups occurred and we were operating late at night in an outage window. However, any restore (due to pilot error) would be politically and technically painful since FlashBack recovery was not enabled.

DBMS_METADATA to the rescue!

With this package we were quickly able to generate DDL for all the indexes and were confident that the DDL was correct.

The Ingrian install went smoothly (it's a great solution by the way) and although exhausted, we completed the install 5 hours later and the client met their PCI compliance deadline.

Here's some examples:

-- Note I am a user called CASH in this example

create table x as select * from all_objects where rownum < 1;

-- WARNING !!! Oracle CREATE TABLE statements are not case sensitive but the call to DBMS_METADATA.GET_DDL below is CASE sensitive. SO if you execute the call looking for a table named 'x' you get an error, whereas 'X' exists. Also -- DESCRIBE is not case sensitive either (see below):

describe x;
Name                           Null     Type                                                                                                                                                                                         
------------------------------ -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OWNER                          NOT NULL VARCHAR2(30)                                                                                                                                                                                 
OBJECT_NAME                    NOT NULL VARCHAR2(30)                                                                                                                                                                                 
SUBOBJECT_NAME                          VARCHAR2(30)                                                                                                                                                                                 
OBJECT_ID                      NOT NULL NUMBER                                                                                                                                                                                       
DATA_OBJECT_ID                          NUMBER                                                                                                                                                                                       
OBJECT_TYPE                             VARCHAR2(19)                                                                                                                                                                                 
CREATED                        NOT NULL DATE                                                                                                                                                                                         
LAST_DDL_TIME                  NOT NULL DATE                                                                                                                                                                                         
TIMESTAMP                               VARCHAR2(19)                                                                                                                                                                                 
STATUS                                  VARCHAR2(7)                                                                                                                                                                                  
TEMPORARY                               VARCHAR2(1)                                                                                                                                                                                  
GENERATED                               VARCHAR2(1)                                                                                                                                                                                  
SECONDARY                               VARCHAR2(1)                                                                                                                                                                                  

13 rows selected
SELECT DBMS_METADATA.GET_DDL('TABLE','X','CASPH')|| ';'  FROM DUAL;
CREATE TABLE "CASH"."X"
(    "OWNER" VARCHAR2(30) NOT NULL ENABLE,
"OBJECT_NAME" VARCHAR2(30) NOT NULL ENABLE,
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER NOT NULL ENABLE,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE NOT NULL ENABLE,
"LAST_DDL_TIME" DATE NOT NULL ENABLE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1)
) 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 "USERS_LARGE"
;