oracle数据库表空间，CPU过高问题排查sql记录

排查表空间占用多少

SELECT a.tablespace_name "表空间名",
       a.bytes / 1024 / 1024 "表空间大小(M)",
       (a.bytes - b.bytes) / 1024 / 1024 "已使用空间(M)",
       b.bytes / 1024 / 1024 "空闲空间(M)",
       round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "使用比"
  FROM (SELECT tablespace_name, sum(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) a,
       (SELECT tablespace_name, sum(bytes) bytes, max(bytes) largest
          FROM dba_free_space
         GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name
 ORDER BY ((a.bytes - b.bytes) / a.bytes) DESC;

查看对应的表空间所使用的文件路径

SELECT * FROM dba_data_files t WHERE t.tablespace_name = 'XXXX';

拓展表空间

1. 第一种

alter database datafile '路径XXXXX.dbf' resize 200000M;

2. 第二种

alter tablespace DATA_YQ12345 add datafile '路径XXXXX.dbf' size 1G autoextend on next 100M;

oracle中查询被锁的表并释放session

SELECT A.OWNER,
       A.OBJECT_NAME,
       B.XIDUSN,
       B.XIDSLOT,
       B.XIDSQN,
       B.SESSION_ID,
       B.ORACLE_USERNAME,
       B.OS_USER_NAME,
       B.PROCESS,
       B.LOCKED_MODE,
       C.MACHINE,
       C.STATUS,
       C.SERVER,
       C.SID,
       C.SERIAL#,
       C.PROGRAM
  FROM ALL_OBJECTS A, V$LOCKED_OBJECT B, SYS.GV_$SESSION C
 WHERE (A.OBJECT_ID = B.OBJECT_ID)
   AND (B.PROCESS = C.PROCESS)
 ORDER BY 1, 2

释放session Sql:

--alter system kill session 'sid, serial#'
alter system kill session '379, 21132'

找出耗cpu较多的session

select a.username,
       a.sid,
       spid,
       status,
       substr(a.program, 1, 40) prog,
       a.terminal,
       osuser,
       value / 60 / 100 value
  from v$session a, v$process b, v$sesstat c
 where c.statistic# = 12
   and c.sid = a.sid
   and a.paddr = b.addr
 order by value desc