--数据库空间检查
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
T.TOTAL_BYTES "表空间大小(M)",
T.TOTAL_BYTES - F.FREE_BYTES "已使用空间(M)",
F.FREE_BYTES "空闲空间(M)",
TO_CHAR(ROUND((T.TOTAL_BYTES - F.FREE_BYTES) / T.TOTAL_BYTES * 100,
2),
'90.99') || '%' "使用率"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES
FROM SYS.DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) T
WHERE T.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY F.TABLESPACE_NAME;
--查看表大小
select t.segment_name, sum(bytes) / (1024 * 1024 * 1024) as "size(G)"
from dba_segments t
where t.segment_name like 'SAV_ACCT_BAL_D_STAT%'
group by t.segment_name
order by 1
;
--检查数据库锁
select distinct t.SESSION_ID, t2.SERIAL#, t1.OBJECT_NAME
from v$locked_object t, dba_objects t1, v$session t2
where t.OBJECT_ID=t1.OBJECT_ID
and t.SESSION_ID=t2.SID;
--根据锁查进程号
select pro.spid from v$session ses,v$process pro where ses.sid=459 and ses.paddr=pro.addr;
--杀掉进程 sid,serial#
alter system kill session'210,11562';
--查询正在执行的SQL-----A
SELECT DISTINCT A.ADDR,
A.PID,
A.SPID,
B.SID,
B.SERIAL#,
B.USERNAME,
B.STATUS,
C.SQL_ID,
C.SQL_TEXT
FROM GV$PROCESS A, GV$SESSION B, GV$SQL C
WHERE A.ADDR = B.PADDR
AND B.SQL_HASH_VALUE = C.HASH_VALUE
AND B.STATUS = 'ACTIVE'
order by B.SID;
--查看SQL执行计划------B
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('36cxhfthyq9rq'));
--查看表统计信息——————————D
SELECT * FROM USER_TAB_STATISTICS V WHERE V.TABLE_NAME = 'T03_AGREEMENT';
Oracle中一些字典表的用法
最新推荐文章于 2023-02-12 18:31:32 发布