python oracle表空间,Python 检查oracle数据库表空间脚本

#!/usr/bin/env python3# -*- coding: utf-8 -*-#Oracle数据库检查脚本V1.0#20191120-增加表空间检查，支持oracle11g、oracle19cimport cx_Oracleimport prettytable as ptdef oraclesql(cursor):dbsql1 = open ('./dbcheck.sql','r')d

池幼

390人浏览 · 2021-04-04 13:05:58

池幼 · 2021-04-04 13:05:58 发布

#!/usr/bin/env python3

# -*- coding: utf-8 -*-

#Oracle数据库检查脚本V1.0

#20191120-增加表空间检查，支持oracle11g、oracle19c

import cx_Oracle

import prettytable as pt

def oraclesql(cursor):

dbsql1 = open ('./dbcheck.sql','r')

dbsql = dbsql1.read()

cursor.execute(dbsql)

data = cursor.fetchall()

return data

oinfo = open('./oracle_config.cfg','r')

dbinfo = oinfo.readlines()

oinfo.close()

for i in dbinfo:

info=i.split()

ipaddress = info[0].strip()

dbuser = info[1].strip()

dbpasswd = info[2].strip()

dbport = info[3].strip()

dbsid = info[4].strip()

try:

db = cx_Oracle.connect(dbuser+'/'+dbpasswd+'@'+ipaddress+':'+dbport+'/'+dbsid)

except Exception as e:

print('IP：%s 的数据库无法正常连接,请检查!' % ipaddress)

else:

cursor = db.cursor()

data = oraclesql(cursor)

cursor.close()

db.close()

print('下面开始对IP：%s 数据库进行检查' % ipaddress)

print('/t下面的输出为Oracle数据库表空间使用情况信息表')

row = list(range(len(data)))

tb = pt.PrettyTable()

tb.field_names = ['Hostname', 'sid', 'Instance_name', 'Sysdate', 'Logmode', 'TSB_NAME', 'file_count','allocated_space_mb', 'Used_mb', 'free_space_mb', 'USAGE_%', 'AUTOSIZE_mb', 'AUTOUSAGE_%']

for i in row:

tb.add_row(data[i])

print(tb)

print()

oracle_config.cfg 文件模板

172.16.108.224 system oracle 1521 zhou1

172.16.108.224 system oracle 1521 zhou2

172.16.108.224 system oracle 1521 zhou3

172.16.108.224 system oracle 1521 zhou4

172.16.108.224 system oracle 1521 zhou5

172.16.108.224 system oracle 1521 zhou6

172.16.108.224 system oracle 1521 zhou7

172.16.108.224 system oracle 1521 zhou8

172.16.108.224 system oracle 1521 zhou9

172.16.108.224 system oracle 1521 zhou10

172.16.108.115 system oracle 1521 ncloans

172.16.108.111 system oracle 1521 NCLOANSTB

dbcheck.sql

SELECT /*+ NO_CPU_COSTING */

F.HOST_NAME,

d.dbid,

F.INSTANCE_NAME,

SYSDATE,

D.LOG_MODE,

a.tablespace_name,

E1.file_count,

TRUNC (a.total) allocated_space_mb,

TRUNC (a.total - b.free) Used_mb,

TRUNC (b.free) free_space_mb,

ROUND (1 - b.free / a.total, 4) * 100 "USAGE_%",

c.AUTOSIZE AUTOSIZE_mb,

ROUND ( (a.total - b.free) / c.AUTOSIZE, 4) * 100 "AUTOUSAGE_%"

FROM ( SELECT tablespace_name, SUM (NVL (bytes, 2)) / 1024 / 1024 total

FROM dba_data_files DBA_DATA_FILES1

GROUP BY tablespace_name) a,

( SELECT tablespace_name, SUM (NVL (bytes, 2)) / 1024 / 1024 free

FROM dba_free_space

GROUP BY tablespace_name) b,

( SELECT tablespace_name, COUNT (file_name) file_count

FROM dba_data_files DBA_DATA_FILES2

GROUP BY tablespace_name) E1,

( SELECT x.TABLESPACE_NAME, SUM (x.AUTOSIZE) AUTOSIZE

FROM (SELECT TABLESPACE_NAME,

CASE

WHEN MAXBYTES / 1024 / 1024 = 0

THEN

BYTES / 1024 / 1024

ELSE

MAXBYTES / 1024 / 1024

END

AUTOSIZE

FROM DBA_DATA_FILES DBA_DATA_FILES3) x

GROUP BY x.tablespace_name) c,

v$database d,

v$instance f

WHERE b.tablespace_name = a.tablespace_name

AND c.TABLESPACE_NAME = b.TABLESPACE_NAME

AND E1.tablespace_name = a.tablespace_name

AND a.tablespace_name = c.TABLESPACE_NAME

AND E1.tablespace_name = b.tablespace_name

AND E1.tablespace_name = c.TABLESPACE_NAME

ORDER BY 3 DESC

/usr/local/bin/python3 /Users/jaja/PycharmProjects/Jaja/oracle_check.py