-
Notifications
You must be signed in to change notification settings - Fork 16
/
snapinfo
executable file
·124 lines (106 loc) · 5.55 KB
/
snapinfo
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
#!/bin/env python
##################################################################################################
# Name: snapinfo #
# Author: Randy Johnson #
# Description: Display information about AWR snapshots (interval, retention, TopNSQL). #
# #
# Usage: snapinfo [options] #
# #
# Options: #
# -h, --help show this help message and exit #
# -s print SQL query. #
# -v print version info. #
# #
# History: #
# #
# Date Ver. Who Change Description #
# ---------- ---- ---------------- ------------------------------------------------------------- #
# mm/dd/yyyy 1.00 Randy Johnson Initial write. #
# 07/17/2015 2.00 Randy Johnson Updated for Python 2.4-3.4 compatibility. #
# 07/17/2015 2.20 Randy Johnson Added prompts for username, password, tnsname. #
##################################################################################################
# --------------------------------------
# ---- Import Python Modules -----------
# --------------------------------------
from optparse import OptionParser
from os import environ
from os.path import basename
from sys import argv
from sys import exit
from sys import version_info
from signal import SIGPIPE
from signal import SIG_DFL
from signal import signal
from Oracle import RunSqlplus
from Oracle import SetOracleEnv
from Oracle import ParseConnectString
# --------------------------------------
# ---- Main Program --------------------
# --------------------------------------
if (__name__ == '__main__'):
Cmd = basename(argv[0]).split('.')[0]
CmdDesc = 'AWR Snapshot Configuration Info.'
Version = '2.20'
VersionDate = 'Tue Sep 15 21:02:11 CDT 2015'
DevState = 'Production'
Banner = CmdDesc + ': Release ' + Version + ' ' + DevState + '. Last updated: ' + VersionDate
Sql = ''
SqlHeader = '/***** ' + CmdDesc.upper() + ' *****/'
ErrChk = False
ArgParser = OptionParser()
InStr = ''
ConnStr = ''
# For handling termination in stdout pipe; ex: when you run: oerrdump | head
signal(SIGPIPE, SIG_DFL)
ArgParser.add_option('--s', dest='Show', action='store_true', default=False, help="print SQL query.")
ArgParser.add_option('--v', dest='ShowVer', action='store_true', default=False, help="print version info.")
# Parse command line arguments
Options, args = ArgParser.parse_args()
Show = Options.Show
ShowVer = Options.ShowVer
if (ShowVer):
print('\n%s' % Banner)
exit()
Sql += "column int_min format a10 heading \"Interval|Minutes\"\n"
Sql += "column ret_min format a10 heading \"Retention|Days\"\n"
Sql += "column topnsql format a20 heading \"TopNSQL\"\n"
Sql += "\n"
Sql += "SELECT " + SqlHeader + "\n"
Sql += " TO_CHAR(EXTRACT(day from snap_interval) * 24 * 60 +\n"
Sql += " EXTRACT(hour from snap_interval) * 60 +\n"
Sql += " EXTRACT(minute from snap_interval)) int_min\n"
Sql += " , TO_CHAR((EXTRACT(day from retention) * 24 * 60 +\n"
Sql += " EXTRACT(hour from retention) * 60 +\n"
Sql += " EXTRACT(minute from retention)) / (24 * 60)) ret_min\n"
Sql += " , LTRIM(topnsql) topnsql\n"
Sql += " FROM dba_hist_wr_control;"
Sql = Sql.strip()
if(Show):
print('-----------cut-----------cut-----------cut-----------cut-----------cut-----------')
print(Sql)
print('-----------cut-----------cut-----------cut-----------cut-----------cut-----------')
exit()
# Check/setup the Oracle environment
if (not('ORACLE_SID' in list(environ.keys()))):
print('ORACLE_SID is required.')
exit(1)
else:
# Set the ORACLE_HOME just in case it isn't set already.
if (not('ORACLE_HOME' in list(environ.keys()))):
(OracleSid, OracleHome) = SetOracleEnv(environ['ORACLE_SID'])
# Parse the connect string if any, prompt for username, password if needed.
if (len(args) > 0 and not Show):
InStr = args[0]
ConnStr = ParseConnectString(InStr)
# Execute the report
if (ConnStr != ''):
(Stdout) = RunSqlplus(Sql, ErrChk, ConnStr)
else:
(Stdout) = RunSqlplus(Sql, ErrChk)
# Print the report
if (Stdout != ''):
print('\n%s' % Stdout)
exit(0)
# --------------------------------------
# ---- End Main Program ----------------
# --------------------------------------