|
0
|
1 #!/usr/bin/python
|
|
|
2 # MySqlPy V1.3
|
|
|
3 # Author: Luca.Canali@cern.ch
|
|
|
4 #
|
|
|
5 #
|
|
|
6 # Companion of SqlPython, a python module that reproduces Oracle's command line within python
|
|
|
7 # 'sqlplus inside python'
|
|
|
8 # See also: http://twiki.cern.ch/twiki/bin/view/PSSGroup/SqlPython
|
|
|
9 # http://catherine.devlin.googlepages.com/
|
|
|
10
|
|
|
11 from sqlpyPlus import *
|
|
|
12 import binascii
|
|
|
13
|
|
|
14 class mysqlpy(sqlpyPlus):
|
|
|
15 '''
|
|
|
16 MySqlPy V1.3 - 'sqlplus in python'
|
|
|
17 Author: Luca.Canali@cern.ch
|
|
|
18 Rev: 1.3.0, 17-Oct-07
|
|
|
19
|
|
|
20 Companion of SqlPython, a python module that reproduces Oracle's command line within python
|
|
|
21 and sqlpyPlus. Major contributions by Catherine Devlin, http://catherinedevlin.blogspot.com
|
|
|
22 Quick start command list:
|
|
|
23
|
|
|
24 - top -> executes a query to list all active sessions in (Oracle 10g and RAC)
|
|
|
25 (use: instance activity monitoring, a DBA tool)
|
|
|
26 - tselect -> prints the result set in trasposed form, useful to print result sets with
|
|
|
27 many columns such as dba_ or v$ views (ex: dba_tables or v$instance)
|
|
|
28 - py -> execute a python command (C.D.)
|
|
|
29 - db -> quick connect using credentials in pass.txt file
|
|
|
30 (Ex: write username and pass in pass.txt and then "db db_alias" to connect)
|
|
|
31 - sql -> prints the sql text from the cache. parameter: sql_id of the statement
|
|
|
32 (Ex: sql fzqa1qj65nagki)
|
|
|
33 - explain -> prints the execution plan from the cache. parameter: sql_id of the statement
|
|
|
34 - sessinfo-> prints session information. 1 parameter sid (Ex: sql 101 print info for sid 101)
|
|
|
35 - longops -> prints from gv$session_longops (running full scans, etc)
|
|
|
36 - load -> prints the OS load on all cluster nodes (10g RAC)
|
|
|
37 - sleect,slect -> alias for select (I mistyped select this way too many times...)
|
|
|
38 - top9i -> 9i (and single instance) version of top
|
|
|
39 - describe, @, !, spool, show, set, list, get, write -> sql*plus-like, from sqlpyPlus (C.D.)
|
|
|
40 - shortcuts: \c (connect), \d (describe), etc, from sqlpyPlus (C.D.)
|
|
|
41 - :myvarname = xx, set autobind 1, print -> bind variables management extension, to sqlplus (C.D.)
|
|
|
42
|
|
|
43 Example:
|
|
|
44 SQL> connect username@dbalias or username/pass@dbalias
|
|
|
45 SQL> select sysdate from dual;
|
|
|
46 SQL> exit
|
|
|
47 '''
|
|
|
48
|
|
|
49 def __init__(self):
|
|
|
50 sqlpyPlus.__init__(self)
|
|
|
51 self.maxtselctrows = 10
|
|
|
52 self.query_load10g = '''
|
|
|
53 ins.instance_name,ins.host_name,round(os.value,2) load
|
|
|
54 from gv$osstat os, gv$instance ins
|
|
|
55 where os.inst_id=ins.inst_id and os.stat_name='LOAD'
|
|
|
56 order by 3 desc;
|
|
|
57 '''
|
|
|
58 self.query_top9i = '''
|
|
|
59 sid,username,osuser||'@'||terminal "Server User@terminal",program,taddr, status,
|
|
|
60 module, sql_hash_value hash, fixed_table_sequence seq, last_call_et elaps
|
|
|
61 from v$session
|
|
|
62 where username is not null and program not like 'emagent%' and status='ACTIVE'
|
|
|
63 and audsid !=sys_context('USERENV','SESSIONID');
|
|
|
64 '''
|
|
|
65 self.query_ractop = '''
|
|
|
66 inst_id||'_'||sid inst_sid,username,osuser||'@'||terminal "User@Term",program, decode(taddr,null,null,'NN') tr,
|
|
|
67 sql_id, '.'||mod(fixed_table_sequence,1000) seq, state||': '||event event,
|
|
|
68 case state when 'WAITING' then seconds_in_wait else wait_time end w_tim, last_call_et elaps
|
|
|
69 from gv$session
|
|
|
70 where status='ACTIVE' and username is not null
|
|
|
71 and not (event like '% waiting for messages in the queue' and state='WAITING')
|
|
|
72 and audsid !=sys_context('USERENV','SESSIONID');
|
|
|
73 '''
|
|
|
74 self.query_longops = '''
|
|
|
75 inst_id,sid,username,time_remaining remaining, elapsed_seconds elapsed, sql_hash_value hash, opname,message
|
|
|
76 from gv$session_longops
|
|
|
77 where time_remaining>0;
|
|
|
78 '''
|
|
|
79
|
|
|
80 def do_top9i(self,args):
|
|
|
81 '''Runs query_top9i defined above, to display active sessions in Oracle 9i'''
|
|
|
82 self.do_select(self.query_top9i)
|
|
|
83
|
|
|
84 def do_top(self,args):
|
|
|
85 '''Runs query_ractop defined above, to display active sessions in Oracle 10g (and RAC)'''
|
|
|
86 self.do_select(self.query_ractop)
|
|
|
87
|
|
|
88 def do_longops(self,args):
|
|
|
89 '''Runs query_longops defined above, to display long running operations (full scans, etc)'''
|
|
|
90 self.do_select(self.query_longops)
|
|
|
91
|
|
|
92 def do_load(self,args):
|
|
|
93 '''Runs query_load10g defined above, to display OS load on cluster nodes (10gRAC)'''
|
|
|
94 self.do_select(self.query_load10g)
|
|
|
95
|
|
|
96 def do_db(self,args,filepath='pass.txt'):
|
|
|
97 '''Exec do_connect to db_alias in args (credentials form the file pass.txt) '''
|
|
|
98 f = open(filepath,'r')
|
|
|
99 connectstr = f.readline().strip() +'@'+args
|
|
|
100 self.do_connect(connectstr)
|
|
|
101 f.close()
|
|
|
102
|
|
|
103 def do_py(self, arg):
|
|
|
104 '''Executes a python command'''
|
|
|
105 try:
|
|
|
106 exec(arg)
|
|
|
107 except Exception, e:
|
|
|
108 print e
|
|
|
109
|
|
|
110 def do_tselect(self, arg, rowlimit=None):
|
|
|
111 '''executes a query and prints the result in trasposed form. Useful when querying tables with many columns'''
|
|
|
112 self.query = sqlpython.Statement('select '+arg).query
|
|
|
113 try:
|
|
|
114 print self.query
|
|
|
115 self.curs.execute(self.query)
|
|
|
116 rows = self.curs.fetchmany(min(self.maxtselctrows, rowlimit or self.maxtselctrows))
|
|
|
117 desc = self.curs.description
|
|
|
118 self.rc = self.curs.rowcount
|
|
|
119 rows.insert(0,[desc[x][0] for x in range(len(desc))]) # adds column name to the row set
|
|
|
120 transpr = [[rows[y][x] for y in range(len(rows))]for x in range(len(rows[0]))] # matrix transpose
|
|
|
121 newdesc = [['ROW N.'+str(y),10] for y in range(len(rows))]
|
|
|
122 for x in range(len(desc)):
|
|
|
123 if str(desc[x][1]) == "<type 'cx_Oracle.BINARY'>": # handles RAW columns
|
|
|
124 rname = transpr[x][0]
|
|
|
125 transpr[x] = map(binascii.b2a_hex, transpr[x])
|
|
|
126 transpr[x][0] = rname
|
|
|
127 self.debg=transpr
|
|
|
128 newdesc[0][0] = 'COLUMN NAME'
|
|
|
129 if self.rc > 0:
|
|
|
130 print '\n' + sqlpython.pmatrix(transpr,newdesc)
|
|
|
131 if self.rc == 0:
|
|
|
132 print '\nNo rows Selected.\n'
|
|
|
133 elif self.rc == 1:
|
|
|
134 print '\n1 row selected.\n'
|
|
|
135 elif self.rc < self.maxtselctrows:
|
|
|
136 print '\n%d rows selected.\n' % self.rc
|
|
|
137 else:
|
|
|
138 print '\nSelected Max Num rows (%d)' % self.rc
|
|
|
139 except Exception, e:
|
|
|
140 print e
|
|
|
141
|
|
|
142 def do_sql(self,args):
|
|
|
143 '''prints sql statement give the sql_id (Oracle 10gR2)'''
|
|
|
144 self.query = "select inst_id, sql_fulltext from gv$sqlstats where sql_id='"+args+"'"
|
|
|
145 try:
|
|
|
146 self.curs.execute(self.query)
|
|
|
147 row = self.curs.fetchone()
|
|
|
148 print "\nSQL statement from cache"
|
|
|
149 print "------------------------\n"
|
|
|
150 while row:
|
|
|
151 print "\nINST_ID = "+str(row[0])+" - SQL TEXT:\n", row[1].read()
|
|
|
152 row = self.curs.next()
|
|
|
153 except Exception, e:
|
|
|
154 print e
|
|
|
155
|
|
|
156 def do_explain(self,args):
|
|
|
157 '''prints the plan of a given statement from the sql cache. 1 parameter: sql_id, see also do_sql '''
|
|
|
158 self.query = "select * from table(dbms_xplan.display_cursor('"+args+"'))"
|
|
|
159 try:
|
|
|
160 self.curs.execute(self.query)
|
|
|
161 rows = self.curs.fetchall()
|
|
|
162 desc = self.curs.description
|
|
|
163 self.rc = self.curs.rowcount
|
|
|
164 if self.rc > 0:
|
|
|
165 print '\n' + sqlpython.pmatrix(rows,desc,200)
|
|
|
166 except Exception, e:
|
|
|
167 print e
|
|
|
168
|
|
|
169 def do_sessinfo(self,args):
|
|
|
170 '''Reports session info for the give sid, extended to RAC with gv$'''
|
|
|
171 self.do_tselect('* from gv$session where sid='+args+';')
|
|
|
172
|
|
|
173 def do_sleect(self,args):
|
|
|
174 '''implements sleect = select, a common typo'''
|
|
|
175 self.do_select(args)
|
|
|
176
|
|
|
177 do_slect = do_sleect
|
|
|
178
|
|
|
179 def run():
|
|
|
180 my=mysqlpy()
|
|
|
181 print my.__doc__
|
|
|
182 my.cmdloop()
|
|
|
183
|
|
|
184 if __name__ == '__main__':
|
|
|
185 run()
|
