- 浏览: 668120 次
- 性别:
- 来自: 中山
文章分类
最新评论
-
wuhuizhong:
jFinal支持Rest风格吗?可以想spring mvc那样 ...
在JFinal的Controller中接收json数据 -
wuhuizhong:
在jfinal中应如何获取前端ajax提交的Json数据?ht ...
在JFinal的Controller中接收json数据 -
wuhuizhong:
jfinal如何处理json请求的数据:问题: 在某些api接 ...
在JFinal的Controller中接收json数据 -
wuhuizhong:
Ubuntu14.04 安装 Oracle 11g R2 Ex ...
Oracle 11g release 2 XE on Ubuntu 14.04 -
alanljj:
这个很实用,已成功更新,谢过了!
odoo薪酬管理模块l10n_cn_hr_payroll
1.創建PACKAGE:
CREATE OR REPLACE PACKAGE statspack_admin AS PROCEDURE purge_older_than_days(days IN INTEGER, area_size IN INTEGER DEFAULT NULL); /* -- submit a job to run every day at 3am deleting snaps older than 30 days -- specifying 50Mb for PGA. DECLARE j BINARY_INTEGER; BEGIN DBMS_JOB.SUBMIT(j, 'statspack_admin.purge_older_than_days(30, 52428800);', TRUNC(SYSDATE) (3/24), 'TRUNC(SYSDATE) 1 (3/24)' ); COMMIT; END; */ PROCEDURE purge(lo_snap IN NUMBER, hi_snap IN NUMBER, area_size IN INTEGER DEFAULT NULL); PROCEDURE move_tablespace(tablespace_name IN VARCHAR2, window_in_hours IN NUMBER); END statspack_admin; / CREATE OR REPLACE PACKAGE BODY statspack_admin AS on_9i BOOLEAN := FALSE; dbversion VARCHAR2(512); dbcompat VARCHAR2(512); PROCEDURE purge(lo_snap IN NUMBER, hi_snap IN NUMBER, area_size IN INTEGER DEFAULT NULL) IS dbid v$database.dbid%TYPE; inst_num v$instance.instance_number%TYPE; inst_name v$instance.instance_name%TYPE; db_name v$database.name%TYPE; btime DATE; etime DATE; BEGIN SELECT d.dbid AS dbid, i.instance_number AS inst_num, i.instance_name AS inst_name, d.name AS db_name INTO dbid, inst_num, inst_name, db_name FROM v$database d, v$instance i; select snap_time into btime from stats$snapshot b where b.snap_id = (SELECT MIN(x.snap_id) FROM stats$snapshot x WHERE x.snap_id >= lo_snap) and b.dbid = dbid and b.instance_number = inst_num; select snap_time into etime from stats$snapshot e where e.snap_id = (SELECT MAX(x.snap_id) FROM stats$snapshot x WHERE x.snap_id <= hi_snap) and e.dbid = dbid and e.instance_number = inst_num; IF on_9i AND area_size IS NOT NULL THEN EXECUTE IMMEDIATE 'alter session set workarea_size_policy=MANUAL'; EXECUTE IMMEDIATE 'alter session set hash_area_size=' || TO_CHAR(area_size); EXECUTE IMMEDIATE 'alter session set sort_area_size=' || TO_CHAR(area_size); ELSIF area_size IS NOT NULL THEN EXECUTE IMMEDIATE 'alter session set hash_area_size=' || TO_CHAR(area_size); EXECUTE IMMEDIATE 'alter session set sort_area_size=' || TO_CHAR(area_size); END IF; delete from stats$snapshot where instance_number = inst_num and dbid = dbid and snap_id between lo_snap and hi_snap; /*-- Delete any dangling SQLtext -- The following statement deletes any dangling SQL statements which -- are no longer referred to by ANY snapshots. This statment has been -- commented out as it can be very resource intensive. --*/ delete -- index_ffs(st) from stats$sqltext st where (hash_value, text_subset) not in (select -- hash_aj full(ss) no_expand hash_value, text_subset from stats$sql_summary ss where ((snap_id < lo_snap or snap_id > hi_snap) and dbid = dbid and instance_number = inst_num) or (dbid != dbid or instance_number != inst_num)); -- Adding an optional STATS$SEG_STAT_OBJ delete statement delete -- index_ffs(sso) from stats$seg_stat_obj sso where (dbid, dataobj#, obj#) not in (select -- hash_aj full(ss) no_expand dbid, dataobj#, obj# from stats$seg_stat ss where ((snap_id < lo_snap or snap_id > hi_snap) and dbid = dbid and instance_number = inst_num) or (dbid != dbid or instance_number != inst_num)); /* Delete any undostat rows that cover the snap times */ delete from stats$undostat us where dbid = dbid and instance_number = inst_num and begin_time < btime and end_time > etime; /* Delete any dangling database nstance rows for that startup time */ delete from stats$database_instance di where instance_number = inst_num and dbid = dbid and not exists (select 1 from stats$snapshot s where s.dbid = di.dbid and s.instance_number = di.instance_number and s.startup_time = di.startup_time); /* Delete any dangling statspack parameter rows for the database instance */ delete from stats$statspack_parameter sp where instance_number = inst_num and dbid = dbid and not exists (select 1 from stats$snapshot s where s.dbid = sp.dbid and s.instance_number = sp.instance_number); COMMIT; END purge; /* procedure to move tablespaces */ PROCEDURE move_tablespace(tablespace_name IN VARCHAR2, window_in_hours IN NUMBER) IS ts DATE := SYSDATE; te DATE := ts(window_in_hours / 24); BEGIN -- do the tables that haven't been rebuilt recently first FOR t IN (SELECT object_name AS table_name FROM user_objects WHERE object_type = 'TABLE' ORDER BY last_ddl_time ASC) LOOP EXECUTE IMMEDIATE 'ALTER TABLE ' || t.table_name || ' MOVE TABLESPACE ' || tablespace_name; -- now immediately rebuild the indexes -- I could use dbms_job to do this -- asynchronously, maybe in a future revision ? -- not using ONLINE because moving -- tables cannot be done ONLINE ! -- maybe use dbms_redef in future version? FOR i IN (SELECT index_name FROM user_indexes WHERE table_name = t.table_name) LOOP EXECUTE IMMEDIATE 'ALTER INDEX ' || i.index_name || ' REBUILD TABLESPACE ' || tablespace_name; END LOOP; IF SYSDATE > te THEN EXIT; END IF; END LOOP; EXCEPTION WHEN OTHERS THEN IF dbms_job.is_jobq THEN /* remove the job preventing nasty reruns outside the window but generating a trace file for the DBA */ DBMS_JOB.REMOVE(NVL(SYS_CONTEXT('USERENV', 'BG_JOB_ID'), SYS_CONTEXT('USERENV', 'FG_JOB_ID'))); COMMIT; END IF; RAISE; END move_tablespace; /* purge records older than X days */ PROCEDURE purge_older_than_days(days IN INTEGER, area_size IN INTEGER DEFAULT NULL) IS losnap stats$snapshot.SNAP_ID%TYPE; hisnap stats$snapshot.SNAP_ID%TYPE; BEGIN SELECT s.snap_id INTO hisnap FROM stats$snapshot s WHERE s.snap_id = (SELECT MAX(s.snap_id) FROM stats$snapshot s WHERE s.snap_time < TRUNC(SYSDATE) - days); SELECT s.snap_id INTO losnap FROM stats$snapshot s WHERE s.snap_id = (SELECT MIN(s.snap_id) FROM stats$snapshot s WHERE s.snap_id <= hisnap); -- in case the highest snap is the only one to delete --DBMS_OUTPUT.PUT_LINE('Lo Snap: '||TO_CHAR(losnap)); --DBMS_OUTPUT.PUT_LINE('Hi Snap: '||TO_CHAR(hisnap)); purge(losnap, hisnap, area_size); EXCEPTION WHEN NO_DATA_FOUND THEN NULL; -- select into hisnap returned no rows, --which means no snaps are older than days specified END purge_older_than_days; BEGIN SYS.DBMS_UTILITY.DB_VERSION(dbversion, dbcompat); IF TO_NUMBER(REPLACE(dbversion, '.')) >= 90000 THEN -- we are on a 9i DB on_9i := TRUE; ELSE on_9i := FALSE; END IF; EXCEPTION WHEN OTHERS THEN on_9i := FALSE; END statspack_admin; /
2.排程:
DECLARE j BINARY_INTEGER; BEGIN DBMS_JOB.SUBMIT(j, 'statspack_admin.purge_older_than_days (30, 52428800);', TRUNC(SYSDATE) (3 / 24), 'TRUNC(SYSDATE) 1 (3/24)'); COMMIT; END;
发表评论
-
用函数unistr将Oracle数据库中的Unicode转换为中文
2016-07-19 11:51 7875例子: DECLARE V_EXT_DES V ... -
ORACLE APPLICATION EXPRESS 5.0 升级
2016-05-12 11:43 540Oracle11GR2 XE 缺省是安装了oracle ap ... -
Oracle ACL(Access Control List)
2016-05-12 11:36 846在oralce 11g中假如你想获取server的ip或者h ... -
了解systemstate dump
2016-04-26 14:09 457当数据库出现严重的性能问题或者hang了的时候,我们非常需要 ... -
通过ORACLE的UTL_HTTP工具包发送包含POST参数的请求
2016-03-18 16:25 5081DECLARE req utl_http. ... -
Shell: extract more from listener.log(分析监听日志)
2016-03-16 14:57 1110统计一天内每小时的session请求数 # fgrep ... -
ORA-01031: insufficient privileges 问题解决笔记
2016-02-01 15:53 1152A) File $Oracle_HOME/network/a ... -
listener.log中报Warning: Subscription For Node Down Event Still Pending问题的解决方法
2016-01-07 16:34 1603一套Oracle 10.2.0.1 for aix的数据库环 ... -
Oracle触发器和MySQL触发器之间的区别
2015-11-19 12:55 617Oracle触发器格式: CREATE [OR RE ... -
查询正在执行的存储过程
2015-11-13 09:27 19911、找正在执行的PROCEDURE的 sid ,serial# ... -
undo表空间损坏的处理过程
2015-10-14 13:49 1187磁碟陣列故障,分區/rman上包括undo和archivel ... -
登录oracle资料库时很久无反应的问题处理一例
2015-10-11 10:56 963原因是系统存在僵死的进程,促使session处于激活状态.首 ... -
TNS-12560问题解决
2015-10-01 19:52 581tnsping远程主机实例出现TNS-12560: TNS ... -
查看undo中sql语句的占用情况
2015-08-06 17:18 1706查看undo中sql语句的占用情况 select * ... -
Install Open System Architect And ODBC Instant Client
2015-05-21 14:03 717How to Install Open System Arc ... -
恢复oracle中用pl sql误删除drop掉的表
2015-04-03 16:12 527查看回收站中表 select object_name,or ... -
在Oracle Linux 6.6上安装Oracle 10gR2
2015-01-15 15:36 2602查看硬體配置 # df -h Filesystem ... -
kill
2015-01-03 11:36 428--根据某一对象查询进程 col owner fo ... -
Oracle 数据库Storage存储迁移笔记
2014-12-27 11:08 9431.确认数据文件、控制文件、临时文件、日志文件 位置 / ... -
異地備份資料庫的開啟步驟
2014-11-19 14:03 450使用EMC設備執行異地備份, 資料庫的複製是開啟的狀態下, ...
相关推荐
statspack statspack statspack statspack
statspack完整使用指南: 包括: statspack的安装; statspack的自动数据收集; statspack的门限调整; statspack的报表自动产生与邮件发送(aix环境下); statspack的报表详细解析.
statspack安装与分析
statspack Oracle数据库下载
statspack安装
一个电信数据库的STATSPACK分析 .
Statspack工具
Oracle提供的statspack是一个很强大的工具,通过Statspack,可以 收集系统信息,诊断数据库故障、顺利确定Oracle数据库的瓶颈所在,记录数据库性能状态。
细化解析oracle 10g statspack 细化解析oracle 10g statspack
你采集了oracle的statspack,但是你对statspack全面细致地了解吗,这个报告详细解释了相关参数和该注意的地方.
详细解读 STATSPACK 报告
Statspack报告如何分析和生产等,主要是怎么利用该工具去分析性能瓶颈
Statspack是一款功能强大的,免费的,oracle自带的性能分析工具。需要用具有sysdba权限的用户登陆进行安装。
找了好久的书,终于找到了,发上来共享下。Oracle STATSPACK高性能调整技术 。
主要从STATSPACK 的3个方面去分析oracle数据库性能的问题
statspack大家都有用吧. 哪session级别的呢? 有用过吗?
Oracle STATSPACK高性能调整技术
详细介绍oracle 9i statspack的安装与使用
详细解读 STATSPACK 报告详细解读 STATSPACK 报告详细解读 STATSPACK 报告详细解读 STATSPACK 报告详细解读 STATSPACK 报告详细解读 STATSPACK 报告详细解读 STATSPACK 报告