查看表空间信息:
SELECT A.TABLESPACE_NAME,
A.TOTAL_MB,
ROUND(B.FREE_MB, 2) FREE_MB,
TO_CHAR(ROUND((A.TOTAL_MB - B.FREE_MB) / A.TOTAL_MB * 100, 2), '990.99') || '%' "USAGE %"
FROM (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 TOTAL_MB
FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 FREE_MB
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) B,
DBA_TABLESPACES D
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
AND A.TABLESPACE_NAME = D.TABLESPACE_NAME(+)
ORDER BY 4 DESC;
表空间扩容:
--表空间扩容有两种方式,一种是给本来的数据文件扩容,另一种是给表空间增加数据文件
ALTER TABLESPACE <TABLESPACE_NAME> ADD DATAFILE 'XXX.DBF' SIZE 10240 ;
ALTER TABLESPACE <TABLESPACE_NAME> RESIZE DATAFILE 'XXX.DBF' TO 10240;
查询数据库会话、句柄
SELECT '活动会话数量:' AS "NODE",COUNT(1) AS COUNT_NUM FROM V$SESSIONS
WHERE STATE ='ACTIVE' AND SESS_ID !=SESSID UNION ALL
SELECT '全部会话数量:' AS "NODE",COUNT(1) AS COUNT_NUM FROM V$SESSIONS
WHERE SESS_ID !=SESSID UNION ALL
SELECT '单会话最大句柄数:' AS "NODE",MAX(N_USED_STMT) AS COUNT_NUM FROM V$SESSIONS
WHERE SESS_ID !=SESSID union ALL
SELECT '总句柄数量:' AS "NODE",SUM(N_USED_STMT) AS COUNT_NUM FROM V$SESSIONS
WHERE SESS_ID !=SESSID;
查询数据库等待事件--找到阻塞事务的源头
SELECT * FROM V$TRXWAIT ORDER BY 1;
SELECT A.TRX_ID,A.SQL_TEXT,A.CLNT_IP,A.APPNAME,A.CLNT_HOST,A.LAST_SEND_TIME,A.SESS_ID,
B.TRX_ID WAIT_FOR_TEX_ID,B.SQL_TEXT WAIT_FOR_SQL_TEXT,B.CLNT_IP WAIT_FOR_CLNT_IP,
B.APPNAME WAIT_FOR_APPNAME,B.CLNT_HOST WAIT_FOR_CLNT_HOST,
B.LAST_SEND_TIME WAIT_FOR_LAST_SEND_TIME,B.SESS_ID WAIT_FOR_SESS_ID
FROM V$TRXWAIT C
LEFT JOIN (SELECT * FROM V$SESSIONS ) A
ON A.TRX_ID = C.ID
LEFT JOIN (SELECT * FROM V$SESSIONS )B
ON B.TRX_ID = C.WAIT_FOR_ID
ORDER BY 1;
查询数据库事务锁
--通过TRX_ID查询事务对应的锁信息
SELECT * FROM V$LOCK WHERE TRX_ID=?;
--通过TRX_ID查询到事务上锁的表
SELECT DISTINCT NAME,LMODE FROM SYS.SYSOBJECTS
WHERE ID IN(SELECT TABLE_ID FROM V$LOCK WHERE TRX_ID=?);
杀掉阻塞的源头会话
SP_CLOSE_SESSION(SESS_ID);--结束会话
查询数据库死锁事件
SELECT V.*, S.SESS_ID BLOCKER_SEID,S.SQL_TEXT BLOCKER_SQL, S.TRX_ID BLOCKER_TRXID
FROM V$SESSIONS S,
(SELECT S.SESS_ID ,S.SQL_TEXT, S.TRX_ID,L.ROW_IDX FROM V$LOCK L, V$SESSIONS S
WHERE BLOCKED<>0 AND L.TRX_ID=S.TRX_ID ) V
WHERE S.TRX_ID=V.ROW_IDX;
查询慢sql
select thrd_id,dbms_lob.substr(sf_get_session_sql(sess_id)) ,datediff(ss,last_send_time,sysdate)
from v$sessions where state='ACTIVE' and sess_id != sessid order by 3 desc;
查询内存使用情况
SELECT NAME,SUM(N_PAGES)/1024.0*PAGE()/1024 ALL_M,SUM(FREE)/1024.0*PAGE()/1024 FREE_M,
SUM(N_DIRTY)/1024.0*PAGE()/1024 DIRTY_M,SUM(N_LOGIC_READS) LOGIC_READS,
SUM(N_DISCARD) N_DISCARD,
SUM(N_PHY_READS) PHY_READS
FROM V$BUFFERPOOL GROUP BY NAME;