Oracle常用脚本
把常用的脚本贴上来，方便查看
一、查询表空间使用情况，如果使用百分比高于85%则可能需要扩充表空间
set linesize 1000;
set pagesize 1000;
column segment_name format a10;
column tablespace_name format a15;
column status format a10;
Select A.TABLESPACE_NAME,A.BYTES/1024/1024 TOTAL,B.BYTES/1024/1024 USED,
C.BYTES/1024/1024 FREE, (B.BYTES*100)/A.BYTES "% USED",
(C.BYTES*100)/A.BYTES "% FREE"
FROM $TS_AVAIL A,$TS_USED B,$TS_FREE C
Where A.TABLESPACE_NAME=B.TABLESPACE_NAME
AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;
一、查询表空间使用情况，如果使用百分比高于85%则可能需要扩充表空间
set linesize 1000;
set pagesize 1000;
column segment_name format a10;
column tablespace_name format a15;
column status format a10;
Select A.TABLESPACE_NAME,A.BYTES/1024/1024 TOTAL,B.BYTES/1024/1024 USED,
C.BYTES/1024/1024 FREE, (B.BYTES*100)/A.BYTES "% USED",
(C.BYTES*100)/A.BYTES "% FREE"
FROM $TS_AVAIL A,$TS_USED B,$TS_FREE C
Where A.TABLESPACE_NAME=B.TABLESPACE_NAME
AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;
二、查看数据库缓冲区命中率，如果命中率小于80%则可能需要调整SGA区
select 1-sum(decode(name,'physical reads',value,0))/
(sum(decode(name,'db block gets',value,0))+
sum(decode(name,'consistent gets',value,0)))
from v$sysstat;
select 1-sum(decode(name,'physical reads',value,0))/
(sum(decode(name,'db block gets',value,0))+
sum(decode(name,'consistent gets',value,0)))
from v$sysstat;
三、查看数据库高速缓存命中率，如果命中率不是很接近于0甚至大于1则要调整共享池
Select sum(reloads)/sum(pins) from v$librarycache;
Select sum(reloads)/sum(pins) from v$librarycache;
四、查看数据字典缓存命中率，如果命中率>15%则要调整共享池
Select sum(getmisses)/sum(gets) from v$rowcache;
Select sum(getmisses)/sum(gets) from v$rowcache;
五、查看日志缓冲区的使用,如果缓冲区申请失败率不是很接近于0，则要调整Redo Log缓冲区大小
select sum(decode(name,'redo log space requests',value,0))/
sum(decode(name,'redo entries',value,0))
from v$sysstat;
select sum(decode(name,'redo log space requests',value,0))/
sum(decode(name,'redo entries',value,0))
from v$sysstat;
六.索引状态查看
select SEGMENT_NAME, SEGMENT_TYPE,TABLESPACE_NAME, BYTES ,NEXT_EXTENT
,MAX_EXTENTS from user_segments;
select SEGMENT_NAME, SEGMENT_TYPE,TABLESPACE_NAME, BYTES ,NEXT_EXTENT
,MAX_EXTENTS from user_segments;
七，获取当前的等待事件
select sid, event,p1, p1text from v$session_wait;
select sid, event,p1, p1text from v$session_wait;
八，通过sid获取当前的sql
select sql_text from v$sqltext a
where a.hash_value=(select sql_hash_value from v$session b where b.sid='&sid')
order by piece ASC
select sql_text from v$sqltext a
where a.hash_value=(select sql_hash_value from v$session b where b.sid='&sid')
order by piece ASC
九，查看数据库系统级等待事件
select * from
(select event, time_waited from v$system_event order by time_waited desc)
where rownum<10;
十，添加redo日志组或者成员
添加日志成员
Alter DATABASE ADD LOGFILE MEMBER 'D:\ORACLE\ORADATA\FOUNDER\redo.ora' TO GROUP 1；
添加日志组
Alter DATABASE ADD LOGFILE GROUP 4 ('D:\ORACLE\ORADATA\FOUNDER\log4.ora') SIZE 100m；
添加日志成员
Alter DATABASE ADD LOGFILE MEMBER 'D:\ORACLE\ORADATA\FOUNDER\redo.ora' TO GROUP 1；
添加日志组
Alter DATABASE ADD LOGFILE GROUP 4 ('D:\ORACLE\ORADATA\FOUNDER\log4.ora') SIZE 100m；
十一，检查用户使用的表空间情况
col used format 999,999.999 head "Used (MB)"
col quota format 999,999.999 head "Quota (MB)"
col tablespace_name format a15
select username, tablespace_name, bytes/1024/1024 used, max_bytes/1024/1024 quota from dba_ts_quotas order by username;
col used format 999,999.999 head "Used (MB)"
col quota format 999,999.999 head "Quota (MB)"
col tablespace_name format a15
select username, tablespace_name, bytes/1024/1024 used, max_bytes/1024/1024 quota from dba_ts_quotas order by username;
十二，定位某个对象存在free list 争用
select s.segment_name, s.segment_type, s.freelists
from dba_segments s, v$session_wait w
where w.event ='buffer busy waits' and w.p1 = s.header_file
and w.p2 = s.header_block;
from dba_segments s, v$session_wait w
where w.event ='buffer busy waits' and w.p1 = s.header_file
and w.p2 = s.header_block;
十三，查看文件的IO情况
select d.tablespace_name, d.file_name, f.PHYRDS, f.PHYBLKRD, f.READTIM,
f.PHYWRTS,f.PHYBLKWRT,f.WRITETIM
from v$filestat f, dba_data_files d
where f.FILE# = d.file_id
order by d.tablespace_name, d.file_name;
f.PHYWRTS,f.PHYBLKWRT,f.WRITETIM
from v$filestat f, dba_data_files d
where f.FILE# = d.file_id
order by d.tablespace_name, d.file_name;
