pysql.pysqlaudit

1 #!/usr/bin/python 2 # -*- coding: utf-8 -*- 3 4 """ This module defines all high level audit functions of pysql 5 @author: Sébastien Delcros (Sebastien.Delcros@gmail.com) 6 @license: GNU GPL V3 7 """ 8 9 # pylint: disable-msg=E1101 10 11 # Python imports: 12 import os 13 14 # Pysql imports: 15 from pysqlqueries import * 16 from pysqlexception import PysqlException, PysqlNotImplemented, PysqlActionDenied 17 from pysqloraobjects import * 18 from pysqlcolor import * 19 from pysqlconf import PysqlConf 20 from pysqldb import PysqlDb 21 22 # High level pysql audit functions

23 -def listSnapshotId(db, numDays=1):

24 """Prompts user to choose a snapshot id 25 @arg db: connection object 26 @arg numDays: the number of days of snapshots""" 27 try: 28 return db.executeAll(perfSql["snapshots"], [unicode(numDays)]) 29 except Exception, e: 30 raise PysqlActionDenied(_("Insufficient privileges"))

31

32 -def addmReport(db, begin_snap="0", end_snap="0", type="TEXT", level="TYPICAL"):

33 """Generates ADDM report 34 @arg db: connection object 35 @arg begin_snap: snapshot 36 @arg end_snap: snapshot""" 37 38 # Gets database id and instance number 39 try: 40 dbid = db.executeAll(perfSql["db_id"])[0][0] 41 inum = db.executeAll(perfSql["instance_num"])[0][0] 42 except Exception, e: 43 raise PysqlActionDenied(_("Insufficient privileges")) 44 45 if begin_snap == "0" or end_snap == "0": 46 raise PysqlActionDenied(_("Invalid snapshot pair: (%s ; %s)") % (begin_snap, end_snap)) 47 48 # PL/SQL procedure because of bloody in/out parameters in create_task function 49 sql = """BEGIN 50 DECLARE 51 dbid number; 52 inum number; 53 bid number; 54 eid number; 55 id number; 56 name varchar2(100); 57 descr varchar2(500); 58 59 BEGIN 60 dbid := %s; 61 inum := %s; 62 bid := %s; 63 eid := %s; 64 name := ''; 65 descr := 'ADDM run: snapshots [' || bid || ', ' || eid || '], instance ' || inum || ', database id ' || dbid; 66 67 -- creates task 68 dbms_advisor.create_task('ADDM', id, name, descr, null); 69 70 -- sets task parameters 71 dbms_advisor.set_task_parameter(name, 'DB_ID', dbid); 72 dbms_advisor.set_task_parameter(name, 'INSTANCE', inum); 73 dbms_advisor.set_task_parameter(name, 'END_SNAPSHOT', eid); 74 dbms_advisor.set_task_parameter(name, 'START_SNAPSHOT', bid); 75 76 -- executes task 77 dbms_advisor.execute_task(name); 78 79 -- displays task name 80 dbms_output.enable; 81 dbms_output.put_line(name); 82 83 END; 84 END; 85 """ % (dbid, inum, begin_snap, end_snap) 86 87 # Creates task 88 try: 89 db.execute(sql) 90 except Exception, e: 91 raise PysqlException(_("Insufficient privileges")) 92 # Gets task name 93 task_name = db.getServerOuput()[0] 94 # Generates report from task 95 result = db.executeAll(perfSql["addm_report_text"], [unicode(task_name), unicode(type.upper()), unicode(level.upper())]) 96 return result

97

98 -def awrReport(db, type="txt", begin_snap="0", end_snap="0"):

99 """Generates AWR report 100 @arg db: connection object 101 @arg type: output format (html or text) 102 @arg begin_snap: snapshot 103 @arg end_snap: snapshot""" 104 105 # Gets database id and instance number 106 try: 107 dbid = db.executeAll(perfSql["db_id"])[0][0] 108 inum = db.executeAll(perfSql["instance_num"])[0][0] 109 except Exception, e: 110 raise PysqlActionDenied(_("Insufficient privileges")) 111 112 if begin_snap == "0" or end_snap == "0": 113 raise PysqlActionDenied(_("Invalid snapshot pair: (%s ; %s)") % (begin_snap, end_snap)) 114 115 # Generates report 116 try: 117 if type.upper() == "HTML": 118 result = db.executeAll(perfSql["awr_report_html"], [dbid, inum, begin_snap, end_snap]) 119 else: 120 result = db.executeAll(perfSql["awr_report_text"], [dbid, inum, begin_snap, end_snap]) 121 except Exception, e: 122 raise PysqlActionDenied(_("Insufficient privileges")) 123 return result

124

125 -def sqlTune(db, statement, type="TEXT", level="TYPICAL"):

126 """Generates a SQL tunung advice report 127 @arg db: connection object 128 @arg statement: sql statement to be tuned 129 """ 130 # Doubles quote because of sql statement parsing 131 statement = statement.replace("'", "''") 132 133 # PL/SQL procedure because of bloody in/out parameters in create_task function 134 sql = """BEGIN 135 DECLARE 136 sql_text clob; 137 user_name varchar2(30); 138 task_name varchar2(30); 139 140 BEGIN 141 sql_text := '%s'; 142 user_name := '%s'; 143 task_name := ''; 144 145 -- creates new task 146 task_name := dbms_sqltune.create_tuning_task( 147 sql_text => sql_text, 148 scope => 'comprehensive', 149 time_limit => 60, 150 task_name => task_name, 151 description => 'task to tune a query'); 152 153 -- executes task 154 dbms_sqltune.execute_tuning_task(task_name => task_name); 155 156 -- displays task name 157 dbms_output.enable; 158 dbms_output.put_line(task_name); 159 END; 160 END; 161 """ % (statement, db.getUsername()) 162 163 # Creates task 164 try: 165 db.execute(sql) 166 except PysqlException, e: 167 raise PysqlException(_("Insufficient privileges")) 168 # Gets task name 169 task_name = db.getServerOuput()[0] 170 # Generates report from task 171 try: 172 result = db.executeAll(perfSql["sqltune_text"], [unicode(task_name), unicode(type.upper()), unicode(level.upper())]) 173 except PysqlException, e: 174 if str(e).count(unicode(task_name)) > 0: 175 raise PysqlException(_("Insufficient privileges")) 176 else: 177 raise e 178 179 return result

180

181 -def duReport(db, segmentType, tbs="%", user="%", nbRows=-1):

182 """Generates storage report 183 @arg db: connection object 184 @arg segmentType: type (table or index) 185 @arg tbs: tablespace to analyze, all if not specified 186 @arg user: user to analyze, all users if not specified 187 @arg nbRows: number of lines to return, all if not specified 188 """ 189 # Gets storage size used considering user and tablespace restrictions 190 try: 191 size = db.executeAll(durptSql["nbTotalBlocks"], [tbs, user])[0][0] 192 except PysqlException, e: 193 raise PysqlActionDenied(_("Insufficient privileges")) 194 195 # Generates report 196 if segmentType.lower() == "table": 197 header = [_("Owner"), _("Tablespace"), _("Table"), _("Part?"), _("#Cols"), _("#Rows"), _("Size(blk)"), _("Size(MB)"), _("Size(%)")] 198 result = db.executeAll(durptSql["tablesForTbsAndUser"], [unicode(size), tbs, user]) 199 elif segmentType.lower() == "index": 200 header = [_("Owner"), _("Tablespace"), _("Index"), _("Part?"), _("Level"), _("Keys"), _("Size(blk)"), _("Size(MB)"), _("Size(%)")] 201 result = db.executeAll(durptSql["indexesForTbsAndUser"], [unicode(size), tbs, user]) 202 else: 203 raise PysqlException(_("Internal error: type %s not supported") % segmentType) 204 205 return (result[:nbRows], header)

206

207 -def assmReport(db, name):

208 """Generates a storage report (may take a while) 209 @arg db: connection object 210 @arg name: table name 211 """ 212 table = OraTable(tableName=name) 213 table.guessInfos(db) 214 try: 215 neededBlocks = table.getNeededBlocks(db) 216 except PysqlException, e: 217 raise PysqlException(_("Table %s does not exist") % table.getName()) 218 try: 219 allocatedBlocks = table.getUsedBlocks(db) 220 except PysqlException, e: 221 raise PysqlActionDenied(_("Insufficient privileges")) 222 lostBlocks = allocatedBlocks - neededBlocks 223 224 header = [_("Owner"), _("Name"), _("Allocated"), _("Needed"), _("Lost"), _("Lost(%)")] 225 result = [[table.getOwner(), table.getName(), allocatedBlocks, neededBlocks, lostBlocks, round(100*float(lostBlocks)/allocatedBlocks, 1)]] 226 227 return (result, header)

228

Source Code for Module pysql.pysqlaudit