1、dbms_metadata.get_ddl
set pagesize 0
set long 90000
set feedback off
set echo off
spool get_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;
2、imp.indexfile
1）先导出用户的数据
[oracle@oracle ~]$ exp scott/scott file=test.dmp owner=scott log=test.log;
2)从 dump 文件获取这些 DDL 语句
[oracle@oracle ~]$ imp scott/scott file=test.dmp fromuser=scott touser=scott indexfile=test.sql;
3、impdp.sqlfile
导出用户数据
[oracle@oracle ~]$ expdp scott/scott directory=DATA_PUMP_DIR dumpfile=sqlfile.dmp schemas=scott;
获取 DDL 语句
[oracle@oracle ~]$ impdp scott/scott directory=DATA_PUMP_DIR dumpfile=sqlfile.dmp sqlfile=scott.sql;