set pagesize 0
set long 90000
set feedback off
set echo off
SELECT DBMS_METADATA.GET_DDL('TABLE', U.TABLE_NAME) FROM USER_TABLES U;
SELECT DBMS_METADATA.GET_DDL('INDEX', U.INDEX_NAME) FROM USER_INDEXES U;
SELECT DBMS_METADATA.GET_DDL('VIEW', U.VIEW_NAME) FROM User_Views U;
3、利用user_tables and user_tab_columns導(dǎo)出，優(yōu)點(diǎn)是不涉及到storage，僅僅導(dǎo)出建表的create語句。
SELECT decode(ta.column_id,
tb.mincol_id,
'CREATE TABLE ' || ta.table_name || chr(10) || '(' || chr(10),
'') || rpad(column_name, 40) || data_type ||
decode(data_type,
'NUMBER',
decode(sign(data_precision),
1,
'(' || data_precision || ',' || data_scale || ')',
''),
decode(sign(instr('DATE,LONG,LONG RAW,BLOB,CLOB,FLOAT,UNDEFINED,MLSLABEL,',
data_type || ',')),
1,
'',
'(' || data_length || ')')) ||
decode(ta.column_id, tb.maxcol_id, chr(10) || ');', ',')
FROM user_tab_columns ta,
(SELECT table_name,
MAX(column_id) maxcol_id,
MIN(column_id) mincol_id
FROM user_tab_columns
GROUP BY table_name) tb,
user_tables tc
WHERE ta.table_name = tb.table_name
AND ta.table_name = tc.table_name
ORDER BY ta.table_name, ta.column_id;
