Dba常用的sql和视图

 

alterindex&index_name monitoring usage;ITPUB个人空间G3q8W#w W _-kq
alter index &index_name nomonitoring usage;
)]D#MF:a+p0select * from v$object_usage where index_name = &index_name;
+[LX:vQR,D0--求数据文件的I/O分布ITPUB个人空间5iL5^j.e
select df.name,phyrds,phywrts,phyblkrd,phyblkwrt,singleblkrds,readtim,writetimITPUB个人空间c.Y)Gy(wAA
from v$filestat fs,v$dbfile df
-bQTA~ aY0hi0where fs.file#=df.file# order by df.name;ITPUB个人空间O6M;R[r(o/I Z
--求某个隐藏参数的值
#W$b*K `#W/[%ii0col ksppinm format a54ITPUB个人空间2u~bEMTe?']k,c
col ksppstvl format a54ITPUB个人空间xN3^&kd#H
select ksppinm, ksppstvl
R3W,~$pea _0from x$ksppi pi, x$ksppcv cvITPUB个人空间~"W t_;J/Q p,E
where cv.indx=pi.indx and pi.ksppinm like '\_%' escape '\' and pi.ksppinm like '%meer%';ITPUB个人空间#d EXZ2}
--求系统中较大的latch
)ea^5C(ENVBtv0select name,sum(gets),sum(misses),sum(sleeps),sum(wait_time)
;f#`8lcbqH?Uf2] }0from v$latch_childrenITPUB个人空间%A7Pe lr.{(I
group by name having sum(gets) > 50 order by 2;ITPUB个人空间p.f;dfr
--求归档日志的切换频率(生产系统可能时间会很长)
1Nc,Lg ~$Z"E*HGf0select start_recid,start_time,end_recid,end_time,minutes from (select test.*, rownum as rnITPUB个人空间+X P4{.Ux~n
from (select b.recid start_recid,to_char(b.first_time,'yyyy-mm-dd hh24:mi:ss') start_time,ITPUB个人空间\FR Cp6p~!eO4]w%O
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) minutesITPUB个人空间L\2{2yG
from v$log_history a,v$log_history b where a.recid=b.recid+1 and b.first_time > sysdate - 1ITPUB个人空间.[ h8e+?3qJ1H
order by a.first_time desc) test) y where y.rn < 30ITPUB个人空间sBd$N\ z
--求回滚段正在处理的事务ITPUB个人空间)VIw#I j}i
select a.name,b.xacts,c.sid,c.serial#,d.sql_textITPUB个人空间R-U|{\~
from v$rollname a,v$rollstat b,v$session c,v$sqltext d,v$transaction e
^?z%Z6i(r0where a.usn=b.usn and b.usn=e.xidusn and c.taddr=e.addrITPUB个人空间)G'p_:lA?&|%K
and c.sql_address=d.address and c.sql_hash_value=d.hash_value order by a.name,c.sid,d.piece;
f8k^N+} _/@7}0--求出无效的对象ITPUB个人空间Q!Q'x)T9Ld#}*I
select 'alter procedure '||object_name||' compile;'
C wF}1`LU0from dba_objects
miI1FUX0IU0where status='INVALID' and wner='&' and object_type in ('PACKAGE','PACKAGE BODY');ITPUB个人空间n3q6P ?-i:v
/ITPUB个人空间kQo S7WZ
select owner,object_name,object_type,status from dba_objects where status='INVALID';
5~IF;^ qX$aQ8Z0--求process/session的状态
X!G+l6|$qt(y e0select p.pid,p.spid,s.program,s.sid,s.serial#ITPUB个人空间 RGq(o \ sDr4r
from v$process p,v$session s where s.paddr=p.addr;
nqqU+mkvF,\T0--求当前session的状态ITPUB个人空间Q#q,I"Lce-e,Mc
select sn.name,ms.value
e5w(zz a-XX8n0from v$mystat ms,v$statname sn
F \p3A;v;b0where ms.statistic#=sn.statistic# and ms.value > 0;ITPUB个人空间2G5P6lT8C
--求表的索引信息
{ {x5a w#`%O*b3^`0select ui.table_name,ui.index_name
~5B|7@0KK3R?io0from user_indexes ui,user_ind_columns uicITPUB个人空间1UdiK-R7M,t h
where ui.table_name=uic.table_name and ui.index_name=uic.index_nameITPUB个人空间&Sa'I6S8Q/P,tLd
and ui.table_name like '&table_name%' and uic.column_name='&column_name';
v+E Q/{+Y} ~0--显示表的外键信息
-bILH+jIx8[0col search_condition format a54ITPUB个人空间.g&^JkH[`
select table_name,constraint_name
N_.bBZ,m)mWi0from user_constraints
T%{w He/X4X(qHK#v;j0where constraint_type ='R' and constraint_name in (select constraint_name from user_cons_columns where column_name='&1');ITPUB个人空间'@'cSi.E"yjy3F
select rpad(child.table_name,25,' ') child_tablename,ITPUB个人空间9W)d"oqiW
rpad(cp.column_name,17,' ') referring_column,rpad(parent.table_name,25,' ') parent_tablename,
:[/Hv"xS8tZ0rpad(pc.column_name,15,' ') referred_column,rpad(child.constraint_name,25,' ') constraint_nameITPUB个人空间*M:zO g1`R
from user_constraints child,user_constraints parent,ITPUB个人空间eG`&p-`+v
user_cons_columns cp,user_cons_columns pc
v0H;Ff&PBXP8e0where child.constraint_type = 'R' and child.r_constraint_name = parent.constraint_name andITPUB个人空间d?0vc0k\
child.constraint_name = cp.constraint_name and parent.constraint_name = pc.constraint_name and
X)[V|J8RN,M0cp.position = pc.position and child.table_name ='&table_name'
{0w PX v H|!I4E-Y0order by child.owner,child.table_name,child.constraint_name,cp.position;ITPUB个人空间4I/i;Z$`W9zDZ
--显示表的分区及子分区(user_tab_subpartitions)
R c1H.TI0col table_name format a16ITPUB个人空间7XE eR5J2} V3y
col partition_name format a16
,Ug/du'D t5}?}!z0col high_value format a81ITPUB个人空间$v u}h|.QY0u Z7J
select table_name,partition_name,HIGH_VALUE from user_tab_partitions where table_name='&table_name'ITPUB个人空间4EJ yM6po
--使用dbms_xplan生成一个执行计划ITPUB个人空间4^s+_(Wo#SWz.U
explain plan set statement_id = '&sql_id' for &sql;
J d|!E z!sV0select * from table(dbms_xplan.display);
"c$f4`8_]-RC0--求某个事务的重做信息(bytes)ITPUB个人空间)qX x.XG0E5`e9|
select s.name,m.valueITPUB个人空间 M$r U1KD'|qI
from v$mystat m,v$statname sITPUB个人空间*RD&WgB6Z,mZ$|
where m.statistic#=s.statistic# and s.name like '%redo size%';
X\1Dg#~S,}#]0--求cache中缓存超过其5%的对象
Hqb[xP6z#CYB0select o.owner,o.object_type,o.object_name,count(b.objd)
dm U$h)J%o*l0from v$bh b,dba_objects oITPUB个人空间0}5D|i:^s3KH
where b.objd = o.object_id
Dtv3XY6|.K_0group by o.owner,o.object_type,o.object_nameITPUB个人空间j"bg'W%b[
having count(b.objd) > (select to_number(value)*0.05 from v$parameter where name = 'db_block_buffers');
7v1z7qekH-^W0--求谁阻塞了某个session(10g)
#WgY1ME*V;j0select sid, username, event, blocking_session,ITPUB个人空间f&_ NoSw.a
seconds_in_wait, wait_time
.v4~o%Rr!rWg0from v$session where state in ('WAITING') and wait_class != 'Idle';
y3FGvU-V5U&n0--求session的OS进程IDITPUB个人空间+?c`2g}RI
col program format a54
7g{4LCG8BDw y9j0select p.spid "OS Thread", b.name "Name-User", s.program
amJg7JQ%s9}0from v$process p, v$session s, v$bgprocess b
Gs U M M@6}P)D0where p.addr = s.paddr and p.addr = b.paddrITPUB个人空间:M(Z5{*V4y9|D@]
UNION ALL
{7t~R.[%QN0select p.spid "OS Thread", s.username "Name-User", s.program
?8ZIX@;u mGy2~0from v$process p, v$session s where p.addr = s.paddr and s.username is not null;
ZG"Be,@0--查会话的阻塞
'h}],p(iwc \6j6h0col user_name format a32ITPUB个人空间u(\/m}wA
select /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username user_name, o.owner,o.object_name,s.sid,s.serial#
MCZ%cy-f!Xy_e,h0from v$locked_object l,dba_objects o,v$session sITPUB个人空间vrpOOK
where l.object_id=o.object_id and l.session_id=s.sid order by o.object_id,xidusn desc ;
"p'XZx4c0col username format a15
)Bi+S#X-c2w#av]0col lock_level format a8ITPUB个人空间@V@A%Y'_RR
col owner format a18
b"aBG3\J)A t0col object_name format a32ITPUB个人空间1p9Y1x-OIx8ay
select /*+ rule */ s.username, decode(l.type,'tm','table lock', 'tx','row lock', null) lock_level, o.owner,o.object_name,s.sid,s.serial#
Vh3q2X,i!O/\)|"Q M+oa0from v$session s,v$lock l,dba_objects oITPUB个人空间.I(T$H)x8gq0Xh']
where l.sid = s.sid and l.id1 = o.object_id(+) and s.username is not null ;ITPUB个人空间 IhI7f,n
--求等待的事件及会话信息/求会话的等待及会话信息
HQ*w i#n-g0select se.sid,s.username,se.event,se.total_waits,se.time_waited,se.average_wait
2?(o pXX;YRN0from v$session s,v$session_event se
NY$Y/yL4[,c0where 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;ITPUB个人空间U{WW S)J
select s.sid,s.username,sw.event,sw.wait_time,sw.state,sw.seconds_in_waitITPUB个人空间 nDs"i [
from v$session s,v$session_wait sw
?,tT/jT.G\0where s.username is not null and sw.sid=s.sid and sw.event not like '%SQL*Net%' order by s.username;
@u}8TR*}_a ~(d6y0--求会话等待的file_id/block_idITPUB个人空间'V"A9?;c;M,|&jW
col event format a24
cg*a.D6N+Q0col p1text format a12
9m%\7K'\D!d]1x6Jd0col p2text format a12ITPUB个人空间Q#v{*Xa ux-izB
col p3text format a12
$X peaH"C&m0select sid,event,p1text, p1, p2text, p2, p3text, p3
3M,QJ[lt6Qx&lC1J0from v$session_waitITPUB个人空间]C`Mi
where event not like '%SQL%' and event not like '%rdbms%' and event not like '%mon%' order by event;
*V7sX?4Q'I0select name,wait_time from v$latch l where exists (select 1 from (select sid,event,p1text, p1, p2text, p2, p3text, p3
'F}LL,q'n0from v$session_waitITPUB个人空间j ^*rxe.D2a w
where event not like '%SQL%' and event not like '%rdbms%' and event not like '%mon%'
8F!k/F(a$?MC!Af1M0) x where x.p1= l.latch#);ITPUB个人空间o4G(Nu9HX S3Q:a5c uL
--求会话等待的对象
/W]wS%[ A0col owner format a18ITPUB个人空间!L*C]LK'?-sg5eq
col segment_name format a32
[5[S Xr:x\%V"_~z*SR0col segment_type format a32ITPUB个人空间&U-_i1yAG4jM
select owner,segment_name,segment_type
)e0Vy`2o6p%]u0from dba_extentsITPUB个人空间R5WjqJv {z
where file_id = &file_id and &block_id between block_id and block_id + blocks - 1;
&xp)N.E D p Gl%fF0--求buffer cache中的块信息ITPUB个人空间\7pE"G&q:mpQb
select o.OBJECT_TYPE, substr(o.OBJECT_NAME,1,10) objname , b.objd , b.status, count(b.objd)ITPUB个人空间#tWnN,b~{
from v$bh b, dba_objects o
0g^4f&~5r5[0where b.objd = o.data_object_id and o.owner = '&1' group by o.object_type, o.object_name,b.objd, b.status ;
z0w:@+a#f0p.MBI-d0--求日志文件的空间使用
(^OrX8m)L^0select le.leseq current_log_sequence#, 100*cp.cpodr_bno/le.lesiz percentage_fullITPUB个人空间-o)C&h1k8F3B3HS]
from x$kcccp cp,x$kccle le
ts/wQ+y/M0where le.leseq =cp.cpodr_seq;
qN9C7v2zj-m Pm0--求等待中的对象ITPUB个人空间(K+^hh$iG EC9eX
select /*+rule */ s.sid, s.username, w.event, o.owner, o.segment_name, o.segment_type,ITPUB个人空间J,pfs*fUxbd
o.partition_name, w.seconds_in_wait seconds, w.state
8X}$JJ9z t.o{ }1|0from v$session_wait w, v$session s, dba_extents oITPUB个人空间%cJQ!@G9N o U9PL
where w.event in (select name from v$event_name where parameter1 = 'file#'
3GD:D)|:vT0and parameter2 = 'block#' and name not like 'control%')ITPUB个人空间-CHUJ5r{
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.blocksITPUB个人空间?TxE.y(n:CL
--求当前事务的重做尺寸
4tZ5~vps I kw0select valueITPUB个人空间(qU n'Y)Lhi$\ Ys
from v$mystat, v$statname
Tg&n~['z0where v$mystat.statistic# = v$statname.statistic# and v$statname.name = 'redo size';
k6s.^3UvZvK0--唤醒smon去清除临时段ITPUB个人空间!y1ujT^N9Y0Fy$C^
column pid new_value Smon
*{"rKlvB}0set termout off
0S j;A"N(T [ E~h0select p.pid from sys.v_$bgprocess b,sys.v_$process p where b.name = 'SMON' and p.addr = b.paddr
[/l [2i#rFs\J0/
G!yx}l0set termout onITPUB个人空间1uN*QD?bAg
oradebug wakeup &Smon
;oY|B OA*PG9F0undefine SmonITPUB个人空间9N-DTb!L
--求回退率ITPUB个人空间*\1W~;vA6d-o.A
select b.value/(a.value + b.value),a.value,b.value from v$sysstat a,v$sysstat bITPUB个人空间'ufa)o F/p
where a.statistic#=4 and b.statistic#=5;
\O7w \{u9Z4wP0--求DISK READ较多的SQLITPUB个人空间+X8F)G^0B%m
select st.sql_text from v$sql s,v$sqltext stITPUB个人空间g8rC%hnN
where s.address=st.address and s.hash_value=st.hash_value and s.disk_reads > 300;
5I-E6H$Gaj4OJ&l0--求DISK SORT严重的SQLITPUB个人空间l;C;]A`? w.u
select sess.username, sql.sql_text, sort1.blocks
m}+Tk$[ h7\w0from v$session sess, v$sqlarea sql, v$sort_usage sort1
Y-e eL^[*v6y@0where sess.serial# = sort1.session_numITPUB个人空间0`]Z P4a$B*Arp*N
and sort1.sqladdr = sql.address
*TB?'{7l#lk(Z? q:^ `0and sort1.sqlhash = sql.hash_value and sort1.blocks > 200;ITPUB个人空间 m]&sy%~&o5teV
--求对象的创建代码ITPUB个人空间y*] Ab2SZe Z
column column_name format a36
H'vt;m`](D0column sql_text format a99ITPUB个人空间:C.sA2xsU.?[+|6G@
select dbms_metadata.get_ddl('TABLE','&1') from dual;
;y jrUvz:u t&q#g0select dbms_metadata.get_ddl('INDEX','&1') from dual;ITPUB个人空间sj,VzV;TvPl
--求表的索引
w2w+^a2D0set linesize 131ITPUB个人空间)?"L K nE(do;r
select a.index_name,a.column_name,b.status, b.index_type
.aOb'uW$s"@0from user_ind_columns a,user_indexes bITPUB个人空间c.ALY4[&H6X
where a.index_name=b.index_name and a.table_name='&1';ITPUB个人空间{)f4G'T ND}K8lA
求索引中行数较多的ITPUB个人空间e|0A.RWr C;h
select index_name,blevel,num_rows,CLUSTERING_FACTOR,status from user_indexes where num_rows > 10000 and blevel > 0ITPUB个人空间!d(gdK2}I
select table_name,index_name,blevel,num_rows,CLUSTERING_FACTOR,status from user_indexes where status <> 'VALID'
:| DXq4L yh mt0--求当前会话的SID，SERIAL#
,n,AT yQ v0select sid, serial# from v$session where audsid = SYS_CONTEXT('USERENV','SESSIONID');
_t jig4\+z0--求表空间的未用空间ITPUB个人空间Aj4z-g.fn
col mbytes format 9999.9999
3a7h6e(}lC%v3B0select tablespace_name,sum(bytes)/1024/1024 mbytes from dba_free_space group by tablespace_name;
!NCf7J |,tfdli0--求表中定义的触发器ITPUB个人空间xn#~n6D8Yq
select table_name,index_type,index_name,uniqueness from user_indexes where table_name='&1';
U0]}2n/Z T0select trigger_name from user_triggers where table_name='&1';ITPUB个人空间.O"_6be~h/H g
--求未定义索引的表ITPUB个人空间-d$c;T\!h+uu!hYu
select table_name from user_tables where table_name not in (select table_name from user_ind_columns);
JrV(H3V0]0--执行常用的过程
"}8IW6b8s#w+H:kv0exec print_sql('select count(*) from tab');ITPUB个人空间m#KsG Up6bS
exec show_space2('table_name');
{ c5^u0aL0--求free memoryITPUB个人空间a6v|p;b1n2uF1_
select * from v$sgastat where name='free memory';ITPUB个人空间.V T@$mr
select a.name,sum(b.value) from v$statname a,v$sesstat b where a.statistic# = b.statistic# group by a.name;ITPUB个人空间5n5z)zd)N
查看一下谁在使用那个可以得回滚段，或者查看一下某个可以得用户在使用回滚段，ITPUB个人空间 WT_ Y:\V8r7m!`/l
找出领回滚段不断增长的事务，再看看如何处理它，是否可以将它commit，再不行
+o XLq^ [5]+f-xh0就看看能否kill它，等等,查看当前正在使用的回滚段的用户信息和回滚段信息:
%MF9]_I1e"U0set linesize 121
RC)T/SRHT%k(PU0SELECT r.name "ROLLBACK SEGMENT NAME ",l.sid "ORACLEPID",p.spid "SYSTEM PID ",s.username "ORACLE USERNAME"
kR8m0Z;` c0FROM v$lock l, v$process p, v$rollname r, v$session s
q;^/t&w9D5}0WHERE 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;
OPDD} z0--查看用户的回滚段的信息
"CCR"h9cL1u7SQ2np0select s.username, rn.name from v$session s, v$transaction t, v$rollstat r, v$rollname rnITPUB个人空间-k!|-R#vQX E
where s.saddr = t.ses_addr and t.xidusn = r.usn and r.usn = rn.usnITPUB个人空间_JEw/Q.?C+A
--生成执行计划
X+U mW E0L L0explain plan set statement_id='a1' for &1;ITPUB个人空间`Pp [Q
--查看执行计划
v,`(M)d*z&Z0select lpad(' ',2*(level-1))||operation operation,options,OBJECT_NAME,position from plan_tableITPUB个人空间&@_:i4Oi'r+w
start with id=0 and statement_id='a1' connect by prior id=parent_id and statement_id='a1'ITPUB个人空间VR6p0mUx&QM2}
执行计划
IRSG#B7|aJ0   1）根据SID，从v$sql中找到相应SQL的HASH_VALUE和ADDRESS；ITPUB个人空间7q}4@(}7ra!y
   SELECT a.sql_text , a.address , a.hash_valueITPUB个人空间j-K5Gw5LQ!E'L
   FROM   v$sql a , v$session bITPUB个人空间k)_9{q!zqL4t(WTe
   where  a.hash_value = b.sql_hash_valueITPUB个人空间F?n"RK A
   and    b.sid = &sid ;
)zb7@],U B_[0Alan Lee(160921) 22:58:07ITPUB个人空间!he'xr:Qk(^\
2）根据hash_value和address的值，从v$sql_plan中找到真实的执行计划。ITPUB个人空间4SSe d1NF K6`KC
   set line 200;
]qDK`"O"ET0   col oper format a100;ITPUB个人空间'^s;Gr?-iL F
   select lpad(oper,length(oper)+level*2,' ') oper,cost
9j;o0mm+w0   from   (
7b#{SV'yc$SR8X#k0           select object_name||':'||operation||' '||options as oper,cost,id,parent_idITPUB个人空间4Xa9LS bV Y{
           from   v$sql_planITPUB个人空间i uqQ\UAh
           where  hash_value = &hash_valueITPUB个人空间q^;n/t H |.T
           and    address = '&address'ITPUB个人空间#SUyv8nye
          )ITPUB个人空间F"|+qoc
   start with id=0
'j.t:w v"D0   connect by prior id = parent_id;
4V~tu$d/hL0Alan Lee(160921) 22:58:26ITPUB个人空间6L}]KT*@
这2步，就可以找出实际正在跑的SQL使用的是什么执行计划ITPUB个人空间(~*{0N!a%kW'Q+Zv
set autotrace traceonly statistics
m`r4JvT0set autotrace traceonly explain
B vKY6y0set autotrace traceonly on explainITPUB个人空间/_:Z"p(nF%r2@`D,Y
--查看内存中存的使用ITPUB个人空间l/r#B2NXC9kE&B
select decode(greatest(class,10),10,decode(class,1,'Data',2,'Sort',4,'Header',to_char(class)),'Rollback') "Class",
t\ qg-h6_0sum(decode(bitand(flag,1),1,0,1)) "Not Dirty",sum(decode(bitand(flag,1),1,1,0)) "Dirty",ITPUB个人空间+Hw8@G h{a
sum(dirty_queue) "On Dirty",count(*) "Total"
*A3CmD8V I2I%u3t0from x$bh group by decode(greatest(class,10),10,decode(class,1,'Data',2,'Sort',4,'Header',to_char(class)),'Rollback');
7tA+J,\Oj5e0--查看表空间状态
C1];i"xk]0select tablespace_name,extent_management,segment_space_management from dba_tablespaces;ITPUB个人空间Ab*U(y%Nxu$g
select table_name,freelists,freelist_groups from user_tables;
Nd7C1D!ba%A0--查看系统请求情况ITPUB个人空间1Eg5b!?+q4wu
SELECT DECODE (name, 'summed dirty write queue length', value)/
5m'JV'uKk0DECODE (name, 'write requests', value) "Write Request Length"
$d|4X?*_!{1mS0FROM v$sysstat WHERE name IN ( 'summed dirty queue length', 'write requests') and value>0;
s~'Z.i4m$G!v Bv'Bs0--计算databuffer命中率ITPUB个人空间7y#KD@w-l Q S
select a.value + b.value "logical_reads", c.value "phys_reads",ITPUB个人空间.N Rq%B%T;k;QK
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"ITPUB个人空间(J&])sF nb"Q
from v$sysstat a, v$sysstat b, v$sysstat c
m y j7p!q w0where a.statistic# = 40 and b.statistic# = 41 and c.statistic# = 42;ITPUB个人空间^{ u5L%TZ
SELECT name, (1-(physical_reads/(db_block_gets+consistent_gets)))*100 H_RATIO FROM v$buffer_pool_statistics;ITPUB个人空间(gb7pM%{Zez^
--查看内存使用情况ITPUB个人空间g.^:H O\%^4M l;Oj
select least(max(b.value)/(1024*1024),sum(a.bytes)/(1024*1024)) shared_pool_used,
0^ T N'S,R)ZRD0max(b.value)/(1024*1024) shared_pool_size,greatest(max(b.value)/(1024*1024),sum(a.bytes)/(1024*1024))-
vd9X4qg D0(sum(a.bytes)/(1024*1024)) shared_pool_avail,((sum(a.bytes)/(1024*1024))/(max(b.value)/(1024*1024)))*100 avail_pool_pct
0t8T&xBc/e*p7N f_ jq0from v$sgastat a, v$parameter b where (a.pool='shared pool' and a.name not in ('free memory')) and b.name='shared_pool_size';ITPUB个人空间-h#GB'^_].]z\L
--查看用户使用内存情况
#m[#LT2I)n0select username, sum(sharable_mem), sum(persistent_mem), sum(runtime_mem)
*?!AruV'U4p B0from sys.v_$sqlarea a, dba_users bITPUB个人空间8h6leo3L-o:r\
where a.parsing_user_id = b.user_id group by username;ITPUB个人空间e4J aSm\b
--查看对象的缓存情况ITPUB个人空间l/qfZ_qL EMu
select OWNER,NAMESPACE,TYPE,NAME,SHARABLE_MEM,LOADS,EXECUTIONS,LOCKS,PINS,KEPTITPUB个人空间 B0?%bA ^)y3f R*Sz
from v$db_object_cache where type not in ('NOT LOADED','NON-EXISTENT','VIEW','TABLE','SEQUENCE')ITPUB个人空间td"B|D
and executions>0 and loads>1 and kept='NO' order by owner,namespace,type,executions desc;ITPUB个人空间%q5o:\bR.h%V7y,j8`
select type,count(*) from v$db_object_cache group by type;ITPUB个人空间M9h6RT|jL/x G%^
--查看库缓存命中率ITPUB个人空间{3[ a%I/_
select namespace,gets, gethitratio*100 gethitratio,pins,pinhitratio*100 pinhitratio,RELOADS,INVALIDATIONS from v$librarycache
} Q G.y*EqO~ a0--查看某些用户的hash
O)\N#\$Q z0select a.username, count(b.hash_value) total_hash,count(b.hash_value)-count(unique(b.hash_value)) same_hash,ITPUB个人空间2bq k\a z}(E.w
(count(unique(b.hash_value))/count(b.hash_value))*100 u_hash_ratioITPUB个人空间 } E w(m3WL'o^
from dba_users a, v$sqlarea b where a.user_id=b.parsing_user_id group by a.username;
H;Ue4un6c8i"h+j5T0--查看字典命中率
&fFG^e~ b$N kY0select (sum(getmisses)/sum(gets)) ratio from v$rowcache;
'Ai2~ C'v0--查看undo段的使用情况
sA|"At&w,]2b0SELECT d.segment_name,extents,optsize,shrinks,aveshrink,aveactive,d.status
1nz2@*@Q@D\0FROM v$rollname n,v$rollstat s,dba_rollback_segs d
9L E.c{E-\0WHERE d.segment_id=n.usn(+) and d.segment_id=s.usn(+);ITPUB个人空间^W7S/] R'Y]L(l
--无效的对象
4b!zM\/G{Q`0select owner,object_type,object_name from dba_objects where status='INVALID';ITPUB个人空间c WDk*R(C @-[2@O'M
select constraint_name,table_name from dba_constraints where status='INVALID';ITPUB个人空间 ](q5M$Zrx~
--求出某个进程，并对它进行跟踪ITPUB个人空间#uO G Qn8|-W:Y
select s.sid,s.serial# from v$session s,v$process p where s.paddr=p.addr and p.spid=&1;ITPUB个人空间A ~f+?9?,V _ _Y
exec dbms_system.SET_SQL_TRACE_IN_SESSION(&1,&2,true);
.R GJ%O+w4yphD'{6C"~0exec dbms_system.SET_SQL_TRACE_IN_SESSION(&1,&2,false);
'pS|~RXWa)r/u0--求出锁定的对象
5l6gC Y c }8X'Nm5k0select do.object_name,session_id,process,locked_mode
+Qi ^,wcU"v8^0from v$locked_object lo, dba_objects do where lo.object_id=do.object_id;ITPUB个人空间C-y[2V1~2Z-]6R
--求当前session的跟踪文件ITPUB个人空间0Y%N wF5R:`-Y8A2?
SELECT p1.value || '/' || p2.value || '_ora_' || p.spid || '.ora' filename
m"^ dY(b0X]0FROM v$process p, v$session s, v$parameter p1, v$parameter p2ITPUB个人空间 S%JW^~A Tjl G@$A
WHERE p1.name = 'user_dump_dest' AND p2.name = 'instance_name'ITPUB个人空间DQVJ TPh3B3\
AND p.addr = s.paddr AND s.audsid = USERENV('SESSIONID') AND p.background is null AND instr(p.program,'CJQ') = 0;
1i9Xt a2Ew G-l(}0--求对象所在的文件及块号
5b$X^*rr0select segment_name,header_file,header_block
s8`kX+j&s9?F0from dba_segments where segment_name like '&1';
qHeIU:R0--求对象发生事务时回退段及块号ITPUB个人空间5j\ yXB6nh
select a.segment_name,a.header_file,a.header_blockITPUB个人空间p}M:v h~
from dba_segments a,dba_rollback_segs b
M:sV/@(w0where a.segment_name=b.segment_name and b.segment_id='&1'ITPUB个人空间b6s ^/H rk.p hIT
--9i的在线重定义表ITPUB个人空间l6s p(N/o@N'T
/*如果在线重定义的表没有主键需要创建主键*/
F${q_n-x!e2`^U0exec dbms_redefinition.can_redef_table('cybercafe','announcement');
&s%sC2c:V;xY0create table anno2 as select * from announcement
vB B]^/}/sk$f b0exec dbms_redefinition.start_redef_table('cybercafe','announcement','anno2');ITPUB个人空间m:i.@8rAq+u"@&l
exec dbms_redefinition.sync_interim_table('cybercafe','announcement','anno2');ITPUB个人空间i-L!v9f)h_.?
exec dbms_redefinition.finish_redef_table('cybercafe','announcement','anno2');
c{4ro.YT0drop table anno2
{djaq/V5Y x{EL0exec dbms_redefinition.abort_redef_table('cybercafe','announcement','anno2');ITPUB个人空间5V9j/t^ fn:\e8_*u[
--常用的logmnr脚本(cybercafe)
1UD/k3S{x0exec sys.dbms_logmnr_d.build(dictionary_filename =>'esal',dictionary_location =>'/home/oracle/logmnr');ITPUB个人空间2HB W i-@{7k
exec sys.dbms_logmnr.add_logfile(logfilename=>'/home/oracle/oradata/esal/archive/1_24050.dbf', ptions=>sys.dbms_logmnr.new);
W7vrGl![#y!`6MK0exec sys.dbms_logmnr.add_logfile(logfilename=>'/home/oracle/oradata/esal/archive/1_22912.dbf', ptions=>sys.dbms_logmnr.addfile);
z&BtS iG0exec sys.dbms_logmnr.add_logfile(logfilename=>'/home/oracle/oradata/esal/archive/1_22913.dbf', ptions=>sys.dbms_logmnr.addfile);ITPUB个人空间;Q%~g m p5D
exec sys.dbms_logmnr.add_logfile(logfilename=>'/home/oracle/oradata/esal/archive/1_22914.dbf', ptions=>sys.dbms_logmnr.addfile);
,T}0u O#YT#y0exec sys.dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logmnr/esal.ora');
p+tYPSUA4q%v0create table logmnr2 as select * from v$logmnr_contents;ITPUB个人空间5c9f)E.I6[2H
死锁问题：1）查找死锁的进程：ITPUB个人空间4zL6qCoJ
sqlplus "/as sysdba"ITPUB个人空间]N"OqsE
SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,l.ORACLE_USERNAME,ITPUB个人空间9Tir5M%|BBy9Fg
l.OS_USER_NAME,l.PROCESS FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;ITPUB个人空间)B&MO^d#v^
ITPUB个人空间(C)O7w1V Nl5Fur
2）kill掉这个死锁的进程：ITPUB个人空间9@4L C q;| m
alter system kill session 'sid,serial#';（其中sid=l.session_id）ITPUB个人空间Q2g]Z_6GN n2ywf
3）如果还不能解决，ITPUB个人空间y"c T:ZI`%^
select pro.spid from v$session ses,v$process pro where ses.sid=XX and ses.paddr=pro.addr;ITPUB个人空间 fp/m2CZ P
其中sid用死锁的sid替换。ITPUB个人空间-F M:@0\P b;H
exit
I_&I0Z&N;tj0
h8A;aBp0--与权限相关的字典
.F+\9p!mipa3T0ALL_COL_PRIVS表示列上的授权，用户和PUBLIC是被授予者ITPUB个人空间&I Z&V_[
ALL_COL_PRIVS_MADE表示列上的授权，用户是属主和被授予者ITPUB个人空间o&E},ed'~7b
ALL_COL_RECD表示列上的授权，用户和PUBLIC是被授予者
(@0N9tsik|j0ALL_TAB_PRIVS表示对象上的授权，用户是PUBLIC或被授予者或用户是属主ITPUB个人空间s!r0lH{0h^,^
ALL_TAB_PRIVS_MADE表示对象上的权限，用户是属主或授予者ITPUB个人空间Ms ?pg8b@2U
ALL_TAB_PRIVS_RECD表示对象上的权限,用户是PUBLIC或被授予者ITPUB个人空间5u)A]"MydV9l
DBA_COL_PRIVS数据库列上的所有授权ITPUB个人空间 ?4q E1b"[_
DBA_ROLE_PRIVS显示已授予用户或其他角色的角色
7C JAyL)K:s|9i]0DBA_SYS_PRIVS已授予用户或角色的系统权限
lieYw Z)q?+b*@0DBA_TAB_PRIVS数据库对象上的所有权限ITPUB个人空间%piuz%hc7_mo
ROLE_ROLE_PRIVS显示已授予用户的角色
#s.V/LmQk8j0ROLE_SYS_PRIVS显示通过角色授予用户的系统权限ITPUB个人空间T?ga g
ROLE_TAB_PRIVS显示通过角色授予用户的对象权限
3e rR"?z%P? {R0SESSION_PRIVS显示用户现在可利用的所有系统权限ITPUB个人空间3y ~_~jt2nMdz
USER_COL_PRIVS显示列上的权限，用户是属主、授予者或被授予者
K&wC'~#Iw0USER_COL_PRIVS_MADE显示列上已授予的权限，用户是属主或授予者
)H&w h4V%Sd0USER_COL_PRIVS_RECD显示列上已授予的权限，用户是属主或被授予者
7Tm)BfE/Jz7Zy4B-a0USER_ROLE_PRIVS显示已授予给用户的所有角色ITPUB个人空间b3FR1pQ3V0d`
USER_SYS_PRIVS显示已授予给用户的所有系统权限
M K/V;E:K0USER_TAB_PRIVS显示已授予给用户的所有对象权限
y:gy$rR2Uq0USER_TAB_PRIVS_MADE显示已授予给其他用户的对象权限，用户是属主ITPUB个人空间0J2`NJ$j N
USER_TAB_PRIVS_RECD显示已授予给其他用户的对象权限，用户是被授予者ITPUB个人空间(Ki7t0?:n;l,M N)W!|
--如何用dbms_stats分析表及模式？
&s2hQna A!v0exec dbms_stats.gather_schema_stats(ownname=>'&USER_NAME',estimate_percent=>dbms_stats.auto_sample_size,
(Y:g B P!dn6Vh0method_opt => 'for all columns size auto',degree=> DBMS_STATS.DEFAULT_DEGREE);
{3h4yIJ0exec dbms_stats.gather_schema_stats(ownname=>'&USER_NAME',estimate_percent=>dbms_stats.auto_sample_size,cascade=>true);ITPUB个人空间r1|7bBm3L!f-`
/*
4~[oaV6Mv ^&u l0FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
pE%p!nH4I]3b0Y0H6^0FOR COLUMNS [size clause] column|attribute [size_clause] [,column|attribute [size_clause]...],ITPUB个人空间 `~4Gf8HA-i
where size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}ITPUB个人空间.P,Y1L ]*^^)k*Y
integer--Number of histogram buckets. Must be in the range [1,254].
T U+J9kBm0REPEAT--Collects histograms only on the columns that already have histograms.ITPUB个人空间\4ae${ ?,puvZ,C
AUTO--Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.ITPUB个人空间-D-H$];Q] MP o;T#r
SKEWONLY--Oracle determines the columns to collect histograms based on the data distribution of the columns
0|.Eplx%j2L0*/
_s9X7x,t+X9UQ0常用系统表,视图和作用
? ~Ds oRR,E0查看有关用户的信息:dba_usersITPUB个人空间"Z!g6sF!q+}/w}M_"P
查看有关角色的信息:dba_roles,dba_role_privs,role_sys_privs
6oW~u I9]Ix3K&u e0查看有关系统权限的信息:dba_sys_privs
!ZJ3aG9Z+Hv*p0查看当前数据库表空间状况:dba_tablespacesITPUB个人空间Qm6KAI#xz
查看用户的系统权限:user_sys_privsITPUB个人空间,w5G@[(|
查看某个用户对另外一个用户授予的权限:user_tab_privs_madeITPUB个人空间/H `%S'D.[N!w
查看某个用户对另外一个用户授予的列级权限:user_col_privs_madeITPUB个人空间 @'d,i&z'\
查看某个用户接受的权限:user_tab_privs_recd
aeCn'~]b0查看某个用户接受的列级权限:user_col_privs_recd
u;lx\-a7K Q;J0查看有关用户的角色信息:user_role_privsITPUB个人空间 l?)D.xj;{ia
查看有关授予某个角色的系统权限信息:role_sys_privs
XWi pX;H S0查看有关授予某个角色的对象权限信息:role_tab_privs
d r6pkYv@5l0查看当前用户所拥有的表信息:user_tablesITPUB个人空间#@"~q}b0MSt
查看当前用户有权限访问的表信息:all_tablesITPUB个人空间r?3uv5QV ?
查看当前用户所拥有的所有表的列信息:user_tab_columnsITPUB个人空间*}.i7c F vKB/Z5f
查看当前用户可以访问的表中的列信息:all_tab_columns
)HJ+]eV0K;G0查看当前用户所拥有的所有约束信息:user_constraintITPUB个人空间,bio,[ J
查看当前用户所拥有的所有约束和列的关系:user_cons_constraint
.X$o `5F1{rh0查看表中注释内容:user_tab_comments
P{*Z#Okd(r0查看表中列注释内容：user_col_commentsITPUB个人空间mO9Q!d9|:C!R
提供练习的表:dual
HS6qe Xz{4D^0查看相关时区的名称和简称:v$timezone_names
^"v;~-@`.Lq0V$OPTION:显示已安装的Oracle选项ITPUB个人空间H#z/f/iY a GL
select * from v$option;ITPUB个人空间y$_n1bIq
取得Oracle版本的详细信息
:O9r ~G'Bq+Q g0select * from v$version;
8a:Z/p k*O1C0取得初始化参数的详细信息
8v]#r8fEHw0select name,value,description from v$parameter;ITPUB个人空间\;D*NL&Z
取得当前例程的详细信息ITPUB个人空间3f?5I-C$e;GRe
select * from v$instance;
Q} }l9cU3~%Y0ITPUB个人空间 u&H6Lq%K(T(ptd0b
1、用户ITPUB个人空间 v!BV6l#_vE
　　查看当前用户的缺省表空间ITPUB个人空间 ?(CYB{iPQ }
　　SQL>select username,default_tablespace from user_users;
em wy#o&w+pEu0　　查看当前用户的角色
s2`P2R W/YHk0　　SQL>select * from user_role_privs;ITPUB个人空间 L+b[:[6C9w
　　查看当前用户的系统权限和表级权限
V i+`@'k`X!TH0　　SQL>select * from user_sys_privs;或ITPUB个人空间 c$T4`4d6y%s:NQ)A
select  username,  default_tablespace,  temporary_tablespace, priv granted_role,  default_role  from dba_users u,      (select grantee,granted_role priv,default_role           from dba_role_privs          union all         select grantee,privilege  priv,''           from dba_sys_privs c       ) r where u.username = r.grantee order by username ;ITPUB个人空间W8YAmR2XUK
　　SQL>select * from user_tab_privs;
1c b#A:yW[!y1AU0　　显示当前会话所具有的权限ITPUB个人空间({:CZD#U(ur^7t
　　SQL>select * from session_privs;ITPUB个人空间yG.h8C \I(],?:K&n
　　显示指定用户所具有的系统权限
"S|0C sO x7Jw(L,o o0　　SQL>select * from dba_sys_privs where grantee='GAME';
0QB"V_.R'o+j02、表ITPUB个人空间1xuF6P[ resf
　　查看用户下所有的表ITPUB个人空间iU)bC/j&V'z
　　SQL>select * from user_tables;
(LCa-@ ]J2a0　　查看名称包含log字符的表ITPUB个人空间 aL]hP8J(I8A N#s
　　SQL>select object_name,object_id from user_objects
!o3Z#xY%rL0　　where instr(object_name,'LOG')>0;ITPUB个人空间,`"vx1Y!i l;X~ ~'p
　　查看某表的创建时间
[2N^ ku6@\0　　SQL>select object_name,created from user_objects where object_name=upper('&table_name');
.~ ` S4p&s0h/C0　　查看某表的大小ITPUB个人空间n@;p+_fbW)?
　　SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segmentsITPUB个人空间0| TY+X:k#^$I
　　where segment_name=upper('&table_name');
0\m3L#}1L AhH a0　　查看放在ORACLE的内存区里的表
$g sZ)K'aT?0　　SQL>select table_name,cache from user_tables where instr(cache,'Y')>0;ITPUB个人空间,pF*I||,g/b0TG
3、索引
^`@(Cb}r0　　查看索引个数和类别ITPUB个人空间/{@N i0KR/Zp
　　SQL>select index_name,index_type,table_name from user_indexes order by table_name;ITPUB个人空间;Wz,[}^ f%i^0h
　　查看索引被索引的字段ITPUB个人空间1t"U0[5h"K$?`
　　SQL>select * from user_ind_columns where index_name=upper('&index_name');
5p)K PgD0　　查看索引的大小
9}[0_,Z} \0　　SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segmentsITPUB个人空间-Qp L6W B7TbdDw;]
　　where segment_name=upper('&index_name');ITPUB个人空间2}A r/ni;gJ |
4、序列号
6{:kj d{0QY)r0　　查看序列号，last_number是当前值ITPUB个人空间p8d B?a U |_
　　SQL>select * from user_sequences;ITPUB个人空间~~/e_9ph#k0S.?

5、视图ITPUB个人空间:L(Z lSn&]f
　　查看视图的名称
)c"?}Zw&R0　　SQL>select view_name from user_views;
m u v/wA,^Q4G0　　查看创建视图的select语句ITPUB个人空间'e}[!\5_b4r
　　SQL>set view_name,text_length from user_views;
q2m(r-Yz5yt L0　　SQL>set long 2000;说明：可以根据视图的text_length值设定set long的大小ITPUB个人空间NCL K3Me-\P
　　SQL>select text from user_views where view_name=upper('&view_name');
/M8LpW }tL06、同义词
3`E2Q0E;Y)tm gH%?0　　查看同义词的名称
N&i O"A$i)l8b0　　SQL>select * from user_synonyms;ITPUB个人空间2qGhN y-J
7、约束条件ITPUB个人空间 SF3vn w bV#i
　　查看某表的约束条件ITPUB个人空间F!?5i,s(r/b Dy{
　　SQL>select constraint_name, constraint_type,search_condition, r_constraint_name
P*p'l#e6Ee b/[0　　from user_constraints where table_name = upper('&table_name');ITPUB个人空间d:P }0|we
　　SQL>select c.constraint_name,c.constraint_type,cc.column_name
E-q!sQO7G0　　from user_constraints c,user_cons_columns ccITPUB个人空间.uN9nw3T-~@L Ee
　　where c.owner = upper('&table_owner') and c.table_name = upper('&table_name')
z)^*[zZJ%@@/z{0　　and c.owner = cc.owner and c.constraint_name = cc.constraint_nameITPUB个人空间,N*v.Q_!`(Et
　　order by cc.position;ITPUB个人空间g1d&Rm ad"|.fC*a
8、存储函数和过程ITPUB个人空间4p9q,vWqZ[z[
　　查看函数和过程的状态
f`e ?HH0　　SQL>select object_name,status from user_objects where object_type='FUNCTION';ITPUB个人空间:q-I|+Z4|A(Pf
　　SQL>select object_name,status from user_objects where object_type='PROCEDURE';
~^[.b-@l(D0　　查看函数和过程的源代码
a$i5v;Ab3`w;L6_0　　SQL>select text from all_source where wner=user and name=upper('&plsql_name');