554
快速導出 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 信息， 未必可以直接調用， 需再次編輯。
最後更新：2017-04-03 15:21:55