The method of getting DDL statement of TABLE in Oracle

The method of getting the DDL of a table

When getting the DDL of a table, you can get it in a number of ways:

1. Use DBMS_METADATA.GET_DDL to get the DDL statement of the table

2 、 imp.indexfile

3 、 impdp.sqlfile

1. Use DBMS_METADATA.GET_DDL to get the DDL statement of the table

Method 1:

Execute in SQL PLUS:

First run set echo offset heading offset feedback offset verify offset pagesize 0set linesize 132define schema=&1 to enter the name of schema and then execute: define CR=chr (10) define TAB=chr (9) col x noprintcol y noprintSELECT TABLE_NAME Y, 0X, 'CREATE TABLE' | | RTRIM (TABLE_NAME) |'('FROM DBA_TABLESWHERE OWNER = UPPER (' & schema') UNIONSELECT TC.TABLE_NAME Y, COLUMN_ID X, 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, 'naming,' NOT NULL', NULL)) FROM DBA_TAB_COLUMNS TC DBA_OBJECTS OWHERE O.OWNER = TC.OWNER AND O.OBJECT_NAME = TC.TABLE_NAME AND O.OBJECT_TYPE = 'TABLE' AND O.OWNER = UPPER (' & schema') UNIONSELECT TABLE_NAME Y, 999999 X ')' | & CR | | 'STORAGE (' | | & CR | 'INITIAL' | | INITIAL_EXTENT | | & CR | | 'NEXT' | | NEXT_EXTENT | | & CR | 'MINEXTENTS' | MIN_EXTENTS | | & CR | | 'MAXEXTENTS' | MAX_EXTENTS | & CR | | 'PCTINCREASE' | PCT_INCREASE | |')'| | & CR | | 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 | & CRFROM DBA_TABLESWHERE OWNER = UPPER ('& schema') ORDER BY 1 2

Method 2:

Set pagesize 0set long 90000set feedback offset echo offspool table_ddl.sqlselect dbms_metadata.get_ddl ('TABLE','tablename','username') from dual;select dbms_metadata.get_ddl (' VIEW','viewname','username') from dual;select dbms_metadata.get_ddl ('INDEX','indexname','username') from dual;spool off;2, imp.indexfile1) first export user's data [oracle@oracle11g ~] $exp demo/demo file=test.dmp owner=demo log=test.log 2) get these DDL statements [oracle@oracle11g ~] $imp demo/demo file=test.dmp fromuser=demo touser=demo indexfile=test.sql;3 from dump files, and impdp.sqlfile export user data [oracle@oracle11g ~] $expdp demo/demo directory=DATA_PUMP_DIR dumpfile=sqlfile.dmp schemas=demo; to get DDL statements [oracle@oracle11g ~] $impdp demo/demo directory=DATA_PUMP_DIR dumpfile=sqlfile.dmp sqlfile=demo.sql