使用python创建数据库表空间

由于工作原因,经常需要在客户那里创建一个容灾数据库,数据库的表空间结构需要和生产库一致,这个时候手工编辑挺麻烦的,下面写了一个python脚本,直接添加表空间,首先将生产库的表空间找出来,按照格式放到一个文件中,然后执行python脚本,读取文件生成表空间创建语句,例如下面我使用

col tablespace_name for a20

col file_name for a50

set pages 999 line 200 feedback off set head off

select 'DATA',tablespace_name,file_name,ceil(bytes/1024/1024) from dba_data_files

UNION ALL

select 'TEMP',tablespace_name,file_name,ceil(bytes/1024/1024) from dba_temp_files

order by 1,2

;

获得了表空间的文本格式语句,然后将它放到/home/oracle/1.txt中,需要创建的表空间的数据文件都放在/oradata/orcl/下面

具体如下:

适用于python3:

   
   

    
    

     
     #!/usr/local/python/bin/python
    
    
    
    

     
     '''
    
    
    
    

     
     1.txt format like :
    
    
    
    

     
     DATA USERS /root/user01.dbf 10
    
    
    
    

     
     TEMP TEMP /root/temp01.dfb 10
    
    
    
    

     
     can use sql:
    
    
    
    

     
     col tablespace_name for a20
    
    
    
    

     
     col file_name for a50
    
    
    
    

     
     set pages 999 line 200 feedback off set head off
    
    
    
    

     
     select 'DATA',tablespace_name,file_name,ceil(bytes/1024/1024) from dba_data_files 
    
    
    
    

     
     UNION ALL
    
    
    
    

     
     select 'TEMP',tablespace_name,file_name,ceil(bytes/1024/1024) from dba_temp_files 
    
    
    
    

     
     order by 1,2 
    
    
    
    

     
     ;
    
    
    
    

     
     '''
    
    
    
    

     
     import os
    
    
    
    

     
      
    
    
    
    

     
     tablespace=''
    
    
    
    

     
     datadir='/oradata/orcl/'
    
    
    
    

     
     fd=open('/home/oracle/1.txt')
    
    
    
    

     
     for line in fd:
    
    
    
    

     
             tablist=line.split()
    
    
    
    

     
             filename=os.path.basename(tablist[2])
    
    
    
    

     
             filesize=int(tablist[3])
    
    
    
    

     
             if tablist[0] == 'DATA':
    
    
    
    

     
                     if tablist[1] == tablespace:
    
    
    
    

     
                             print('alter tablespace %s add datafile \'%s%s\' size %dm autoextend on;' %(tablist[1],datadir,filename,filesize))
    
    
    
    

     
                     else:
    
    
    
    

     
                             print('create tablespace %s datafile \'%s%s\' size %dm autoextend on;' %(tablist[1],datadir,filename,filesize))
    
    
    
    

     
                     tablespace=tablist[1]
    
    
    
    

     
             else:
    
    
    
    

     
                     if tablist[1] == tablespace:
    
    
    
    

     
                             print('alter tablespace %s add tempfile \'%s%s\' size %dm autoextend on;' %(tablist[1],datadir,filename,filesize))
    
    
    
    

     
                     else:
    
    
    
    

     
                             print('create temporary tablespace %s tempfile \'%s%s\' size %dm autoextend on;' %(tablist[1],datadir,filename,filesize))
    
    
    
    

     
                     tablespace=tablist[1]
    
    
    
    

     
                     
    
    
    
    

     
      
    
    
    
    

     
     fd.close()
    
    
   
   

适用于python2:

   
   

    
    

     
     #!/usr/bin/python
    
    
    
    

     
     '''
    
    
    
    

     
     1.txt format like :
    
    
    
    

     
     USERS /root/user01.dbf 10
    
    
    
    

     
     TEMP TEMP /root/temp01.dfb 10

    
    
    
    

     
     can usr sql:
    
    
    
    

     
     col tablespace_name for a20
    
    
    
    

     
     col file_name for a50
    
    
    
    

     
     set pages 999 line 200 feedback off set head off
    
    
    
    

     
     select 'DATA',tablespace_name,file_name,ceil(bytes/1024/1024) from dba_data_files
    
    
    
    

     
     UNION ALL
    
    
    
    

     
     select 'TEMP',tablespace_name,file_name,ceil(bytes/1024/1024) from dba_temp_files
    
    
    
    

     
     order by 1,2
    
    
    
    

     
     ;
    
    
    
    

     
     '''
    
    
    
    

     
     import os
    
    
    
    

     
      
    
    
    
    

     
     tablespace=''
    
    
    
    

     
     datadir='/oradata/orcl/'
    
    
    
    

     
     fd=open('/home/oracle/1.txt')
    
    
    
    

     
     for line in fd:
    
    
    
    

     
             tablist=line.split()
    
    
    
    

     
             filename=os.path.basename(tablist[2])
    
    
    
    

     
             filesize=int(tablist[3])
    
    
    
    

     
             if tablist[0] == 'DATA':
    
    
    
    

     
                     if tablist[1] == tablespace:
    
    
    
    

     
                             print 'alter tablespace %s add datafile \'%s%s\' size %dm autoextend on;' %(tablist[1],datadir,filename,filesize)
    
    
    
    

     
                     else:
    
    
    
    

     
                             print 'create tablespace %s datafile \'%s%s\' size %dm autoextend on;' %(tablist[1],datadir,filename,filesize)
    
    
    
    

     
                     tablespace=tablist[1]
    
    
    
    

     
             else:
    
    
    
    

     
                     if tablist[1] == tablespace:
    
    
    
    

     
                             print 'alter tablespace %s add tempfile \'%s%s\' size %dm autoextend on;' %(tablist[1],datadir,filename,filesize)
    
    
    
    

     
                     else:
    
    
    
    

     
                             print 'create temporary tablespace %s tempfile \'%s%s\' size %dm autoextend on;' %(tablist[1],datadir,filename,filesize)
    
    
    
    

     
                     tablespace=tablist[1]
    
    
    
    

     
      
    
    
    
    

     
      
    
    
    
    

     
     fd.close()