安装statspcak
SQL> create tablespace STATSPACK datafile '/oracle9idata/MGSSFCS/statspack01.dbf' size 500M;
Tablespace created.
sql>@$ORACLE_HOME/rdbms/admin/spcreate --安装
删除:
sql>@$ORACLE_HOME/rdbms/admin/spdrop
SQL> exec statspack.snap;
BEGIN statspack.snap; END;
BEGIN statspack.snap; END;
*
ERROR at line 1:
ORA-01401: inserted value too large for column
ORA-06512: at "PERFSTAT.STATSPACK", line 1148
ORA-06512: at "PERFSTAT.STATSPACK", line 2134
ORA-06512: at "PERFSTAT.STATSPACK", line 72
ORA-06512: at line 1
ERROR at line 1:
ORA-01401: inserted value too large for column
ORA-06512: at "PERFSTAT.STATSPACK", line 1148
ORA-06512: at "PERFSTAT.STATSPACK", line 2134
ORA-06512: at "PERFSTAT.STATSPACK", line 72
ORA-06512: at line 1
这个问题只会出现在多位的字符集, 需要修改spcpkg.sql 脚本,$ORACLE_HOME/rdbms/admin/spcpkg.sql,将"substr" 修改为 "substrb",然后重新运行该脚本。
sql>@$ORACLE_HOME/rdbms/admin/spcpkg.sql
收集信息
SQL> exec statspack.snap;
PL/SQL procedure successfully completed.
生成报告
sql>@$ORACLE_HOME/rdbms/admin/spreport
利用计划任务自动采集数据
sql>host vi $ORACLE_HOME/rdbms/admin/spauto.sql
begin
select instance_number into :instno from v$instance;
dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH
'')', TRUE, :instno);
commit;
end;
select instance_number into :instno from v$instance;
dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH
'')', TRUE, :instno);
commit;
end;
启动计划 任务
sql>@$ORACLE_HOME/rdbms/admin/spauto.sql
sql>select * from user_jobs;
sql>select * from stats$snapshot; --查计划任务及执行结果
sql>@spreport
sql>exec dbms_job.remove('$JOB')
删除数据
SQL
>
delete
from
stats$snapshot
where
snap_id
<
=
166
;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24849178/viewspace-745224/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24849178/viewspace-745224/