Oracle数据库DBA日常Sql列表

--监控索引是否使用alter index &index_name monitoring usage;alter index &index_name nomonitoring usage;select * from v$object_usage where index_name = &index_name;--求数据文件的I/O分布select df.name,phyrds,phywrts,phyblkrd,phyblkwrt,singleblkrds,readtim,writetimfrom v$filestat fs,v$dbfile dfwhere fs.file#=df.file# order by df.name;--求某个隐藏参数的值col ksppinm format a54col ksppstvl format a54select ksppinm, ksppstvlfrom x$ksppi pi, x$ksppcv cvwhere cv.indx=pi.indx and pi.ksppinm like "\_%" escape "" and pi.ksppinm like "%meer%";--求系统中较大的latchselect name,sum（gets）,sum（misses）,sum（sleeps）,sum（wait_time）from v$latch_childrengroup by name having sum（gets） > 50 order by 2;--求归档日志的切换频率（生产系统可能时间会很长）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 < 30--求回滚段正在处理的事务select a.name,b.xacts,c.sid,c.serial#,d.sql_textfrom v$rollname a,v$rollstat b,v$session c,v$sqltext d,v$transaction ewhere a.usn=b.usn and b.usn=e.xidusn and c.taddr=e.addrand c.sql_address=d.address and c.sql_hashvalue=d.hash_value order by a.name,c.sid,d.piece;--求出无效的对象select "alter procedure "||object_name||" compile;"from dba_objectswhere status="INVALID" and wner="&" and object_type in （"PACKAGE","PACKAGE BODY"）;/select owner,object_name,object_type,status from dba_objects where status="INVALID";--求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的状态select sn.name,ms.valuefrom v$mystat ms,v$statname snwhere ms.statistic#=sn.statistic# and ms.value > 0;--求表的索引信息select ui.table_name,ui.index_namefrom user_indexes ui,user_ind_columns uicwhere ui.table_name=uic.table_name and ui.index_name=uic.index_nameand ui.table_name like "&table_name%" and uic.column_name="&column_name";--显示表的外键信息col search_condition format a54select table_name,constraint_namefrom user_constraintswhere constraint_type ="R" and constraint_name in （select constraint_name from user_cons_columns where column_name="&1"）;select rpad（child.table_name,25," "） child_tablename,rpad（cp.column_name,17," "） referring_column,rpad（parent.table_name,25," "） parent_tablename,rpad（pc.column_name,15," "） referred_column,rpad（child.constraint_name,25," "） constraint_namefrom user_constraints child,user_constraints parent,user_cons_columns cp,user_cons_columns pcwhere child.constraint_type = "R" and child.r_constraint_name = parent.constraint_name andchild.constraint_name = cp.constraint_name and parent.constraint_name = pc.constraint_name andcp.position = pc.position and child.table_name ="&table_name"order by child.owner,child.table_name,child.constraint_name,cp.position;

Oracle 查询表空间占用率超时CentOS5下MySQL源码安装方式相关资讯 Oracle教程

Oracle中纯数字的varchar2类型和（07/29/2015 07:20:43）
Oracle教程：Oracle中查看DBLink密（07/29/2015 07:16:55）
[Oracle] SQL*Loader 详细使用教程（08/11/2013 21:30:36）

Oracle教程：Oracle中kill死锁进程（07/29/2015 07:18:28）
Oracle教程：ORA-25153 临时表空间（07/29/2015 07:13:37）
Oracle教程之管理安全和资源（04/08/2013 11:39:32）

本文评论查看全部评论（0）