Oracle或者数据库管理员日常工作中常用sql列表.doc

目录-* 数据库实例基本情况6-* ORACLE系统实例名字:6-* ORACLE实例安装的产品列表:6-* Oracle及工具 Tools 版本信息:6-* Oracle实例是否归档:7-* ORACLE系统用户情况:7-* ORACLE系统资源文件情况:7-* ORACLE系统字符集情况7-* ORACLE数据库连接的信息7-* ORACLE数据库用户权限报告:8-* ORACLE实例后台进程信息9-* ORACLE 系统参数情况9-* ORACLE 系统参数情况2(9i/10g视图)9-查看数据库的创建日期和归档方式:9-* Oracle系统日志文件与控制文件9-* 日志文件信息19-* 日志文件信息210-求日志文件的空间使用10-求归档日志的切换频率(生产系统可能时间会很长)10-* 控制文件信息10-*表空间信息11-*查看表空间状态11-*查看表空间使用情况11-*检查表空间的使用情况11-*查询表空间的大小,分布等信息11-*查看表空间物理文件的名称及大小12-*检查剩余表空间 （速度比较慢)12-*查看所有表空间的碎片程度12-*确定需要固定的PL/SQL对象12-*监控表空间的 I/O 比例 ?13-*求数据文件的I/O分布13-*监控文件系统的 I/O 比例13-*检查表空间碎片1MB以上14-*表空间的自由空间情况14-*表空间使用率14-*是否存在空间无法扩展的情况（时间长）14-*检查是否存在需要合并的表空间15-*检查自由表空间接近10%的情况（时间长）:15-*求表空间的未用空间16-* *回滚段使用情况 :16-*系统回滚段使用统计情况1:16-* 系统回滚段使用统计情况2:17-*查看回滚段的使用情况，哪个用户正在使用回滚段的资源18-* 检查undo表空间情况:18-* 查询undo表空间信息:18-*.V$SYSSTAT视图可以确定总的数据请求的数量：19-*.DBA_ROLLBACK_SEGS视图可以确定回滚段的存储信息和状态等等信息：19-*.V$TRANSACTION视图可以确定每个事务使用Undo空间的数量：19-*查看回滚段的争用情况20-*查看回滚段名称及大小20-*求回滚段正在处理的事务20-*查看用户的回滚段的信息20-*查看undo段的使用情况20-* *Oracle系统排序区使用情21-* 内存与磁盘的排序情况:21-*求free memory21-* 查询排序操作命中率:21-* 查询临时段:22-* 用户与临时表空间名:22-* 显示排序参数当前值:22-* 监视临时段的使用情况:22-* 检查不能分配的临时表空间:23-* *10g版本回收站有关信息23- *Oracle 10g 功能检查-Flashback：23-* 回收站信息23-* 有关路径与大小23- * 闪回有关信息24-* *Oracle系统SGA24-*查看用户使用内存情况24-*查看内存使用情况24-*查看内存中存的使用25-* 内存分配情况:25-* 查询数据高速缓冲区的命中率1:25-* 查询数据高速缓冲区的命中率2:25-* 查询数据高速缓冲区的命中率3:26-*测定数据的命中率(V$SYSSTAT)26-* 查询数据高速缓冲区的使用情况4:26-* 查询数据高速缓冲区的使用情况5:27-* 监视会话内存及磁盘活动情况6:27-* 查询共享池命中率:27-* 共享池高速缓存区的命中率1:27-* 共享池高速缓存区的命中率2:27-* 查询字典缓冲区的命中率3:28-* 查询数据字典命中率:28-*查看字典命中率28-* 查询库缓存的命中率:29-* 查询库缓存的命中率:29-*查看库缓存命中率29-* 查询库缓存的使用百分比:29-* 查询对象缓存情况:29-* 日志缓存区的命中率30-* 查询系统缓冲区命中率:30-* 查询系统缓冲区命中率及评分:30-*求cache中缓存超过其5%的对象31-* 统计恢复日志有无竞争的情况:31-* 系统是否设置共享池保留区:31-* 数据缓冲区的使用情况:31-* 其它影响性能的信息搜集32-* ORACLE 系统会话情况:32-* ORACLE 系统会话过多的情况:32-* 显示用户等待的时间 :32-*查看系统请求情况33-* *等待事件等信息33-*查询系统运行以来的等待事件33-*监控实例的等待:34-*找使用CPU多的用户session34-*求等待的事件及会话信息/求会话的等待及会话信息35-*求等待中的对象 (运行时间超长！）35-*查找前10条性能差的sql:35-*查找耗资源的进程(top session):36-*查看占io较大的正在运行的session:36-*查看表空间数据文件的读写性能:36-*Disk Read最高的SQL语句的获取：37-*1 buffer gets top 10 sql:37-*2 Physical Reads top 10 sql:37-*3 Executions top 10 sql:38-*4 Parse Calls top 10 sql:38-*5 Sharable Memory top 10 sql:38-*6 CPU usage top 10 sql:39-*7 Running Time top 10 sql:39-*捕捉运行很久的SQL39-*求DISK READ较多的SQL40-*求DISK SORT严重的SQL40- *SQL语句冲突:40-*等待时间最多的5个系统等待事件的获取：40-*造成等待的LOCK信息，如LOCK类型等：40-*等待事件信息：41-* *10g版本统计搜集41-* 查询没有分析的表的信息（运行时间长）：41-* 查询最近没有分析的表的信息（运行时间长）：41-*无效对象检查42-* 检查索引无用信息:42-*查看无效的对象42-*查看无效的数据库对象42-*查看不起作用的约束42-*查看无效的trigger42-*求出无效的对象43-*ORACLE系统数据文件情况43-*查看数据文件的状态记录状态不是“online”的数据文件43-*会话有关43-*求当前session的跟踪文件43-*求process/session的状态44-*求当前session的状态44-*求当前会话的SID，SERIAL#44-*求当前数据库建立的会话:44-*求当前会话的SID，SERIAL#44-*求使用CPU多的用户session44-*求会话等待的file_id/block_id45-*求谁阻塞了某个session(10g)45-*求当前事务的重做尺寸45-*查看耗资源的进程（top session）45*46-*查看正在运行的job46-*求系统中较大的latch46-*求某个事务的重做信息(bytes)46-*求回退率46-*求索引中行数较多的46-*求未定义索引的表47-*求出锁定的对象47-*查看对象的缓存情况47-*查看某些用户的hash47-*求某个隐藏参数的值47-*与权限相关的字典48-*在AIX系统中根据pid获得sql48-*跟踪用户session 的方法49-*在oracle中查找有多少表被锁住49-*查找一个表是否 被锁的方法，例如：临时表SHZGY.SHZGY_PZ_BB_ERROR149-*oracle kill session的方法50-*ORACLE10G的回收站52-*Oracle 总量和剩余量的查询54-*Oracle表空间增加数据文件（扩展表空间）54-*根据sql_id来获得sql文本58-* 找出用户及系统权限的信息:58-* 找出用户及角色的信息:58-* 找出授予角色的权限信息:59-* 找出数据库安全信息:59-* Oracle用户及默认表空间:60-* 产生无效对象编译脚本:60-* 列出表与同义词信息:61-* 产生重建索引脚本:62-* 应用系统表分区情况*62-* 应用系统索引分区情况*63-* 多余索引报告:63-* 查询所有快照信息：66-* 导出用户存储过程:66-* 导出用户包:66-* 导出用户包体:66-* 导出用户触发器:67-*undo 表空间的认识67-*AWR使用多少空间？69-*查看SGA内存分配的细节69-*查看SGA内存分配69-*使用新的V$DB_CACHE_ADVICE视图来帮助改变数据缓存的大小69-*查看所有session正在等待的，已经发生的等待事件70-*针对所有session找出这两个等待事件对应的段名和段类型。70-*查找P1, P2, P3代表什么-查询 V$EVENT_NAME70-*会话开始后的所有等待数-查询 V$SESSION_EVENT71-*所有会话等待数71-*查看系统等待数v$system_wait_event71-*用户SCOTT的系统级权限71-*用户SCOTT的对象级权限71-*用户SCOTT的角色72-*用户SCOTT的所有权限72-*查询用户名及相应的配置文件、默认的表空间和临时表空间73-*查询显示了整个系统在整体上的等待数73-*查所有/某个段的状态73-*查找使用多会话的用户73-*关闭有问题的会话74-*查询产生锁的用户锁sql74-*查找用户正在访问的对象75-*将V$SESSION和V$SQLTEXT连接就可以显示目前每一个会话正在执行的SQL语句75-*显示每个会话的物理磁盘命中率和内存命中率76-*查询到正在运行的事务76-*USED_UBLK和USED_UREC可以看到事务进行到什么程度了76-*查看当前用户的spid76-*确定SCN77-*查看归档日志信息77-*查看重做日志信息77-*根据SPID找SID,SERIAL78数据库管理员日常工作中必备的sql列表-* 数据库实例基本情况-* ORACLE系统实例名字:select NAME ,CREATED ,LOG_MODE , to_char(sysdate,yyyy/mm/dd:HH24:MI) SYS_DATEfrom v$database;-* ORACLE实例安装的产品列表:COL PARAMETER for a40COL VALUE for a30select * from v$option;-* Oracle及工具 Tools 版本信息:col BANNER for a60select BANNER from v$version;-* Oracle实例是否归档:select dbid, name, log_mode from v$database;-* ORACLE系统用户情况:col USERNAME for a20col DEFAULT_TABLESPACE for a20col TEMPORARY_TABLESPACE for a20col PROFILE for a20select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,PROFILE,CREATED FROM dba_users;-* ORACLE系统资源文件情况:col RESOURCE_NAME for a28col LIMIT for a20SELECT * from dba_profiles order by PROFILE;-* ORACLE系统字符集情况 COL VALUE$ for a40SELECT name,value$ from ps$;-* ORACLE数据库连接的信息 col owner for a20col db_link for a38col username for a20SELECT owner,db_link,username from dba_db_links ;-* ORACLE数据库用户权限报告:col Username for a20col Owner for a14col Obj for a20col Obj_Priv for a10col Sys_Priv for a20col Granted_Role for a22select username Username, owner, table_name Obj, privilege Obj_Priv, Sys_Priv, Granted_Role, 1 dummyfrom dba_users u, dba_tab_privs twhere u.username = t.granteeand u.username = upper(&user_name) unionselect username, , , , privilege, , 2 dummyfrom dba_users u, dba_sys_privs swhere u.username = s.granteeand u.username = upper(&user_name)unionselect username, , , , , granted_role, 3 dummyfrom dba_users u, dba_role_privs rwhere u.username = r.granteeand u.username = upper(&user_name) order by 1, 7;-* ORACLE实例后台进程信息 col DESCRIPTION for a40col name for a30SELECT * 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系统日志文件与控制文件-* 日志文件信息1SELECT GROUP# 组号,MEMBERS 成员,BYTES 大小(字节),ARCHIVED 归档?,STATUS 状态FROM V$LOG;-* 日志文件信息2Col member for a56SELECT * FROM V$LOGFILE;-求日志文件的空间使用select le.leseq current_log_sequence#, 100*cp.cpodr_bno/le.lesiz percentage_fullfrom x$kcccp cp,x$kccle lewhere le.leseq =cp.cpodr_seq;-求归档日志的切换频率(生产系统可能时间会很长)select start_recid,start_time,end_recid,end_time,minutes from (select test.*, rownum as rnfrom (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) minutesfrom v$log_history a,v$log_history b where a.recid=b.recid+1 and b.first_time sysdate - 1order by a.first_time desc) test) y where y.rn 100000and 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 ,phyrds,phywrts,phyblkrd,phyblkwrt,singleblkrds,readtim,writetimfrom v$filestat fs,v$dbfile dfwhere fs.file#=df.file# order by ;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_Timefrom V$FileStat A, V$DataFile B, Sys.DBA_Data_Files Cwhere B.File# = A.File#and B.File# = C.File_Idorder by Disk,C.Tablespace_Name, A.File#;-*监控文件系统的 I/O 比例 select substr(a.file#,1,2) #, substr(,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 bytes1000000 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) 1group by f.tablespace_name ,d.extent_management ;-*检查自由表空间接近10%的情况（时间长）:col 表空间 for a18col 文件名 for a50col 总空间(Mb) for 999,999,999,999col 自由空间(Mb) for 999,999,999,999col 自由空间% for 9999.99SELECT 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) bwhere a.file_id = b.file_id (+)order by 1, 2 ;-*求表空间的未用空间col mbytes format 9999.9999select 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,999col 回滚段 for a16col 比率 for 99.99col 等待 for 999,999SELECT 回滚段 , rs.gets 得到, rs.waits 等待 ,(rs.waits/rs.gets)*100 比率from v$rollstat rs, v$rollname rnwhere rs.usn=rn.usn ;V$ROLLSTAT中的常用列USN：回滚段标识RSSIZE：回滚段默认大小XACTS：活动事务数在一段时间内增量用到的列WRITES：回滚段写入数(单位:bytes)SHRINKS：回滚段收缩次数EXTENDS：回滚段扩展次数WRAPS：回滚段翻转(wrap)次数GETS：获取回滚段头次数WAITS：回滚段头等待次数-*系统回滚段使用统计情况1:col USN for 999col NAME for a18col EXTENTS for 999col RSSIZE for 999,999,999,999col WRITES for 999col XACTS for 9999col GETS for 999col WAITS for 999col HWMSIZE for 999,999,999,999col OPTSIZE for 999,999,999col STATUS for a10Set linesize 150Select a.usn, name, extents, rssize, optsize, hwmsize,waits,statusFrom v$rollstat a , v$rollname bWhere a.usn=b.usn order by a.usn;SELECT , s.extents, s.rssize, s.optsize, s.hwmsize, s.xacts, s.statusFROM v$rollname n, v$rollstat sWHERE n.usn = s.usn;-* 系统回滚段使用统计情况2:Column Class Format A20 Heading Header TypeColumn Count Format 999,999,999 Heading Number |of waitsColumn Con_Get Format 999,999,999,999 Heading Logical| ReadsColumn Pct Format 990.99 Heading Pct of |ContentionColumn Instance New_Value _Instance NoPrintColumn Today New_Value _Date NoPrintDefine Wait_Ratio = 1 (Number) Select A.Class, Count, Sum(Value) Con_Get, (Count / Sum(Value) * 100) pctFrom V$WaitStat A, V$SysStat BWhere 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|ActivitiesColumn Waits Format 999,999,999 Heading Number|of WaitsColumn Pct Format 990.99 Heading Pct of|GetsSelect Name, Gets, Waits, (Waits / Gets) * 100) PctFrom V$RollStat A, V$RollName BWhere A.USN = B.USN;-*查看回滚段的使用情况，哪个用户正在使用回滚段的资源select s.username, from v$transaction t, v$rollstat r, v$rollname u, v$session swhere s.taddr = t.addrand t.xidusn = r.usnand r.usn = u.usnorder 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,999col 事务执行块数 for 999,999,999,999col 查询最长秒 for 99,999,999col ORA-01555次数 for 999,999col 无可用空间数 for 999,999select 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 a20SELECT segment_name, owner, tablespace_name, status, initial_extent, next_extentFROM DBA_ROLLBACK_SEGS;-*.V$TRANSACTION视图可以确定每个事务使用Undo空间的数量：SELECT MAX(used_ub