oracle批量建立某用户下的所有表结构

如果无法使用pl/sql developer中，工具->导出用户对象功能 

 

declare 
   cursor cemp is select table_name from all_tables where owner = 'USERNAME';
   tbn all_tables.table_name%type;
   col_num int ;
begin
  open cemp;
  loop
       fetch cemp into tbn;
       exit when cemp%notfound;
   select count(1) into col_num from all_tab_columns T1 where owner = 'USERNAME' and Table_Name= tbn ;
  insert into temp_USERNAME_tables
       select case 
         when rn=1 and 1 = col_num then
          'CREATE TABLE ' ||tbn || ' ( ' || COLUMN_NAME || '  '|| DATA_TYPE ||  ' );'
         when rn=1 and 1 <> col_num then
          'CREATE TABLE ' ||tbn || ' ( ' || COLUMN_NAME || '  '|| DATA_TYPE || ','
         when rn=col_num and 1 <> col_num  then
          COLUMN_NAME || '  ' || DATA_TYPE || ' );'
         else
          COLUMN_NAME || '  ' || DATA_TYPE || ','
        end create_table
       from (select rownum rn,
               T1.COLUMN_NAME,
               T1.DATA_TYPE || '(' || T1.DATA_LENGTH || ') ' as DATA_TYPE
          from all_tab_columns T1
         where owner = 'USERNAME'
           and Table_Name = tbn ) r1;

  end loop;
  close cemp;
end;