一点一滴努力,满仓满谷收成

1 问题
当批量生成建表或建索引ddl的时侯,dbartisan工具还是力不从心，特别是量级上万，而oracle的dbms_metadata.get_ddl输出的结果又不够美观。所以想想办法美化输出。本文的例是做的是索引，其它请自行调整脚本。

2 使用pl/sql批量生成建索引的ddl
1) metadata_regis表结构

SQL> desc metadata_regis
Name Null? Type
———————– ——– —————–
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(100)
MD CLOB

2) 生成创建索引的pl/sql

–指定生成语句带分号(；)结尾
execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,’SQLTERMINATOR’,true);
–去掉存储选项
execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,’STORAGE’,false);

set linesize 200
set pagesize 0
set long 1000000

DECLARE
IV_SQL CHAR(1000);
V_N NUMBER;
CURSOR C1 IS
SELECT OWNER, INDEX_NAME
FROM DBA_INDEXES
WHERE OWNER = ‘REGIS’
AND TABLE_NAME IN (SELECT TNAME FROM REGIS_TABLES)
AND INDEX_TYPE <> ‘LOB’
ORDER BY TABLE_NAME;
L_C1 C1%ROWTYPE;
DOC CLOB;
BEGIN
EXECUTE IMMEDIATE ‘truncate table qiuyb.metadata_regis’;
OPEN C1;
LOOP
FETCH C1
INTO L_C1;
EXIT WHEN C1%NOTFOUND;

DOC := DBMS_METADATA.GET_DDL(‘INDEX’, L_C1.INDEX_NAME, L_C1.OWNER);

INSERT INTO METADATA_REGIS (owner,OBJECT_NAME,MD) VALUES (l_c1.owner,l_c1.index_name,DOC);
— dbms_output.put_line(iv_sql);
— EXECUTE IMMEDIATE IV_SQL;
COMMIT;
END LOOP;
CLOSE C1;
END;

3 把表里的数据spool 到文本文件

set linesize 200
set pagesize 0
spool gg.txt
select md from metadata_regis;
spool off

gg.txt内容类似如下：

SQL> select md from metadata_regis;

CREATE INDEX “REGIS”.”IDX_ADDRESS_CREATED_DT_1301″ ON “REGIS”.”ADDRESS_1301″ (“CREATED_DT”)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE “TBS_DATACENTER” ;

CREATE INDEX “REGIS”.”IDX_ADDRESS_SPELL_1301″ ON “REGIS”.”ADDRESS_1301″ (“SIMPLE_SPELL”)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE “TBS_DATACENTER” ;

CREATE INDEX “REGIS”.”IDX_ADDRESS_NAME_1301″ ON “REGIS”.”ADDRESS_1301″ (“NAME”)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE “TBS_DATACENTER” ;

把第一行去掉

4 用python格式化gg.txt,脚本名：fmtsqlfile.py
1)python脚本如下：

#!/usr/bin/python
# coding=utf-8
import sys
import re

def fmtsqlfile(s,t):
file_s = open(s,”r”)
file_t = open(t,”w”)

while True:
line = file_s.readline()
if not line:
break

if (line[:2]==’ ‘ or line==’\n’):
file_t.write(‘\n’)

line = line.strip()
if (re.search(‘COMPUTE STATISTICS’,line)):
file_t.write(‘online parallel 8’)
else:
file_t.write(line)

file_t.write(‘\n’)
file_s.close()
file_t.close()

if __name__ == ‘__main__’:
if len(sys.argv) == 3:
fmtsqlfile(sys.argv[1],sys.argv[2])
else:
print(‘Command format:fmtsqlfile.py source_filename target_filename’)

2)格式

./fmtsqlfile.py gg.txt cr_regis_dsg_index.sql

python版本2.6.6

5 结果
生成的结果已经很美观了，乱换行的去除了

CREATE INDEX “REGIS”.”IDX_ADDRESS_CREATED_DT_1301″ ON “REGIS”.”ADDRESS_1301″ (“CREATED_DT”)
online parallel 8
TABLESPACE “TBS_DATACENTER” ;

CREATE INDEX “REGIS”.”IDX_ADDRESS_SPELL_1301″ ON “REGIS”.”ADDRESS_1301″ (“SIMPLE_SPELL”)
online parallel 8
TABLESPACE “TBS_DATACENTER” ;

… …