Z.S.K.'s Records

python中调用sqlplus生成多实例oracle-awr-html报告

这里不多说,说是稍微改了下官方的脚本,这里使用了subprocess调用sqlplus,关于subprocess,可以参考这里

其中,包含了两个业务相关的参数,这里就不过多说明…

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
def gen_sqlfile(_file):
with open(_file,"w") as f:
f.write(
"""set define off
set feedback off
set termout off
set pages 0
set lines 120
set serveroutput on
spool oracle_awr_html.sql
declare
v_str varchar2(180);
procedure p (l_str varchar2)
is
begin
dbms_output.put_line(l_str);
end;
begin
p('set pages 0');
p('variable dbid number');
p('variable inst_num number');
p('variable inst_name varchar2(20)');
p('variable bid number');
p('variable eid number');
p('variable rpt_options number');
p('variable ENABLE_ADDM number');
p('variable cur_date varchar2(20)');
p('variable city varchar2(20)');
p('');
p('declare');
p('begin');
p('select distinct first_value (snap_id) over( ');
p(' order by snap_id desc rows between unbounded preceding and unbounded following) max_snap_id ');
p('into :eid ');
p(' from dba_hist_snapshot;');
p('select dbid into :dbid from dba_hist_database_instance where rownum=1;');
p('end;');
p('/');

p('exec :rpt_options :=0;');
p('exec :ENABLE_ADDM :=8;');
p('exec :cur_date :=to_char(sysdate,''yyyymmdd'');');
p('exec :city :=''&1'';');
p('exec :bid :=:eid-to_number(''&2'');');
p('column rpt_name new_value rpt_name noprint;');

for c1 in (select instance_number,instance_name from gv$instance order by instance_number)
loop
p('exec :inst_num :='||c1.instance_number||';');
p('exec :inst_name :='''||c1.instance_name||''';');
v_str:= q'[select :cur_date||'_'||:city||'_'||:inst_name||'_'||:inst_num||'_'||:bid||'_'||:eid||'.html' rpt_name from dual;]';
p(v_str);
p('set lines 8000');
p('spool &rpt_name');
p(' select output from table(sys.dbms_workload_repository.awr_report_html( :dbid,');
p(' :inst_num,');
p(' :bid, :eid,');
p(' :rpt_options ));');
p('spool off');
v_str := q'[select :cur_date||'_'||:city||'_'||:inst_name||'_'||:inst_num||'_'||:bid||'_'||:eid||'.html' file_name from dual;]';
p(v_str);
p('set termout off');
end loop;
p('exit');
end;
/
spool off
set define on
set feedback off
set termout on
set termout off
@oracle_awr_html.sql &1 &2
set termout on
set pagesize 24
""")

class callsqluldr():
def __init__(self,username,password,databaseName,city,beforethis):
self.username = username
self.password = password
self.databaseName = databaseName
self.city = city
self.beforethis = beforethis
def run(self):
try:
command = "sqlplus " + self.username + "/" + self.password + "@" + self.databaseName + " @gen_oracle_awr_html.sql " + self.city + " " + self.beforethis
child = subprocess.Popen(command,shell=True)
child.wait()
#print("<<%s done.>>" %self.city)
except Exception as e:
err_txt = "ERROR: CALL ORACLE SQLPLUS ERROR!"
print(err_txt)
write_errtxt(err_txt)

def write_errtxt(err_txt):
pass

参考文章:

转载请注明原作者: 周淑科(https://izsk.me)


 wechat
Scan Me To Read on Phone
I know you won't do this,but what if you did?