— 查看表空间使用情况
SELECT tablespace_name,
round(total_bytes/1024/1024,2) as total_mb,
round(used_bytes/1024/1024,2) as used_mb,
round((used_bytes/total_bytes)*100,2) as used_percent
FROM (
SELECT tablespace_name,
sum(bytes) as total_bytes,
sum(bytes – nvl(free_bytes,0)) as used_bytes
FROM (
SELECT tablespace_name, bytes,
CASE WHEN autoextensible = ‘YES’ THEN maxbytes ELSE bytes END – bytes as free_bytes
FROM dba_data_files
UNION ALL
SELECT tablespace_name, bytes, bytes – nvl(free_bytes,0) as free_bytes
FROM dba_temp_files
)
GROUP BY tablespace_name
);

— 查看数据文件状态
SELECT file_name, tablespace_name, status, bytes/1024/1024 as size_mb
FROM dba_data_files
ORDER BY tablespace_name, file_name;– 查看临时表空间使用情况
SELECT tablespace_name,
round(total_bytes/1024/1024,2) as total_mb,
round(used_bytes/1024/1024,2) as used_mb,
round((used_bytes/total_bytes)*100,2) as used_percent
FROM (
SELECT tablespace_name,
sum(bytes) as total_bytes,
sum(bytes – nvl(free_bytes,0)) as used_bytes
FROM (
SELECT tablespace_name, bytes,
CASE WHEN autoextensible = ‘YES’ THEN maxbytes ELSE bytes END – bytes as free_bytes
FROM dba_temp_files
)
GROUP BY tablespace_name
);

— 查看ASM磁盘组使用情况
SELECT name, state, total_mb, free_mb,
round((total_mb – free_mb)/total_mb*100,2) as used_percent
FROM v$asm_diskgroup;– 查看ASM磁盘状态
SELECT name, group_number, disk_number, state, total_mb, free_mb
FROM v$asm_disk;

— 创建表空间使用监控脚本
SELECT tablespace_name,
round(total_bytes/1024/1024,2) as total_mb,
round(used_bytes/1024/1024,2) as used_mb,
round((used_bytes/total_bytes)*100,2) as used_percent
FROM (
SELECT tablespace_name,
sum(bytes) as total_bytes,
sum(bytes – nvl(free_bytes,0)) as used_bytes
FROM (
SELECT tablespace_name, bytes,
CASE WHEN autoextensible = ‘YES’ THEN maxbytes ELSE bytes END – bytes as free_bytes
FROM dba_data_files
UNION ALL
SELECT tablespace_name, bytes, bytes – nvl(free_bytes,0) as free_bytes
FROM dba_temp_files
)
GROUP BY tablespace_name
)
WHERE (used_bytes/total_bytes)*100 > 80
ORDER BY used_percent DESC;– 输出结果示例
TABLESPACE_NAME TOTAL_MB USED_MB USED_PERCENT
—————— ———- ———- ————
USERS 100 94.19 94.19
SYSAUX 500 420.56 84.11

— 查看I/O统计信息
SELECT file_name,
phyrds as physical_reads,
phywrts as physical_writes,
readtim as read_time,
writetim as write_time
FROM v$filestat fs, dba_data_files df
WHERE fs.file# = df.file_id
ORDER BY physical_reads DESC
FETCH FIRST 10 ROWS ONLY;– 输出结果示例
FILE_NAME PHYSICAL_READS PHYSICAL_WRITES READ_TIME WRITE_TIME
————————————————– ————– ————— ——— ———-
/oracle/app/oracle/oradata/fgedudb/system01.dbf 12345 6789 12.34 5.67
/oracle/app/oracle/oradata/fgedudb/sysaux01.dbf 9876 4567 8.91 3.45

— 查看ASM磁盘组详细信息
SELECT name, state, total_mb, free_mb,
round((total_mb – free_mb)/total_mb*100,2) as used_percent,
offline_disks, voting_files
FROM v$asm_diskgroup;– 查看ASM磁盘状态
SELECT name, group_number, disk_number, state, total_mb, free_mb,
round((total_mb – free_mb)/total_mb*100,2) as used_percent
FROM v$asm_disk
ORDER BY group_number, disk_number;– 输出结果示例
NAME STATE TOTAL_MB FREE_MB USED_PERCENT OFFLINE_DISKS VOTING_FILES
—————————— ——– ———- ———- ———— ————- ————
DATA MOUNTED 10240 5120 50.0 0 N
FRA MOUNTED 5120 2560 50.0 0 N