現行で動いているテーブルからCreate文を作りたいな〜とおもってもあんまりいい方法が無い。
- ダンプファイルでshow=yにしてCreate文を抽出
- 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;
