SQL语句大全—查看表空间

查看表空间的名称及大小:

Vd9i,rS(n;~'_ v1f0SQL>SELECTT.TABLESPACE_NAME, ROUND(SUM(BYTES/(1024 * 1024)), 0) TS_SIZE
V9h;H6tX+y0FROMDBA_TABLESPACES T, DBA_DATA_FILES D51Testing软件测试网%\"p l|!ej.w
WHERET.TABLESPACE_NAME = D.TABLESPACE_NAME
4K@ S6Nn7Vn0GROUPBYT.TABLESPACE_NAME;
GEB/a?&PP&j!r051Testing软件测试网 j#`2?e a/\
查看表空间物理文件的名称及大小:51Testing软件测试网:A0Z+R-i%p2H+L5s+N,yv

SQL>SELECTTABLESPACE_NAME,FILE_ID,FILE_NAME,ROUND(BYTES / (1024 * 1024), 0) TOTAL_SPACE
'M5`LJr%]/_ w jy0FROMDBA_DATA_FILES
,X]g/?4i!t{C[0ORDERBYTABLESPACE_NAME;51Testing软件测试网jr*H(I\l
51Testing软件测试网#a]:pY-[m1]
查看回滚段名称及大小:
^7q4@T
rYot@0SQL>SELECTSEGMENT_NAME,
h3{&^F0Xs?-BCf J0       TABLESPACE_NAME,
$p/I-D~ kg0       R.STATUS,
^7k;`e0Qz)}0       (INITIAL_EXTENT / 1024) INITIALEXTENT,
[2Y1hHM`R0       (NEXT_EXTENT / 1024) NEXTEXTENT,51Testing软件测试网{'dGTGH g3t,~x
       MAX_EXTENTS,
U8_ c^]8B3o\0       V.CUREXT CUREXTENT51Testing软件测试网WTp6U3H N4p2~
FROMDBA_ROLLBACK_SEGS R, V$ROLLSTAT V51Testing软件测试网5le|(ow&E3A
WHERER.SEGMENT_ID = V.USN(+)51Testing软件测试网3dVi swU1t
ORDERBYSEGMENT_NAME;
8l,e7dvBPy/m051Testing软件测试网-aS)bLe]7Yr Z
如何查看某个回滚段里面，跑的什么事物或者正在执行什么sql语句:
\hUj9{,A!}5h8j0SQL>SELECTD.SQL_TEXT, A.NAME51Testing软件测试网[*w!m6L"zP%x
FROMV$ROLLNAME A, V$TRANSACTION B, V$SESSION C, V$SQLTEXT D51Testing软件测试网:C.P1vk MH9K`H
WHEREA.USN = B.XIDUSN
)V,?@s7sa0   ANDB.ADDR = C.TADDR51Testing软件测试网J"s#kxk ~U9q
   ANDC.SQL_ADDRESS = D.ADDRESS51Testing软件测试网RJ8uo6h;pG0s
   ANDC.SQL_HASH_VALUE = D.HASH_VALUE
w4?'t K^Y0   ANDA.USN = 1;51Testing软件测试网*](F8Nk.CP
yWx3w
(备注：你要看哪个，就把usn=?写成几就行了)

0M$E%q'S,i/nm0查看控制文件:
`~0T_%Z9`x0SQL>SELECT*FROMV$CONTROLFILE;51Testing软件测试网gE]qcN~]
51Testing软件测试网2]Sf
J6FhCI+eF5C
查看日志文件:
)o+E}t-ju7p5w0SQL> COLMEMBERFORMAT A50
ZlO;J2}Xj0SQL>SELECT*FROMV$LOGFILE;51Testing软件测试网?S(J {u5~n9I,d ~l

(F)|.B~*r0如何查看当前SQL*PLUS用户的sid和serial#:51Testing软件测试网hEfM{Q9Q ?F
SQL>SELECTSID, SERIAL#, STATUSFROMV$SESSIONWHEREAUDSID=USERENV('SESSIONID');
4xf hSI051Testing软件测试网`axNS ]
如何查看当前数据库的字符集:
s.I(o.B$r$t0SQL>SELECTUSERENV('LANGUAGE')FROMDUAL;
P(e_W7y0SQL>SELECTUSERENV('LANG')FROMDUAL;
Y@ \x/dZZ s\`051Testing软件测试网_*r;?M.C&kW

ouuT,r0怎么判断当前正在使用何种SQL优化方式:51Testing软件测试网.S5Kl6P+}S"bZ
用EXPLAIN PLAN產生EXPLAIN PLAN¡檢查PLAN_TABLE中ID=0的POSITION列的值51Testing软件测试网Ir4E9?!W
SQL>SELECTDECODE(NVL(POSITION,-1),-1,'RBO',1,'CBO')FROMPLAN_TABLEWHEREID=0;
oHJa8P051Testing软件测试网
N1J)QE)] N
如何查看系统当前最新的SCN号：51Testing软件测试网?+A{'YB aQ
SQL>SELECTMAX(KTUXESCNW * POWER(2,32) + KTUXESCNB)FROMX$KTUXE;
\L K&}4f0r3S&t*d k051Testing软件测试网8e |5b.VxR9PR
在ORACLE中查找TRACE文件的脚本:

SQL>SELECTU_DUMP.VALUE|| '/' ||INSTANCE.VALUE|| '_ORA_' ||51Testing软件测试网F&B:PpA0dJ6M
V$PROCESS.SPID || NVL2(V$PROCESS.TRACEID, '_' || V$PROCESS.TRACEID,NULL) || '.TRC'"TRACE FILE"FROMV$PARAMETER U_DUMPCROSSJOINV$PARAMETERINSTANCECROSSJOINV$PROCESSJOINV$SESSIONONV$PROCESS.ADDR = V$SESSION.PADDRWHEREU_DUMP.NAME= 'USER_DUMP_DEST'AND51Testing软件测试网"b_5?/dx\U5`
INSTANCE.NAME= 'INSTANCE_NAME'ANDV$SESSION.AUDSID=SYS_CONTEXT('USERENV','SESSIONID');51Testing软件测试网k&},I+ex!ph!G x

2J2H6q2CWr-{E)GiD f0SQL>SELECTD.VALUE|| '/ORA_' || P.SPID || '.TRC' TRACE_FILE_NAME
!` h(i:xe0FROM(SELECTP.SPIDFROMSYS.V_$MYSTAT M,SYS.V_$SESSION S,
\&[u Be:K(b[0SYS.V_$PROCESS PWHEREM.STATISTIC# = 1AND
!} IK k$t2o#o0S.SID = M.SIDANDP.ADDR = S.PADDR) P,(SELECTVALUEFROMSYS.V_$PARAMETERWHERENAME='USER_DUMP_DEST') D;51Testing软件测试网J7m"s5a SCb8J$X
51Testing软件测试网J9FI il Q @`4^
如何查看客户端登陆的IP地址:51Testing软件测试网y? hH$c
W+HG
SQL>SELECTSYS_CONTEXT('USERENV','IP_ADDRESS')FROMDUAL;
;Jd&f-A.JJ051Testing软件测试网4Ge N)I&p8?V/z
如何在生产数据库中创建一个追踪客户端IP地址的触发器：51Testing软件测试网0~R5PP1qivn)w
SQL>CREATEORREPLACETRIGGERON_LOGON_TRIGGERAFTERLOGONONDATABASE
2Y1qdJ3Zu0BEGIN51Testing软件测试网G4L[ihN-p
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(SYS_CONTEXT('USERENV', 'IP_ADDRESS'));
MW$GK\vcGX0END;
c R5[[jYF w1w$H0
'vo&|.P^0REM 记录登陆信息的触发器51Testing软件测试网,auvH|b
CREATEORREPLACETRIGGERLOGON_HISTORY
*`-r&C^\'g4\ h0AFTERLOGONONDATABASE--WHEN (USER='WACOS') --ONLY FOR USER 'WACOS'
)W^,cfM0BEGIN
uH;S(Z&h0INSERTINTOSESSION_HISTORYSELECTUSERNAME,SID,SERIAL#,AUDSID,OSUSER,ACTION,SYSDATE,NULL,SYS_CONTEXT('USERENV','IP_ADDRESS'),TERMINAL,MACHINE,PROGRAMFROMV$SESSIONWHEREAUDSID = USERENV('SESSIONID');51Testing软件测试网5SB2J [z&C
w]
END;51Testing软件测试网rv5g,A ZTJ"Y

'UTMj}9Vu0查询当前日期:51Testing软件测试网Yq$H$fH'a2|,T
SQL>SELECTTO_CHAR(SYSDATE,'YYYY-MM-DD,HH24:MI:SS')FROMDUAL;
:F&ucSKSk051Testing软件测试网'qc.n6RO B7\'y8`
查看所有表空间对应的数据文件名：

@6bOhN0SQL>SELECTDISTINCTFILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLEFROMDBA_DATA_FILES;
/^3P(W#h0n051Testing软件测试网5Jj&M&G"gO.|
查看表空间的使用情况:51Testing软件测试网R/XJnr6f3ChO@&r+T
SQL>SELECTSUM(BYTES)/(1024*1024)ASFREE_SPACE,TABLESPACE_NAME
Q:u6J~*I-UZ2_0FROMDBA_FREE_SPACEGROUPBYTABLESPACE_NAME;51Testing软件测试网 I|1OiYX#X&y-pf-A

:Y.x _`}eX1F0SQL>SELECTA.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,51Testing软件测试网,V0ZG c\-Xaj
(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
.rt3}!k
BF0FROMSYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
EX&w8qpo0WHEREA.TABLESPACE_NAME=B.TABLESPACE_NAMEANDA.TABLESPACE_NAME=C.TABLESPACE_NAME;
/kll/wq[4U#~051Testing软件测试网,Z;R$Q8A m"w C N
COLUMNTABLESPACE_NAME FORMAT A18;
q^n y {Gj0COLUMNSUM_M FORMAT A12;
Ot8DS}P@0COLUMNUSED_M FORMAT A12;51Testing软件测试网6u9ZdhN&S0K8\iP
COLUMNFREE_M FORMAT A12;
M7s*c%g7h6Oa0COLUMNPTO_M FORMAT 9.99;
+UW+?)^cR0
`0E1l)C RI0SELECTS.TABLESPACE_NAME,CEIL(SUM(S.BYTES/1024/1024))||'M' SUM_M,CEIL(SUM(S.USEDSPACE/1024/1024))||'M' USED_M,CEIL(SUM(S.FREESPACE/1024/1024))||'M' FREE_M,SUM(S.USEDSPACE)/SUM(S.BYTES) PTUSEDFROM(SELECTB.FILE_ID,B.TABLESPACE_NAME,B.BYTES, (B.BYTES-SUM(NVL(A.BYTES,0))) USEDSPACE,SUM(NVL(A.BYTES,0)) FREESPACE,(SUM(NVL(A.BYTES,0))/(B.BYTES)) * 100 FREEPERCENTRATIOFROMSYS.DBA_FREE_SPACE A,SYS.DBA_DATA_FILES BWHEREA.FILE_ID(+)=B.FILE_IDGROUPBYB.FILE_ID,B.TABLESPACE_NAME,B.BYTESORDERBYB.TABLESPACE_NAME) SGROUPBYS.TABLESPACE_NAMEORDERBYSUM(S.FREESPACE)/SUM(S.BYTES)DESC;51Testing软件测试网;n5Lh8IEgz9g9F'M
51Testing软件测试网1h'Y#@lf,nk
查看数据文件的hwm（可以resize的最小空间）和文件头大小:
q0su'\O/d+^BI0SELECTV1.FILE_NAME,V1.FILE_ID,NUM1 TOTLE_SPACE,NUM3 FREE_SPACE,51Testing软件测试网}:GMX$zl
NUM1-NUM3 "USED_SPACE(HWM)",NVL(NUM2,0) DATA_SPACE,NUM1-NUM3-NVL(NUM2,0) FILE_HEAD51Testing软件测试网0J6G:] LRt"F6}M
FROM51Testing软件测试网+[:K5GMA
(SELECTFILE_NAME,FILE_ID,SUM(BYTES) NUM1FROMDBA_DATA_FILESGROUPBYFILE_NAME,FILE_ID) V1,
/G d5|2Y!cH0(SELECTFILE_ID,SUM(BYTES) NUM2FROMDBA_EXTENTSGROUPBYFILE_ID) V2,
|6~.x'QS1f0(SELECTFILE_ID,SUM(BYTES) NUM3FROMDBA_FREE_SPACEGROUPBYFILE_ID) V3
4yiU8q ^ gUAq0WHEREV1.FILE_ID=V2.FILE_ID(+)ANDV1.FILE_ID=V3.FILE_ID(+);
t
MGy/~#Y0数据文件大小及头大小:51Testing软件测试网B+SWi {(P]P5p
SELECTV1.FILE_NAME,V1.FILE_ID,51Testing软件测试网F {-n5MuN)Cp
NUM1 TOTLE_SPACE,
D7|!o$av*bD,v*fz9j0NUM3 FREE_SPACE,51Testing软件测试网U2xu
k7\1k;N;y/]
NUM1-NUM3 USED_SPACE,
mH7a_v^Ki0NVL(NUM2,0) DATA_SPACE,
+Y[ |._1A7~eT0NUM1-NUM3-NVL(NUM2,0) FILE_HEAD51Testing软件测试网 ]
}/bTr(`
a2N
FROM
D!v1GH$~K3ID,m/a,Z1J0(SELECTFILE_NAME,FILE_ID,SUM(BYTES) NUM1FROMDBA_DATA_FILESGROUPBYFILE_NAME,FILE_ID) V1,51Testing软件测试网#m hQJ?(Qb s
(SELECTFILE_ID,SUM(BYTES) NUM2FROMDBA_EXTENTSGROUPBYFILE_ID) V2,51Testing软件测试网{B5k4We"M8z!D
(SELECTFILE_ID,SUM(BYTES) NUM3FROMDBA_FREE_SPACEGROUPBYFILE_ID) V3
)w+l&ED;]6a.U6q |i0WHEREV1.FILE_ID=V2.FILE_ID(+)
0X#ia{$H,] b0ANDV1.FILE_ID=V3.FILE_ID(+);
:x ~9Q2xaFz051Testing软件测试网Z2KJ/CQS)l
(运行以上查询，我们可以如下信息：51Testing软件测试网 Jz@;~-R v2b
Totle_pace:该数据文件的总大小，字节为单位51Testing软件测试网0VZMtT,lkMq
Free_space:该数据文件的剩于大小，字节为单位
P!T2U$lK7B0Used_space:该数据文件的已用空间，字节为单位
(y'x,EJ*Qc7Ic:C B0Data_space:该数据文件中段数据占用空间，也就是数据空间，字节为单位51Testing软件测试网'SN(N-D{L
File_Head:该数据文件头部占用空间，字节为单位)51Testing软件测试网2WHMz*{X

?#@w
Wva0

\
d U,d3B0

{|6ru$j0SELECTSW.SID,S.USERNAME,SW.EVENT,SW.WAIT_TIME,SW.STATE,SW.SECONDS_IN_WAIT SEC_IN_WAIT51Testing软件测试网L(hK@!l{
FROMV$SESSION S,V$SESSION_WAIT SWWHERES.USERNAMEISNOTNULLANDSW.SID=S.SID51Testing软件测试网/?8Hob
gE
ANDSW.EVENTNOTLIKE'%SQL*NET%'ORDERBYSW.WAIT_TIMEDESC;
-~:I.Z(__0\I
C/do0
izUgC3~ ~0Oracle所有回滚段状态的检查：

SQL>SELECTSEGMENT_NAME,OWNER,TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,DBA_ROLLBACK_SEGS.STATUSFROMDBA_ROLLBACK_SEGS,V$DATAFILEWHEREFILE_ID=FILE#;51Testing软件测试网 ARN_+h(_4s6z
51Testing软件测试网z,}z_?Hc
Oracle回滚段扩展信息的检查:
6rD+e T G
\0COLNAMEFORMAT A10
-m,TO,F6e&R9z/X T'[X0SETLINESIZE 140         
eq
{k:E0O"z'dL*L0SELECTSUBSTR(NAME,1,40)NAME,EXTENTS,RSSIZE,OPTSIZE,AVEACTIVE,EXTENDS,WRAPS,SHRINKS,HWMSIZE
q X4y1t+bE0FROMV$ROLLNAME RN,V$ROLLSTAT RSWHERE(RN.USN=RS.USN);
K.g"UP;c!]8\051Testing软件测试网%Mv AZ(f R
EXTENTS:回滚段中的盘区数量。
7LF'v;L\7T0Rssize:以字节为单位的回滚段的尺寸。
1Jr)L G+d&`+fef%T0optsize：为optimal参数设定的值。
0T3y-`aO2x1dp0Aveactive:从回滚段中删除盘区时释放的以字节为单位的平均空间的大小。
%fzUTu0Extends:系统为回滚段增加的盘区的次数。51Testing软件测试网hEg
Q"o}[
Shrinks:系统从回滚段中清除盘区（即回滚段收缩）的次数。回滚段每次清除盘区时，系统可能会从这个回滚段中消除一个或多个盘区。
]&Rk;cWxp0Hwmsize:回滚段尺寸的上限，即回滚段曾经达到的最大尺寸。51Testing软件测试网E#y\1]b4|0B
(如果回滚段平均尺寸接近OPTIMAL的值，那么说明OPTIMAL的值设置正确，如果回滚段动态增长次数或收缩次数很高，那么需要提高OPTIMAL的值)

查看回滚段的使用情况，哪个用户正在使用回滚段的资源:
3S.t7xS[
_O)c0SELECTS.USERNAME, U.NAMEFROMV$TRANSACTION T,V$ROLLSTAT R,
Q)DU9po@"x6Q0V$ROLLNAME U,V$SESSION SWHERES.TADDR=T.ADDRAND51Testing软件测试网0^Ej(M&|%p
T.XIDUSN=R.USNANDR.USN=U.USNORDERBYS.USERNAME;
.K#s(Du/N*[F0
D6ydv0Y}sA?0如何查看一下某个shared_server正在忙什么:51Testing软件测试网g};^2z6H1X}Hv
SELECTA.USERNAME,A.MACHINE,A.PROGRAM,A.SID,51Testing软件测试网@(WHF\9B#`v#n
A.SERIAL#,A.STATUS,C.PIECE,C.SQL_TEXT
!blW8tl Nd7H6I0FROMV$SESSION A,V$PROCESS B,V$SQLTEXT C51Testing软件测试网m]*e(T#T2k`)po A
WHEREB.SPID=13161ANDB.ADDR=A.PADDR
?XCu)S`/DKv
G6D0ANDA.SQL_ADDRESS=C.ADDRESS(+)ORDERBYC.PIECE;
Zyq'a!nh,nw0
'm9k;G:qOF4r5x0数据库共享池性能检查:51Testing软件测试网9C3X _v@7^
SELECTNAMESPACE,GETS,GETHITRATIO,PINS,PINHITRATIO,RELOADS,INVALIDATIONSFROMV$LIBRARYCACHEWHERENAMESPACEIN('SQLAREA','TABLE/PROCEDURE','BODY','TRIGGER');
h8k#zq5M5In0
&Bc-fQyW0检查数据重载比率:
q_-\an0SELECTSUM(RELOADS)/SUM(PINS)*100 "RELOAD RATIO"FROM
5{5z/W:^3V9Q:rU0j"oR0a0V$LIBRARYCACHE;51Testing软件测试网,T9N`^YZ/t.Pr
51Testing软件测试网Lt0}E'`Zu
检查数据字典的命中率:
0|3\tX'O0SELECT1-SUM(GETMISSES)/SUM(GETS) "DATA DICTIONARY HIT51Testing软件测试网Y|;^-x2} @r-g`
RATIO"FROMV$ROWCACHE;
,C7f0a3x!Wsq0(对于library cache, gethitratio和pinhitratio应该大于90%,对于数据重载比率,reload ratio应该小于1%,对于数据字典的命中率,data dictionary hit ratio应该大于85%)51Testing软件测试网S\ R,|1`\t

5TnEj V Fn~!B0检查共享内存的剩余情况:
I#\"r#VX3Zl1_@0SELECTREQUEST_MISSES, REQUEST_FAILURESFROMV$SHARED_POOL_RESERVED;
"EZ:C
ZFe y;{3T0(对于共享内存的剩余情况, request_misses 和request_failures应该接近0)51Testing软件测试网G*eZ O%d(L&Vg

数据高速缓冲区性能检查:51Testing软件测试网L+N!yu7i0m
SELECT1-P.VALUE/(B.VALUE+C.VALUE) "DB BUFFER CACHE HIT RATIO"FROMV$SYSSTAT P,V$SYSSTAT B,V$SYSSTAT CWHEREP.NAME='PHYSICAL READS'ANDB.NAME='DB BLOCK GETS'ANDC.NAME='CONSISTENT GETS';51Testing软件测试网
Af&Z|l

0E9y5M+{f(g;q0检查buffer pool HIT_RATIO执行
R]-fu3Z'c}5J4Lef"|0SELECTNAME, (PHYSICAL_READS/(DB_BLOCK_GETS+CONSISTENT_GETS)) "MISS_HIT_RATIO"FROMV$BUFFER_POOL_STATISTICSWHERE(DB_BLOCK_GETS+ CONSISTENT_GETS)> 0;51Testing软件测试网U@/l^
c*{"nhH1~ q
(正常时db buffer cache hit ratio 应该大于90%,正常时buffer pool MISS_HIT_RATIO 应该小于10%)

5W l6N4Y#TTgk;i0数据库回滚段性能检查:
1Km1z!xb0检查Ratio执行
!v u]&A-D.~:L"_0SELECTSUM(WAITS)* 100 /SUM(GETS) "RATIO",SUM(WAITS) "WAITS",SUM(GETS) "GETS"FROMV$ROLLSTAT;
&~8{0e9^h0
:f6y
G.e)U0检查count/value执行:51Testing软件测试网x.tf)d)eB
SELECTCLASS,COUNTFROMV$WAITSTATWHERECLASSLIKE'%UNDO%';51Testing软件测试网#r2[)YJ8g%l
SELECTVALUEFROMV$SYSSTATWHERENAME='CONSISTENT GETS';51Testing软件测试网|vU$c%xa4Ir
(两者的value值相除)51Testing软件测试网W,]iB'_1H%O
51Testing软件测试网EHb/~)`M
检查average_wait执行:
W+hK`4gc0LqNN0SELECTEVENT,TOTAL_WAITS,TIME_WAITED,AVERAGE_WAITFROMV$SYSTEM_EVENTWHEREEVENTLIKE'%UNDO%';51Testing软件测试网G,|&I8TW^;_%\D?f
51Testing软件测试网fU9g&HHu*t7a)K"g]
检查RBS header get ratio执行:51Testing软件测试网.^c#Mx ck
SELECTN.NAME,S.USN,S.WRAPS, DECODE(S.WAITS,0,1,1- S.WAITS/S.GETS)"RBS HEADER GET RATIO"FROMV$ROLLSTAT S,V$ROLLNAME NWHERES.USN=N.USN;51Testing软件测试网l.S5c p4B
(正常时Ratio应该小于1%, count/value应该小于0.01%,average_wait最好为0，该值越小越好,RBS header get ratio应该大于95%)

杀会话的脚本:
Gna)_jS"ml0SELECTA.SID,B.SPID,A.SERIAL#,A.LOCKWAIT,A.USERNAME,A.OSUSER,A.LOGON_TIME,A.LAST_CALL_ET/3600 LAST_HOUR,A.STATUS, 'ORAKILL '||SID||' '||SPID HOST_COMMAND,'ALTER SYSTEM KILL SESSION '''||A.SID||','||A.SERIAL#||'''' SQL_COMMANDFROMV$SESSION A,V$PROCESS BWHEREA.PADDR=B.ADDRANDSID>6;51Testing软件测试网1{+LH;|1b }tk
R*^
51Testing软件测试网!O YR^tj.d2F]$B
查看排序段的性能:51Testing软件测试网YB,z RPf\~
SQL>SELECTNAME,VALUEFROMV$SYSSTATWHERENAMEIN('SORTS (MEMORY)', 'SORTS (DISK)');
ERg[gXD0
3M X7g n)O;?3|07、查看数据库库对象:51Testing软件测试网/r-tV-aMT
SELECTOWNER, OBJECT_TYPE, STATUS,COUNT(*)COUNT#FROMALL_OBJECTSGROUPBYOWNER, OBJECT_TYPE, STATUS;51Testing软件测试网f
@tH"eQ3}q.['u

s
\9n#n*K Su-Ei08、查看数据库的版本:　
:?${%[cp$ZYyv$A0SELECT*FROMV$VERSION;
3xF }.[L|+{051Testing软件测试网%k`:}TiQ }n O
9、查看数据库的创建日期和归档方式:
8n$l0V{)ah%p mu0SELECTCREATED, LOG_MODE, LOG_MODEFROMV$DATABASE;51Testing软件测试网e\.@1}8E*@]B

10、捕捉运行很久的SQL:
I$K-a#Un%UEQ0COLUMNUSERNAME FORMAT A1251Testing软件测试网0`;U0GBPy#f? z Xq
COLUMNOPNAME FORMAT A16
t9R3QQvOh
^6A0COLUMNPROGRESS FORMAT A8
?t%Z:x uM0SELECTUSERNAME,SID,OPNAME,ROUND(SOFAR*100 / TOTALWORK,0) || '%'ASPROGRESS,TIME_REMAINING,SQL_TEXTFROMV$SESSION_LONGOPS , V$SQLWHERETIME_REMAINING <> 0ANDSQL_ADDRESS=ADDRESSANDSQL_HASH_VALUE = HASH_VALUE;51Testing软件测试网A"E8?yAZ
51Testing软件测试网7Pb*GcaO-F9w
11、查看数据表的参数信息:
4t3PZ|H)~6X0SELECTPARTITION_NAME, HIGH_VALUE, HIGH_VALUE_LENGTH, TABLESPACE_NAME,PCT_FREE, PCT_USED, INI_TRANS, MAX_TRANS, INITIAL_EXTENT,NEXT_EXTENT, MIN_EXTENT, MAX_EXTENT, PCT_INCREASE,FREELISTS,FREELIST_GROUPS,LOGGING,BUFFER_POOL, NUM_ROWS, BLOCKS,EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, SAMPLE_SIZE,LAST_ANALYZEDFROMDBA_TAB_PARTITIONS51Testing软件测试网3T'd5?Z|Q
--WHERE TABLE_NAME = :TNAME AND TABLE_OWNER = :TOWNER
a-Qi
GoB0ORDERBYPARTITION_POSITION;
,g{f"T4o0
Mq(rZMH+s%NA012、查看还没提交的事务:
'OfF }1I4H\0SELECT*FROMV$LOCKED_OBJECT;51Testing软件测试网.I_Me/v}2sIi4s&z j
SELECT*FROMV$TRANSACTION;51Testing软件测试网
v'L8@!v3nZ V;qJ
51Testing软件测试网5e7D)u a5[Z~ J
13、查找object为哪些进程所用:
u aH!_E0SELECTP.SPID,S.SID,S.SERIAL# SERIAL_NUM,S.USERNAME USER_NAME,
/K A[(X4vE;N0A.TYPEOBJECT_TYPE,S.OSUSER OS_USER_NAME,A.OWNER,A.OBJECTOBJECT_NAME,DECODE(SIGN(48 - COMMAND),1,51Testing软件测试网)i2h"` tb[ p:x
TO_CHAR(COMMAND), 'ACTION CODE #' || TO_CHAR(COMMAND) ) ACTION,
Kq+];`P"Yx {0P.PROGRAM ORACLE_PROCESS,S.TERMINAL TERMINAL,S.PROGRAM PROGRAM,S.STATUS SESSION_STATUSFROMV$SESSION S, V$ACCESS A, V$PROCESS PWHERES.PADDR = P.ADDRANDS.TYPE= 'USER'ANDA.SID = S.SID  ANDA.OBJECT='SUBSCRIBER_ATTR'ORDERBYS.USERNAME, S.OSUSER;51Testing软件测试网5@+r:}4t5h'R

x]'Ak y014、查看回滚段:
U2zg&]M9K-_0SQL>COLNAMEFORMAT A10
CGTI)rG:?Dkf0SQL>SETLINESIZE 10051Testing软件测试网-OOn8q{/g%j6H
SQL>SELECTROWNUM,SYS.DBA_ROLLBACK_SEGS.SEGMENT_NAMENAME, V$ROLLSTAT.EXTENTSEXTENTS, V$ROLLSTAT.RSSIZE SIZE_IN_BYTES, V$ROLLSTAT.XACTS XACTS, V$ROLLSTAT.GETS GETS, V$ROLLSTAT.WAITS WAITS, V$ROLLSTAT.WRITES WRITES,SYS.DBA_ROLLBACK_SEGS.STATUS STATUSFROMV$ROLLSTAT,SYS.DBA_ROLLBACK_SEGS, V$ROLLNAMEWHEREV$ROLLNAME.NAME(+) =SYS.DBA_ROLLBACK_SEGS.SEGMENT_NAMEANDV$ROLLSTAT.USN (+) = V$ROLLNAME.USNORDERBYROWNUM;51Testing软件测试网 Lp5B7~w7D
51Testing软件测试网1r8xZ3n bk
15、耗资源的进程(top session):51Testing软件测试网9w}6f'f!\o
SELECTS.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.VALUECRITERIA_VALUEFROMV$SESSTAT ST,V$SESSION S,V$PROCESS PWHEREST.SID = S.SIDANDST.STATISTIC# = TO_NUMBER('38')AND('ALL'='ALL'ORS.STATUS ='ALL')ANDP.ADDR=S.PADDRORDERBYST.VALUEDESC,P.SPIDASC,S.USERNAMEASC,S.OSUSERASC;
v;Z,iK{!_MJf051Testing软件测试网2P/IUe[Er
根据PID查找相应的语句:51Testing软件测试网j!JVA*Fd Z]

~io'v#s0SELECTA.USERNAME, A.MACHINE,A.PROGRAM,A.SID,A.SERIAL#,A.STATUS,C.PIECE,C.SQL_TEXTFROMV$SESSION A,V$PROCESS B,V$SQLTEXT CWHEREB.SPID=SPIDANDB.ADDR=A.PADDRANDA.SQL_ADDRESS=C.ADDRESS(+)ORDERBYC.PIECE;

CPU用率最高的2条SQL语句的获取
zc(wMs H2v;D9q0执行：top，通过top获得CPU占用率最高的进程的pid。51Testing软件测试网0uxs4@ G
SQL>SELECTSQL_TEXT,SPID,V$SESSION.PROGRAM,PROCESSFROMV$SQLAREA,V$SESSION,V$PROCESSWHEREV$SQLAREA.ADDRESS=V$SESSION.SQL_ADDRESSANDV$SQLAREA.HASH_VALUE=V$SESSION.SQL_HASH_VALUEANDV$SESSION.PADDR=V$PROCESS.ADDRANDV$PROCESS.SPIDIN(PID);
}sI2_6e5B0COL MACHINE FORMAT A30
5G.tI)k i3@V&NK0COL PROGRAM FORMAT A40
8r;_;T4P'yX0SETLINE 20051Testing软件测试网 \e\r)B8},B
SQL>SELECTSID,SERIAL# ,USERNAME,OSUSER,MACHINE,PROGRAM,PROCESS,TO_CHAR(LOGON_TIME,'YYYY/MM/DD HH24:MI:SS')FROMV$SESSIONWHEREPADDRIN(SELECTADDRFROMV$PROCESSWHERESPIDIN([$SPID]));
:S:O0T`?2nV051Testing软件测试网y}2p6D du'H e
SELECTSQL_TEXTFROMV$SQLTEXT_WITH_NEWLINES51Testing软件测试网i@4Iz%L5k/{dG
WHEREHASH_VALUE=(SELECTSQL_HASH_VALUEFROMV$SESSIONWHERESID=&SID)
TL)j1r8K'Kf*j0ORDERBYPIECE;51Testing软件测试网:J!Vht!tn;C[ F l,v(O
51Testing软件测试网jb\,x)V~
16、查看锁（lock）情况:51Testing软件测试网juZ8i`Xfk0] u
SQL>SELECT/*+ RULE */51Testing软件测试网 s%J%y&N$Ca5~/VOJ E
LS.OSUSER OS_USER_NAME,51Testing软件测试网U'P4jBa'E/]8~U
LS.USERNAME USER_NAME,51Testing软件测试网ZU([1`s&{(Q
DECODE(LS.TYPE,
i;YfN)G}0        'RW','ROW WAIT ENQUEUE LOCK',51Testing软件测试网#x/v L4l1n2}9Xs!gi
        'TM','DML ENQUEUE LOCK',51Testing软件测试网MH/B4Z0e"mAJq
        'TX','TRANSACTION ENQUEUE LOCK',
xw%^+vw)IT0        'UL','USER SUPPLIED LOCK') LOCK_TYPE,51Testing软件测试网.p`V&}TK?
O.OBJECT_NAMEOBJECT,51Testing软件测试网n)v%C'R]8b^X
DECODE(LS.LMODE,51Testing软件测试网FI*d;h;[7v;U!Ts
        1,NULL,
6LL#~8mk M0        2,'ROW SHARE',51Testing软件测试网9y,`i:`5I
        3,'ROW EXCLUSIVE',51Testing软件测试网7jP"w:\:l)g.s%c
l
        4,'SHARE',51Testing软件测试网;\^*evm`2VG(b
        5,'SHARE ROW EXCLUSIVE',51Testing软件测试网~Knm4z?o*bR0}J._b n
        6,'EXCLUSIVE',
6}7?q`k$F)vJ-ZX0        NULL) LOCK_MODE,51Testing软件测试网#^o8W&t9}in
O.OWNER,
+b6TZ U@zq0LS.SID,51Testing软件测试网zb"Es'P*fvn
LS.SERIAL# SERIAL_NUM,51Testing软件测试网'k6L&I@oI^
LS.ID1,
+I"O4tqv0LS.ID251Testing软件测试网7Q%}fR3?9F K m:MW
FROMSYS.DBA_OBJECTS O,51Testing软件测试网7V)w8@^f_l
       (SELECTS.OSUSER,
EzRW3w%[j i0               S.USERNAME,51Testing软件测试网v](X ^~5l[d-k n
               L.TYPE,
Q w7Q}d0               L.LMODE,51Testing软件测试网aX GHf.w-{ vZB^
               S.SID,51Testing软件测试网5V2W_R"{2R~
               S.SERIAL#,51Testing软件测试网@&J-@ ZDY5P
               L.ID1,51Testing软件测试网7~&EmQ!|uF
               L.ID2
1o5`#R`prGt0          FROMV$SESSION S, V$LOCK L51Testing软件测试网a\"Fb#b$HC)edc
         WHERES.SID = L.SID) LS51Testing软件测试网.`w(V K"t1Q
WHEREO.OBJECT_ID = LS.ID151Testing软件测试网? X FzX5_ D
   ANDO.OWNER <> 'SYS'
)_cpv QkrR0ORDERBYO.OWNER, O.OBJECT_NAME;
g u+j;P5xQ Y051Testing软件测试网o(NTss\#t;d9j#i
SQL>SELECTSYS.V_$SESSION.OSUSER,
M tK9Cs-C0       SYS.V_$SESSION.MACHINE,
:VGCDp0       V$LOCK.SID,51Testing软件测试网_%P.AV0E`)M0S9[9Ub
       SYS.V_$SESSION.SERIAL#,51Testing软件测试网 Tz,H%["e*^8kt.t
       DECODE(V$LOCK.TYPE,51Testing软件测试网%Fvj'N l
              'MR','MEDIA RECOVERY',
$of9`r4Q0              'RT','REDO THREAD',51Testing软件测试网^Y J)SE7?!B s
              'UN','USER NAME',51Testing软件测试网KYj2EQ5O2tU
              'TX','TRANSACTION',
+s.to)e PK(u#d/G0              'TM','DML',51Testing软件测试网$H/B5y1W|fZ4N
              'UL','PL/SQL USER LOCK',51Testing软件测试网} ~$Al|!@#A&\~F%U
              'DX','DISTRIBUTED XACTION',
#f#t0A}C;})]m0              'CF','CONTROL FILE',
]-KWHP oF0              'IS','INSTANCE STATE',51Testing软件测试网h!K#W2Y7l)S4f7J&I
              'FS','FILE SET',51Testing软件测试网N+@JIv
X8Z"V`
              'IR','INSTANCE RECOVERY',
&u r m^-s"K$qD*m#U0              'ST','DISK SPACE TRANSACTION',51Testing软件测试网 X2op~\Ig
              'TS','TEMP SEGMENT',51Testing软件测试网 \x(}!s$Zfc FW?F
              'IV','LIBRARY CACHE INVALIDA-TION',51Testing软件测试网K7Ig4pA
^h
              'LS','LOG START OR SWITCH',51Testing软件测试网SPJnLQ[
              'RW','ROW WAIT',
!f)b3x:wU&t
As3d*^0              'SQ','SEQUENCE NUMBER',
@+l2eZ7_*Y I0              'TE','EXTEND TABLE',
/zcWD7Z q9K0              'TT','TEMP TABLE',51Testing软件测试网K/h@#u D*FQe*x"{
              'UNKNOWN') LOCKTYPE,51Testing软件测试网 DpT4\*D x
       RTRIM(OBJECT_TYPE) || ' ' || RTRIM(OWNER) || '.' || OBJECT_NAME OBJECT_NAME,
Q7K+DHwE5YS5m0       DECODE(LMODE,51Testing软件测试网avZ6u/eX/E!W
              0,'NONE',51Testing软件测试网s{/|4b4tKH TX&W
              1,'NULL',51Testing软件测试网Y%zui7I
              2,'ROW-S',51Testing软件测试网K g3G!zs)d
              3,'ROW-X',51Testing软件测试网,WyNT.P,_w/X
p
              4,'SHARE',51Testing软件测试网,C2k3Dsv6M:UN7u
              5,'S/ROW-X',51Testing软件测试网ObL'?C5Ww WK.n
              6,'EXCLUSIVE',51Testing软件测试网(?'|^SJS*c
              'UNKNOWN') LOCKMODE,51Testing软件测试网,U DD*Y6A)Ew.dO
       DECODE(REQUEST,
6[_-M%T&q F8i+wv0              0,'NONE',
)K4bI;a&J,X1~3U{?0              1,'NULL',51Testing软件测试网9k wFXW4J5T
              2,'ROW-S',51Testing软件测试网 S)Lc6qh}l^
              3,'ROW-X',
I8O%U _kYA'm0              4,'SHARE',
$G$e2[ mE2Pc1k1A0              5,'S/ROW-X',51Testing软件测试网2s1T;mhM/^)U8U
              6,'EXCLUSIVE',51Testing软件测试网N~@ M
d@O
              'UNKNOWN') REQUESTMODE,51Testing软件测试网
F6f$DhL0FDI/v
       CTIME,
_`Ffx$^9V:v&O&x0       BLOCKB
F`n~ul1Q F0FROMV$LOCK, ALL_OBJECTS,SYS.V_$SESSION
1_]8M AL!K0WHEREV$LOCK.SID > 651Testing软件测试网2vG H/D \s6r8{
   ANDSYS.V_$SESSION.SID = V$LOCK.SID51Testing软件测试网1G[v5|f^X2T
   ANDV$LOCK.ID1 = ALL_OBJECTS.OBJECT_ID;
?x"R$}!SH8[ ]#p0
5\F!c(H
~![~&f'w!j%}r0以DBA角色, 查看当前数据库里锁的情况可以用如下SQL语句：
)IcF8ja-I6u!h'_0COL OWNERFORA12
mmD4^A_0COL OBJECT_NAMEFORA1651Testing软件测试网yq@+V(h9O
SELECTB.OWNER,B.OBJECT_NAME,L.SESSION_ID,L.LOCKED_MODE51Testing软件测试网*HTH,_8g
FROMV$LOCKED_OBJECT L, DBA_OBJECTS B
%dw P5H'pH0WHEREB.OBJECT_ID=L.OBJECT_ID;
.hMwRXo2y$}?051Testing软件测试网2Ij+~,l2t
SQL>SELECTT2.USERNAME,T2.SID,T2.SERIAL#,T2.LOGON_TIMEFROMV$LOCKED_OBJECT T1,V$SESSION T2WHERET1.SESSION_ID=T2.SIDORDERBYT2.LOGON_TIME;51Testing软件测试网G"W ]c2u/e

0n!Se,E9v
zR#O Y0SQL>SELECTSQL_ADDRESSFROMV$SESSIONWHERESID=;51Testing软件测试网,N8GJ"` h@

fY
W0Uw#`0SQL>SELECT*FROMV$SQLTEXTWHEREADDRESS=;

/V(c^mM%G0SQL>SELECTCOMMAND_TYPE,PIECE,SQL_TEXTFROMV$SQLTEXTWHEREADDRESS=(SELECTSQL_ADDRESSFROMV$SESSION AWHERESID=18);    

XIpY_p}051Testing软件测试网`O$xR(? z{ Fd4J
SQL>SELECTOBJECT_IDFROMV$LOCKED_OBJECT;51Testing软件测试网#tY+I^:}&J B

51Testing软件测试网-@)q[._3G {
SQL>SELECTOBJECT_NAME,OBJECT_TYPEFROMDBA_OBJECTSWHEREOBJECT_ID='';
p`3m#\&L|b0如果有长期出现的一列，可能是没有释放的锁。我们可以用下面SQL语句杀掉长期没有释放非正常的锁：SQL>ALTERSYSTEMKILLSESSION'SID,SERIAL#';51Testing软件测试网Pl:Hm5@!?%x9E{[
51Testing软件测试网#])q9M2V"b"sP'[u
17、查看等待（wait）情况:
:~)b_/Lc,Ge,Ca0SQL>SELECTV$WAITSTAT.CLASS,V$WAITSTAT.COUNTCOUNT,SUM(V$SYSSTAT.VALUE) SUM_VALUEFROMV$WAITSTAT,V$SYSSTATWHEREV$SYSSTAT.NAMEIN('DB BLOCK GETS','CONSISTENT GETS')GROUPBYV$WAITSTAT.CLASS,V$WAITSTAT.COUNT;51Testing软件测试网I;ow_x)v4Y
51Testing软件测试网)A!wM e5Z K un7^
18、查看sga情况:
h`*y@'SS6E9bX0SQL>SELECTNAME, BYTESFROMSYS.V_$SGASTATORDERBYNAMEASC;
%e8@
sQu0
Bp6[)Kg1B R019、查看catched object:51Testing软件测试网F(a9|$Q4b&[
SQL>SELECTOWNER,NAME,DB_LINK,NAMESPACE,TYPE,SHARABLE_MEM,LOADS, EXECUTIONS,LOCKS,PINS,KEPTFROMV$DB_OBJECT_CACHE;
fECGa&ND051Testing软件测试网s3|f:r1p
20、查看V$SQLAREA:
+f?g1P
b[A0SQL>SELECTSQL_TEXT,SHARABLE_MEM,PERSISTENT_MEM,RUNTIME_MEM,SORTS,
+~&RlM6D2j0VERSION_COUNT,LOADED_VERSIONS,OPEN_VERSIONS,USERS_OPENING,EXECUTIONS,
)\*y]-k|9JG0USERS_EXECUTING,LOADS,FIRST_LOAD_TIME,INVALIDATIONS,PARSE_CALLS,51Testing软件测试网_`1nfO#|1p.Z
DISK_READS,BUFFER_GETS,ROWS_PROCESSEDFROMV$SQLAREA;
$V'a!^4B1VB#jb^0
;bpmM/LH+`{Ab021、查看object分类数量:51Testing软件测试网9vD|"x_?8y
SELECTDECODE(O.TYPE#,1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6, 'SEQUENCE','OTHER') OBJECT_TYPE ,COUNT(*) QUANTITYFROMSYS.OBJ$ OWHEREO.TYPE# > 1GROUPBYDECODE(O.TYPE#,1,'INDEX',2,'TABLE',3,'CLUSTER' ,4,'VIEW',5,'SYNONYM',6,'SEQUENCE','OTHER')UNIONSELECT'COLUMN',COUNT(*)FROMSYS.COL$UNIONSELECT'DB LINK' ,COUNT(*)FROMALL_OBJECTS;
-uC o8X%bUv&pv~\ \f051Testing软件测试网LAa)}~Ney
22、有关connection的相关信息:51Testing软件测试网] l/o9_"@Di)H&z K
1）查看有哪些用户连接51Testing软件测试网;z6nF*tC
SELECTS.OSUSER OS_USER_NAME,DECODE(SIGN(48 - COMMAND),1,TO_CHAR(COMMAND),51Testing软件测试网,Dy_h7g/BO
'ACTION CODE #' || TO_CHAR(COMMAND))ACTION,P.PROGRAM ORACLE_PROCESS, STATUS SESSION_STATUS,S.TERMINAL TERMINAL,S.PROGRAM PROGRAM, S.USERNAME USER_NAME,S.FIXED_TABLE_SEQUENCE ACTIVITY_METER,''QUERY,0 MEMORY,0 MAX_MEMORY,0 CPU_USAGE,S.SID,S.SERIAL# SERIAL_NUMFROMV$SESSION S,V$PROCESS PWHERES.PADDR=P.ADDRANDS.TYPE= 'USER'ORDERBYS.USERNAME, S.OSUSER;
SiEE?2F3bB#B0
!tr"XBA+R#[2?02）根据v.sid查看对应连接的资源占用等情况51Testing软件测试网
^Y4vKT
SELECTN.NAME,V.VALUE,N.CLASS,N.STATISTIC#FROMV$STATNAME N,V$SESSTAT VWHEREV.SID=18ANDV.STATISTIC# = N.STATISTIC#ORDERBYN.CLASS, N.STATISTIC#;
&VI-ce0Uq051Testing软件测试网,nyG$_"i7y3V
3）根据sid查看对应连接正在运行的sql51Testing软件测试网O;UT#|5w LQ?3V0~
SELECT/*+ PUSH_SUBQ */COMMAND_TYPE,SQL_TEXT,SHARABLE_MEM, PERSISTENT_MEM,RUNTIME_MEM,SORTS,VERSION_COUNT,LOADED_VERSIONS,OPEN_VERSIONS,USERS_OPENING,EXECUTIONS, USERS_EXECUTING,LOADS,FIRST_LOAD_TIME,INVALIDATIONS,PARSE_CALLS,DISK_READS,BUFFER_GETS,ROWS_PROCESSED,SYSDATESTART_TIME,SYSDATEFINISH_TIME,'>'|| ADDRESS SQL_ADDRESS, 'N' STATUSFROMV$SQLAREAWHEREADDRESS = (SELECTSQL_ADDRESSFROMV$SESSIONWHERESID=8);
0p?)J
X;i'~mGv$y051Testing软件测试网jd%N n8b:dHNS2@
根据pid查看sql语句:

u5V}6Hh&m;q[U]0SELECTSQL_TEXTFROMV$SQLWHEREADDRESSIN(SELECTSQL_ADDRESSFROMV$SESSIONWHERESIDIN(SELECTSIDFROMV$SESSIONWHEREPADDRIN(SELECTADDRFROMV$PROCESSWHERESPID=&PID)));