快速導出 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','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;

spool off;

3. 表結構導出到 /tmp/desc.sql

注意，由于從資料字典中獲得表結構， 是以包含很多 storage 或者 segment 資訊， 未必可以直接調用， 需再次編輯。