数据文件大小及头大小:
select v1.file_name,v1.file_id,
num1 totle_space,
num3 free_space,
num1-num3 used_space,
nvl(num2,0) data_space,
num1-num3-nvl(num2,0) file_head
from
(select file_name,file_id,sum(bytes) num1 from dba_data_files group by file_name,file_id) v1,
(select file_id,sum(bytes) num2 from dba_extents group by file_id) v2,
(select file_id,sum(bytes) num3 from dba_free_space group by file_id) v3
where v1.file_id=v2.file_id(+)
and v1.file_id=v3.file_id(+);
(运行以上查询，我们可以如下信息：
totle_pace:该数据文件的总大小，字节为单位
free_space:该数据文件的剩于大小，字节为单位
used_space:该数据文件的已用空间，字节为单位
data_space:该数据文件中段数据占用空间，也就是数据空间，字节为单位
file_head:该数据文件头部占用空间，字节为单位)
数据库各个表空间增长情况的检查：
sql>select a.tablespace_name,(1-(a.total)/b.total)*100 used_percent
from (select tablespace_name,sum(bytes) total from dba_free_space group by tablespace_name) a,(select tablespace_name,sum(bytes) total from dba_data_files group by tablespace_name) b where a.tablespace_name=b.tablespace_name;
sql>select upper(f.tablespace_name) "表空间名",
d.tot_grootte_mb "表空间大小(m)",
d.tot_grootte_mb - f.total_bytes "已使用空间(m)", to_char(round((d.tot_grootte_mb - f.total_bytes) / d.tot_grootte_mb * 100, 2), '990.99') "使用比", f.total_bytes "空闲空间(m)",
f.max_bytes "最大块(m)" from (select tablespace_name,
round(sum(bytes) / (1024 * 1024), 2) total_bytes,
round(max(bytes) / (1024 * 1024), 2) max_bytes
from sys.dba_free_space group by tablespace_name) f,
(select dd.tablespace_name,round(sum(dd.bytes) / (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 4 desc;
查看各个表空间占用磁盘情况:
sql>col tablespace_name format a20;
sql>select b.file_id file_id,
b.tablespace_name tablespace_name,
b.bytes bytes,
(b.bytes-sum(nvl(a.bytes,0))) used,
sum(nvl(a.bytes,0)) free,
sum(nvl(a.bytes,0))/(b.bytes)*100 percent
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_id,b.bytes
order by b.file_id;
数据库对象下一扩展与表空间的free扩展值的检查：
sql>select a.table_name, a.next_extent, a.tablespace_name
from all_tables a,(select tablespace_name, max(bytes) as big_chunk
from dba_free_space group by tablespace_name ) f where f.tablespace_name = a.tablespace_name and a.next_extent > f.big_chunk
union select a.index_name, a.next_extent, a.tablespace_name
from all_indexes a,(select tablespace_name, max(bytes) as big_chunk
from dba_free_space group by tablespace_name ) f where f.tablespace_name = a.tablespace_name and a.next_extent > f.big_chunk;
disk read最高的sql语句的获取：
sql>select sql_text from (select * from v$sqlarea order by disk_reads)
where rownum<=5;
查找前十条性能差的sql
select * from (select parsing_user_id
executions,sorts,command_type,disk_reads,
sql_text from v$sqlarea order by disk_reads desc)
where rownum<10 ;
等待时间最多的5个系统等待事件的获取：
sql>select * from (select * from v$system_event where event not like 'sql%' order by total_waits desc) where rownum<=5;
查看当前等待事件的会话:
col username format a10
set line 120
col event format a30
select se.sid,s.username,se.event,se.total_waits,se.time_waited,se.average_wait
from v$session s,v$session_event se where s.username is not null and se.sid=s.sid
and s.status='active' and se.event not like '%sql*net%';
select sid, event, p1, p2, p3, wait_time, seconds_in_wait, state from v$session_wait where event not like '%message%' and event not like 'sql*net%' and event not like '%timer%' and event != 'wakeup time manager';
找到与所连接的会话有关的当前等待事件：
select sw.sid,s.username,sw.event,sw.wait_time,sw.state,sw.seconds_in_wait sec_in_wait
from v$session s,v$session_wait sw where s.username is not null and sw.sid=s.sid
and sw.event not like '%sql*net%' order by sw.wait_time desc;
oracle所有回滚段状态的检查：
2
如对本文有疑问，
点击进行留言回复！！
我要评论
网友评论