1. Filesystem Usage Report

from fabric import Connection
import colorama

username = "your_acct"
password = "your_pswd"

colorama.init(autoreset=True)
FS_WARNING = 70
FS_CRITICAL = 85

command = """
df -P -B G | grep ora | grep -v redo
"""


def get_mem_info(p_host, p_cmd):
    try:
        c = Connection(host=username + '@' + p_host, connect_kwargs={"password": password})
        result = c.run(p_cmd, pty=True, hide=True)
        for line in result.stdout.splitlines():
            (v_dev, v_alloc, v_used, v_aval, v_pct, v_fs) = line.split()
            if (0 <= int(v_pct[:-1]) < FS_WARNING):
                # print( f'{p_host:>30}:{v_fs:<30}{v_alloc:>15}{v_used:>15}{v_aval:>15}{v_pct:>20}')
                pass
            elif (FS_WARNING <= int(v_pct[:-1]) < FS_CRITICAL):
                print( f'{p_host:>30}:{v_fs:<30}{v_alloc:>15}{v_used:>15}{v_aval:>15}{colorama.Fore.YELLOW + colorama.Style.BRIGHT}{v_pct:>20}')
            elif (FS_CRITICAL <= int(v_pct[:-1])):
                print( f'{p_host:>30}:{v_fs:<30}{v_alloc:>15}{v_used:>15}{v_aval:>15}{colorama.Fore.RED + colorama.Style.BRIGHT}{v_pct:>20}')
    except Exception as e:
        print(f'{p_host:<30}{"Error: "}{e}')


print(f'{"Hostname":>30}:{"File_System":<30}{"Allocated":>15}{"Used":>15}{"Available":>15}{"Capacity":>20}')
db_inv = open("inv/os_inv.txt")
for line in db_inv.readlines():
    if line[0:1] != '#':
        (v_host) = line.strip('\n')
        get_mem_info(v_host, command)

2. Tablespace Usage Report

# -*- coding: utf-8 -*-
import cx_Oracle
import colorama

PCT_WARNING = 70
PCT_CRITICAL = 85

GB_WARNING = 100


orastmt = """SELECT NVL(B.TABLESPACE_NAME, NVL(A.TABLESPACE_NAME,'UNKNOWN')) TS_NAME,
    round(V_KBYTES_ALLOC/1024,1) V_ALLOC_GB,
    round(KBYTES_ALLOC/1024,1) ALLOC_GB,
    round((KBYTES_ALLOC-NVL(KBYTES_FREE,0))/1024,1) USED_GB,
    round(NVL(KBYTES_FREE/1024,0),1) FREE_GB,
    round(((KBYTES_ALLOC-NVL(KBYTES_FREE,0))/KBYTES_ALLOC)*100,1) USED_PCT,
    round((V_KBYTES_ALLOC-(KBYTES_ALLOC-NVL(KBYTES_FREE,0)))/1024,1) V_FREE_GB,
    round(((KBYTES_ALLOC-NVL(KBYTES_FREE,0))/V_KBYTES_ALLOC)*100,1) V_USED_PCT,
    DATA_FILES FILES_NUM
FROM
    (SELECT SUM(BYTES)/1024/1024 KBYTES_FREE,
        MAX(BYTES)      /1024/1024 LARGEST,
        TABLESPACE_NAME
    FROM SYS.DBA_FREE_SPACE
    GROUP BY TABLESPACE_NAME
    ) A,
    (SELECT SUM(BYTES)/1024/1024 KBYTES_ALLOC,
        SUM(DECODE(AUTOEXTENSIBLE,'NO',BYTES, 'YES',MAXBYTES, 0))/1024/1024 V_KBYTES_ALLOC,
        TABLESPACE_NAME,
        COUNT(*) DATA_FILES
    FROM SYS.DBA_DATA_FILES
    GROUP BY TABLESPACE_NAME
    )B
WHERE A.TABLESPACE_NAME (+) = B.TABLESPACE_NAME
AND ((KBYTES_ALLOC-NVL(KBYTES_FREE,0))/V_KBYTES_ALLOC)*100 >= """ + str(PCT_WARNING) + """
ORDER BY 6 DESC"""

colorama.init(autoreset=True)


def ora_get_ts_free(p_conn):
    try:
        oraconn = cx_Oracle.connect(p_conn)
        oracur = oraconn.cursor()
        cursid = p_conn.split('/')[-1]
        oracur.execute(orastmt)

        for (ts_name, v_alloc_gb, alloc_gb, used_gb, free_gb, used_pct, v_free_gb, v_used_pct, files_num) in oracur:
            if (GB_WARNING <= v_free_gb):
                free_gb_color = colorama.Fore.WHITE + colorama.Style.BRIGHT
            else:
                free_gb_color = colorama.Fore.YELLOW + colorama.Style.BRIGHT

            if (0 <= v_used_pct <= PCT_WARNING):
                ts_color = colorama.Fore.WHITE + colorama.Style.BRIGHT
            elif (PCT_WARNING <= v_used_pct < PCT_CRITICAL):
                ts_color = colorama.Fore.YELLOW + colorama.Style.BRIGHT
            elif (PCT_CRITICAL <= v_used_pct):
                ts_color = colorama.Fore.RED + colorama.Style.BRIGHT

            print(f'{cursid:<15}{ts_name:<25}{v_alloc_gb:>12.1f}{alloc_gb:>12.1f}{used_gb:>12.1f}{free_gb:>12.1f}{used_pct:>12.1f}{free_gb_color}{v_free_gb:>12.1f}{colorama.Fore.WHITE}{ts_color}{v_used_pct:>12.1f}{colorama.Fore.WHITE}{files_num:>12d}')
        oraconn.close()
    except cx_Oracle.DatabaseError as exc:
        error, = exc.args
        print(p_conn)
        print(error.message)


print(f'{"DB_NAME":<15s}{"TS_NAME":<25s}{"V_ALLOC_GB":>12s}{"ALLOC_GB":>12s}{"USED_GB":>12s}{"FREE_GB":>12s}{"USED_PCT":>12s}{"V_FREE_GB":>12s}{"V_USED_PCT":>12s}{"FILES_NUM":>12s}')
db_inv = open("inv\db_inv.txt")
lines = db_inv.readlines()
for line in lines:
    if line[0:1] != '#':
        # the line format is username/password@hostname:port/sid
        ora_get_ts_free(line.strip('\n'))

print('Done...')

3. Partition Table Report

# -*- coding: utf-8 -*-
import cx_Oracle
import colorama

orastmt = """SELECT tp.table_owner
  ,tp.table_name
  ,pt.partition_count as part_count
  ,substr(tp.max_high_value, instr(tp.max_high_value, ' ') + 1, 19) AS part_tail
  ,round(to_date(substr(tp.max_high_value, instr(tp.max_high_value, ' ') + 1, 19), 'yyyy-mm-dd hh24:mi:ss') - sysdate) AS left_days
  ,nvl(pt.interval, ' ') AS interval
FROM (
  SELECT table_owner
    ,table_name
    ,max(high_value) AS max_high_value
  FROM (
    WITH date_partitions AS (
        SELECT dbms_xmlgen.getxmltype('
    select p.table_owner, 
        p.table_name, 
        p.high_value
    from   dba_part_key_columns k, 
        dba_tab_cols c, 
        dba_tab_partitions p
    where  k.owner = c.owner
    and    k.column_name = c.column_name
    and    k.name = c.table_name
    and    k.owner = p.table_owner
    and    k.name = p.table_name
    and    (c.data_type = ''DATE'' or 
            c.data_type like ''TIMESTAMP%'')') AS XML
        FROM dual
        )
    SELECT x.*
    FROM date_partitions p
      ,xmltable('/ROWSET/ROW' passing p.XML columns 
      table_owner varchar2(30) path '/ROW/TABLE_OWNER', 
      table_name varchar2(30) path '/ROW/TABLE_NAME', 
      high_value varchar2(30) path '/ROW/HIGH_VALUE') x
    )
  WHERE table_owner NOT IN ('SYS', 'SYSTEM', 'DBMON', 'AUDSYS')
  GROUP BY table_owner
    ,table_name
  ) tp
  ,dba_part_tables pt
WHERE tp.table_owner = pt.OWNER
  AND tp.table_name = pt.table_name
  AND to_date(substr(tp.max_high_value, instr(tp.max_high_value, ' ') + 1, 19), 'yyyy-mm-dd hh24:mi:ss') < sysdate
"""

colorama.init(autoreset=True)

def ora_part_tab_tail(p_conn):
    oraconn = cx_Oracle.connect(p_conn)
    oracur = oraconn.cursor()
    cursid = p_conn.split('/')[-1]

    oracur.execute(orastmt)

    for (table_owner,table_name,part_count,part_tail,left_days,interval) in oracur:
        print(f'{cursid:<10s}{table_owner:<20s}{table_name:<30s}{part_count:>10d}{part_tail:>25s}{left_days:>10d}{interval:>30s}')
    oraconn.close()


print (f'{"DB_NAME":<10s}{"TABLE_OWNER":<20s}{"TABLE_NAME":<30s}{"PART_COUNT":>10s}{"PART_TAIL":>25s}{"LEFT_DAYS":>10s}{"INTERVAL":>30s}')
db_inv = open("inv\db_inv.txt")
lines = db_inv.readlines()
for line in lines:
    if line[0:1] != '#':
        ora_part_tab_tail(line.strip('\n'))

print ('Done...')