目录
--** ORACLE 系统参数情况2(9i/10g视图) 9
--**查看回滚段的使用情况，哪个用户正在使用回滚段的资源 18
--**.V$SYSSTAT视图可以确定总的数据请求的数量： 19
--**.DBA_ROLLBACK_SEGS视图可以确定回滚段的存储信息和状态等等信息： 19
--**.V$TRANSACTION视图可以确定每个事务使用Undo空间的数量： 20
-- **Oracle 10g 功能检查-Flashback： 23
--**清空Oracle 10g回收站中以BIN$开头的表 24
--**求等待的事件及会话信息/求会话的等待及会话信息 36
--**1 buffer gets top 10 sql: 38
--**2 Physical Reads top 10 sql: 38
--**3 Executions top 10 sql: 39
--**4 Parse Calls top 10 sql: 39
--**5 Sharable Memory top 10 sql: 39
--**6 CPU usage top 10 sql: 40
--**7 Running Time top 10 sql: 40
--**查看数据文件的状态记录状态不是"online"的数据文件 45
************************************************* 48
--**查找一个表是否 被锁的方法，例如：临时表SHZGY.SHZGY_PZ_BB_ERROR1 51
--**使用新的V$DB_CACHE_ADVICE视图来帮助改变数据缓存的大小 71
--**查看所有session正在等待的，已经发生的等待事件 72
--**针对所有session找出这两个等待事件对应的段名和段类型。 72
--**查找P1, P2, P3代表什么--查询 V$EVENT_NAME 72
--**会话开始后的所有等待数--查询 V$SESSION_EVENT 73
--**查看系统等待数v$system_wait_event 73
--**查询用户名及相应的配置文件、默认的表空间和临时表空间 75
--**将V$SESSION和V$SQLTEXT连接就可以显示目前每一个会话正在执行的SQL语句 77
--**USED_UBLK和USED_UREC可以看到事务进行到什么程度了 78
----oracle 通过pid 找到sid 再找出执行sql 80
--**请问如何分辨某个用户是从哪台机器登陆ORACLE的? 81
数据库管理员日常工作中必备的sql列表
--**** 数据库实例基本情况
--** ORACLE系统实例名字:
select NAME ,CREATED ,LOG_MODE ,
to_char(sysdate,'yyyy/mm/dd:HH24:MI') "SYS_DATE"
from v$database;
--** ORACLE实例安装的产品列表:
COL PARAMETER for a40
COL VALUE for a30
select * from v$option;
--** Oracle及工具 Tools 版本信息:
col BANNER for a60
select BANNER from v$version;
--** Oracle实例是否归档:
select dbid, name, log_mode from v$database;
--** ORACLE系统用户情况:
col USERNAME for a20
col DEFAULT_TABLESPACE for a20
col TEMPORARY_TABLESPACE for a20
col PROFILE for a20
select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,
PROFILE,CREATED FROM dba_users;
--** ORACLE系统资源文件情况:
col RESOURCE_NAME for a28
col LIMIT for a20
SELECT * from dba_profiles order by PROFILE;
--** ORACLE系统字符集情况
COL VALUE$ for a40
SELECT name,value$ from sys.props$;
--** ORACLE数据库连接的信息
col owner for a20
col db_link for a38
col username for a20
SELECT owner,db_link,username from dba_db_links ;
--** ORACLE数据库用户权限报告:
col Username for a20
col Owner for a14
col Obj for a20
col Obj_Priv for a10
col Sys_Priv for a20
col Granted_Role for a22
select username "Username",
owner,
table_name "Obj",
privilege "Obj_Priv",
' ' "Sys_Priv",
' ' "Granted_Role",
1 "dummy"
from dba_users u, dba_tab_privs t
where u.username = t.grantee
and u.username = upper('&&user_name')
union
select username,
' ',
' ',
' ',
privilege,
' ',
2 "dummy"
from dba_users u, dba_sys_privs s
where u.username = s.grantee
and u.username = upper('&&user_name')
union
select username,
' ',
' ',
' ',
' ',
granted_role,
3 "dummy"
from dba_users u, dba_role_privs r
where u.username = r.grantee
and u.username = upper('&&user_name')
order by 1, 7;
--** ORACLE实例后台进程信息
col DESCRIPTION for a40
col name for a30
SELECT * from v$bgprocess;
--** ORACLE 系统参数情况
Show parameter;
--** ORACLE 系统参数情况2(9i/10g视图)
COL SID for a30
COL NAME for a30
COL VALUE for a40
--查看数据库的创建日期和归档方式:
Select Created, Log_Mode, Log_Mode From V$Database;
--** Oracle系统日志文件与控制文件
--** 日志文件信息1
SELECT GROUP# "组号",
MEMBERS "成员",
BYTES "大小(字节)",
ARCHIVED "归档?",
STATUS "状态"
FROM V$LOG;
--** 日志文件信息2
Col member for a56
SELECT * FROM V$LOGFILE;
--求日志文件的空间使用
select le.leseq current_log_sequence#, 100*cp.cpodr_bno/le.lesiz percentage_full
from x$kcccp cp,x$kccle le
where le.leseq =cp.cpodr_seq;
--求归档日志的切换频率(生产系统可能时间会很长)
select start_recid,start_time,end_recid,end_time,minutes from (select test.*, rownum as rn
from (select b.recid start_recid,to_char(b.first_time,'yyyy-mm-dd hh24:mi:ss') start_time,
a.recid end_recid,to_char(a.first_time,'yyyy-mm-dd hh24:mi:ss') end_time,round(((a.first_time-b.first_time)*24)*60,2) minutes
from v$log_history a,v$log_history b where a.recid=b.recid+1 and b.first_time > sysdate - 1
order by a.first_time desc) test) y where y.rn < 30;
--** 控制文件信息
col "状态" for a20
col "控制文件名" for a60
SELECT STATUS "状态",
NAME "控制文件名"
FROM v$controlfile ;
--****表空间信息
--**查看表空间状态
select tablespace_name,status,extent_management,segment_space_management from dba_tablespaces;
select table_name,freelists,freelist_groups from user_tables;
--**查看表空间使用情况
select sum(bytes)/(1024*1024) as free_space,tablespace_name from dba_free_space group by tablespace_name;
--**检查表空间的使用情况
SELECT tablespace_name, max_m,
count_blocks free_blk_cnt, sum_free_m,
to_char(100*sum_free_m/sum_m, '99.99') || '%' AS pct_free
FROM ( SELECT tablespace_name,sum(bytes)/1024/1024 AS sum_m
FROM dba_data_files GROUP BY tablespace_name),
( SELECT tablespace_name AS fs_ts_name, max(bytes)/1024/1024
AS max_m, count(blocks)
AS count_blocks, sum(bytes/1024/1024)
AS sum_free_m FROM dba_free_space GROUP BY tablespace_name )
WHERE tablespace_name = fs_ts_name order by pct_free;
--**查询表空间的大小,分布等信息
select tablespace_name, sum ( blocks ) free_blk , trunc ( sum ( bytes ) /(1024*1024) ) free_m, max ( bytes ) / (1024) as big_chunk_k, count (*) num_chunks from dba_free_space group by tablespace_name;
--**查看表空间物理文件的名称及大小
select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;
--**检查剩余表空间 （速度比较慢)
select tablespace_name,sum(blocks) as free_blk,
trunc(sum(bytes)/(1024*1024)) as free_m,
max(bytes)/(1024) as big_chunk_k,
count(*) as num_chunks
from dba_free_space
group by tablespace_name;
--**查看所有表空间的碎片程度
select tablespace_name,sum(bytes),sum(free),sum(free)*100/sum(bytes)
from (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)
group by tablespace_name order by sum(free)*100/sum(bytes);
--**确定需要固定的PL/SQL对象
碎片化现象造成共享池中的可用空间均成为许多零散的片段，而没有足够大的连续空间，这是共享池中的普遍现象。
较大的PL/SQL对象容易引起共享池故障，因为他们需要进入内存的时候，不容易得到空间。
下面程序清单中的示例搜索那些所需空间大于100KB的对象（只限于目前已经在shared pool中对象）
select name, sharable_mem
from v$db_object_cache
where sharable_mem > 100000
and type in ('PACKAGE', 'PACKAGE BODY','FUNCTION', 'PROCEDURE')
and kept = 'NO';
--**监控表空间的 I/O 比例 ?????
select df.tablespace_name name,df.file_name "file",f.phyrds pyr,
f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw
from v$filestat f, dba_data_files df
where f.file# = df.file_id
order by df.tablespace_name;
--**求数据文件的I/O分布
select df.name,phyrds,phywrts,phyblkrd,phyblkwrt,singleblkrds,readtim,writetim
from v$filestat fs,v$dbfile df
where fs.file#=df.file# order by df.name;
select SubStr(B.Name, 1, 40) Disk, C.TableSpace_Name,
A.Phyblkrd + A.Phyblkwrt Total,
A.Phyrds, A.Phywrts,A.Phyblkrd, A.Phyblkwrt,
((A.ReadTim /Decode(A.Phyrds,0,1,A.Phyblkrd))/100) Avg_Rd_Time,
((A.WriteTim / Decode(A.PhyWrts,0,1,A.PhyblkWrt)) /100) Avg_Wrt_Time
from V$FileStat A, V$DataFile B, Sys.DBA_Data_Files C
where B.File# = A.File#
and B.File# = C.File_Id
order by Disk,C.Tablespace_Name, A.File#;
--**监控文件系统的 I/O 比例
select substr(a.file#,1,2) "#", substr(a.name,1,30) "Name",
a.status, a.bytes, b.phyrds, b.phywrts
from v$datafile a, v$filestat b
where a.file# = b.file#;
--**检查表空间碎片1MB以上
select tablespace_name "表空间" ,
count(*) "分布在", sum(bytes) "自由空间(Mb)"
from dba_free_space
where bytes>100000 group by tablespace_name;
--**表空间的自由空间情况
select f.tablespace_name "TABLESPACE_NAME",
decode(d.extent_management,'DICTIONARY','DICT','LOCAL') " TABS_TYPE ",
trunc(sum(f.bytes/1024000),2 ) || 'MB' "FREE_MB",
max(f.bytes) "MAX_BYTES",
avg(f.bytes) " AVG_BYTES ",count(f.tablespace_name) " COUNT "
from dba_free_space f, dba_tablespaces d
where f.tablespace_name = d.tablespace_name
group by f.tablespace_name ,d.extent_management;
--**表空间使用率
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;
--**是否存在空间无法扩展的情况（时间长）
select segment_name "段名", segment_type "段类型",
owner "段所有者", a.tablespace_name "表空间名",
Initial_extent "初始扩展", next_extent "下次", pct_increase "增涨",
b.bytes "最大字节"
from dba_segments a,
( select tablespace_name, max(bytes) bytes from dba_free_space
group by tablespace_name ) b
where a.tablespace_name=b.tablespace_name and next_extent > b.bytes ;
--**检查是否存在需要合并的表空间
select f.tablespace_name "表空间",
trunc(sum(f.bytes/1024000),2 ) || 'MB' "自由空间",
min(f.bytes) "最小字节", MAX(f.bytes) "最大字节",
avg(f.bytes) "平均字节",COUNT(f.tablespace_name) "分布在"
from dba_free_space f, dba_tablespaces d
where f.tablespace_name = d.tablespace_name
having count(f.tablespace_name) >1
group by f.tablespace_name ,d.extent_management ;
--**检查自由表空间接近10%的情况（时间长）:
col "表空间" for a18
col "文件名" for a50
col "总空间(Mb)" for 999,999,999,999
col "自由空间(Mb)" for 999,999,999,999
col "自由空间%" for 9999.99
SELECT a.tablespace_name "表空间",
a.file_name "文件名",
a.avail "总空间(Mb)",
nvl(b.free,0) "自由空间(Mb)",
nvl(round(((free/avail)*100),2),0) "自由空间%"
from (select tablespace_name, substr(file_name,1,45) file_name,
file_id, round(sum(bytes/(1024*1024)),3) avail
from sys.dba_data_files
group by tablespace_name, substr(file_name,1,45),
file_id) a,
(select tablespace_name, file_id,
round(sum(bytes/(1024*1024)),3) free
from sys.dba_free_space
group by tablespace_name, file_id) b
where a.file_id = b.file_id (+)
order by 1, 2 ;
--**求表空间的未用空间
col mbytes format 9999.9999
select tablespace_name,sum(bytes)/1024/1024 mbytes from dba_free_space group by tablespace_name;
--** **回滚段使用情况 :
/* WAITS 在理想下，等待值应该是 0；GETS 是对回滚段访问次数；
如果等待值大于0，则肯定存在争用，可以在创建一些新的回滚段。
如果 ratio > 2 ,则有竞争，应建立更多的回滚段 */
col "得到" for 999,999,999
col "回滚段" for a16
col "比率" for 99.99
col "等待" for 999,999
SELECT rn.name "回滚段" , rs.gets "得到",
rs.waits "等待" ,
(rs.waits/rs.gets)*100 "比率"
from v$rollstat rs, v$rollname rn
where rs.usn=rn.usn ;
V$ROLLSTAT中的常用列
USN：回滚段标识
RSSIZE：回滚段默认大小
XACTS：活动事务数
在一段时间内增量用到的列
WRITES：回滚段写入数(单位:bytes)
SHRINKS：回滚段收缩次数
EXTENDS：回滚段扩展次数
WRAPS：回滚段翻转(wrap)次数
GETS：获取回滚段头次数
WAITS：回滚段头等待次数
--**系统回滚段使用统计情况1:
col USN for 999
col NAME for a18
col EXTENTS for 999
col RSSIZE for 999,999,999,999
col WRITES for 999
col XACTS for 9999
col GETS for 999
col WAITS for 999
col HWMSIZE for 999,999,999,999
col OPTSIZE for 999,999,999
col STATUS for a10
Set linesize 150
Select a.usn, name, extents, rssize, optsize, hwmsize,waits,status
From v$rollstat a , v$rollname b
Where a.usn=b.usn order by a.usn;
SELECT n.name, s.extents, s.rssize, s.optsize, s.hwmsize, s.xacts, s.status
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;
--** 系统回滚段使用统计情况2:
Column Class Format A20 Heading 'Header Type'
Column Count Format 999,999,999 Heading 'Number |of waits'
Column Con_Get Format 999,999,999,999 Heading 'Logical| Reads'
Column Pct Format 990.99 Heading 'Pct of |Contention'
Column Instance New_Value _Instance NoPrint
Column Today New_Value _Date NoPrint
Define Wait_Ratio = 1 (Number)
Select A.Class, Count, Sum(Value) Con_Get, ((Count / Sum(Value)) * 100) pct
From V$WaitStat A, V$SysStat B
Where Name In ('db block gets', 'consistent gets')
Group by A.Class, Count;
Column Name Format A30
Column Gets Format 999,999,999 Heading 'Number of|Activities'
Column Waits Format 999,999,999 Heading 'Number|of Waits'
Column Pct Format 990.99 Heading 'Pct of|Gets'
Select Name, Gets, Waits, ((Waits / Gets) * 100) Pct
From V$RollStat A, V$RollName B
Where A.USN = B.USN;
--**查看回滚段的使用情况，哪个用户正在使用回滚段的资源
select s.username, u.name
from v$transaction t, v$rollstat r, v$rollname u, v$session s
where s.taddr = t.addr
and t.xidusn = r.usn
and r.usn = u.usn
order by s.username;
--** 检查undo表空间情况:
Select to_char(begin_time,'yyyy.mm.dd hh24:mi:ss'),
to_char(end_time,'yyyy.mm.dd hh24:mi:ss'), undoblks,
txncount, maxquerylen from v$undostat;
--** 查询undo表空间信息:
SELECT (rd * (ups * overhead) + overhead) AS "Bytes"
FROM
(SELECT value AS RD
FROM v$parameter
WHERE name = 'undo_retention'),
(SELECT (SUM(undoblks) /
SUM( ((end_time - begin_time) * 86400)))
AS UPS
FROM v$undostat),
(SELECT value AS overhead
FROM v$parameter
WHERE name = 'db_block_size');
--** 查询事务在Undo空间中执行的时间：
col "用撤消块数" for 999,999,999,999
col "事务执行块数" for 999,999,999,999
col "查询最长秒" for 99,999,999
col "ORA-01555次数" for 999,999
col "无可用空间数" for 999,999
select TO_CHAR(MIN(Begin_Time),'DD HH24:MI:SS') "开始时间",
TO_CHAR(MAX(End_Time),'DD HH24:MI:SS') "结束时间",
SUM(Undoblks) "用撤消块数",
SUM(Txncount) "事务执行块数",
MAX(Maxquerylen) "查询最长秒",
MAX(Maxconcurrency) "最高事务数",
SUM(Ssolderrcnt) "ORA-01555次数",
SUM(Nospaceerrcnt) "无可用空间数"
from V$UNDOSTAT;
-- ORA-01555次数出现,可减少UNDO_RETENTION
--**V$WAITSTAT视图可以被用来确定每个回滚段数据块的等待情况：
SELECT class, count
FROM V$WAITSTAT
WHERE class LIKE '%undo%'
AND COUNT > 0;
--**.V$SYSSTAT视图可以确定总的数据请求的数量：
SELECT SUM(value) "DATA REQUESTS"
FROM V$SYSSTAT
WHERE name IN ('db block gets', 'consistent gets');
--**.DBA_ROLLBACK_SEGS视图可以确定回滚段的存储信息和状态等等信息：
COL segment_name for a20
SELECT segment_name, owner, tablespace_name, status,
initial_extent, next_extent
FROM DBA_ROLLBACK_SEGS;
--**.V$TRANSACTION视图可以确定每个事务使用Undo空间的数量：
SELECT MAX(used_ublk) FROM V$TRANSACTION;
--**查看回滚段的争用情况
select name, waits, gets, waits/gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;
--**查看回滚段名称及大小
select segment_name, tablespace_name, r.status,
(initial_extent/1024) InitialExtent,(next_extent/1024)
NextExtent,
max_extents, v.curext CurExtent
From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+)
order by segment_name ;
--**求回滚段正在处理的事务
select a.name,b.xacts,c.sid,c.serial#,d.sql_text
from v$rollname a,v$rollstat b,v$session c,v$sqltext d,v$transaction e
where a.usn=b.usn and b.usn=e.xidusn and c.taddr=e.addr
and c.sql_address=d.address and c.sql_hash_value=d.hash_value order by a.name,c.sid,d.piece;
--**查看用户的回滚段的信息
select s.username, rn.name from v$session s, v$transaction t, v$rollstat r, v$rollname rn
where s.saddr = t.ses_addr and t.xidusn = r.usn and r.usn = rn.usn;
--**查看undo段的使用情况
SELECT d.segment_name,extents,optsize,shrinks,aveshrink,aveactive,d.status
FROM v$rollname n,v$rollstat s,dba_rollback_segs d
WHERE d.segment_id=n.usn(+) and d.segment_id=s.usn(+);
--** **Oracle系统排序区使用情
--** 内存与磁盘的排序情况:
COL "排序操作" for a40
SELECT name "排序操作" , value "操作次数"
FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');
select a.value "disk sort" , b.value "memory sort" ,
round(a.value/(b.value+a.value)*100,2) "disk sort percentage"
from V$SYSSTAT a , V$SYSSTAT b
where a.name = 'sort ( disk ) ' and b.name = 'sort (memory)' ;
--**求free memory
select * from v$sgastat where name='free memory';
select a.name,sum(b.value) from v$statname a,v$sesstat b where a.statistic# = b.statistic# group by a.name;
查看一下谁在使用那个可以的回滚段，或者查看一下某个可以的用户在使用回滚段，找出领回滚段不断增长的事务，再看看如何处理它，是否可以将它commit，再不行就看能否kill它,查看当前正在使用的回滚段的用户信息和回滚段信息:
set linesize 121
SELECT r.name "ROLLBACK SEGMENT NAME ",l.sid "ORACLE PID",p.spid "SYSTEM PID ",s.username "ORACLE USERNAME"
FROM v$lock l, v$process p, v$rollname r, v$session s
WHERE l.sid = p.pid(+) AND s.sid=l.sid AND TRUNC(l.id1(+)/65536) = r.usn AND l.type(+) = 'TX' AND l.lmode(+) = 6 ORDER BY r.name;
--** 查询排序操作命中率:
select a.value "Disk Sort",b.value "Memory Sort",
round((100*b.value)/decode((a.value+b.value),0,1,(a.value+b.value)),2)
"Pct Memory Sorts"
from v$sysstat a,v$sysstat b
where a.name = 'sorts(disk)'
and b.name='sorts(memory)' ;
--** 查询临时段:
col file_name for a30
col bytes for 999,999,999,999
col tablespace_name for a18
select file_name,tablespace_name,bytes,maxbytes,increment_by
from dba_temp_files;
--** 用户与临时表空间名:
COL USERNAME heading "用户名" for a20
COL TEMPORARY_TABLESPACE heading "临时表空间名" for a20
COL DEFAULT_TABLESPACE heading "默认表空间名" for a20
SELECT USERNAME,
TEMPORARY_TABLESPACE,
DEFAULT_TABLESPACE,
TO_CHAR(CREATED,'YYYY.MM.DD' ) "创建用户日期"
FROM DBA_USERS ORDER BY USERNAME ;
--** 显示排序参数当前值:
show parameter SORT_AREA_SIZE
show parameter SORT_AREA_RETAINED_SIZE
--**ALTER Session set SORT_AREA_SIZE=1024000 ;
--**ALTER SYSTEM set SORT_AREA_SIZE=1024000 DEFERRED ;
--** 监视临时段的使用情况:
select tablespace_name, current_users, total_extents,
used_extents, extent_hits, max_used_blocks,
max_sort_blocks
from v$sort_segment;
SELECT session_num, tablespace, extents, blocks
FROM v$sort_usage;
--** 检查不能分配的临时表空间:
SELECT '无法分配临时表空间的 INITIAL和NEXT '
FROM dba_tablespaces dt,
(SELECT MAX(bytes) max_size , tablespace_name FROM dba_free_space
WHERE tablespace_name ='TEMP'
GROUP BY tablespace_name ) fs
WHERE dt.tablespace_name = fs.tablespace_name AND
( dt.initial_extent > fs.max_size OR dt.next_extent > fs.max_size );
--** **10g版本回收站有关信息
-- **Oracle 10g 功能检查-Flashback：
show parameter RECYCLEBIN
--** 回收站信息
col owner for a20
col ORIGINAL_NAME for a28
select owner,object_name,original_name,type,space
from dba_recyclebin order by owner,type;
--**清空Oracle 10g回收站中以BIN$开头的表
使用oracle 10g时出现了许多奇怪的表名，以BIN$开头，上网查找后发现是oracle10g的回收站功能，并没有彻底的删除表，而是把表放入回收站！！！
清除的方法如下：
purge table origenal_tableName;--原来的表名
或者在删除表时加上PURGE选项，如：
DROP TABLE t_test PURGE;
清空整个回收站的命令：
PURGE recyclebin;
查询回收站垃圾信息的SQL语句：
SELECT object_name,type ,original_name FROM user_recyclebin ;
--** 有关路径与大小
--DB_RECOVERY_FILE_DEST
show parameter DB_RECOVERY_FILE_DEST
--
show parameter db_flashback_retention_target
-- ** 闪回有关信息
SELECT estimated_flashback_size,
flashback_size
FROM v$flashback_database_log;
--
SELECT oldest_flashback_scn,
oldest_flashback_time
FROM v$flashback_database_log;
--
SELECT * FROM v$flashback_database_stat;
--** **Oracle系统SGA
--** 要获得详细信息采用STATSPACK(见另外脚本)
--** SGA 当前参数情况：
COL VALUE for 999,999,999,999
select * from V$SGA ;
--**查看用户使用内存情况
select username, sum(sharable_mem), sum(persistent_mem), sum(runtime_mem)
from sys.v_$sqlarea a, dba_users b
where a.parsing_user_id = b.user_id group by username;
--**查看内存使用情况
select least(max(b.value)/(1024*1024),sum(a.bytes)/(1024*1024)) shared_pool_used,max(b.value)/(1024*1024) shared_pool_size,greatest(max(b.value)/(1024*1024),sum(a.bytes)/(1024*1024))-(sum(a.bytes)/(1024*1024)) shared_pool_avail,((sum(a.bytes)/(1024*1024))/(max(b.value)/(1024*1024)))*100 avail_pool_pct
from v$sgastat a, v$parameter b where (a.pool='shared pool' and a.name not in ('free memory')) and b.name='shared_pool_size';
--**查看内存中存的使用
select decode(greatest(class,10),10,decode(class,1,'Data',2,'Sort',4,'Header',to_char(class)),'Rollback') "Class",
sum(decode(bitand(flag,1),1,0,1)) "Not Dirty",sum(decode(bitand(flag,1),1,1,0)) "Dirty",
sum(dirty_queue) "On Dirty",count(*) "Total"
from x$bh group by decode(greatest(class,10),10,decode(class,1,'Data',2,'Sort',4,'Header',to_char(class)),'Rollback');
--** 内存分配情况:
COL Name for a40
COL Values for a40
select name, value
from v$parameter
where name in
('db_block_buffers','db_cache_size','db_block_size','shared_pool_size','sort_area_size');
--**如果90-95% 间,表示缓冲区设置低了.
--** 查询数据高速缓冲区的命中率1:
select a.value + b.value "逻辑读", c.value "物理读",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "缓冲区命中率"
from v$sysstat a, v$sysstat b, v$sysstat c
where (a.value !=0 OR b.value != 0 ) and
a.STATISTIC# = 38 and b.statistic# = 39
and c.statistic# = 40;
-- v$statname
--** 查询数据高速缓冲区的命中率2:
SELECT 'buffer cache hit ratio',
round((congets.value+dbgets.value-physreads.value)*100/
(congets.value+dbgets.value),4) "HIT RATE"
from v$sysstat congets,
v$sysstat dbgets,
v$sysstat physreads
where congets.name='consistent gets'
and dbgets.name = 'db block gets'
and physreads.name = 'physical reads' ;
--** 查询数据高速缓冲区的命中率3:
col name for a30
col value for 999,999,999,999
select name, value from v$sysstat
where name in ('consistent gets','db block gets','physical reads' ) ;
/* 如果 ratio = 1- ( physical reads / ( db block gets + consistent gets ) ) 低于 70%。
则应该加大INITsid.ORA文件中的DB_BLOCK_BUFFERS参数值
9I用ALter system set db_cache_size= 100m scope=spfile ; */
--**测定数据的命中率(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))))) "Read Hit Ratio"
from v$sysstat;
--** 查询数据高速缓冲区的使用情况4:
/*
?Total Reads = db block gets + consistent gets
?Hit Ratio = 1 - (physical reads) / (db block gets + consistent gets)
?Buffer Hit Ratio > 95% for an Interactive Intensive System and 85% for a Batch Intensive System
select sum(decode(name, 'physical reads', value, 0)) dsk_rds,
sum(decode(name, 'db block gets', value, 0)) blk_gts,
sum(decode(name, 'consistent gets', value, 0)) con_gts,
((1 - (sum(decode(name, 'physical reads', value, 0)) /
(sum(decode(name, 'db block gets', value, 0)) +
sum(decode(name, 'consistent gets', value, 0))))) * 100) hit_rate
from v$sysstat;
--** 查询数据高速缓冲区的使用情况5:
select decode(state, 0, 'Free', 1, 'Modified',2, 'Not Modified', 3, 'Being Read', 'Other') stats ,
count(*) cnt
from sys.x$bh
group by state;
--** 监视会话内存及磁盘活动情况6:
select a.sid, a.username, b.block_gets, b.consistent_gets,
b.physical_reads
from v$session a, v$sess_io b
where a.sid = b.sid
order by a.username;
--** 查询共享池命中率:
select 'recursive calls/total calls',
(rc.value/(rc.value+uc.value))*100
from v$sysstat rc, v$sysstat uc
where rc.name='recursive calls' and uc.name='user calls';
--** 共享池高速缓存区的命中率1:
select sum(pinhits-reloads)/sum(pins) "命中率",
sum(reloads)/sum(pins) "重加载%"
from v$librarycache;
--** 共享池高速缓存区的命中率2:
Column Pins Format 999,999,990 Heading 'Executions'
Column PinHits Format 999,999,990 Heading 'Executions'
Column PHitRat Format 990.99 Heading 'Hit |Ratio'
Column Reloads Format 999,999,990 Heading 'Misses'
Column HitRat Format 990.99 Heading 'Hit |Ratio'
Column Instance New_Value _Instance NoPrint
Column Today New_Value _Date NoPrint
Define Hit_Ratio = 90 (Number)
Define Reld_Ratio = 1 (Number)
Select Sum(Pins) Pins, Sum(PinHits) PinHits,
((Sum(PinHits) / Sum(Pins)) * 100) PHitRat,
Sum(Reloads) Reloads,
((Sum(Pins) / (Sum(Pins) + Sum(Reloads))) * 100) HitRat
From V$LibraryCache;
--** 查询字典缓冲区的命中率3:
select parameter "参数", gets "得到",Getmisses "失败",
TRUNC(getmisses/(gets+getmisses)*100,2) "失败比率",
TRUNC((1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100,2) "命中率"
from v$rowcache
where gets+getmisses <>0
group by parameter, gets, getmisses;
--** 查询数据字典命中率:
--**命中率>95%以上是可接受的
select sum(gets) "Gets", sum(getmisses) "Misses",
(1 - (sum(getmisses) / (sum(gets) +
sum(getmisses))))*100 "HitRate"
from v$rowcache;
--**查看字典命中率
select (sum(getmisses)/sum(gets)) ratio from v$rowcache;
--** 查询库缓存的命中率:
select sum(pins)/(sum(pins)+sum(reloads))*100 "Hit Ratio"
from v$librarycache;
--** 查询库缓存的命中率:
select sum(pins) Executions, sum(pinhits) "Execution Hits",
((sum(pinhits) / sum(pins)) * 100) phitrat,
sum(reloads) Misses,
((sum(pins) / (sum(pins) + sum(reloads))) * 100) hitrat
from v$librarycache;
--**查看库缓存命中率
select namespace,gets, gethitratio*100 gethitratio,pins,pinhitratio*100 pinhitratio,RELOADS,INVALIDATIONS from v$librarycache;
--** 查询库缓存的使用百分比:
col value for 999,999,999 heading "Shared Pool Size"
col bytes for 999,999,999 heading "Free Bytes"
select to_number(v$parameter.value) value, v$sgastat.bytes,
(v$sgastat.bytes/NVL(v$parameter.value,1) )*100 "Percent Free"
from v$sgastat, v$parameter
where v$sgastat.name = 'free memory'
and v$parameter.name = 'shared_pool_size';
--** 查询对象缓存情况:
-- 查询结果过多 !
/*
select type, name, sharable_mem, kept
from v$db_object_cache
order by type, kept, sharable_mem;
*/
--** 日志缓存区的命中率
col "操作名" for a20
col "得到" for 999,999,999
col "失败" for 999,999,999
col "立即得到" for 999,999,999
col "立即失败" for 999,999,999
SELECT name "操作名",
Gets "得到" , misses "失败", immediate_gets "立即得到",
immediate_misses "立即失败" ,
Decode(gets,0,0,misses/gets*100) "失败比率" ,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) "立即失败比率"
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
--** 查询系统缓冲区命中率:
select ( 1-(sum(decode(name,'physical read',value,0))/
(sum(decode(name,'db block gets',value,0))+
sum(decode(name,'consistent gets',value,0)) )))*100 "Hit ratio"
from v$sysstat;
--** 查询系统缓冲区命中率及评分:
select ( 1-(sum(decode(name,'physical read',value,0))/
(sum(decode(name,'db block gets',value,0))+
sum(decode(name,'consistent gets',value,0)) )))*100 "Hit ratio",
decode(sign(( 1-(sum(decode(name,'physical read',value,0))/
(sum(decode(name,'db block gets',value,0)) +
sum(decode(name,'consistent gets',value,0)) )))*100-98),1,30,
decode(sign(( 1-(sum(decode(name,'physical read',value,0))/
(sum(decode(name,'db block gets',value,0))+
sum(decode(name,'consistent gets',value,0)) )))*100-95),1,20,
decode(sign(( 1-(sum(decode(name,'physical read',value,0))/
(sum(decode(name,'db block gets',value,0))+
sum(decode(name,'consistent gets',value,0)) )))*100-90),1,10,0))) "Score"
from v$sysstat;
--**求cache中缓存超过其5%的对象
select o.owner,o.object_type,o.object_name,count(b.objd)
from v$bh b,dba_objects o
where b.objd = o.object_id
group by o.owner,o.object_type,o.object_name
having count(b.objd) > (select to_number(value)*0.05 from v$parameter where name = 'db_block_buffers');
--** 统计恢复日志有无竞争的情况:
/* 如果immediate_contention > 1，则存在竞争 */
col name for a30
select name,(immediate_misses/
decode((immediate_gets+immediate_misses),0,-1,
(immediate_gets+immediate_misses)))*100 immediate_contention,
(misses/decode((gets - misses),0,-1))*100 wait_contention
from v$latch where name in('redo copy','redo allocation');
--** 系统是否设置共享池保留区:
--** 最好：REQUEST_MISS和REQUEST_FAILURES都接近 0 才行。
select REQUEST_MISSES, request_failures,requests,free_space
from V$SHARED_POOL_RESERVED ;
--** 数据缓冲区的使用情况:
col username for a20
col osuser for a20
select username, osuser,
(io.physical_reads/(io.block_gets+io.CONSISTENT_GETS)) "hit ratio"
from v$sess_io io , v$session sess
where io.sid = sess.sid
and (io.block_gets+io.CONSISTENT_GETS) != 0
and username is not null;
--**** 其它影响性能的信息搜集
--** ORACLE 系统会话情况:
Col sid for 99999999
col WAIT_TIME for 999,999,999
col SECONDS_IN_WAIT for 999,999,999
SELECT SID, username,terminal ,
WAIT_TIME ,
SECONDS_IN_WAIT, LOCKWAIT
FROM v$session;
--** ORACLE 系统会话过多的情况:
select 'Too many sessions ( '||to_char(count(*))||' running ',
substr(sql_text,1,80) query
from v$session s, v$sqlarea sa
where s.sql_address = sa.address
and s.sql_hash_value = sa.hash_value
and s.status = 'ACTIVE'
and s.audsid != 0
and sql_text not like 'select sql_text%'
group by substr(sql_text,1,80)
having count(*) > 100;
--** 显示用户等待的时间 :
SELECT b.username, a.wait_time, a.seconds_in_wait, a.state
From V$SESSION_WAIT a, V$SESSION b
Where a.SID=b.SID ;
--**查看系统请求情况
SELECT DECODE (name, 'summed dirty write queue length', value)/
DECODE (name, 'write requests', value) "Write Request Length"
FROM v$sysstat WHERE name IN ( 'summed dirty queue length', 'write requests') and value>0;
--** **等待事件等信息
--**查询系统运行以来的等待事件
/**********************************************************************
V$SYSTEM_EVENT是一个动态性能视图,记录自系统启动以来的等待事件,
包括各种类型的数据库冲突等.输出结果按照V$SYSTEM_EVENT视图的
TIME_WAITED列的顺序排列,如果TIMED_STATISTICS未设为TRUE,
则TIME_WAITED列不会有内容.
*********************************************************************/
--截断查询结果最后的空格 set trimspool on
set trimspool on
set trimout on
-- 显示执行脚本的命令 set echo on
set echo on
col event format a26 truncate
col total_waits format 999,990.00
col total_timeouts format 999,990.00
col time_waited format 999,990.00
col pct_significant format 90.00
col average_wait format 990.00
col instance new_value V_INSTANCE noprint
col total_time_waited new_value V_TOTAL_TIME_WAITED noprint
select sum(time_waited) total_time_waited
from sys.v_$system_event
where event not in ('SQL*Net message from client',
'rdbms ipc message',
'slave wait',
'pmon timer',
'smon timer',
'rdbms ipc reply',
'SQL*Net message to client',
'SQL*Net break/reset to client',
'inactive session',
'Null event');
select event,
(total_waits / 1000) total_waits,
(total_timeouts / 1000) total_timeouts,
(time_waited / 360000) time_waited,
decode(event,
'SQL*Net message from client', 0,
'rdbms ipc message', 0,
'slave wait', 0,
'pmon timer', 0,
'smon timer', 0,
'rdbms ipc reply', 0,
'SQL*Net message to client', 0,
'SQL*Net break/reset to client', 0,
'inactive session', 0,
'Null event', 0,
(time_waited / &&V_TOTAL_TIME_WAITED)*100) pct_significant,
(average_wait / 100) average_wait
from sys.v_$system_event
where (time_waited/360000) >= 0.01
order by pct_significant desc, time_waited desc;
--**监控实例的等待:
select event,sum(decode(wait_Time,0,0,1)) "Prev",
sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot"
from v$session_Wait
group by event order by 4;
--**找使用CPU多的用户session
COL program for a40
COL spid for A12
Select a.sid, spid, status,
substr(a.program,1,40) program,
a.terminal,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by status,value desc;
--其中:12是统计编号,即cpu used by this session;可用:
--select STATISTIC#,name from v$statname查到.
--**求等待的事件及会话信息/求会话的等待及会话信息
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%' order by s.username;
select s.sid,s.username,sw.event,sw.wait_time,sw.state,sw.seconds_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 s.username;
--**求等待中的对象 (运行时间超长！！）
select /*+rule */ s.sid, s.username, w.event, o.owner, o.segment_name, o.segment_type,
o.partition_name, w.seconds_in_wait seconds, w.state
from v$session_wait w, v$session s, dba_extents o
where w.event in (select name from v$event_name where parameter1 = 'file#'
and parameter2 = 'block#' and name not like 'control%')
and o.owner <> 'sys' and w.sid = s.sid and w.p1 = o.file_id and w.p2 >= o.block_id and w.p2 < o.block_id + o.blocks;
--**查找前10条性能差的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;
--**查找耗资源的进程(top session):
col SCHEMA_NAME for a18
col OS_USER_NAME for a18
col ACTION for a30
col USER_NAME for a18
SELECT s.schemaname schema_name,
decode(sign(48 - command), 1, to_char(command), 'Action Code #' || to_char(command) ) action,
status session_status,s.osuser os_user_name,
s.sid,p.spid,s.serial# serial_num,nvl(s.username,'[Oracle process]') user_name,
s.terminal terminal,s.program program,st.value criteria_value
FROM v$sesstat st,v$session s,v$process p
WHERE st.sid = s.sid and st.statistic# = to_number('38')
and ('ALL'='ALL' or s.status ='ALL') and p.addr=s.paddr
order by st.value desc,p.spid asc,s.username asc,s.osuser asc;
--**查看占io较大的正在运行的session:
SELECT se.sid,se.serial#,pr.SPID,se.username,se.status,se.terminal,
se.program, se.MODULE,se.sql_address,st.event,
st.p1text,si.physical_reads,si.block_changes
FROM v$session se,v$session_wait st,v$sess_io si,v$process pr
WHERE st.sid=se.sid AND st.sid=si.sid AND se.PADDR=pr.ADDR
AND se.sid>6 AND st.wait_time=0
AND st.event NOT LIKE '%SQL%' ORDER BY physical_reads DESC;
--**查看表空间数据文件的读写性能:
Select name,phyrds,phywrts,avgiotim,miniotim,maxiowtm,maxiortm from v$filestat,v$datafile where v$filestat.file#=v$datafile.file#;
Select fs.name name,f.phyrds,f.phyblkrd,f.phywrts,f.phyblkwrt ,f.readtim,f.writetim
from v$filestat f, v$datafile fs where f.file# = fs.file# order by fs.name;
/*注意：如果phyblkrd与phyrds很接近的话，则表明这个表空间中存在全表扫描的表，这些表需要调整索引或优化SQL语句*/
--**Disk Read最高的SQL语句的获取：
Select sql_text from (select * from v$sqlarea order by disk_reads)
where rownum<=5;
--**1 buffer gets top 10 sql:
select * from
(select substr(sql_text,1,40) sql, buffer_gets,
executions, buffer_gets/executions "Gets/Exec",
hash_value,address
from v$sqlarea
where buffer_gets > 10000
order by buffer_gets desc)
where rownum <= 10 ;
select * from
(select substr(sql_text,1,40) sql, buffer_gets,
executions, buffer_gets/executions "Gets/Exec",
hash_value,address
from v$sqlarea
where buffer_gets > 0 and executions>0
order by buffer_gets desc)
where rownum <= 10 ;
--**2 Physical Reads top 10 sql:
select * from
(select substr(sql_text,1,40) sql, disk_reads,
executions, disk_reads/executions "Reads/Exec",
hash_value,address
from v$sqlarea where disk_reads > 1000
order by disk_reads desc) where rownum <= 10;
select * from
(select substr(sql_text,1,40) sql, disk_reads,
executions, disk_reads/executions "Reads/Exec",
hash_value,address
from v$sqlarea where disk_reads > 0 and executions >0
order by disk_reads desc) where rownum <= 10;
--**3 Executions top 10 sql:
select * from
(select substr(sql_text,1,40) sql, executions,
rows_processed, rows_processed/executions "Rows/Exec",
hash_value,address
from v$sqlarea where executions > 100
order by executions desc) where rownum <= 10 ;
--**4 Parse Calls top 10 sql:
select * from
(select substr(sql_text,1,40) sql, parse_calls,
executions, hash_value,address
from v$sqlarea where parse_calls > 1000
order by parse_calls desc) where rownum <= 10 ;
--**5 Sharable Memory top 10 sql:
select * from
(select substr(sql_text,1,40) sql, sharable_mem,
executions, hash_value,address
from v$sqlarea where sharable_mem > 1048576
order by sharable_mem desc)
where rownum <= 10;
--**6 CPU usage top 10 sql:
select * from
(select sql_text, round(cpu_time/1000000)
cpu_time,
round(elapsed_time/1000000) elapsed_time,
disk_reads,
buffer_gets,
rows_processed
from v$sqlarea order by cpu_time desc, disk_reads desc )
where rownum < 10;
--**7 Running Time top 10 sql:
select * from
(select t.sql_fulltext,
(t.last_active_time-to_date(t.first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60 time,
disk_reads,buffer_gets,rows_processed,
t.last_active_time,t.last_load_time,t.first_load_time
from v$sqlarea t order by t.first_load_time desc)
Where rownum < 10 order by time desc;
select * from
(select
(t.last_active_time-to_date(t.first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60 time,
T.sql_id,
t.last_active_time,t.last_load_time,t.first_load_time
from v$sqlarea t order by t.first_load_time desc)
order by time asc;
--**最占资源的10个查询
select b.username username,a.disk_reads reads,
a.executions exec,a.disk_reads/decode(a.executions,0,1,a.executions) rds_exec_ratio,
a.sql_text Statement
from v$sqlarea a,dba_users b
where a.parsing_user_id=b.user_id
and a.disk_reads > 100000 and rownum<10
order by a.disk_reads desc;
--**捕捉运行很久的SQL ---正在运行的
select username,sid,opname,
round(sofar*100 / totalwork,0) || '%' as progress,
time_remaining,sql_text
from v$session_longops , v$sql
where time_remaining <> 0
and sql_address = address
and sql_hash_value = hash_value;
round(sofar*100 / totalwork,0) || '%' as progress,
time_remaining,start_time from v$session_longops , v$sql
and sql_hash_value = hash_value;
--**求DISK READ较多的SQL
--**求DISK SORT严重的SQL
v$session.serial# = V$TEMPSEG_USAGE.session_num
V$TEMPSEG_USAGE.sqladdr = v$sqlarea.address
V$TEMPSEG_USAGE.sqlhash = v$sqlarea.hash_value
-- **SQL语句冲突:
select 'Too many sessions ( '||to_char(count(*))||' running '||
substr(sa.sql_text,1,80) query
from v$session s, v$sqlarea sa
where s.sql_address = sa.address
and s.sql_hash_value = sa.hash_value
and sql_text not like 'select sql_text%'
group by substr(sa.sql_text,1,80)
--**等待时间最多的5个系统等待事件的获取：
--**造成等待的LOCK信息，如LOCK类型等：
select b.sid,b.serial#,b.username,machine,a.event,a.wait_time,
chr(bitand(a.p1,-16777216)/16777215)||chr(bitand(a.p1, 16711680)/65535) "Enqueue Type"
from v$session_wait a,v$session b
where a.event not like 'SQL*N%'
and a.event not like 'rdbms%' and a.sid=b.sid
and b.sid>8 and a.event='enqueue'
--**等待事件信息：
select event,count(*),sum(seconds_in_wait) siw
--** **10g版本统计搜集
--** 查询没有分析的表的信息（运行时间长）：
where owner not in ('SYS','SYSTEM')
--** 查询最近没有分析的表的信息（运行时间长）：
-- Not analyzed in for pre-determined number of days
where owner not in ('SYS','SYSTEM')
and trunc(last_analyzed) < trunc(sysdate)-5;
--****无效对象检查
--** 检查索引无用信息:
--**查看无效的对象
--**查看无效的数据库对象
SELECT owner, object_name, object_type FROM dba_objects WHERE status='INVALID';
--**查看不起作用的约束
--**查看无效的trigger
SELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE status = 'DISABLED' ;
--**求出无效的对象
--****ORACLE系统数据文件情况
--**查看数据文件的状态记录状态不是"online"的数据文件
Select file_name from dba_data_files where status='OFFLINE';
---****会话有关
--**求当前session的跟踪文件
--**求process/session的状态
select p.pid,p.spid,s.program,s.sid,s.serial#
from v$process p,v$session s where s.paddr=p.addr;
--**求当前session的状态
--**求当前会话的SID，SERIAL#
select sid, serial# from v$session where audsid = SYS_CONTEXT('USERENV','SESSIONID');
--**求当前数据库建立的会话:
select sid,serial#,username,program,machine,status from v$session;
--**求当前会话的SID，SERIAL#
SELECT Sid, Serial# FROM V$session WHERE Audsid = Sys_Context('USERENV', 'SESSIONID');
--**求使用CPU多的用户session
select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;
--**求会话等待的file_id/block_id
--**求谁阻塞了某个session(10g)
--**求当前事务的重做尺寸
--**查看耗资源的进程（top session）
SELECT s.Schemaname Schema_Name,
1, To_Char(Command), 'Action Code #' || To_Char(Command)) Action,
Status Session_Status, s.Osuser Os_User_Name, s.Sid, p.Spid,
s.Serial# Serial_Num, Nvl(s.Username, '[Oracle process]') User_Name,
s.Terminal Terminal, s.Program Program, St.VALUE Criteria_Value
FROM V$sesstat St, V$session s, V$process p
AND St.Statistic# = To_Number('38')
AND ('ALL' = 'ALL' OR s.Status = 'ALL')
ORDER BY St.VALUE DESC, p.Spid ASC, s.Username ASC, s.Osuser ASC;
*************************************************
--**查看正在运行的job
select job from dba_jobs_running;
--**求系统中较大的latch
--**求某个事务的重做信息(bytes)
--**求回退率
--**求索引中行数较多的
--**求未定义索引的表
--**求出锁定的对象
--**查看对象的缓存情况
--**查看某些用户的hash
--**求某个隐藏参数的值
--****与权限相关的字典
--****在AIX系统中根据pid获得sql
发现user中的cpu过高，其中oracle的进程CPU比较高。
比如oracle其中最高的进程pid为1138782 占CPU 27%
select s.sid,s.serial# from v$session s,v$process p
where s.paddr=p.addr and p.spid='&pid';
WHERE a.hash_value = (SELECT sql_hash_value
--**跟踪用户session 的方法
3、select * from v$sql a where a.SQL_ID={上面查询结果中的sql_id}
--**在oracle中查找有多少表被锁住
select b.owner table_owner,b.object_name,c.username,c.sid,c.serial#
from v$locked_object a,dba_objects b,v$session c
where a.object_id=b.object_id and c.sid=a.session_id;
--**查找一个表是否 被锁的方法，例如：临时表SHZGY.SHZGY_PZ_BB_ERROR1
select object_id, owner||'.'||object_name object_name
and object_name like 'SHZGY_PZ_BB_ERROR1';
---------- ----------------------------------
105421 SHZGY.SHZGY_PZ_BB_ERROR1
select * from v$lock where id1 = 105421;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
A08E1304 A08E1314 29 TO 105421 1 3 0 1209396 0
A08D4904 A08D4914 30 TO 105421 1 3 0 1295439 0
A08E1188 A08E1198 32 TO 105421 1 3 0 1284027 0
A08E12B8 A08E12C8 77 TO 105421 1 3 0 1209674 0
A08E11D4 A08E11E4 120 TO 105421 1 3 0 1280668 0
A08E1350 A08E1360 144 TO 105421 1 3 0 1209330 0
A08E1220 A08E1230 151 TO 105421 1 3 0 1279593 0
--**oracle kill session的方法
select b.owner table_owner,b.object_name,c.username,c.sid,c.serial#
from v$locked_object a,dba_objects b,v$session c
where a.object_id=b.object_id and c.sid=a.session_id;
这样根据sid和serial#就可以直接杀掉（具体方法下面介绍）
查找一个表是否 被锁的方法，例如：临时表SHZGY.SHZGY_PZ_BB_ERROR1
select object_id, owner||'.'||object_name object_name
and object_name like 'SHZGY_PZ_BB_ERROR1';
---------- ----------------------------------
105421 SHZGY.SHZGY_PZ_BB_ERROR1
select * from v$lock where id1 = 105421;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
A08E1304 A08E1314 29 TO 105421 1 3 0 1209396 0
A08D4904 A08D4914 30 TO 105421 1 3 0 1295439 0
A08E1188 A08E1198 32 TO 105421 1 3 0 1284027 0
A08E12B8 A08E12C8 77 TO 105421 1 3 0 1209674 0
A08E11D4 A08E11E4 120 TO 105421 1 3 0 1280668 0
A08E1350 A08E1360 144 TO 105421 1 3 0 1209330 0
A08E1220 A08E1230 151 TO 105421 1 3 0 1279593 0
如果可以kill,就可以 drop table shzgy.shzgy_pz_bb_error1;
select a.sid, a.SERIAL#, b.spid, a.status, a.PROGRAM
where a.sid in (29, 30, 32, 77, 120, 144, 151)
SID SERIAL# SPID STATUS PROGRAM
---------- ---------- ------------ -------- ------------------------
29 619 9738 INACTIVE JDBC Thin Client
30 47 7608 INACTIVE JDBC Thin Client
32 148 8014 INACTIVE JDBC Thin Client
77 198 9736 INACTIVE JDBC Thin Client
120 258 8204 INACTIVE JDBC Thin Client
144 17 9762 INACTIVE JDBC Thin Client
151 253 8232 INACTIVE JDBC Thin Client
这七个会话都是JDBC调用,status=INACTIVE。
然后根据这些调用和活动状态判断是否可以kill掉这些session。
使用 dbms_system.set_sql_trace_in_session包来对这个session进行trace：
SQL > alter system kill session 'sid,SERIAL#' immediate;
这时这些session的状态就被标记为killed，Oracle会在该用户下一次 touch时清除该进程。
我们发现当一个session被kill掉以后，该session的paddr被修改，如果有多个session被kill，那么多个session的paddr都被更改为相同的进程地址:
SQL> select saddr,sid,serial#,paddr,username,status from v$session where username is not null;
SADDR SID SERIAL# PADDR USERNAME STATUS
-------- ---------- ---------- -------- ------------------------------ --------
542E0E6C 11 314 542B70E8 EYGLE INACTIVE
542E5044 18 662 542B6D38 SYS ACTIVE
SQL> alter system kill session '11,314';
SQL> select saddr,sid,serial#,paddr,username,status from v$session where username is not null;
SADDR SID SERIAL# PADDR USERNAME STATUS
-------- ---------- ---------- -------- ------------------------------ --------
542E0E6C 11 314 542D6BD4 EYGLE KILLED
542E5044 18 662 542B6D38 SYS ACTIVE
SQL> select saddr,sid,serial#,paddr,username,status from v$session where username is not null;
SADDR SID SERIAL# PADDR USERNAME STATUS
-------- ---------- ---------- -------- ------------------------------ --------
542E0E6C 11 314 542D6BD4 EYGLE KILLED
542E2AA4 14 397 542B7498 EQSP INACTIVE
542E5044 18 662 542B6D38 SYS ACTIVE
SQL> alter system kill session '14,397';System altered.
SQL> select saddr,sid,serial#,paddr,username,status from v$session where username is not null;
SADDR SID SERIAL# PADDR USERNAME STATUS
-------- ---------- ---------- -------- ------------------------------ --------
--**ORACLE10G的回收站
今天在10g库中发现有许多BIN$...等开头的一些表，忽然想起来是回收站的表。%bV&gDm"lw!PT@0
R)ai$p"b5B0在Oralce 10g中的回收站其实是一个逻辑容器（一个数据字典表，它包含了被删除的表的相关的信息）z"ST4`'h B/Vl0
z0L*_1HT__N:LB2b0desc recyclebin;查看recyclebin视图的表结构信息。7X OBQ7YvD-Z,O]U@0ITPUB个人空间'blEb M7])sn$uM
查看回收站中的内容：
pNr:D~*\;n.v0SQL>select object_name,droptime,dropscn,purge_object from recyclebin;L{G`#e!km GR0
U[(e1G*}e)@0在10R2中，我们可以通过控制初始化参数recyclebin关闭闪回删除功能，此参数默认值为ON，所以数据库默认情况下将把所有删除的表都要进入回收站，这样呢，我们可以利用闪回删除特性恢复它们；如果设置为OFF，那么将关闭数据库闪回删除功能，表在被删除之后将不进入回收站。XW$? MA6sc['^+z&[F0ITPUB个人空间 X7M/f1oOa|n |*B}
如果我们确认要删除一个张表，毫不含糊，那么可以使用：
?7cE^S}H0SQL>drop table table_name purge;这样的话，被删除的表将被彻底删除，不再会进回收站。
*nm1H0QsI+U0
Q-m.P:?*d@i0我们可以查看自己回收站中的内容，使用user_recyclebin视图；亦可以查看数据库中所有的回收站内容，使用dba_recyclebin视图。
.x_4Ykf]gs0如：
n"y7ypG0SQL>select owner,original_name,object_name,ts_name,droptime from dba_recyclebin;!W1O@(_.gwf3k0ITPUB个人空间kMv*x3]
我们可以看到不管是recyclebin、user_recyclebin还是dba_recyclebin；他们中的object_name都是一些很奇怪的名字（以BIN$开头，是系统自动生成的，此名称的长度通常为30个字符）。如果我们想查看回收站中某个对象的内容，那么我们必须使用系统生成的名称（以BIN$开头的），且用双引号括住。ITPUB个人空间8Ny TKt{%V
如：
b R L|6F3So!ot&{0SQL>select * from "BIN$.....";ITPUB个人空间2y8GD9\J
8DgR REQ0如果需要恢复回收站中的对象，那么：
3bX5@$jX0SQL>flashback table table_name to before drop;B#{\Q\i`@s0ITPUB个人空间(B0N&S!L']gBoR`'d
如果需要删除回收站中的对象，那么：ITPUB个人空间BYMI5Oo
SQL>purge table table_name;/purge index index_name;ITPUB个人空间dtPAYJ}
还有更省劲的呢，呵呵！那就是：
QdWAO9s5a"h$VD0SQL>purge recyclebin;清空整个回收站。查看select
trunc(completion_time) as "Date"
,count(*) as "Count"
,trunc((sum(blocks * block_size)) /1024 /1024) as "MB"
from v$archived_log
group by trunc(completion_time)
order by "Date"
;SQL> select
2 trunc(completion_time) as "Date"
3 ,count(*) as "Count"
4 ,trunc((sum(blocks * block_size)) /1024 /1024) as "MB"
5 from v$archived_log
6 group by trunc(completion_time)
7 order by "Date"
8 ;Date Count MB
--------------- ---------- ----------
02-JUN-10 87 3727
17-JUN-10 266 11344
18-JUN-10 655 29995
19-JUN-10 2 86
20-JUN-10 1 43
21-JUN-10 27 1140
22-JUN-10 18 777
23-JUN-10 6 247
24-JUN-10 4 172
25-JUN-10 6 259
26-JUN-10 4 172Date Count MB
--------------- ---------- ----------
27-JUN-10 3 129
28-JUN-10 4 172
29-JUN-10 4 172
30-JUN-10 7 294
01-JUL-10 3 129
02-JUL-10 4 172
03-JUL-10 7 302
04-JUL-10 3 129
05-JUL-10 2 8620 rows selected.
--**Oracle 总量和剩余量的查询
select sum(bytes)/1024/1024/1024 from dba_data_files; 所有的总量
select sum(bytes)/1024/1024/1024 G from dba_free_space; 所有的余量
select sum(bytes)/1024/1024/1024 from dba_data_files where TABLESPACE_NAME='APPS_UNDOTS1'; 查看某个表空间的总量
select sum(bytes)/1024/1024/1024 G from dba_free_space where TABLESPACE_NAME='APPS_UNDOTS1';
--**Oracle表空间增加数据文件（扩展表空间）
监控表空间的使用是DBA的日常工作之一，Oracle扩展表空间很简单，一般有扩展某个数据文件大小或增加新的数据文件两种办法。举例说明如下。
首先我们要获取某个表空间的数据文件信息：
select file_name,file_id,tablespace_name,bytes/1024/1024 from dba_data_files
order by file_name;根据file_id扩展某个数据文件大小：
alter database datafile 12 resize 13312m;
要注意的是，如果使用裸设备，一般要先查看LV是否足够，否则以上扩展命令可能出错，查看方式如下：
查看VG信息：
ERPDB1@/home/oracle>lsvg
rootvg
oraclevg查看VG的LV：
ERPDB1@/home/oracle>lsvg -l oraclevg
oraclevg:
LV NAME TYPE LPs PPs PVs LV STATE MOUNT POINT
db_oravote raw 1 1 1 closed/syncd N/A
db_oraocr raw 1 1 1 closed/syncd N/A
db_system raw 3 3 1 open/syncd N/A
db_sysaux raw 5 5 1 open/syncd N/A
db_undotbs1 raw 3 3 1 open/syncd N/A
db_undotbs2 raw 3 3 1 open/syncd N/A
db_temp raw 46 46 1 closed/syncd N/Adb_erp_ht2 raw 56 56 1 open/syncd N/A
db_erp2 raw 72 72 1 open/syncd N/A查看VG的详细信息：
ERPDB1@/home/oracle>lsvg oraclevg
VOLUME GROUP: oraclevg VG IDENTIFIER: 00c65fbf00004c000000011697e0f5f9
VG STATE: active PP SIZE: 256 megabyte(s)
VG PERMISSION: read/write TOTAL PPs: 2328 (595968 megabytes)
MAX LVs: 512 FREE PPs: 1146 (293376 megabytes)
LVs: 57 USED PPs: 1182 (302592 megabytes)
OPEN LVs: 39 QUORUM: 5
TOTAL PVs: 8 VG DESCRIPTORS: 8
STALE PVs: 0 STALE PPs: 0
ACTIVE PVs: 8 AUTO ON: no
Concurrent: Enhanced-Capable Auto-Concurrent: Disabled
VG Mode: Concurrent
Node ID: 1 Active Nodes:
MAX PPs per VG: 130048
MAX PPs per PV: 1016 MAX PVs: 128
LTG size (Dynamic): 1024 kilobyte(s) AUTO SYNC: no
HOT SPARE: no BB POLICY: relocatable这里可见还有1146个可用的pp ，根据pp size能计算出VG的容量。
查看某个LV的详细信息：
ERPDB1@/home/oracle>lslv db_erp2
LOGICAL VOLUME: db_erp2 VOLUME GROUP: oraclevg
LV IDENTIFIER: 00c65fbf00004c000000011697e0f5f9.44 PERMISSION: read/write
VG STATE: active/complete LV STATE: opened/syncd
TYPE: raw WRITE VERIFY: off
MAX LPs: 512 PP SIZE: 256 megabyte(s)
COPIES: 1 SCHED POLICY: parallel
LPs: 72 PPs: 72
STALE PPs: 0 BB POLICY: relocatable
INTER-POLICY: minimum RELOCATABLE: no
INTRA-POLICY: middle UPPER BOUND: 128
MOUNT POINT: N/A LABEL: None
MIRROR WRITE CONSISTENCY: off
EACH LP COPY ON A SEPARATE PV ?: no
Serialize IO ?: NO
DEVICESUBTYPE : DS_LVZ注意这里可以根据pp 的数量和大小计算能提供的容量。
扩展某个LV，增加32个pp ：
p5a1@/orabak/arch/transmited#extendlv db_erp_cw 32
p5a1@/orabak/arch/transmited#ls -l /dev/rdb_erp_cw
crw-rw---- 1 oracle dba 53, 35 Dec 02 2007 /dev/rdb_erp_cwLV增加了以后，就可以用上面的命令扩展对应的数据文件了。
如果要新增LV，则一般这样操作：
p5a1@/#mklv -y db_erp_index2 -T O -w n -t raw -s n -r n oraclevg 156
p5a1@/#chown oracle.dba /dev/rdb_erp_index2
p5a1@/#ls -l /dev/rdb_erp_index2
crw-rw---- 1 oracle dba 53, 58 Mar 26 20:02 /dev/rdb_erp_index2然后增加数据文件：
SQL> ALTER TABLESPACE erp_index ADD DATAFILE '/dev/rdb_erp_index2' SIZE 4096m;
Tablespace altered.
如果是ASM环境，查看可用空间和为表空间增加数据文件的脚本如下：
select group_number,name,total_mb,free_mb from v$asm_diskgroup;
ALTER TABLESPACE erp_index ADD DATAFILE '+DATA' SIZE 4096m;
单个数据文件的大小对性能基本没什么影响，单个数据文件过大就是可能有管理维护方面的潜在问题，比如数据文件损坏带来的数据损失。
--**根据sql_id来获得sql文本
select sql_text from v$sql where sql_id='064wqx7c5b81z';
select sql_text from v$sqltext where sql_id='064wqx7c5b81z';
--** 找出用户及系统权限的信息:
Col what_granted for a30
Col GRANTEE for a20
select b.privilege what_granted,
b.admin_option, a.username
from sys.dba_users a, sys.dba_sys_privs b
where a.username = b.grantee
order by 1,2;
/*
select privilege what_granted,
admin_option, grantee
from sys.dba_sys_privs
where not exists
(select 'x' from sys.dba_users
where username = grantee)
order by 1,2;
*/
--** 找出用户及角色的信息:
COL granted_role for a50
select b.granted_role ||decode(admin_option, 'YES', ' (With Admin Option)', null) granted_role, a.username FROM sys.dba_users a, sys.dba_role_privs b
where a.username = b.grantee order by 1;
--** 找出授予角色的权限信息:
COL what_granted for a50
select granted_role || decode(admin_option, 'YES', ' (With Admin Option)', null) what_granted, grantee
from sys.dba_role_privs
where not exists (select 'x' from sys.dba_users where username = grantee)
order by 1;
--** 找出数据库安全信息:
select a.username, b.granted_role || decode(admin_option,'YES',
' (With Admin Option)',null) what_granted
from sys.dba_users a, sys.dba_role_privs b
where
a.username = upper('&&user_name') AND
a.username = b.grantee
UNION
select a.username, b.privilege || decode(admin_option,'YES',
' (With Admin Option)', null) what_granted
from sys.dba_users a, sys.dba_sys_privs b
where
a.username = upper('&&user_name') AND
a.username = b.grantee
UNION
select a.username,
b.table_name || ' - ' || b.privilege
|| decode(grantable,'YES',
' (With Grant Option)',null) what_granted
from sys.dba_users a, sys.dba_tab_privs b
where
a.username = upper('&&user_name') AND a.username = b.grantee
order by 1;
--** Oracle用户及默认表空间:
col PROFILE for a20
col temporary_tablespace for a20
col DEFAULT_TABLESPACE for a20
select username, profile, default_tablespace,
temporary_tablespace, created
from sys.dba_users
order by username;
select username from
dba_users
where default_tablespace =
'SYSTEM';
select username from
dba_users
where temporary_tablespace =
'SYSTEM';
--** 产生无效对象编译脚本:
SELECT 'PROMPT Compile VIEW ' || OBJECT_NAME || ' ...' || CHR(10) ||
'ALTER VIEW ' || OBJECT_NAME || ' COMPILE;' || CHR(10)
|| 'SHOW ERRORS;' || CHR(10) "--COMPILE VIEW "
FROM DBA_OBJECTS
WHERE STATUS <> 'VALID'
AND OBJECT_TYPE = 'VIEW'
ORDER BY OBJECT_NAME;
SELECT 'PROMPT Compile TRIGGER ' || OBJECT_NAME || ' ...' || CHR(10) ||
'ALTER TRIGGER ' || OBJECT_NAME || ' COMPILE;' || CHR(10)
|| 'SHOW ERRORS;' || CHR(10) "--COMPILE TRIGGER "
FROM DBA_OBJECTS
WHERE STATUS <> 'VALID'
AND OBJECT_TYPE = 'TRIGGER'
ORDER BY OBJECT_NAME;
COL –Compile_PROCEDURE for a80
SELECT 'PROMPT Compile PROCEDURE ' || OBJECT_NAME || ' ...' || CHR(10) ||
'ALTER PROCEDURE ' || OBJECT_NAME || ' COMPILE;' || CHR(10)
|| 'SHOW ERRORS;' || CHR(10) "—COMPILE_PROCEDURE "
FROM DBA_OBJECTS
WHERE STATUS <> 'VALID'
AND OBJECT_TYPE = 'PROCEDURE'
ORDER BY OBJECT_NAME;
SELECT 'PROMPT Compile FUNCTION ' || OBJECT_NAME || ' ...' || CHR(10) ||
'ALTER FUNCTION ' || OBJECT_NAME || ' COMPILE;' || CHR(10)
|| 'SHOW ERRORS;' || CHR(10) "--COMPILE FUNCTION "
FROM DBA_OBJECTS
WHERE STATUS <> 'VALID'
AND OBJECT_TYPE = 'FUNCTION'
ORDER BY OBJECT_NAME;
SELECT 'PROMPT Compile PACKAGE ' || OBJECT_NAME || ' ...' || CHR(10) ||
'ALTER PACKAGE ' || OBJECT_NAME || ' COMPILE;' || CHR(10)
|| 'SHOW ERRORS;' || CHR(10) "--COMPILE PACKAGE "
FROM DBA_OBJECTS
WHERE STATUS <> 'VALID'
AND OBJECT_TYPE = 'PACKAGE'
ORDER BY OBJECT_NAME;
--** 列出表与同义词信息:
column owner format a15 heading 'Synonym Owner'
column table_owner format a12 heading 'Object Owner'
column table_name format a30 heading 'Object Name'
column table_type format a6 heading 'Type'
column synonym_name format a30 heading 'Synonym Name'
column db_link format a18 heading 'DB Link'
break on owner skip page on table_type on table_owner skip 1
select ds.owner,
table_owner,
substr(dc.table_type,1,1) table_type,
ds.table_name,
synonym_name,
db_link
from sys.dba_synonyms ds,
sys.dba_catalog dc
where ds.table_owner = upper('&&user_name') and
ds.table_owner = dc.owner
and ds.table_name = dc.table_name ;
SELECT 'PROMPT Compile PACKAGE BODY ' || OBJECT_NAME || ' ...' || CHR(10) ||
'ALTER PACKAGE ' || OBJECT_NAME || ' COMPILE BODY;'
|| CHR(10) || 'SHOW ERRORS;' || CHR(10) "--COMPILE BODY"
FROM DBA_OBJECTS
WHERE STATUS <> 'VALID'
AND OBJECT_TYPE = 'PACKAGE BODY'
ORDER BY OBJECT_NAME;
--** 产生重建索引脚本:
/* 本命令所产生的脚本不要试图运行，因为可能影响当前Oracle的运行 */
select 'alter index "'||owner||'"."'||index_name||
'" rebuild nologging compute statistics;' cmd
from dba_indexes
where status = 'UNUSABLE'
union
select 'alter index "'||index_owner||'"."'||index_name||
'" rebuild partition "'||partition_name||
'" nologging compute statistics;' cmd
from dba_ind_partitions
where status = 'UNUSABLE'
union
select 'alter index "'||index_owner||'"."'||index_name||
'" rebuild subpartition "'||subpartition_name||';' cmd
from dba_ind_subpartitions
where status = 'UNUSABLE'
order by 1 ;
--** 应用系统表分区情况*
SELECT TABLE_OWNER "表所有者" ,TABLE_NAME "表名" ,
PARTITION_NAME "分区名"
from dba_tab_partitions
WHERE TABLE_OWNER = upper('&&user_name');
--** 应用系统索引分区情况*
SELECT INDEX_OWNER ,
INDEX_NAME ,PARTITION_NAME ,TABLESPACE_NAME
FROM dba_ind_partitions
WHERE INDEX_OWNER = upper('&&user_name') ;
--** 多余索引报告:
COLUMN table_owner FORMAT a10 HEADING 'Table|Owner'
COLUMN table_name FORMAT a30 word_wrapped HEADING 'Table Name'
COLUMN index_name FORMAT a30 word_wrapped HEADING 'Index Name'
COLUMN index_cols FORMAT a30 word_wrapped HEADING 'Index Columns'
column redun_index FORMAT a30 word_wrapped HEADING 'Redundant Index'
COLUMN redun_cols FORMAT a30 word_wrapped HEADING 'Redundant Columns'
clear breaks
break on owner skip 0
TTITLE -
center 'Redudnant Index Analysis' skip 1 -
center '~~~~~~~~~~~~~~~~~~~~~~~~' skip 2
SELECT ai.table_owner table_owner,
ai.table_name table_name,
ai.index_name index_name,
ai.columns index_cols,
bi.index_name redun_index,
bi.columns redun_cols
FROM
( SELECT a.table_owner,
a.table_name,
a.index_name,
MAX(DECODE(column_position, 1,
SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position, 2,',
'||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position, 3,',
'||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position, 4,',
'||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position, 5,',
'||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position, 6,',
'||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position, 7,',
'||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position, 8,',
'||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position, 9,',
'||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position,10,',
'||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position,11,',
'||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position,12,',
'||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position,13,',
'||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position,14,',
'||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position,15,',
'||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position,16,',
'||SUBSTR(column_name,1,30),NULL)) columns
FROM dba_ind_columns a
WHERE a.index_owner = upper('&&user_name')
GROUP BY a.table_owner,
a.table_name,
a.index_owner,
a.index_name) ai,
( SELECT b.table_owner,
b.table_name,
b.index_name,
MAX(DECODE(column_position, 1,
SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position, 2,',
'||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position, 3,',
'||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position, 4,',
'||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position, 5,',
'||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position, 6,',
'||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position, 7,',
'||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position, 8,',
'||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position, 9,',
'||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position,10,',
'||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position,11,',
'||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position,12,',
'||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position,13,',
'||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position,14,',
'||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position,15,',
'||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position,16,',
'||SUBSTR(column_name,1,30),NULL)) columns
FROM dba_ind_columns b
GROUP BY b.table_owner,
b.table_name,
b.index_owner,
b.index_name ) bi
WHERE ai.table_owner = bi.table_owner
AND ai.table_name = bi.table_name
AND ai.columns LIKE bi.columns || ',%'
AND ai.columns <> bi.columns
ORDER BY ai.table_owner,
ai.table_name,
bi.index_name
/
--** 查询所有作业信息
：Select job,next_sec,failures ,broken from DBA_jobs
WHERE LOG_USER= upper('&&user_name');
--** 查询所有快照信息：
SELECT OWNER ,NAME , TABLE_NAME , REFRESH_METHOD ,
LAST_REFRESH , ERROR , NEXT ,START_WITH
FROM DBA_SNAPSHOTS
WHERE OWNER= upper('&&user_name');
--** 导出用户存储过程:
--set lines 9999 trimsp on pages 0 echo off feed off ver off sqlbl on
set heading off
select decode(line,1,'create or replace '||text,text)
from DBA_source where owner = upper('&&user_name')
order by owner,name,line
set heading on
--** 导出用户包:
/* 太长的结果导致查询无效 :
ORA-01489: result of string concatenation is too long
*/
set lines 9999 trimsp on pages 0 echo off feed off ver off sqlbl on
set heading off
select decode(line,1,'create or replace '||text,text)
from DBA_source
where owner = upper('&&user_name')
and type = 'PACKAGE'
order by line;
--** 导出用户包体:
prompt
select decode(line,1,'create or replace '||text,text)
from DBA_source
where owner = upper('&&user_name')
and type = 'PACKAGE BODY'
order by line;
set heading on
--
--** 导出用户触发器:
select 'select dbms_metadata.get_ddl('||chr(39)||'PROCEDURE'||chr(39)||','
||chr(39)|| object_name ||chr(39)||','||chr(39)||owner||chr(39)||') from dual ;'
from dba_objects
where owner='&&user_name' AND object_type='TRIGGER'
/
--**undo 表空间的认识
- 首先要描述undo segment的结构是怎么样的
undo segment分两个部分，一个是undo segment head,还有一个undo segment block（事务槽)
undo segment head中包含了这个回滚段的事务信息，而且有一个指针指向undo segment block
2。一个完成事务在undo segment的过程
undo segment block就是包含数据块的前映象，这也是为什么 head会有一个指针指向block的原因，否者事务无法对应。
不只如此，在事务开始之前，必须在undo获得一个事务槽（事务槽是在undo segment head分配的),这样在需要修改的块的ITL（ITL其实也是一个head)也有事务信息指象这个分配的undo segment head，这样一个完整的事务就全部对应起来了。
事务在未完成前，也就是没有commit，那么在块的ITL和undo segment head始终标识为active，如果完成了commit，则在UNDO segment head标识这个事务为inactive，然后进行块清除。（所谓块清除是：因为ITL包含的是一个块的活动的事务信息，当你完成了COMMIT，当然要把这个块上的事务对应的信息清除。）
注意：首先要注意，undo segment head有一个latch，也就是有锁，当你有新事务想要分配一个undo segment block，首先要在undo segment head注册这个信息，由于undo segment head是不能同时被几个事务同时修改，（为了保护整体结构不出现混乱），所以需要这个latch。
undo segment 的访问是由oracle本身根据segment的繁忙程度自动分配的（当然也可以你指定）。
由于latch是一定要串行执行的，所以如果一个undo segment 如果同时的请求的事务很多，就会存在排队的现象。这也是为什么需要多个undo segment的根本原因。
那么问题出来了：
我们到底需要几个undo segment 来保证事务在分配的过程不会出现等待，或者说不会对数据库的运行造成影响。
你一段时间内会有几个事务？ total
一般来说保证一个segment一段时间内不超过5个事务
segment N=total/5 （N就是你需要的回滚段个数)
3。undo segment的设置和创建问题
(1 optsize
(2 shrinks，waits
(3 视图：v$waitstat ,v$rollstat
先来谈谈第一个optsize问题：
首先要理解既然UNDO SEGMENT是循环使用的，那么为什么UNDO SEGMENT又要扩展呢？
因为当一个UNDO SEGMENT在未完成COMMIT的时候是不能重用的，因为一旦重用就会覆盖了未完成COMMIT的活动信息。所以当一个SEGMENT出现未提交的数据时，而且SEGMENT大小到达了你设置的最大UNDO SEGMENT大小时是不能重用的，这个时候就是继续扩展这个SEGMENT。
这也是为什么需要OPTSIZE这个参数的根本原因。 既然有扩展，就必须收缩。OPTSIZE这个参数就是当扩展的大小超过了OPTSIZE时，收缩开始了。（这会出现问题，第4个问题会解释）
个人认为这也是为什么一个UNDO SEGMENT中事务尽量少于5个的根本原因，如果有很多事务出现在这个UNDO SEGMENT，那么肯定会出现没有COMMIT的，那么收缩的成功率将非常小，因为一直存在活动的事务是不能收缩的。这有可能导致UNDO SEGMENT一直扩展而占满你的硬盘空间。
再来谈谈第二个问题：shrinks （收缩的意思啊）
如果在v$rollstat出现收缩的次数非常多，这就说明你开始设置的UNDO SEGMENT大小不能满足要求。否者为什么需要去扩展？
如果waits的数字大于0 ，就说明UNDO SEGMENT的个数太少，由于每个UNDO SEGMENT都处于繁忙状态，当出现新事务的时候必须选择一个UNDO SEGMENT，由于很繁忙，必然出现等待。如果多有几个UNDO SEGMENT就可能不会出现这个问题了。 至少事务能否平均分布，而不会出现繁忙的情况。
4。 最后来谈谈关于UNDO SEGMENT出现的错误问题
所谓错误也就是UNDO SEGMENT大小不够用，为什么会出现不够用呢？ 或许你会说我的UNDO SEGMENT设置很大啊！ 你的系统或许有很多事务，有可能这些事务很小，突然有一个很大的出来了。需要很多UNDO SEGMENT空间， 当然这个情况比较少。
这个时候可以把SEGMENT设置再大点，这样好吗？ 不好，因为就是一个二个事务比较大，没必要增加整个UNDO SEGMENT，解决方法可以自己建立一个比较大的UNDO SEGMENT， 指定这个大事务在这个UNDO SEGMENT运行。或者扩大OPTIMAL参数.
undo 表空间应该是限制大小的，一般用个10g左右,也最好创建两个undo tablespace,数据库出问题时，也可以进行切换undo
0）表空间管理应该采用local 的管理方式
0）段空间管理采用mannul管理方式,这也是oracle对undo tablespace默认的管理方式
0)还有undo_management = AUTO
0）undo_retention设置可以比默认值稍微小一些，这样也有利于undo tablespace的空间回收
--**AWR使用多少空间？
Select occupant_name, occupant_desc, space_usage_kbytes from v$sysaux_occupants where occupant_name like '%AWR%' ;
--**查看SGA内存分配的细节
select * from v$sgastat
--**查看SGA内存分配
select * from v$sga;
--**使用新的V$DB_CACHE_ADVICE视图来帮助改变数据缓存的大小
column buffers_for_estimate format 999,999,999 heading 'Buffers'
column estd_physical_read_factor format 999.90 heading 'Estd Phys|Read Fact'
column estd_physical_reads format 999,999,999 heading 'Estd Phys| Reads'
SELECT size_for_estimate, buffers_for_estimate,
estd_physical_read_factor, estd_physical_reads
FROM V$DB_CACHE_ADVICE
WHERE name = 'DEFAULT'
AND block_size =
(SELECT value
FROM V$PARAMETER
WHERE name = 'db_block_size')
AND advice_status = 'ON';
--**查看所有session正在等待的，已经发生的等待事件
在V$SESSION_WAIT中找到的东西现在在V$SESSION中也可以找到。
WAIT_TIME = 0 means that it's waiting
WAIT_TIME > 0 means that it previously waited this many msselect event, sum(decode(wait_time,0,1,0)) "Waiting Now",
sum(decode(wait_time,0,0,1)) "Previous Waits",
count(*) "Total"
from v$session_wait
group by event
order by count(*);
--**针对所有session找出这两个等待事件对应的段名和段类型。
SELECT /*+ ordered */ sid, event, owner, segment_name, segment_type,p1,p2,p3
FROM v$session_wait sw, dba_extents de
WHERE de.file_id = sw.p1
AND sw.p2 between de.block_id and de.block_id+de.blocks - 1
AND (event = 'buffer busy waits' OR event = 'write complete waits')
AND p1 IS NOT null
ORDER BY event,sid;
找出这两个等待事件对应的段名和段类型。
从历史表中取值，不仅仅是当前buffer中的数据。
--**查找P1, P2, P3代表什么--查询 V$EVENT_NAME
col name for a30
col p1 for a10
col p2 for a10
col p3 for a10
select event#,name,parameter1 p1,parameter2 p2,parameter3 p3
from v$event_name
where name in ('buffer busy waits', 'write complete waits');
--**会话开始后的所有等待数--查询 V$SESSION_EVENT
select sid, event, total_waits, time_waited, event_id
from v$session_event
where time_waited > 0
order by time_waited;
一共多少个session发生过等待事件
select distinct(sid) from v$session_event where time_waited>0;
--**所有会话等待数
select sid, wait_class, total_waits
from v$session_wait_class order by sid;
--**查看系统等待数v$system_wait_event
select wait_class, total_waits
from v$system_wait_class
order by total_waits desc;
---**用户SCOTT的系统级权限
select b.privilege what_granted,b.admin_option, a.username
from sys.dba_users a, sys.dba_sys_privs b
where a.username = b.grantee
and username='SCOTT'
order by 1,2;
--**用户SCOTT的对象级权限
select b.owner || '.' || b.table_name obj,
b.privilege what_granted, b.grantable,a.username
from sys.dba_users a, sys.dba_tab_privs b
where a.username = b.grantee
and a.username='SCOTT'
order by 1,2,3;
---**用户SCOTT的角色
select b.granted_role ||decode(admin_option,'YES','(With Admin Option)',null)
what_granted from sys.dba_users a,sys.dba_role_privs b
where a.username=b.grantee
and username='SCOTT'
order by 1;
--**用户SCOTT的所有权限
select a.username,
b.granted_role || decode(admin_option,'YES',' (With Admin Option)',null)
what_granted
from sys.dba_users a, sys.dba_role_privs b
where a.username = b.grantee
and username='SCOTT'
UNION
select a.username,
b.privilege || decode(admin_option,'YES',' (With Admin Option)', null)
what_granted
from sys.dba_users a, sys.dba_sys_privs b
where a.username = b.grantee
and username='SCOTT'
UNION
select a.username,
b.table_name || ' - ' || b.privilege|| decode(grantable,'YES',' (With Grant Option)',null) what_granted
from sys.dba_users a, sys.dba_tab_privs b
where a.username = b.grantee
and username='SCOTT'
order by 1;
--**查询用户名及相应的配置文件、默认的表空间和临时表空间
Select username, profile, default_tablespace,
temporary_tablespace, created
from sys.dba_users
where username='SCOTT'
order by username;
--**查询显示了整个系统在整体上的等待数
Select event, total_waits,
total_timeouts,(time_waited / 100) tot_time,
(average_wait / 100) Avg_time
from v$system_event
order by total_waits desc;
---**查所有/某个段的状态
select object_name, statistic_name, value
from v$segment_statistics
where value > 100
order by value;
--**查找使用多会话的用户
select username, count(*)
from v$session
group by username;
V$SESSION视图中用户名NULL的行是Oracle的后台进程。
技巧：
确定使用了多会话的用户，并判定是一个管理问题(用户使用了多终端)还是一个系统问题(会话没有被清除，或者产生了多余的进程)。
--**关闭有问题的会话
select username, sid, serial#, program, terminal
from v$session
where username is not null;
alter system kill session '144,9';
我们发现当一个session被kill掉以后，该session的paddr被修改，如果有多个session被kill，那么多个session
的paddr都被更改为相同的进程地址:在这种情况下,很多时候，资源是无法释放的,我们需要查询spid，在操作系统级来kill这些进程.
但是由于此时v$session.paddr已经改变，我们无法通过v$session和v$process关联来获得spid
那还可以怎么办呢?
现在我们获得了进程地址，就可以在v$process中找到spid，然后可以使用Kill或者orakill在系统级来杀掉这些进程.
当在Oracle中kill session以后, Oracle只是简单的把相关session的paddr 指向同一个虚拟地址.
此时v$process和v$session失去关联,进程就此中断.
然后Oracle就等待PMON去清除这些Session.所以通常等待一个被标记为Killed的Session退出需要花费很长的时间.
如果此时被Kill的process，重新尝试执行任务，那么马上会收到进程中断的提示，process退出，此时Oracle会立即启动PMON
来清除该session.这被作为一次异常中断处理.--**查询产生锁的用户锁sql
select a.username username, a.sid sid, a.serial# serial,b.id1 id1, c.sql_text sqltext
from v$session a, v$lock b, v$sqltext c
where b.id1 in
(select distinct e.id1
from v$session d, v$lock e
where d.lockwait = e.kaddr)
and a.sid = b.sid
and c.hash_value = a.sql_hash_value
and b.request = 0;select /*+ ordered */ username, v$lock.sid, trunc(id1/power(2,16)) rbs,
bitand(id1,to_number('ffff','xxxx'))+0 slot,id2 seq, lmode, request
from v$lock, v$session
where v$lock.type = 'TX'
and v$lock.sid = v$session.sid;
查询 的是行级排他锁。
--**查找用户正在访问的对象
一旦发现某些用户或者系统中的查询存在问题，查询V$ACCESS可以为您指出有潜在问题的对象(可能缺少索引)。当想修改一个特殊的对象，或者需要知道在一个给定的时间点上谁在使用该对象时，它也非常有帮助。
select a.sid, a.username, b.owner, b.object, b.type
from v$session a, v$access b
where a.sid = b.sid
and username='SCOTT'
/
用户正在访问的对象。对于已经访问过的对象，不会出现在结果集中。
通过查询V$ACCESS视图可查看在给定的时间点上用户所访问的所有对象。这有助于查明有问题的对象，在想修改一个特定的对象时也很有用(查找谁在访问它)。然而，当系统有一个很大的共享池和数百个用户时，这个操作的开销将很大。
获得详细的用户信息
select a.username, c.name, sum(b.value) value
from v$session a, v$sesstat b, v$statname c
where a.sid = b.sid
and b.statistic# = c.statistic#
and b.value != 0
and username='SCOTT'
group by name, username;
--**将V$SESSION和V$SQLTEXT连接就可以显示目前每一个会话正在执行的SQL语句
select a.sid, a.username, s.sql_id
from v$session a, v$sqltext s
where a.sql_address = s.address
and a.sql_hash_value = s.hash_value
order by a.username, a.sid, s.piece;
--**显示每个会话的物理磁盘命中率和内存命中率
这就非常容易发现哪些用户执行了大量的物理磁盘和内存读操作。
select a.username, b.block_gets, b.consistent_gets,
b.physical_reads, b.block_changes, b.consistent_changes
from v$session a, v$sess_io b
where a.sid = b.sid
order by a.username;
--**查询到正在运行的事务
用如下SQL查询到正在运行的事务，如其used_urec字段不断增加，说明该事物正在继续，如果该字段不断下降，说明该事物正在回滚。
SQL> SELECT a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr;
根据session id可以查到当前session正在执行的事务信息
select * from v$transaction where addr in (select taddr from v$session where sid=&sid );
--**USED_UBLK和USED_UREC可以看到事务进行到什么程度了
select a.sid,a.serial#,a.user#,a.username,b.addr,b.USED_UBLK,b.USED_UREC from v$transaction b,v$session a where b.addr in (select a.taddr from v$session a where a.sid = &sid);
USED_UBLK和USED_UREC可以看到事务进行到什么程度了
重复查询这两个值，可以看到变化，可以估计事务的进度，尤其是长时间的回滚操作，当这两个值为0，回滚也就完成了
--**查看当前用户的spid
select spid from v$process p, v$session s where s.audsid=userenv('sessionid') and s.paddr=p.addr;
select spid from v$process p join v$session s on p.addr=s.paddr and s.audsid=userenv('sessionid');
--**查看当前用户的trace file路径
/*
select p.value || '\' || t.instance || '_ora_' || ltrim(to_char(p.spid,'fm99999')) || '.trc'
from v$process p, v$session s, v$parameter p, v$thread twhere p.addr = s.paddr and s.audsid= userenv('sessionid') and p.name = 'user_dump_dest';
*/
--**确定SCN
select a.name,a.checkpoint_change# start_SCN
,b.checkpoint_change# last_SCN
from v$datafile_header a, v$datafile b
where a.file#=b.file#
--**查看归档日志信息
select name,completion_time,status from v$archived_log;
STATUS=A Available，表示归档日志文件有效
STATUS=U Unavailable，表示归档日志文件无效
STATUS=D Deleted，表示归档日志已经被删除
STATUS=X eXpired，表示归档日志被用户使用操作系统的命令删除了--**查看重做日志信息
select MEMBER,GROUP#,STATUS from v$logfile;
status null表示在使用
select MEMBERS,GROUP#,STATUS from v$log;
MEMBERS GROUP# STATUS
---------- ---------- ----------
3 1 ACTIVE
3 2 ACTIVE
3 3 CURRENT
--**根据SID找SPID
select pro.spid from v$session ses,v$process pro where ses.sid=xxx and ses.paddr=pro.addr;
--**根据SPID找SID,SERIAL
select s.sid,s.serial# from v$session s,v$process p where s.paddr=p.addr and p.spid='xxx';
--**根据PID找SID
SELECT SID,SERIAL#, USERNAME,MACHINE
FROM v$session b
WHERE b.paddr = (SELECT addr
FROM v$process c
WHERE c.spid = '&pid');----oracle 通过pid 找到sid 再找出执行sql
通过sid找出执行的sql语句
1 SELECT SID,SERIAL#, USERNAME,MACHINE
FROM v$session b
WHERE b.paddr = (SELECT addr
FROM v$process c
WHERE c.spid = '&pid');2 select sql_address,sql_hash_value,sql_id from v$session where sid=20;
3 select sql_fulltext from V$sqlarea where sql_id='301r15qus4kp1'
---**当下的等待
select wait_class, event, sid, state, wait_time, seconds_in_wait
from v$session_wait
where wait_class='Application'
order by wait_class, event, sid;
select wait_class, event, sid, state, wait_time, seconds_in_wait
from v$session_wait
order by wait_class, event, sid;
---**查询那些应用的连接数此时是多少
select b.MACHINE, b.PROGRAM , count(*) from v$process a, v$session b where a.ADDR = b.PADDR and b.USERNAME is not null group by b.MACHINE , b.PROGRAM order by count(*) desc;---**查看是否有死锁
select * from v$locked_object;
如果查询结果为no rows selected，说明数据库中没有死锁。否则说明数据库中存在死锁
--**请问如何分辨某个用户是从哪台机器登陆ORACLE的?
SELECT machine , terminal FROM V$SESSION;
Select sql_fulltext from v$sqlarea where sql_id='7pww837hx8qj5';
--求对象的创建代码
--查看数据库本次启动的时间
Select startup_time from v$instance; 当然 从还原中恢复的时间是不算得
--根据SID找SQL
---*检查一个表对应的索引和索引对应的列
select index_name,table_name,column_name from user_ind_columns