既存テーブルからCreate文を作るPL/SQL

現行で動いているテーブルからCreate文を作りたいな〜とおもってもあんまりいい方法が無い。

  1. ダンプファイルでshow=yにしてCreate文を抽出
  2. OEMで見る

と案はあるのだが1の場合整形された奇麗なSQLになってないし、2の場合1つ1つしか確認できない。
あるスキーマの全テーブルのCreate文が欲しい!!なんて思った場合どちらの方法もいけてない。何よりPCFREEとかPCUSEDとかストレージだの表領域だのまでちゃんとCreate文に出来ない。

なのでパンが無ければケーキを(ry作戦で自分で作った。
要はディクショナリからデータをとってきてCreate文にするだけ。
あるスキーマの全テーブルをドロップクリエイトするSQL文が出来た。

DECLARE

    v_file_handle UTL_FILE.FILE_TYPE;
    target_tbl_name   USER_TABLES.TABLE_NAME%TYPE;
    v_tbl_space       USER_TABLES.TABLESPACE_NAME%TYPE;
    v_ind_space       USER_INDEXES.TABLESPACE_NAME%TYPE;
    v_ind_pct_free    USER_INDEXES.PCT_FREE%TYPE;
    v_ind_initrans    USER_INDEXES.INI_TRANS%TYPE;
    v_tab_pct_free    USER_TABLES.PCT_FREE%TYPE;
    v_tab_initrans    USER_TABLES.INI_TRANS%TYPE;
    v_ind_max_trans   USER_INDEXES.MAX_TRANS%TYPE;
    v_tab_max_trans   USER_TABLES.MAX_TRANS%TYPE;
    v_tab_pct_used    USER_TABLES.PCT_USED%TYPE;
    v_tab_freelists   USER_TABLES.FREELISTS%TYPE;
    v_tab_initial     USER_TABLES.INITIAL_EXTENT%TYPE;
    v_tab_next        USER_TABLES.NEXT_EXTENT%TYPE;
    v_tab_pctincrease USER_TABLES.PCT_INCREASE%TYPE;
    v_ind_initial     USER_INDEXES.INITIAL_EXTENT%TYPE;
    v_ind_next        USER_INDEXES.NEXT_EXTENT%TYPE;
    v_ind_pctincrease USER_INDEXES.PCT_INCREASE%TYPE;
    v_defo    VARCHAR2(1000);
    file_dir  VARCHAR2(256);
    file_name VARCHAR2(256);
    tmp_line  VARCHAR2(1000);
    v_index   PLS_INTEGER;
    C_SPACE_8 VARCHAR2(256);
    C_SPACE_4 VARCHAR2(256);

    CURSOR col_cur
    IS
    SELECT TAB.TABLE_NAME,
           TAB.TABLESPACE_NAME,
           TAB.INI_TRANS,
           TAB.PCT_FREE,
           TAB.MAX_TRANS,
           TAB.PCT_USED,
           TAB.FREELISTS,
           TAB.INITIAL_EXTENT,
           TAB.NEXT_EXTENT,
           TAB.PCT_INCREASE,
           COL.COLUMN_NAME,
           COL.DATA_TYPE,
           COL.DATA_LENGTH,
           COL.DATA_PRECISION,
           COL.DATA_SCALE,
           COL.NULLABLE,
           COL.COLUMN_ID,
           COL.DATA_DEFAULT
      FROM USER_TAB_COLUMNS COL,
           USER_TABLES TAB
     WHERE TAB.TABLE_NAME = COL.TABLE_NAME
       AND TAB.table_name = target_tbl_name
     ORDER BY COL.COLUMN_ID
        ;
    col_rec col_cur%ROWTYPE;

    CURSOR ind_cur
    IS
    SELECT IND.INDEX_NAME,
           IND.TABLESPACE_NAME,
           IND.PCT_FREE,
           IND.INI_TRANS,
           IND.MAX_TRANS,
           IND.INITIAL_EXTENT,
           IND.NEXT_EXTENT,
           IND.PCT_INCREASE,
           COL.COLUMN_NAME,
           COl.COLUMN_POSITION
      FROM USER_INDEXES     IND,
           USER_IND_COLUMNS COL
     WHERE IND.TABLE_NAME = target_tbl_name
       AND SUBSTR(IND.INDEX_NAME, LENGTH(IND.INDEX_NAME) - 1, LENGTH(IND.INDEX_NAME)) = 'PK'
       AND IND.UNIQUENESS = 'UNIQUE'
       AND IND.INDEX_NAME = COL.INDEX_NAME
     ORDER BY COl.COLUMN_POSITION
       ;
    ind_rec ind_cur%ROWTYPE;

    CURSOR all_tab_cur
    IS
    SELECT TABLE_NAME
      FROM USER_TABLES
     WHERE TABLE_NAME <> 'PLAN_TABLE';
    all_tab_rec all_tab_cur%ROWTYPE;

BEGIN

    C_SPACE_8       := '        ';
    C_SPACE_4       := '    ';
    target_tbl_name := '';
    file_dir        := '/home/gara/';
    file_name       := 'AllCreate.sql';
    v_file_handle   := UTL_FILE.FOPEN(file_dir, file_name, 'w');

    OPEN all_tab_cur;
    LOOP
        FETCH all_tab_cur INTO all_tab_rec;
        EXIT WHEN all_tab_cur%NOTFOUND;

        target_tbl_name := all_tab_rec.TABLE_NAME;

        OPEN col_cur;
        LOOP
            FETCH col_cur INTO col_rec;
            EXIT WHEN col_cur%NOTFOUND;

            IF col_cur%ROWCOUNT = 1 THEN

                v_tbl_space       := col_rec.TABLESPACE_NAME;
                v_tab_initrans    := col_rec.INI_TRANS;
                v_tab_pct_free    := col_rec.PCT_FREE;
                v_tab_max_trans   := col_rec.MAX_TRANS;
                v_tab_pct_used    := col_rec.PCT_USED;
                v_tab_freelists   := col_rec.FREELISTS;
                v_tab_initial     := col_rec.INITIAL_EXTENT;
                v_tab_next        := col_rec.NEXT_EXTENT;
                v_tab_pctincrease := col_rec.PCT_INCREASE;

                UTL_FILE.PUT_LINE(v_file_handle, '');

                tmp_line := 'PROMPT ' || col_rec.TABLE_NAME ;
                UTL_FILE.PUT_LINE(v_file_handle, tmp_line);
                tmp_line := null;

                tmp_line := 'DROP TABLE ' || col_rec.TABLE_NAME || ';';
                UTL_FILE.PUT_LINE(v_file_handle, tmp_line);
                tmp_line := null;

                tmp_line := 'CREATE TABLE ' || col_rec.TABLE_NAME || '(';
                UTL_FILE.PUT_LINE(v_file_handle, tmp_line);
                tmp_line := null;
            ELSE
                tmp_line := ',';
            END IF;

            tmp_line := tmp_line || C_SPACE_8 ||col_rec.COLUMN_NAME || chr(9)|| chr(9) || col_rec.DATA_TYPE ;

            IF col_rec.DATA_TYPE = 'NUMBER' THEN
                tmp_line := tmp_line || '('|| col_rec.DATA_PRECISION || ',' || col_rec.DATA_SCALE || ')';
            ELSIF col_rec.DATA_TYPE = 'DATE' THEN
                tmp_line := tmp_line ;
            ELSIF col_rec.DATA_TYPE = 'LONG RAW' THEN
                tmp_line := tmp_line ;
            ELSE
                tmp_line := tmp_line || '('|| col_rec.DATA_LENGTH || ')';
            END IF;

            IF col_rec.NULLABLE = 'N' AND col_rec.DATA_DEFAULT IS NULL THEN
                tmp_line := tmp_line || ' NOT NULL';
            END IF;

            IF col_rec.DATA_DEFAULT IS NOT NULL THEN
                v_defo   := col_rec.DATA_DEFAULT;
                v_defo   := REPLACE(REPLACE(v_defo,CHR(10),''),CHR(13),'');
                tmp_line := tmp_line || ' DEFAULT ' || v_defo;
            END IF;

            UTL_FILE.PUT_LINE(v_file_handle, tmp_line);
            tmp_line := null;
        END LOOP;

        CLOSE col_cur;

        v_index := 0;

        OPEN ind_cur;
        LOOP
            FETCH ind_cur INTO ind_rec;
            EXIT WHEN ind_cur%NOTFOUND;

            v_index := v_index + 1;

            IF ind_cur%ROWCOUNT = 1 THEN

                v_ind_space       := ind_rec.TABLESPACE_NAME;
                v_ind_pct_free    := ind_rec.PCT_FREE;
                v_ind_initrans    := ind_rec.INI_TRANS;
                v_ind_max_trans   := ind_rec.MAX_TRANS;
                v_ind_initial     := ind_rec.INITIAL_EXTENT;
                v_ind_next        := ind_rec.NEXT_EXTENT;
                v_ind_pctincrease := ind_rec.PCT_INCREASE;

                tmp_line := ', CONSTRAINT ' || ind_rec.INDEX_NAME || ' PRIMARY KEY(' || ind_rec.COLUMN_NAME;
                UTL_FILE.PUT_LINE(v_file_handle, tmp_line);
                tmp_line := null;
            ELSE
                tmp_line := C_SPACE_8 || ','  || ind_rec.COLUMN_NAME;
                UTL_FILE.PUT_LINE(v_file_handle, tmp_line);
                tmp_line := null;
            END IF;

        END LOOP;

        CLOSE ind_cur;

        IF v_index = 0 THEN
            NULL;
        ELSE
            tmp_line := C_SPACE_8 || ') USING INDEX';
            UTL_FILE.PUT_LINE(v_file_handle, tmp_line);
            tmp_line := null;

            tmp_line := C_SPACE_8 ||  ' PCTFREE ' || v_ind_pct_free;
            UTL_FILE.PUT_LINE(v_file_handle, tmp_line);
            tmp_line := null;

            tmp_line := C_SPACE_8 || ' INITRANS ' || v_ind_initrans;
            UTL_FILE.PUT_LINE(v_file_handle, tmp_line);
            tmp_line := null;

            tmp_line := C_SPACE_8 || ' TABLESPACE ' || v_ind_space;
            UTL_FILE.PUT_LINE(v_file_handle, tmp_line);
            tmp_line := null;

            tmp_line := C_SPACE_8 || ' MAXTRANS ' || v_ind_max_trans;
            UTL_FILE.PUT_LINE(v_file_handle, tmp_line);
            tmp_line := null;

            tmp_line := C_SPACE_8 || ' STORAGE(INITIAL ' || v_ind_initial ;
            tmp_line := tmp_line  || ' NEXT ' || v_ind_next ;
            tmp_line := tmp_line  || ' PCTINCREASE ' || v_ind_pctincrease || ')';
            UTL_FILE.PUT_LINE(v_file_handle, tmp_line);
            tmp_line := null;
        END IF;

        tmp_line := C_SPACE_4 || ')';
        UTL_FILE.PUT_LINE(v_file_handle, tmp_line);
        tmp_line := null;

        tmp_line := C_SPACE_4 || 'PCTFREE ' || v_tab_pct_free;
        UTL_FILE.PUT_LINE(v_file_handle, tmp_line);
        tmp_line := null;

        tmp_line := C_SPACE_4 || 'PCTUSED ' || v_tab_pct_used;
        UTL_FILE.PUT_LINE(v_file_handle, tmp_line);
        tmp_line := null;

        tmp_line := C_SPACE_4 || 'INITRANS ' || v_tab_initrans;
        UTL_FILE.PUT_LINE(v_file_handle, tmp_line);
        tmp_line := null;

        tmp_line := C_SPACE_4 || 'MAXTRANS ' || v_tab_max_trans;
        UTL_FILE.PUT_LINE(v_file_handle, tmp_line);
        tmp_line := null;

        tmp_line := C_SPACE_4 || 'TABLESPACE ' || v_tbl_space;
        UTL_FILE.PUT_LINE(v_file_handle, tmp_line);
        tmp_line := null;

        tmp_line := C_SPACE_4 || 'STORAGE (';
        UTL_FILE.PUT_LINE(v_file_handle, tmp_line);
        tmp_line := null;

        tmp_line := C_SPACE_8 || 'FREELISTS ' || v_tab_freelists;
        UTL_FILE.PUT_LINE(v_file_handle, tmp_line);
        tmp_line := null;

        tmp_line := C_SPACE_8 || 'INITIAL ' || v_tab_initial;
        UTL_FILE.PUT_LINE(v_file_handle, tmp_line);
        tmp_line := null;

        tmp_line := C_SPACE_8 || 'NEXT ' || v_tab_next;
        UTL_FILE.PUT_LINE(v_file_handle, tmp_line);
        tmp_line := null;

        tmp_line := C_SPACE_8 || 'PCTINCREASE ' || v_tab_pctincrease;
        UTL_FILE.PUT_LINE(v_file_handle, tmp_line);
        tmp_line := null;

        tmp_line := C_SPACE_4 || ');';
        UTL_FILE.PUT_LINE(v_file_handle, tmp_line);
        tmp_line := null;

    END LOOP;
    CLOSE all_tab_cur;
    UTL_FILE.FCLOSE(v_file_handle);
END;
広告を非表示にする