获取表的DDL的方法

在获取表的DDL的时候，可以使用多种方法进行获取：

1、利用 DBMS_METADATA.GET_DDL，获取表的 DDL 语句

2、imp.indexfile

3、impdp.sqlfile

1、利用 DBMS_METADATA.GET_DDL，获取表的 DDL 语句

方法1：

SQL PLUS中执行：

首先运行setechooffsetheadingoffsetfeedbackoffsetverifyoffsetpagesize0setlinesize132defineschema=&1输入schema的名字然后执行：defineCR=chr(10)defineTAB=chr(9)colxnoprintcolynoprintSELECTTABLE_NAMEY,0X,'CREATETABLE'||RTRIM(TABLE_NAME)||'('FROMDBA_TABLESWHEREOWNER=UPPER('&schema')UNIONSELECTTC.TABLE_NAMEY,COLUMN_IDX,DECODE(COLUMN_ID,1,'',',')||RTRIM(COLUMN_NAME)||&TAB||&TAB||RTRIM(DATA_TYPE)||RTRIM(DECODE(DATA_TYPE,'DATE',NULL,'LONG',NULL,'NUMBER',DECODE(TO_CHAR(DATA_PRECISION),NULL,NULL,'('),'('))||RTRIM(DECODE(DATA_TYPE,'DATE',NULL,'CHAR',DATA_LENGTH,'VARCHAR2',DATA_LENGTH,'NUMBER',DECODE(TO_CHAR(DATA_PRECISION),NULL,NULL,TO_CHAR(DATA_PRECISION)||','||TO_CHAR(DATA_SCALE)),'LONG',NULL,'******ERROR'))||RTRIM(DECODE(DATA_TYPE,'DATE',NULL,'LONG',NULL,'NUMBER',DECODE(TO_CHAR(DATA_PRECISION),NULL,NULL,')'),')'))||&TAB||&TAB||RTRIM(DECODE(NULLABLE,'N','NOTNULL',NULL))FROMDBA_TAB_COLUMNSTC,DBA_OBJECTSOWHEREO.OWNER=TC.OWNERANDO.OBJECT_NAME=TC.TABLE_NAMEANDO.OBJECT_TYPE='TABLE'ANDO.OWNER=UPPER('&schema')UNIONSELECTTABLE_NAMEY,999999X,')'||&CR||'STORAGE('||&CR||'INITIAL'||INITIAL_EXTENT||&CR||'NEXT'||NEXT_EXTENT||&CR||'MINEXTENTS'||MIN_EXTENTS||&CR||'MAXEXTENTS'||MAX_EXTENTS||&CR||'PCTINCREASE'||PCT_INCREASE||')'||&CR||'INITRANS'||INI_TRANS||&CR||'MAXTRANS'||MAX_TRANS||&CR||'PCTFREE'||PCT_FREE||&CR||'PCTUSED'||PCT_USED||&CR||'PARALLEL(DEGREE'||RTRIM(DEGREE)||')'||&CR||'TABLESPACE'||RTRIM(TABLESPACE_NAME)||&CR||'/'||&CR||&CRFROMDBA_TABLESWHEREOWNER=UPPER('&schema')ORDERBY1,2;

方法2：

setpagesize0setlong90000setfeedbackoffsetechooffspooltable_ddl.sqlselectdbms_metadata.get_ddl('TABLE','tablename','username')fromdual;selectdbms_metadata.get_ddl('VIEW','viewname','username')fromdual;selectdbms_metadata.get_ddl('INDEX','indexname','username')fromdual;spooloff;2、imp.indexfile

1）先导出用户的数据[oracle@oracle11g~]$expdemo/demofile=test.dmpowner=demolog=test.log;2)从dump文件获取这些DDL语句[oracle@oracle11g~]$impdemo/demofile=test.dmpfromuser=demotouser=demoindexfile=test.sql;3、impdp.sqlfile

导出用户数据[oracle@oracle11g~]$expdpdemo/demodirectory=DATA_PUMP_DIRdumpfile=sqlfile.dmpschemas=demo;获取DDL语句[oracle@oracle11g~]$impdpdemo/demodirectory=DATA_PUMP_DIRdumpfile=sqlfile.dmpsqlfile=demo.sql;