使用python创建数据库表空间

来源：互联网发布：px4飞控源码百度云编辑：程序博客网时间：2024/06/06 05:21

由于工作原因,经常需要在客户那里创建一个容灾数据库,数据库的表空间结构需要和生产库一致,这个时候手工编辑挺麻烦的,下面写了一个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()

0 0