stackpack

最新推荐文章于 2024-05-20 19:10:28 发布

weixin_34233679

最新推荐文章于 2024-05-20 19:10:28 发布

阅读量155

点赞数

文章标签：数据库网络 php

原文链接：https://my.oschina.net/l1z2g9/blog/332814

版权

为什么80%的码农都做不了架构师？>>>

Get all snapshot

select * from STATS$snapshot;

Check database instance

select * from STATS$DATABASE_INSTANCE; -- in case database restarted, an error will be thrown as ORA-20200: The instance was shutdown between snapshots 981 and 982

select * from v$instance;

Run stackpack

sqlplus perfstat/password@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xxrac)))"

@$ORACLE_HOME/rdbms/admin/spreport.sql

@?/rdbms/admin/spreport.sql

Purge stackpack

@$ORACLE_HOME/rdbms/admin/sppurge.sql

@?/rdbms/admin/sppurge.sql

Oracle jobs

select * from user_jobs;
SELECT job, next_date, next_sec, failures, broken, SUBSTR(what,1,40) DESCRIPTION FROM dba_jobs;
EXEC dbms_job.broken(24, false); EXEC dbms_job.run(24, false); -- reset job when failure 16 attemps to run

Oracle job for schedule run sppurge

<pre> ---Check the snap present before 20 days---- select count(*) from stats$snapshot where snap_time < sysdate-20 ----Create the procedure which will purge statspack snapshot after 20 days gap--------- create or replace procedure statspackpurge is var_lo_snap number; var_hi_snap number; var_db_id number; var_instance_no number; noofsnapshot number; n_count number ; begin n_count := 0; select count(*) into n_count from stats$snapshot where snap_time < sysdate-20; if n_count > 0 then select min(s.snap_id) , max(s.snap_id),max(di.dbid),max(di.instance_number) into var_lo_snap, var_hi_snap,var_db_id,var_instance_no from stats$snapshot s , stats$database_instance di where s.dbid = di.dbid and s.instance_number = di.instance_number and di.startup_time = s.startup_time and s.snap_time < sysdate-20; noofsnapshot := statspack.purge( i_begin_snap => var_lo_snap , i_end_snap => var_hi_snap , i_snap_range => true , i_extended_purge => false , i_dbid => var_db_id , i_instance_number => var_instance_no); dbms_output.Put_line('snapshot deleted'||to_char(noofsnapshot)); end if; end; / ----check the job already exits in the database------------------------ SELECT job, next_date, next_sec, failures, broken, SUBSTR(what,1,40) DESCRIPTION FROM dba_jobs; ------scheduled the job at mid night---------------------- declare my_job number; begin dbms_job.submit(job => my_job, what => 'statspackpurge;', next_date => trunc(sysdate)+1, interval => 'trunc(sysdate)+1'); end; / -------------- Another way---------------------------------------------------------- variable v_jobno number; begin dbms_job.submit(:v_jobno,'statspack.purge(i_num_days=>15,i_extended_purge=>TRUE);',trunc(sysdate)+1+2/24,'SYSDATE',TRUE); commit; end; / </pre>