数据库的空间使用情况是好多单位需要关注的，当Oracle数据库服务器比较多的时候，手动统计就显得费时费力了，下面编写了Python脚本批量统计Oracle的表空间总使用量
#! /usr/bin/python # -*- coding: UTF-8 -*- import cx_Oracle as oracle import time def nowdate(): #获取当前时间 nowdate=time.strftime("%Y%m%d",time.localtime()) return nowdate def get_connect(userinfo): #获取Oracle数据库的登陆信息 try: conn=oracle.connect(userinfo) cursor=conn.cursor() except Exception as error: print(error) else: return cursor def get_sql(filename): #获取统计Oracle数据库的sql语句 filename=filename try: with open(filename) as file: sql=file.read() except FileNotFoundError: error="Sorry,the file " filename " does not exist." print(error) else: return sql def get_data(sql): #获取Oracle数据库的表空间使用情况 cursor.execute(sql) data = cursor.fetchall() return data def get_instance_name(): #获取Oracle数据库实例名字 cursor.execute('select instance_name from v$instance') data = cursor.fetchall() cursor.close() # conn.close() return data def put_data(instance_name,instance_data,nowtime): #将得到的数据insert到特定的实例的表中,这里选择的是202的实例 host = "10.29.29.1" port = "1521" sid = "test209" dsn = oracle.makedsn(host, port, sid) conn =oracle.connect("liuwenhe", "liuwenhe", dsn) cursor = conn.cursor() insert_sql="insert into liuwenhe.tongji values ('" instance_name "','" str(instance_data) "','" nowtime "')" cursor.execute(insert_sql) cursor.close() conn.commit() conn.close() if __name__=='__main__': try: userinfofile='userinfo.txt' with open(userinfofile) as file: userinfos=file.readlines() for userinfo in userinfos: cursor=get_connect(userinfo) instance_name1=get_instance_name() instance_name=instance_name1[0][0] sql=get_sql('select') cursor=get_connect(userinfo) instance_data1=get_data(sql) instance_data=instance_data1[0][0] nowtime=nowdate() put_data(instance_name,instance_data,nowtime) except Exception as e: print (e)
其中统计Oracle表空间的的sql为（不包含undo表空间和临时表空间）：
select
sum(round(used_gb))used_M
from (select a.tablespace_name tablespace_name,
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / power(2, 30),
2) used_gb,
round(a.maxbytes / power(2, 30), 2) max_gb
from (select f.tablespace_name,
sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible,
'YES',
f.maxbytes,
'NO',
f.bytes)) maxbytes
from dba_data_files f
group by tablespace_name) a,
(select f.tablespace_name, sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name( ) and a.tablespace_name!='UNDOTBS1' and a.tablespace_name!='UNDOTBS' );
