快速导出 ORACLE 表结构

1. 利用下面命令自动生成 dbms_metadata.get_dll  表分析脚本

如:

select 'select dbms_metadata.get_ddl(' || chr(39)|| 'TABLE' || chr(39) || ',' || chr(39) || table_name || chr(39) ||  ') from dual;'from user_tables;

'SELECTDBMS_METADATA.GET_DDL('||CHR(39)||'TABLE'||CHR(39)||','||CHR(39)||TABLE_N
--------------------------------------------------------------------------------
select dbms_metadata.get_ddl('TABLE','TBSCCARD') from dual;
select dbms_metadata.get_ddl('TABLE','SMS_SENDUSER') from dual;
select dbms_metadata.get_ddl('TABLE','T_TRADELOG_2012') from dual;
select dbms_metadata.get_ddl('TABLE','T_TRADELOG_2013') from dual;
select dbms_metadata.get_ddl('TABLE','TBSCCARD_2012') from dual;
select dbms_metadata.get_ddl('TABLE','TBSCCARD_2013') from dual;
select dbms_metadata.get_ddl('TABLE','TBSCCARDNO') from dual;
select dbms_metadata.get_ddl('TABLE','TEMP_20121111') from dual;
select dbms_metadata.get_ddl('TABLE','tbsccard_mysql') from dual;
select dbms_metadata.get_ddl('TABLE','TBSCCARD_2012_MYSQL') from dual;
select dbms_metadata.get_ddl('TABLE','TBSCCARD_MYSQL') from dual;
select dbms_metadata.get_ddl('TABLE','DISTRICTPREFIX_2013_09_03') from dual;
select dbms_metadata.get_ddl('TABLE','DISTRICTPREFIX_MYSQL') from dual;
select dbms_metadata.get_ddl('TABLE','DISTRICTPREFIX') from dual;
select dbms_metadata.get_ddl('TABLE','TEMP_TRADEID') from dual;
select dbms_metadata.get_ddl('TABLE','T_TRADELOG') from dual;

 

2.创建导出结构语法

set head off;
set headsep off;
set newp none;
set linesize 100;
set pagesize 10000;
set sqlblanklines OFF;
set trimspool ON;
set echo off;
set feedback off;
set termout off;
set long 999999;
spool /tmp/desc.sql;
select dbms_metadata.get_ddl('TABLE','TBSCCARD') from dual;
select dbms_metadata.get_ddl('TABLE','SMS_SENDUSER') from dual;
select dbms_metadata.get_ddl('TABLE','T_TRADELOG_2012') from dual;
select dbms_metadata.get_ddl('TABLE','T_TRADELOG_2013') from dual;
select dbms_metadata.get_ddl('TABLE','TBSCCARD_2012') from dual;
select dbms_metadata.get_ddl('TABLE','TBSCCARD_2013') from dual;
select dbms_metadata.get_ddl('TABLE','TBSCCARDNO') from dual;
select dbms_metadata.get_ddl('TABLE','TEMP_20121111') from dual;
select dbms_metadata.get_ddl('TABLE','tbsccard_mysql') from dual;
select dbms_metadata.get_ddl('TABLE','TBSCCARD_2012_MYSQL') from dual;
select dbms_metadata.get_ddl('TABLE','TBSCCARD_MYSQL') from dual;
select dbms_metadata.get_ddl('TABLE','DISTRICTPREFIX_2013_09_03') from dual;
select dbms_metadata.get_ddl('TABLE','DISTRICTPREFIX_MYSQL') from dual;
select dbms_metadata.get_ddl('TABLE','DISTRICTPREFIX') from dual;
select dbms_metadata.get_ddl('TABLE','TEMP_TRADEID') from dual;
select dbms_metadata.get_ddl('TABLE','T_TRADELOG') from dual;
spool off;

 

3. 表结构导出到 /tmp/desc.sql

注意，由于从数据字典中获得表结构， 因此包含很多 storage 或者 segment 信息， 未必可以直接调用， 需再次编辑。