抓取oracle建表语句的代码实例教程

抓取oracle建表语句的代码实例教程

set echo off;
set feedback off;
set pagesize 0;
set trimspool on;
set linesize 10000;
set heading off;
set term off;
set showmode off;
set verify off;

drop table tmp_tabsql;
 
create table tmp_tabsql (
       seq        number,
       table_name varchar2(50), 
       table_sql varchar2(4000)
);

declare
    v_notparttable varchar2(1000):= '&2';
    --v_sql clob;
    v_parttype varchar2(20);
    v_partcolumn varchar2(50);
    v_subparttype varchar2(50);
    v_subpartsql varchar2(4000);
    v_seq number := 10;
begin
  for v_cur in (
    select table_name,partitioned,t.tablespace_name,t.pct_free, t.logging, t.cache from user_tables t 
    where partitioned = 'yes' 
  ) loop
        v_seq := v_seq + 1;
        insert into tmp_tabsql(seq,table_name,table_sql) values(v_seq, v_cur.table_name, 'create table ' || v_cur.table_name || '(' );
        --columns
        for v_column in (
            select case when regexp_like (column_name,'^\d+$') then '"'||column_name||'"' else column_name end column_name, data_type,data_length, data_precision,data_scale,column_id from user_tab_columns 
            where table_name = v_cur.table_name 
            order by column_id asc
       ) loop
            v_seq := v_seq + 1;
            insert into tmp_tabsql(seq, table_name, table_sql) values(v_seq, v_cur.table_name, 
                    case when v_column.column_id <> 1 then '    ,' end
                      || v_column.column_name || ' ' || v_column.data_type
                         || case v_column.data_type when 'timestamp(6)' then '' when 'date' then ''
                         		 when 'clob' then '' when 'blob' then ''
                             when 'number' then 
                                  case 
                                  		 when v_column.data_scale is not null and v_column.data_precision is not null then '('||v_column.data_precision||','||v_column.data_scale||')'
                                       when v_column.data_precision is not null then '(' || v_column.data_precision|| ')'
                                  end    
                             else 
                                  case when v_column.data_length  is not null then '(' || v_column.data_length || ')' end
                             end
                   );
       end loop;
       
       v_seq := v_seq + 1;
       insert into tmp_tabsql(seq, table_name, table_sql) values(v_seq, v_cur.table_name, ')');
       
       if v_cur.partitioned = 'yes' then
           select t.partitioning_type, t.subpartitioning_type , tk.column_name
           into v_parttype, v_subparttype, v_partcolumn
                   from user_part_tables t, user_part_key_columns tk
                   where t.table_name = tk.name
                   and t.table_name = v_cur.table_name
           ;  
           v_seq := v_seq + 1;
           insert into tmp_tabsql(seq, table_name, table_sql) values(v_seq, v_cur.table_name,
                  ' pctfree 0 nologging partition by ' || v_parttype ||'('||v_partcolumn||')' );
           -- subpartition
           v_subpartsql := '';
           if v_subparttype <> 'none' then
                select t.column_name into v_partcolumn from user_subpart_key_columns t
                where t.name = v_cur.table_name
                ;
                v_subpartsql := 'subpartition by '|| v_subparttype ||'('|| v_partcolumn ||') subpartition template (' ||chr(10);
                for v_tmp in (select t.subpartition_name, t.high_bound, t.subpartition_position 
                    from user_subpartition_templates t 
                    where table_name =  v_cur.table_name
                    order by t.subpartition_position asc
                )  loop
                    v_subpartsql := v_subpartsql || case when v_tmp.subpartition_position > 1 then  chr(10) ||'   ,' end
                                 || 'subpartition '|| v_tmp.subpartition_name || ' values (' || v_tmp.high_bound || ')'
                                 ;
                end loop;  
                v_subpartsql := v_subpartsql || chr(10) || ')' ||chr(10);
           end if;
           
           v_seq := v_seq + 1;
           insert into tmp_tabsql(seq, table_name, table_sql) values(v_seq, v_cur.table_name,
              v_subpartsql || '(partition p2011010100 values less than (to_date(''2011-01-01'',''yyyy-mm-dd'')));' 
           );
       else
           v_seq := v_seq + 1;
           insert into tmp_tabsql(seq,table_name,table_sql) values(v_seq, v_cur.table_name, 
                          ' tablespace ' || v_cur.tablespace_name 
                          || ' pctfree ' || v_cur.pct_free 
                          || case trim(v_cur.logging) when 'no' then ' nologging' end
                          || case trim(v_cur.cache) when 'y' then ' cache' end
                          ||';'
                         )
                     ;
       end if;
       commit;
  end loop;
end;
/

spool &1
select table_sql from (
	select 'set echo off;' table_sql,0 seq from dual
	union all
	select 'set feedback off;', 1 seq from dual
	union all
	select table_sql,seq from tmp_tabsql
	union all
	select 'exit;',9999999999 seq from dual
) order by seq asc;
spool off;
drop table tmp_tabsql purge;
exit;

建存放sql的表

-- create table
create table tmp_tabsql
(
  seq        integer,
  table_name varchar2(100),
  table_sql  varchar2(3000)
)
 

提取sql：select table_sql from tmp_tabsql order by seq