oracle 运维

--查询数据库中非空闲等待会话
SELECT inst_id, --实例
       sid, --会话id
       serial#,
       sql_id, --sqlid
       event, --等待事件
       wait_class, --等待类型
       status, --会话状态
       username, --用户名
       osuser, --操作系统用户
       machine, --主机名
       program, --会话连接方式
       module,
       sql_exec_start, --sql执行开始时间
       last_call_et, --sql运行时间
       sess.BLOCKING_INSTANCE, --阻塞该会话的实例
       sess.BLOCKING_SESSION   --阻塞该会话的会话
  FROM gv$session sess
 WHERE wait_class != 'Idle'
 order by last_call_et;
 
关于等待事件类型是IO和Cluster就是在查询数据中，慢就要优化sql，Application，Commit，Configuration，看具体等待事件可能是锁，日志切换，内存等问题。

 
--查询数据库表锁
 select sess.inst_id, --实例id
       sess.sid,     --会话id
       sess.serial#,
       lo.oracle_username, --用户名
       lo.os_user_name,    --操作系统用户名
       ao.object_name,     --被锁的数据库对象
       lo.locked_mode      --锁的模式
  from gv$locked_object lo, dba_objects ao, gv$session sess
where ao.object_id = lo.object_id
   and lo.session_id = sess.SID
   and lo.inst_id = sess.inst_id


select * from v$sql where sql_id='b1gxkjju05a36'; --根据sqlid查看sql文本

alter system kill session 'sdi,serial#';  --杀掉会话，长事务需要等待事务回滚


SELECT /*+ NO_MERGE(D) NO_MERGE(A) NO_MERGE(F) NO_MERGE(U) NO_MERGE(O) */ D.TABLESPACE_NAME "表空间名", D.BLOCK_SIZE/1024 "块大小(KB)",
       D.INITIAL_EXTENT/1024 "初始分配大小(KB)",ROUND(NVL(A.BYTES /1024 /1024,0) ,2) "大小(MB)",
       ROUND(DECODE(D.CONTENTS, 'UNDO', NVL(U.BYTES, 0) / 1024 / 1024,NVL(A.BYTES - NVL(F.BYTES, 0), 0) / 1024 / 1024) ,2) "占用量(MB)",
       TO_CHAR(ROUND(DECODE(D.CONTENTS, 'UNDO', NVL(U.BYTES / A.BYTES * 100, 0),NVL((A.BYTES - NVL(F.BYTES, 0)) / A.BYTES * 100, 0)) ,2),'999.99')||'%' "占用率(MB)",
       ROUND(DECODE(D.CONTENTS, 'UNDO', NVL(A.BYTES - NVL(U.BYTES, 0), 0) / 1024 / 1024,NVL(F.BYTES, 0) / 1024 / 1024) ,2) "空闲空间(MB)",
       D.STATUS "状态",A.AUTOEXTENSIBLE "是否自动扩展",D.LOGGING "是否记录日志", A.COUNT "数据文件", D.CONTENTS "类型",
       D.EXTENT_MANAGEMENT "区管理", D.SEGMENT_SPACE_MANAGEMENT "段管理"
  FROM SYS.DBA_TABLESPACES D,
       (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES, COUNT(FILE_ID) COUNT,
               CASE WHEN SUM(DECODE(AUTOEXTENSIBLE,'YES',10000,'NO',1,AUTOEXTENSIBLE))>=10000 THEN 'YES' ELSE 'NO' END||
               CASE WHEN MOD(SUM(DECODE(AUTOEXTENSIBLE,'YES',10000,'NO',1,AUTOEXTENSIBLE)),10000)>0 THEN CHR(13)||CHR(38)||CHR(13)||'NO'
               ELSE '' END AS AUTOEXTENSIBLE
          FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A,
       (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F,
       (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES
         FROM DBA_UNDO_EXTENTS
         WHERE STATUS IN ('UNEXPIRED', 'EXPIRED')
         GROUP BY TABLESPACE_NAME) U
  WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME(+) AND D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
    AND D.TABLESPACE_NAME = U.TABLESPACE_NAME(+) AND NOT (D.EXTENT_MANAGEMENT = 'LOCAL' AND D.CONTENTS = 'TEMPORARY')
    --AND D.TABLESPACE_NAME LIKE '%COMP%'
UNION ALL
SELECT /*+ NO_MERGE(D) NO_MERGE(A) NO_MERGE(T) */ D.TABLESPACE_NAME "表空间名", D.BLOCK_SIZE/1024 "块大小(KB)",
       D.INITIAL_EXTENT/1024 "初始分配大小(KB)",ROUND(NVL(A.BYTES /1024 /1024,0) ,2) "大小(MB)",
       ROUND(NVL(T.BYTES, 0) / 1024 / 1024 ,2) "占用量(MB)",
       TO_CHAR(ROUND(NVL(T.BYTES / A.BYTES * 100, 0) ,2),'999.99')||'%' "占用率(MB)",
       ROUND((NVL(A.BYTES, 0) / 1024 / 1024 - NVL(T.BYTES, 0) / 1024 / 1024) ,2) "空闲空间(MB)",
       D.STATUS "状态",A.AUTOEXTENSIBLE "是否自动扩展",D.LOGGING "是否记录日志", A.COUNT "数据文件", D.CONTENTS "类型",
       D.EXTENT_MANAGEMENT "区管理", D.SEGMENT_SPACE_MANAGEMENT "段管理"
  FROM SYS.DBA_TABLESPACES D,
       (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES, COUNT(FILE_ID) COUNT,
               CASE WHEN SUM(DECODE(AUTOEXTENSIBLE,'YES',10000,'NO',1,AUTOEXTENSIBLE))>=10000 THEN 'YES' ELSE 'NO' END||
               CASE WHEN MOD(SUM(DECODE(AUTOEXTENSIBLE,'YES',10000,'NO',1,AUTOEXTENSIBLE)),10000)>0 THEN CHR(13)||CHR(38)||CHR(13)||'NO'
               ELSE '' END AS AUTOEXTENSIBLE
          FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) A,
       (SELECT SS.TABLESPACE_NAME, SUM((SS.USED_BLOCKS * TS.BLOCKSIZE)) BYTES
         FROM GV$SORT_SEGMENT SS, SYS.TS$ TS
         WHERE SS.TABLESPACE_NAME = TS.NAME
         GROUP BY SS.TABLESPACE_NAME) T
  WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME(+) AND D.TABLESPACE_NAME = T.TABLESPACE_NAME(+)
    AND D.EXTENT_MANAGEMENT = 'LOCAL'
    AND D.CONTENTS = 'TEMPORARY'
  --AND D.TABLESPACE_NAME LIKE :2
ORDER BY 1 ASC,6 DESC;