Oracle ADG check by Python

[root@netman adg]# more adg.py 
# -*- coding:utf-8 -*- 

import cx_Oracle
import numpy as np
import codecs
from pprint import pprint
import csv
import time
import re
import binascii
import os,sys
import pymysql
import logging
import string

reload(sys)
sys.setdefaultencoding('utf8')



con = cx_Oracle.connect('system/xxxpasswd@IP_ADDR/orcldg')
print con.version

cur = con.cursor()
###查看表空间
sql = """
SELECT UPPER(F.TABLESPACE_NAME) "TBS_NAME",
        D.TOT_GROOTTE_MB "SIZE(M)",
        D.TOT_GROOTTE_MB - F.TOTAL_BYTES "USED(M)",
        TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2), '990.99') "USED%"
FROM (SELECT TABLESPACE_NAME,
        ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
        ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
        FROM SYS.DBA_FREE_SPACE
        GROUP BY TABLESPACE_NAME) F,
        (SELECT DD.TABLESPACE_NAME,
                ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
         FROM SYS.DBA_DATA_FILES DD
         GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC
"""
print sql
cur.execute(sql)
for result in cur:
    print result


###查看DataGuard状态
sql ="""
select * from v$dataguard_stats
"""
print sql
cur.execute(sql)
for result in cur:
    print result


###查看DG进程
sql = """
SELECT PROCESS, STATUS,THREAD#,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY
"""
print sql
cur.execute(sql)
for result in cur:
    print result


###查看session
sql = """
select count(*),inst_id from gv$session group by inst_id
"""
print sql
cur.execute(sql)
for result in cur:
    print result

sql = """
select count(*),inst_id from gv$session where status='ACTIVE' group by inst_id
"""
print sql
cur.execute(sql)
for result in cur:
    print result


###查看最耗时的 SQL TOP10
sql = """
select * from (
    select * from V$SQLSTATS
     order by ELAPSED_TIME DESC
) where rownum <=10
"""
#print sql
#cur.execute(sql)
#for result in cur:
#    print result


###查看读硬盘最多的 SQL TOP10
sql = """
select * from (
        select * from V$SQLSTATS
         order by DISK_READS DESC
) where rownum <=10
"""
#print sql
#cur.execute(sql)
#for result in cur:
#    print result


###查看最费 CPU 的 SQL TOP10
sql = """
select * from (
        select * from V$SQLSTATS
         order by BUFFER_GETS DESC
) where rownum <=10
"""
#print sql
#cur.execute(sql)
#for result in cur:
#    print result


###查看用户连接
sql = """
select username, program, logon_time, status, machine from v$session
"""
print sql
cur.execute(sql)
for result in cur:
    print result

sql = """
select sid,serial#,username,program,machine,client_info 
from v$session
where username is not null
order by username,program,machine
"""
print sql
cur.execute(sql)
for result in cur:
    print result


###查看ASMDISK
sql = """
SELECT 
    name                                     group_name 
  , sector_size                              sector_size 
  , block_size                               block_size 
  , allocation_unit_size                     allocation_unit_size 
  , state                                    state 
  , type                                     type 
  , total_mb                                 total_mb 
  , (total_mb - free_mb)                     used_mb 
  , ROUND((1- (free_mb / total_mb))*100, 2)  pct_used 
FROM 
    v$asm_diskgroup 
WHERE 
    total_mb != 0 
ORDER BY 
    name 
"""
print sql
cur.execute(sql)
for result in cur:
    print result


###test01 last row
sql = """
select * from sys.test01 where rowid in(select max(rowid) from sys.test01)
"""
print sql
cur.execute(sql)
for result in cur:
    print result


cur.close()
con.close()