oracle常用数据统计,办公系统Oracle数据库监控、统计常用SQL语句

在维护生产环境数据库时，如果需要人工修改数据，禁止使用工具锁表式修改数据(例如：Select * from XXX ... forUpdate)，最好使用数据处理脚本，例如在命令行中，一个事务处理完毕(直接提交Commit)。       如下是常用的监控、分析数据的SQL语句。 1、统计表空间语句 select a.a1 表空间名称, c.c2 类型, c.c3 区管理, b.b2/1024/1024 表空间大小M, (b.b2-a.a2)/1024/1024 已使用M, substr((b.b2-a.a2)/b.b2*100,1,5) 利用率 from (select tablespace_name a1,sum(nvl(bytes,0)) a2 from dba_free_spacegroup by tablespace_name) a, (select tablespace_name b1,sum(bytes) b2 from dba_data_files groupby tablespace_name) b, (select tablespace_name c1,contents c2,extent_management c3 fromdba_tablespaces) c where a.a1=b.b1 and c.c1=b.b1; 2、监控关键表 select count(*) from cordys.task_list_wait; select count(*) from cordys.task_list; select count(*) from cordys.workflow_instance_translog; 3、按月统计业务量(每月启动主流程数量) select ny,sum(decode(mo,'公文模块',co))公文模块,sum(decode(mo,'业务流程模块',co)) 业务流程模块,sum(co) 合计 from (select ny,mo,count(*) co from (select to_char(t.start_datetime,'yyyymm') ny,ta.workflow_module mofrom cordys.workflow_instance t,cordys.workflow ta where t.parent_guid is null and t.workflow_id =ta.workflow_id) group by ny,mo) group by ny order by ny 4、查询锁表的方法 SELECT S.MACHINE,S.TERMINAL,S.OSUSER,S.SID SESSION_ID,S.USERNAME,             DECODE(LMODE, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X(SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive',TO_CHAR(LMODE)) MODE_HELD,             DECODE(REQUEST, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X(SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive',TO_CHAR(REQUEST)) MODE_REQUESTED,             O.OWNER||'.'||O.OBJECT_NAME||' ('||O.OBJECT_TYPE||')', S.TYPELOCK_TYPE, L.ID1 LOCK_ID1, L.ID2 LOCK_ID2 FROM V$LOCK L, SYS.DBA_OBJECTS O, V$SESSION S WHERE L.SID = S.SID AND L.ID1 = O.OBJECT_ID and S.USERNAME IS NOTNULL; 5、按日统计处理业务量(文件-含业务流程) select rq,sum(decode(lb,'公文模块',sl,0)) as公文,sum(decode(lb,'业务流程模块',sl,0)) as 业务流程, sum(decode(lb,'发文传阅',sl,0)) as 发文传阅,sum(decode(lb,'公告',sl,0)) as公告, sum(decode(lb,'通用办公模块',sl,0)) as 通用办公模块,sum(sl) as 合计 from ( select to_char(a.create_date, 'yyyy-mm-dd') as rq,'发文传阅' aslb,count(*) as sl   from doc_packing_issue_adv  a   where a.states in ('1','3','5')   group byto_char(a.create_date,'yyyy-mm-dd'),'发文传阅' union   select to_char(t.advice_time, 'yyyy-mm-dd') asrq,'公告'as lb, count(*) as sl   from ofc_advice t,ofc_advice_incept a   where   t.PK_ADVICE_ID=a.PK_ADVICE_ID   group by to_char(t.advice_time,'yyyy-mm-dd'),'公告' union select to_char(t.end_datetime,'yyyy-mm-dd') as rq,t.workflow_moduleas lb,count(*) as sl from cordys.task_list t group byto_char(t.end_datetime,'yyyy-mm-dd'),t.workflow_module union select to_char(t.end_datetime,'yyyy-mm-dd') as rq,t.workflow_moduleas lb,count(*) as sl from cordys.task_list_finish t group byto_char(t.end_datetime,'yyyy-mm-dd'),t.workflow_module ) where rq>'2011-09-30' group by rq order by rq 6、查询SQL语句执行计划     通过此语句获取执行效率低的SQL执行计划。 select substr(s.SQL_TEXT, 0,700) ,count(*) as aa from v$sql s groupby substr(s.SQL_TEXT, 0,700) order by aa desc; selectt.SQL_TEXT,t.SQL_FULLTEXT,t.SHARABLE_MEM,t.PERSISTENT_MEM,t.RUNTIME_MEM from v$sql t where t.PARSING_SCHEMA_NAME = 'OA_OA' and to_char(t.LAST_ACTIVE_TIME,'yyyy-mm-dd')='2013-03-01' order byt.CPU_TIME desc 7、数据占用内存情况 select p.spid,s.sid,pga_max_mem from v$process p ,v$session s wheres.paddr=p.addr and s.username='CORDYS' order by pga_max_mem; selects.USERNAME,s.MACHINE,s.LOGON_TIME,s.SCHEMANAME,s.sid,pga_max_mem/1024/1024,p.PGA_USED_MEM/1024/1024from v$process p ,v$session s where s.paddr=p.addr order bypga_max_mem;