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