create VIEW temp_VIEW as
(select b.column_name,a.table_name,b.column_id,b.data_type
from user_tables a,user_tab_columns b where a.table_name=b.TABLE_NAME);
------------------------------------------
select * from temp_view order by table_name,column_id;
------------------------------------------
spool create_table.sql;
select tt from
(select 'create table ' tt,table_name,0.1 column_id from user_tables u
union all
select table_name||'( ',table_name,0.2 column_id from user_tables u
union all
select column_name||' '||data_type||',',t.table_name,column_id from user_tables u,temp_view t where u.TABLE_NAME=t.table_name
union all
select ' );',table_name,999 column_id from user_tables u) temp
--where table_name<>'TEMP_TABLE'
order by table_name,column_id;
spool off;
SQL>@create_table.sql
转载于:https://blog.51cto.com/askjoey/1671845