抓取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