检查 database 基本信息
select * from v$version;
select name ,open_mode,log_mode from v$database;
select instance_number,instance_name ,status from gv$instance;
show parameter cpu_count；
show parameter block_size；
select group#,thread#,members,bytes/1024/1024 from gv$log;
show sga；
select count(*) from v$controlfile；
select count(*) from v$tempfile;
select count(*) from v$datafile;查看数据文件信息
检查表空间数据文件信息
col tablespace_name for a30
select tablespace_name , sum(bytes)/1024/1024 from dba_temp_files group by tablespace_name; 检查表空间
SELECT TABLESPACE_NAME,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT FROM DBA_TABLESPACES;检查数据文件状态
select count(*),status from v$datafile group by status;检查表空间使用情况
select
f.tablespace_name,
a.total,
f.free,(a.total-f.free)/1024 "used SIZE(G)"
,round((f.free/a.total)*100) "% Free"
from
(select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by
tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space
group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name(+)
order by "% Free"查询临时 segment 使用情况
COL username FORMAT a10;
COL segtype FORMAT a10;
SELECT username, segtype, extents "Extents Allocated"
,blocks "Blocks Allocated"
FROM v$tempseg_usage;查看临时表空间大小
select tablespace_name,file_name,bytes/1024/1024 "file_size(M)",autoextensible from dba_temp_files;
select status,enabled, name, bytes/1024/1024 file_size from v$tempfile;查看临时表空间的使用情况
SELECT temp_used.tablespace_name,
total - used as "Free",
total as "Total",
round(nvl(total - used, 0) * 100 / total, 3) "Free percent"
FROM (SELECT tablespace_name, SUM(bytes_used) / 1024 / 1024 used
FROM GV_$TEMP_SPACE_HEADER
GROUP BY tablespace_name) temp_used,
(SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total
FROM dba_temp_files
GROUP BY tablespace_name) temp_total
WHERE temp_used.tablespace_name = temp_total.tablespace_name查找消耗较多临时表空间的sql
Select se.username,
se.sid,
su.extents,
su.blocks * to_number(rtrim(p.value)) as Space,
tablespace,
segtype,
sql_text
from v$sort_usage su, v$parameter p, v$session se, v$sql s
where p.name = 'db_block_size'
and su.session_addr = se.saddr
and s.hash_value = su.sqlhash
and s.address = su.sqladdr
order by se.username, se.sid ;查看当前临时表空间使用大小与正在占用临时表空间的sql语句
select sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text
from v$sort_usage sort, v$session sess, v$sql sql
where sort.SESSION_ADDR = sess.SADDR
and sql.ADDRESS = sess.SQL_ADDRESS
order by blocks desc;查看数据文件信息 , 若文件较多可以根据需要字段进行排序 输出 top 10
col datafile for a60
SELECT fs.phyrds "Reads", fs.phywrts "Writes"
,fs.avgiotim "Average I/O Time", df.name "Datafile"
FROM v$datafile df, v$filestat fs WHERE df.file# = fs.file#;查看所有数据文件 i/ o 情况
COL ts FORMAT a10 HEADING "Tablespace";
COL reads FORMAT 999990;
COL writes FORMAT 999990;
COL br FORMAT 999990 HEADING "BlksRead";
COL bw FORMAT 999990 HEADING "BlksWrite";
COL rtime FORMAT 999990;
COL wtime FORMAT 999990;
SELECT ts.name AS ts, fs.phyrds "Reads", fs.phywrts "Writes"
,fs.phyblkrd AS br, fs.phyblkwrt AS bw
,fs.readtim "RTime", fs.writetim "WTime"
FROM v$tablespace ts, v$datafile df, v$filestat fs
WHERE ts.ts# = df.ts# AND df.file# = fs.file#
UNION
SELECT ts.name AS ts, ts.phyrds "Reads", ts.phywrts "Writes",ts.phyblkrd AS br, ts.phyblkwrt AS bw
,ts.readtim "RTime", ts.writetim "WTime"
FROM v$tablespace ts, v$tempfile tf, v$tempstat ts
WHERE ts.ts# = tf.ts# AND tf.file# = ts.file# ORDER BY 1;精确计算表空间大小，消耗系统资源，慎用
SELECT F.TABLESPACE_NAME,
A.ALL_TOTAL "总空间" ,
A.ALL_USED "总使用空间" ,
A.ALL_TOTAL - A.ALL_USED "总剩余空间" ,
(A.ALL_TOTAL - A.ALL_USED) / A.ALL_TOTAL * 100 "总剩余比例" ,
A.TOTAL "当前大小" ,
U.USED "当前使用空间" ,
F. FREE "当前剩余空间" ,
(U.USED / A.TOTAL) * 100 "当前使用比例" ,
(F. FREE / A.TOTAL) * 100 "当前剩余比例"
FROM ( SELECT TABLESPACE_NAME,
SUM (BYTES / (1024 * 1024 * 1024)) TOTAL,
SUM (DECODE(AUTOEXTENSIBLE, 'YES' , MAXBYTES, BYTES) /
(1024 * 1024 * 1024)) ALL_TOTAL,
SUM (USER_BYTES) / (1024 * 1024 * 1024) ALL_USED
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A,
( SELECT TABLESPACE_NAME, SUM (BYTES / (1024 * 1024 * 1024)) USED
FROM DBA_EXTENTS
GROUP BY TABLESPACE_NAME) U,
( SELECT TABLESPACE_NAME, SUM (BYTES / (1024 * 1024 * 1024)) FREE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE A.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
AND A.TABLESPACE_NAME = U.TABLESPACE_NAME(+)
ORDER BY (A.ALL_TOTAL - A.ALL_USED) / A.ALL_TOTAL,F. FREE / A.TOTAL ASC ;
