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

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

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

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)

---