文档章节

stackpack

querychinesesto
 querychinesesto
发布于 2014/10/16 12:13
字数 343
阅读 108
收藏 0
  • 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

OR

@?/rdbms/admin/spreport.sql

  • Purge stackpack

@$ORACLE_HOME/rdbms/admin/sppurge.sql

OR

@?/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>

  • Reference

Stackpack

http://www.oracle-base.com/articles/8i/statspack-8i.php

http://docs.oracle.com/cd/B10500_01/server.920/a96533/statspac.htm

Oracle job

http://www.orafaq.com/wiki/DBMS_JOB

http://www.dba-oracle.com/tips_oracle_statspack_purge_utility.htm

http://myoracleworld.hobby-electronics.net/DB-statspack.html

http://dbaworks-sunny.blogspot.hk/2012/12/statspack-purge-script-for-scheduling.html

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_job.htm#i1000769

© 著作权归作者所有

querychinesesto
粉丝 7
博文 122
码字总数 37779
作品 0
广州
程序员
私信 提问
加载中

评论(0)

【Oracle】-【AWR/Stackpack】-AWR(Stackpack)执行权限

Oracle 9i执行Stackpack(10g是AWR),当前是一个普通账户,没有DBA权限。 SQL> @awrrpt.sql Current Instance v$instance i * ERROR at line 6: ORA-00942: table or view does not exist Sp......

bisal
2013/08/13
0
0
杭州阿里金融招聘

阿里贷款车间 岗位名称:资信调查专员 需求人数:11 级别:P5/P6 岗位职责: 1、负责通过视频及电话等形式收集真实、客观、有效的客户数据及信息并加以分析核实; 2、依据远程沟通与访谈的结...

lisiyan
2011/05/30
3K
8

没有更多内容

加载失败,请刷新页面

加载更多

没有更多内容

Java BigDecimal 如何去掉末尾多余的 0

Java BigDecimal 如何去掉末尾多余的 0 呢? /** * To StripTrailingZeros */ @Test public void bigDecimalStripTrailingZerosTest() { BigDecimal bi......

honeymoose
今天
43
0
齐齐哈尔哪里可以开发票-中国新闻网

齐齐哈尔哪里可以开发票【1.3.2 - 2.9.3.0 - 0.5.6.8.】李生,adb的全称为Android Debug Bridge,是Android手机通用的一个USB端口。百度CarLife的部分车机采用了该...

8315581
今天
79
0
衡水哪里可以开发票-中国新闻网

衡水哪里可以开发票【1.3.2 - 2.9.3.0 - 0.5.6.8.】李生,adb的全称为Android Debug Bridge,是Android手机通用的一个USB端口。百度CarLife的部分车机采用了该种连...

457192
今天
60
0
张家口哪里可以开发票-中国新闻网

张家口哪里可以开发票【1.3.2 - 2.9.3.0 - 0.5.6.8.】李生,adb的全称为Android Debug Bridge,是Android手机通用的一个USB端口。百度CarLife的部分车机采用了该种...

253878
今天
66
0
黑龙江哪里可以开发票-中国新闻网

黑龙江哪里可以开发票【1.3.2 - 2.9.3.0 - 0.5.6.8.】李生,adb的全称为Android Debug Bridge,是Android手机通用的一个USB端口。百度CarLife的部分车机采用了该种...

富大范
今天
39
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部