oracle_statpack

1.check init.ora
job_queue_processes >0
timed_statistics =true

2.step by step
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
conn / as sysdba;

查看数据文件的目录
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/erp/oratest/erptestdata/system09.dbf
/erp/oratest/erptestdata/system08.dbf
/erp/oratest/erptestdata/system07.dbf
..............

N Row selected

建立statpack所需表空间
SQL> create tablespace perfstat
2 datafile '/erp/oratest/erptestdata/perfstat.dbf'
3 size 1024M
4 extent management local;

Tablespace created.

SQL> @spcreate

提示输入用户perfstat的密码
input password:perfstat

提示输入指定默认表空间:
Specify PERFSTAT user's default tablespace
Enter value for default_tablespace: perfstat

提示输入指定临时表空间:
Specify PERFSTAT user's temporary tablespace.
Enter value for temporary_tablespace: temp

最后提示:
No errors.
NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.

如果失败则执行
SQL> @spdrop.sql
再执行，重建
SQL> @spcreate

测试成功与否,可以
SQL> exec statspack.snap
SQL> exec statspack.snap
SQL> @spreport.sql

Enter value for begin_snap:1
Enter value for end_snap: 2
Enter value for report_name: 2007022701.log

SQL> @spauto.sql
修改时间可以改spauto.sql中的值

可以得到jobno
SQL> select * from user_jobs;

SQL> select * from dba_jobs;
移除用
SQL> exec dbms_job.remove(&jobno);

删除历史记录:
select max(snap_id) from stats$snapshot;
delete from stats$snapshot where snap_id<=&snapid;

清空还可以用:
SQL> @spturnc.sql

ice.xie个人原创，转载请说明出处！