|
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.
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;
...where 'TABLE' is the type of object, 'DEPT' is the name of the object, and '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"
If you wanted to extract the DDL for an index in the
SCOTT schema you would then replace 'TABLE' with 'INDEX' and 'DEPT' with the name of the index.
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"
Now you have a the DDL for all the tables in the SCOTT schema.
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>
Note: You may have noticed that I'm
using SQLPLUS to run this SQL and so it's 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.
~ Troy Campano ~
|
