背景
Oracle数据库中有table,view,procedure,function,package,type等对象，需要将这些对象导出到不同的文件中。
常用的方法有3种：
1. 通过开发工具直接导出。优点：导出的格式好；缺点：无法自动化。
2. 写一个spool进行导出。优点：实现自动化；缺点：如果长度过程，会自动折行（可能是没有找到解决办法）。
3. 编写一个小程序进行导出。本文采用此方法。
思路
要求：将数据库A中的对象a1,a2,a3..an保存到文件file1中，将b1,b2,b3..bn保存到文件file2中，将c1,c2,c3..cn保存到文件file3中。
思路如下：
1. Input:
a) 数据库A的连接：connect_string
b) file1 file2 file3要保存的文件夹：result_dir
c) 说明文件：list_file，用于说明文件file1要保存哪些对象，file2要保存哪些对象。。。
2. list_file的格式：
#file1
type1 a1
type2 a2
type3 a3
...
typen an
#file2
type1 b1
type2 b2
type3 b3
...
typen bn
#file3
type1 c1
type2 c2
type3
...
typen cn
其中，文件名已“#”开头，type指对象的类型(table,view...)，a1指对象的名字，type和a1中间以tab键隔开。
3. 流程：
定义一个方法：__get_object_list（这些方法见python实现），用于读取文件list_file，确定每个目标文件对应的对象。
解析完list_file之后，进行循环，每次生成一个目标文件。生成目标文件的方法为__get_list_ddl。
不同的对象有不同的定义，比如table,需要获取其定义、注释、索引等信息，而procedure仅仅获取其定义。这些工作由方法__get_single_ddl实现。
4. 相关SQL:
获取定义主要通过DBMS_METADATA.GET_DDL实现，获取表注释通过SQL查询实现。
--调用GET_DDL之前的设置 BEGIN DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE); DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE); DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE); DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'TABLESPACE', FALSE); DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS', TRUE); DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS', TRUE); --DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', TRUE); END; --对用之后恢复初始化 BEGIN DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'DEFAULT'); END; --获取某个对象的DDL SELECT DBMS_METADATA.GET_DDL(:1,:2) FROM DUAL --获取注释 SELECT ' COMMENT ON TABLE ' || T.TABLE_NAME || ' IS ''' || T.COMMENTS || ''' ;' FROM USER_TAB_COMMENTS T WHERE T.COMMENTS IS NOT NULL AND T.TABLE_NAME = :1 UNION ALL SELECT ' COMMENT ON COLUMN ' || T.TABLE_NAME || '.' || T.COLUMN_NAME || ' IS ''' || T.COMMENTS || ''' ;' FROM USER_COL_COMMENTS T WHERE T.COMMENTS IS NOT NULL AND T.TABLE_NAME = :1 --获取索引 SELECT DBMS_METADATA.GET_DDL('INDEX', I.INDEX_NAME) FROM USER_INDEXES I WHERE I.INDEX_NAME NOT IN (SELECT X.CONSTRAINT_NAME FROM USER_CONSTRAINTS X) AND I.TABLE_NAME = :1 ORDER BY INDEX_NAME DESC
python实现
import cx_Oracle,os,sys class OraExpDDl: """ usage: ora=OraExpDDl() ora.open(connect_string) export_ddl(list_file,result_dir) ora.close() """ def __init__(self): self.connect='' self.cursor='' self.status=False def __get_single_ddl(self, object_type,object_name): object_type=object_type.upper() object_name=object_name.upper() print(' {0}:{1}'.format(object_type,object_name)) l=[] #check sql=""" SELECT COUNT(*) FROM USER_OBJECTS T WHERE T.OBJECT_NAME = '{0}' AND T.OBJECT_TYPE = '{1}'""".format(object_name,object_type) self.cursor.execute(sql) cnt=self.cursor.fetchone() if cnt[0]==0: print(' {0}:{1} does not exists'.format(object_type,object_name)) return l l.append('prompt create {0} {1}'.format(object_type,object_name)) #object print(' ddl') sql="""SELECT DBMS_METADATA.GET_DDL('{0}', '{1}') FROM DUAL""".format(object_type,object_name) self.cursor.execute(sql) l.extend([i[0] for i in self.cursor.fetchall()]) if object_type=='TABLE': #index print(' index') sql="""SELECT DBMS_METADATA.GET_DDL('INDEX', I.INDEX_NAME) FROM USER_INDEXES I WHERE I.INDEX_NAME NOT IN (SELECT X.CONSTRAINT_NAME FROM USER_CONSTRAINTS X) AND I.TABLE_NAME = '{0}' ORDER BY INDEX_NAME DESC""".format(object_name) self.cursor.execute(sql) l.extend([i[0] for i in self.cursor.fetchall()]) #comment print(' comment') sql="""SELECT ' COMMENT ON TABLE ' || T.TABLE_NAME || ' IS ''' || T.COMMENTS || ''' ;' FROM USER_TAB_COMMENTS T WHERE T.COMMENTS IS NOT NULL AND T.TABLE_NAME = '{0}' UNION ALL SELECT ' COMMENT ON COLUMN ' || T.TABLE_NAME || '.' || T.COLUMN_NAME || ' IS ''' || T.COMMENTS || ''' ;' FROM USER_COL_COMMENTS T WHERE T.COMMENTS IS NOT NULL AND T.TABLE_NAME = '{0}'""".format(object_name) self.cursor.execute(sql) l.extend([i[0] for i in self.cursor.fetchall()]) return l def __get_list_ddl(self, object_list,file_name): #init sql_before=""" BEGIN DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE); DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE); DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE); DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'TABLESPACE', FALSE); DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS', TRUE); DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS', TRUE); --DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', TRUE); END; """ sql_after= """ BEGIN DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'DEFAULT'); END; """ #start self.cursor.execute(sql_before) f=open(file_name,'wb') #get and write for i in object_list: str_list=self.__get_single_ddl(i[0],i[1]) f.write(chr(10).join(str_list).encode('utf-8')) f.write(chr(10).encode('utf-8')) #end f.close() self.cursor.execute(sql_after) return 0 def __get_object_list(self,list_file): r=[] state=0 label='' data=[] # read data f=open(list_file); l=[i.strip() for i in f.readlines() if len(i.strip())>0] f.close() # parse data for i in l: if state==1: if i[0]!='#': tmp=i.split(' ') if len(tmp)<2: raise Exception("parse file object_list_file error") data.append((tmp[0],tmp[1])) else: if len(data)>0: r.append((label,data)) label=i[1:] data=[] if state==0: if i[0]=='#': label=i[1:] state=1 if len(data)>0: r.append((label,data)) return r def open(self,connect_string): """ connect_string:database connection build a connection of oracle """ def OutputTypeHandler(cursor, name, defaultType, size, precision, scale): if defaultType == cx_Oracle.CLOB: return cursor.var(cx_Oracle.LONG_STRING, 80000, cursor.arraysize) try: self.connect=cx_Oracle.connect(connect_string) self.connect.outputtypehandler=OutputTypeHandler self.cursor=self.connect.cursor(); self.status=True except: self.status=False print("can't open: ",connect_string) def close(self): """ close the connection """ if self.status: self.status=False try: self.cursor.close() self.connect.close(); except: self.status=False def export_ddl(self,list_file,result_dir): """ list_file:the objects you want export result_dir:directory of result file """ #check if not self.status: print("disconnect") return 1 if not os.path.isfile(list_file): print("file {0} does not exists".format(list_file)) return 2 if not os.path.isdir(result_dir): print("result dir {0} does not exists".format(result_dir)) return 3 try: object_list=self.__get_object_list(list_file) except: print("list file parse error") return 4 #do for i in object_list: print(i[0]) file_name=os.path.join(result_dir,i[0]) self.__get_list_ddl(i[1],file_name) print("export ddl finish") return 0 def export_ddl(connect_string,list_file,result_dir): """ connect_string:database connection list_file:the objects you want export result_dir:directory of result file """ ora=OraExpDDl() ora.open(connect_string) ora.export_ddl(list_file, result_dir) ora.close() if __name__=='__main__': if len(sys.argv)<4: print("please input parameter:connection,sql_dir,object_file_path") else: export_ddl(sys.argv[1],sys.argv[2],sys.argv[3])
结束
刚开始的实现，打算使用写个spool来实现，可是发现如果对象的定义太长的话，生成的代码会折行。这个没有找到解决办法，所以就打算使用脚本来实现。还有一个原因就是，需要导出的文件已utf-8格式编码，换行符为unix样式的。
python用的比较多，所以就使用python实现。因为有很多电脑没有安装Python，所以如果能用java重新编写适用性会更强点。