排查表空间占用多少
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';
拓展表空间
- 第一种
alter database datafile '路径XXXXX.dbf' resize 200000M;
- 第二种
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 '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
and c.sid = a.sid
and a.paddr = b.addr
order by value desc