Need to extract DDL from a table in an Oracle database but don't have a tool to
do so? Don't fret, you can query Oracle's Metadata API to retrieve the DDL or
even XML on the given object. This works with oracle 9i and above.
Extracting the DDL from the 'DEPT' table in the 'SCOTT' schema
First, you need a schema/object to extract DDL from. For this example, I ran
the 'demo' schema creation script located here: <ORACLE_HOME>/rdbms/admin/utlsampl.sql.
This sets up a few example schemas including the SCOTT schema. I want to now
extract DDL for the SCOTT.DEPT table. You do this using the DBMS_METADATA.GET_DDL()
function. The DBMS_METADATA.GET_DDL() function takes in a few parameters: Object
Type, Name, and Schema. Here is how we extract the DDL for the SCOTT.DEPT table:
SQL> set pagesize 0
SQL> set long 90000
SQL> SELECT dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') FROM dual;
'SCOTT' is the schema that the object resides in. The above statement would
then return this:
CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13),
CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
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 "USERS" 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 "USERS"then replace 'TABLE' with 'INDEX' and 'DEPT' with the name of the index.
Extracting DDL for an entire schema
Now that we know how to extract DDL for a individual object, we'll now look
at how to extract DDL for all the tables in the entire SCOTT schema. You can
do this by combining the dbms_metadata.get_ddl() function with one of the Oracle
System Views like ALL_TABLES. Here is an example of extracting all the DDL for
the tables in the SCOTT schema:
SQL> set pagesize 0
SQL> set long 90000
SQL> SELECT dbms_metadata.get_ddl('TABLE', table_name, 'SCOTT')
2 FROM ALL_TABLES WHERE OWNER = 'SCOTT';
CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13),
CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
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 "USERS" 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 "USERS"
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" 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 "USERS" ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") 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 "USERS"
CREATE TABLE "SCOTT"."BONUS"
( "ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"SAL" NUMBER,
"COMM" NUMBER
) 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"
CREATE TABLE "SCOTT"."SALGRADE"
( "GRADE" NUMBER,
"LOSAL" NUMBER,
"HISAL" NUMBER
) 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"Extracting XML Metadata
If you like, you can also extract XML formatted metadata from objects using
the DBMS_METADATA.GET_XML() function. Here's an example how:
SQL> SELECT dbms_metadata.get_xml('TABLE','DEPT','SCOTT') FROM dual;
<?xml version="1.0"?><ROWSET><ROW>
<TABLE_T>
<VERS_MAJOR>1</VERS_MAJOR>
<VERS_MINOR>0 </VERS_MINOR>
<OBJ_NUM>45084</OBJ_NUM>
<SCHEMA_OBJ>
<OBJ_NUM>45084</OBJ_NUM>
<DATAOBJ_NUM>45084</DATAOBJ_NUM>
<OWNER_NUM>49</OWNER_NUM>
<OWNER_NAME>SCOTT</OWNER_NAME>
<NAME>DEPT</NAME>
<NAMESPACE>1</NAMESPACE>
<TYPE_NUM>2</TYPE_NUM>
<TYPE_NAME>TABLE</TYPE_NAME>
<CTIME>2005-06-12:18:21:30</CTIME>
<MTIME>2005-06-12:18:21:30</MTIME>
<STIME>2005-06-12:18:21:30</STIME>
<STATUS>1</STATUS>
<FLAGS>0</FLAGS>
<SPARE1>6</SPARE1>
<SPARE2>1</SPARE2>
</SCHEMA_OBJ>
<STORAGE>
<FILE_NUM>4</FILE_NUM>
<BLOCK_NUM>11</BLOCK_NUM>
<TYPE_NUM>5</TYPE_NUM>
<TS_NUM>4</TS_NUM>
<BLOCKS>8</BLOCKS>
<EXTENTS>1</EXTENTS>
<INIEXTS>8</INIEXTS>
<MINEXTS>1</MINEXTS>
<MAXEXTS>2147483645</MAXEXTS>
<EXTSIZE>128</EXTSIZE>
<EXTPCT>0</EXTPCT>
<USER_NUM>49</USER_NUM>
<LISTS>1</LISTS>
<GROUPS>1</GROUPS>
<BITMAPRANGES>0</BITMAPRANGES>
<CACHEHINT>0</CACHEHINT>
<SCANHINT>0</SCANHINT>
<HWMINCR>45084</HWMINCR>
<FLAGS>131329</FLAGS>
</STORAGE>
<TS_NAME>USERS</TS_NAME>
<BLOCKSIZE>8192</BLOCKSIZE>
<DATAOBJ_NUM>45084</DATAOBJ_NUM>
<COLS>3</COLS>
<PCT_FREE>10</PCT_FREE>
<PCT_USED>40</PCT_USED>
<INITRANS>1</INITRANS>
<MAXTRANS>255</MAXTRANS>
<FLAGS>1073741825</FLAGS>
<AUDIT_VAL>--------------------------------------</AUDIT_VAL>
<INTCOLS>3</INTCOLS>
<KERNELCOLS>3</KERNELCOLS>
<PROPERTY>536870912</PROPERTY>
<XMLSCHEMACOLS>N</XMLSCHEMACOLS>
<TRIGFLAG>0</TRIGFLAG>
<SPARE1>736</SPARE1>
<SPARE6>12-JUN-05</SPARE6>
<COL_LIST>
<COL_LIST_ITEM>
<OBJ_NUM>45084</OBJ_NUM>
<COL_NUM>1</COL_NUM>
<INTCOL_NUM>1</INTCOL_NUM>
<SEGCOL_NUM>1</SEGCOL_NUM>
<PROPERTY>0</PROPERTY>
<NAME>DEPTNO</NAME>
<TYPE_NUM>2</TYPE_NUM>
<LENGTH>22</LENGTH>
<PRECISION_NUM>2</PRECISION_NUM>
<SCALE>0</SCALE>
<NOT_NULL>1</NOT_NULL>
<CHARSETID>0</CHARSETID>
<CHARSETFORM>0</CHARSETFORM>
<SPARE1>0</SPARE1>
<SPARE2>0</SPARE2>
<SPARE3>0</SPARE3>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<OBJ_NUM>45084</OBJ_NUM>
<COL_NUM>2</COL_NUM>
<INTCOL_NUM>2</INTCOL_NUM>
<SEGCOL_NUM>2</SEGCOL_NUM>
<PROPERTY>0</PROPERTY>
<NAME>DNAME</NAME>
<TYPE_NUM>1</TYPE_NUM>
<LENGTH>14</LENGTH>
<NOT_NULL>0</NOT_NULL>
<CHARSETID>178</CHARSETID>
<CHARSETFORM>1</CHARSETFORM>
<SPARE1>0</SPARE1>
<SPARE2>0</SPARE2>
<SPARE3>14</SPARE3>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<OBJ_NUM>45084</OBJ_NUM>
<COL_NUM>3</COL_NUM>
<INTCOL_NUM>3</INTCOL_NUM>
<SEGCOL_NUM>3</SEGCOL_NUM>
<PROPERTY>0</PROPERTY>
<NAME>LOC</NAME>
<TYPE_NUM>1</TYPE_NUM>
<LENGTH>13</LENGTH>
<NOT_NULL>0</NOT_NULL>
<CHARSETID>178</CHARSETID>
<CHARSETFORM>1</CHARSETFORM>
<SPARE1>0</SPARE1>
<SPARE2>0</SPARE2>
<SPARE3>13</SPARE3>
</COL_LIST_ITEM>
</COL_LIST>
<CON0_LIST/>
<CON1_LIST>
<CON1_LIST_ITEM>
<OWNER_NUM>49</OWNER_NUM>
<NAME>PK_DEPT</NAME>
<CON_NUM>4180</CON_NUM>
<OBJ_NUM>45084</OBJ_NUM>
<PROPERTY>536870912</PROPERTY>
<NUMCOLS>1</NUMCOLS>
<CONTYPE>2</CONTYPE>
<ENABLED>45085</ENABLED>
<INTCOLS>1</INTCOLS>
<MTIME>12-JUN-05</MTIME>
<FLAGS>4</FLAGS>
<OID_OR_SETID>0</OID_OR_SETID>
<COL_LIST>
<COL_LIST_ITEM>
<CON_NUM>4180</CON_NUM>
<OBJ_NUM>45084</OBJ_NUM>
<INTCOL_NUM>1</INTCOL_NUM>
<POS_NUM>1</POS_NUM>
<SPARE1>0</SPARE1>
<OID_OR_SETID>0</OID_OR_SETID>
<COL>
<OBJ_NUM>45084</OBJ_NUM>
<COL_NUM>1</COL_NUM>
<INTCOL_NUM>1</INTCOL_NUM>
<SEGCOL_NUM>1</SEGCOL_NUM>
<PROPERTY>0</PROPERTY>
<NAME>DEPTNO</NAME>
<TYPE_NUM>2</TYPE_NUM>
</COL>
</COL_LIST_ITEM>
</COL_LIST>
<IND>
<VERS_MAJOR>1</VERS_MAJOR>
<VERS_MINOR>2 </VERS_MINOR>
<OBJ_NUM>45085</OBJ_NUM>
<SCHEMA_OBJ>
<OBJ_NUM>45085</OBJ_NUM>
<DATAOBJ_NUM>45085</DATAOBJ_NUM>
<OWNER_NUM>49</OWNER_NUM>
<OWNER_NAME>SCOTT</OWNER_NAME>
<NAME>PK_DEPT</NAME>
<NAMESPACE>4</NAMESPACE>
<TYPE_NUM>1</TYPE_NUM>
<TYPE_NAME>INDEX</TYPE_NAME>
<CTIME>2005-06-12:18:21:30</CTIME>
<MTIME>2005-06-12:18:21:30</MTIME>
<STIME>2005-06-12:18:21:30</STIME>
<STATUS>1</STATUS>
<FLAGS>0</FLAGS>
<SPARE1>0</SPARE1>
<SPARE2>65535</SPARE2>
</SCHEMA_OBJ>
<COL_LIST>
<COL_LIST_ITEM>
<OBJ_NUM>45085</OBJ_NUM>
<BO_NUM>45084</BO_NUM>
<INTCOL_NUM>1</INTCOL_NUM>
<COL>
<OBJ_NUM>45084</OBJ_NUM>
<COL_NUM>1</COL_NUM>
<INTCOL_NUM>1</INTCOL_NUM>
<SEGCOL_NUM>1</SEGCOL_NUM>
<PROPERTY>0</PROPERTY>
<NAME>DEPTNO</NAME>
<TYPE_NUM>2</TYPE_NUM>
</COL>
<POS_NUM>1</POS_NUM>
<SEGCOL_NUM>0</SEGCOL_NUM>
<SEGCOLLEN>0</SEGCOLLEN>
<OFFSET>0</OFFSET>
<FLAGS>0</FLAGS>
<SPARE2>0</SPARE2>
</COL_LIST_ITEM>
</COL_LIST>
<TS_NAME>USERS</TS_NAME>
<BLOCKSIZE>8192</BLOCKSIZE>
<STORAGE>
<FILE_NUM>4</FILE_NUM>
<BLOCK_NUM>19</BLOCK_NUM>
<TYPE_NUM>6</TYPE_NUM>
<TS_NUM>4</TS_NUM>
<BLOCKS>8</BLOCKS>
<EXTENTS>1</EXTENTS>
<INIEXTS>8</INIEXTS>
<MINEXTS>1</MINEXTS>
<MAXEXTS>2147483645</MAXEXTS>
<EXTSIZE>128</EXTSIZE>
<EXTPCT>0</EXTPCT>
<USER_NUM>49</USER_NUM>
<LISTS>1</LISTS>
<GROUPS>1</GROUPS>
<BITMAPRANGES>0</BITMAPRANGES>
<CACHEHINT>0</CACHEHINT>
<SCANHINT>0</SCANHINT>
<HWMINCR>45085</HWMINCR>
<FLAGS>131329</FLAGS>
</STORAGE>
<DATAOBJ_NUM>45085</DATAOBJ_NUM>
<BASE_OBJ_NUM>45084</BASE_OBJ_NUM>
<BASE_OBJ>
<OBJ_NUM>45084</OBJ_NUM>
<DATAOBJ_NUM>45084</DATAOBJ_NUM>
<OWNER_NUM>49</OWNER_NUM>
<OWNER_NAME>SCOTT</OWNER_NAME>
<NAME>DEPT</NAME>
<NAMESPACE>1</NAMESPACE>
<TYPE_NUM>2</TYPE_NUM>
<TYPE_NAME>TABLE</TYPE_NAME>
<CTIME>2005-06-12:18:21:30</CTIME>
<MTIME>2005-06-12:18:21:30</MTIME>
<STIME>2005-06-12:18:21:30</STIME>
<STATUS>1</STATUS>
<FLAGS>0</FLAGS>
<SPARE1>6</SPARE1>
<SPARE2>1</SPARE2>
</BASE_OBJ>
<INDMETHOD_NUM>0</INDMETHOD_NUM>
<COLS>1</COLS>
<PCT_FREE>10</PCT_FREE>
<INITRANS>2</INITRANS>
<MAXTRANS>255</MAXTRANS>
<TYPE_NUM>1</TYPE_NUM>
<FLAGS>0</FLAGS>
<PROPERTY>4097</PROPERTY>
<INTCOLS>1</INTCOLS>
<NUMCOLSDEP>1</NUMCOLSDEP>
<SPARE6>12-JUN-05</SPARE6>
<FOR_PKOID>0</FOR_PKOID>
</IND>
</CON1_LIST_ITEM>
</CON1_LIST>
<CON2_LIST/>
</TABLE_T>
</ROW></ROWSET>
important to set the PAGESIZE and LONG variables, otherwise the DDL returned
may be truncated.
And that's all you need to do to extract object DDL from a SQL command prompt.
If you have trouble, please post your comments on this web page.
share on: facebook
Posts
0 comments:
Post a Comment