--查询表空间使用情况
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(G)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(G)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
F.TOTAL_BYTES "空闲空间(G)",
F.MAX_BYTES "最大块(G)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM() / (1024 * 1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1;
--查询表空间的free space
select tablespace_name,
count(*) as extends,
round(sum(bytes) / 1024 / 1024 /1024, 2) as GB,
sum(blocks) as blocks
from dba_free_space
group by tablespace_name;
--查询表空间的总容量
select tablespace_name, sum(bytes) / 1024 / 1024 /1024 as "大小（GB）"
from dba_data_files
group by tablespace_name;
--查询表空间使用率
select total.tablespace_name,
round(, 2) as Total_MB,
round( - , 2) as Used_MB,
round((1 - / ) * 100, 2) || '%' as Used_Pct
from (select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_free_space
group by tablespace_name) free,
(select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files
group by tablespace_name) total
where free.tablespace_name = total.tablespace_name;
-监控当前数据库谁在运行什么SQL语句
select osuser, username, sql_text
from v$session a, v$sqltext b
where a.sql_address = order by address, piece;
--找使用CPU多的用户session
select ,spid,status,substr(,1,40) prog, ,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where #=12 and
= and
=
order by value desc;
--查看死锁信息
SELECT (SELECT username
FROM v$session
WHERE SID = ) blocker, , 'is blocking',
(SELECT username
FROM v$session
WHERE SID = ) blockee,
FROM v$lock a, v$lock b
WHERE = 1 AND > 0 AND a.id1 = b.id1 AND a.id2 = b.id2;
--查询当前连接会话数
select ,,
from
v$sesstat S,v$statname N,v$session A
where
#=# and
name='session pga memory'
and =
order by ;
--等待最多的用户
SELECT , , SUM (a.wait_time + a.time_waited) total_wait_time
FROM v$active_session_history a, v$session s
WHERE a.sample_time BETWEEN SYSDATE - 30 / 2880 AND SYSDATE
GROUP BY ,
ORDER BY total_wait_time DESC;
--等待最多的SQL
SELECT , a.session_id, a.user_id, , s.sql_text,
SUM (a.wait_time + a.time_waited) total_wait_time
FROM v$active_session_history a, v$sqlarea s, dba_users d
WHERE a.sample_time BETWEEN SYSDATE - 30 / 2880 AND SYSDATE
AND a.sql_id = s.sql_id
AND a.user_id = d.user_id
GROUP BY , a.session_id, a.user_id, s.sql_text, ;
--查看消耗资源最多的SQL
SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls
FROM V$SQLAREA
WHERE buffer_gets > 10000000 OR disk_reads > 1000000
ORDER BY buffer_gets + 100 * disk_reads DESC;
--查看某条SQL语句的资源消耗
SELECT hash_value, buffer_gets, disk_reads, executions, parse_calls
FROM V$SQLAREA
WHERE hash_Value = 228801498 AND address = hextoraw('CBD8E4B0');
--查询会话执行的实际SQL
SELECT , , s.sql_text
FROM v$session a, v$sqltext s
WHERE a.sql_address =
AND a.sql_hash_value = s.hash_value
AND = 'ACTIVE'
ORDER BY , , ;
--显示正在等待锁的所有会话
SELECT * FROM DBA_WAITERS;