在获取表的DDL的时候，可以使用多种方法进行获取：
1、利用 DBMS_METADATA.GET_DDL，获取表的 DDL 语句
2、imp.indexfile
3、impdp.sqlfile
方法1：
SQL PLUS中执行：
首先运行 set echo off set heading off set feedback off set verify off set pagesize 0 set linesize 132 define schema=&1 输入schema的名字 然后执行： define CR=chr(10) define TAB=chr(9) col x noprint col y noprint SELECT TABLE_NAME Y ,0 X ,'CREATE TABLE ' || RTRIM(TABLE_NAME) || '(' FROM DBA_TABLES WHERE OWNER = UPPER('&schema') UNION SELECT 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, 'N', 'NOT NULL', NULL)) FROM DBA_TAB_COLUMNS TC ,DBA_OBJECTS O WHERE O.OWNER = TC.OWNER AND O.OBJECT_NAME = TC.TABLE_NAME AND O.OBJECT_TYPE = 'TABLE' AND O.OWNER = UPPER('&schema') UNION SELECT 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 || ' 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 || &CR FROM DBA_TABLES WHERE OWNER = UPPER('&schema') ORDER BY 1 ,2; 方法2：
set pagesize 0 set long 90000 set feedback off set echo off spool table_ddl.sql select 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; 1）先导出用户的数据 [oracle@oracle11g ~]$ exp demo/demo file=test.dmp owner=demo log=test.log; 2)从 dump 文件获取这些 DDL 语句 [oracle@oracle11g ~]$ imp demo/demo file=test.dmp fromuser=demo touser=demo indexfile=test.sql;
导出用户数据 [oracle@oracle11g ~]$ expdp demo/demo directory=DATA_PUMP_DIR dumpfile=sqlfile.dmp schemas=demo; 获取 DDL 语句 [oracle@oracle11g ~]$ impdp demo/demo directory=DATA_PUMP_DIR dumpfile=sqlfile.dmp sqlfile=demo.sql;
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
