批量导出oracle中的对象

浏览数：27 / 时间：2015年06月12日

背景

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(‘\t‘)
                    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])

View Code

结束

刚开始的实现，打算使用写个spool来实现，可是发现如果对象的定义太长的话，生成的代码会折行。这个没有找到解决办法，所以就打算使用脚本来实现。还有一个原因就是，需要导出的文件已utf-8格式编码，换行符为unix样式的。
python用的比较多，所以就使用python实现。因为有很多电脑没有安装Python，所以如果能用java重新编写适用性会更强点。

批量导出oracle中的对象,古老的榕树,5-wow.com

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

批量导出oracle中的对象