查看表空间的名称及大小:SQL>SELECTT.TABLESPACE_NAME,ROUND(SUM(BYTES/(1024*1024)),0)TS_SIZEFROMDBA_TABLESPACEST,DBA_DATA_FILESDWHERET.TABLESPACE_NAME=D.TABLESPACE_NAMEGROUPBYT.TABLESPACE_NAME;查看表空间物理文件的名称及大小:SQL>SELECTTABLESPACE_NAME,FILE_ID,FILE_NAME,ROUND(BYTES/(1024*1024),0)TOTAL_SPACEFROMDBA_DATA_FILESORDERBYTABLESPACE_NAME;查看回滚段名称及大小:SQL>SELECTSEGMENT_NAME,TABLESPACE_NAME,R.STATUS,(INITIAL_EXTENT/1024)INITIALEXTENT,(NEXT_EXTENT/1024)NEXTEXTENT,MAX_EXTENTS,V.CUREXTCUREXTENTFROMDBA_ROLLBACK_SEGSR,V$ROLLSTATVWHERER.SEGMENT_ID=V.USN(+)ORDERBYSEGMENT_NAME;如何查看某个回滚段里面,跑的什么事物或者正在执行什么sql语句:SQL>SELECTD.SQL_TEXT,A.NAMEFROMV$ROLLNAMEA,V$TRANSACTIONB,V$SESSIONC,V$SQLTEXTDWHEREA.USN=B.XIDUSNANDB.ADDR=C.TADDRANDC.SQL_ADDRESS=D.ADDRESSANDC.SQL_HASH_VALUE=D.HASH_VALUEANDA.USN=1;(备注:你要看哪个,就把usn=?写成几就行了)查看控制文件:SQL>SELECT*FROMV$CONTROLFILE;查看日志文件:SQL>COLMEMBERFORMATA50SQL>SELECT*FROMV$LOGFILE;如何查看当前SQL*PLUS用户的sid和serial#:SQL>SELECTSID,SERIAL#,STATUSFROMV$SESSIONWHEREAUDSID=USERENV('SESSIONID');如何查看当前数据库的字符集:SQL>SELECTUSERENV('LANGUAGE')FROMDUAL;SQL>SELECTUSERENV('LANG')FROMDUAL;
怎么判断当前正在使用何种SQL优化方式:
用EXPLAINPLAN产生EXPLAINPLAN检查PLAN_TABLE中ID=0的POSITION列的值SQL>SELECTDECODE(NVL(POSITION,-1),-1,'RBO',1,'CBO')FROMPLAN_TABLEWHEREID=0;如何查看系统当前最新的SCN号:SQL>SELECTMAX(KTUXESCNW*POWER(2,32)+KTUXESCNB)FROMX$KTUXE;在ORACLE中查找TRACE文件的脚本:SQL>SELECTU_DUMP.VALUE||'/'||INSTANCE.VALUE||'_ORA_'||V$PROCESS.SPID||NVL2(V$PROCESS.TRACEID,'_'||V$PROCESS.TRACEID,NULL)||'.TRC'"TRACEFILE"FROMV$PARAMETERU_DUMPCROSSJOINV$PARAMETERINSTANCECROSSJOINV$PROCESSJOINV$SESSIONONV$PROCESS.ADDR=V$SESSION.PADDRWHEREU_DUMP.NAME='USER_DUMP_DEST'ANDINSTANCE.NAME='INSTANCE_NAME'ANDV$SESSION.AUDSID=SYS_CONTEXT('USERENV','SESSIONID');SQL>SELECTD.VALUE||'/ORA_'||P.SPID||'.TRC'TRACE_FILE_NAMEFROM(SELECTP.SPIDFROMSYS.V_$MYSTATM,SYS.V_$SESSIONS,SYS.V_$PROCESSPWHEREM.STATISTIC#=1ANDS.SID=M.SIDANDP.ADDR=S.PADDR)P,(SELECTVALUEFROMSYS.V_$PARAMETERWHERENAME='USER_DUMP_DEST')D;如何查看客户端登陆的IP地址:SQL>SELECTSYS_CONTEXT('USERENV','IP_ADDRESS')FROMDUAL;如何在生产数据库中创建一个追踪客户端IP地址的触发器:SQL>CREATEORREPLACETRIGGERON_LOGON_TRIGGERAFTERLOGONONDATABASEBEGINDBMS_APPLICATION_INFO.SET_CLIENT_INFO(SYS_CONTEXT('USERENV','IP_ADDRESS'));END;REM记录登陆信息的触发器CREATEORREPLACETRIGGERLOGON_HISTORYAFTERLOGONONDATABASE--WHEN(USER='WACOS')--ONLYFORUSER'WACOS'BEGININSERTINTOSESSION_HISTORYSELECTUSERNAME,SID,SERIAL#,AUDSID,OSUSER,ACTION,SYSDATE,NULL,SYS_CONTEXT('USERENV','IP_ADDRESS'),TERMINAL,MACHINE,PROGRAMFROMV$SESSIONWHEREAUDSID=USERENV('SESSIONID');END;查询当前日期:SQL>SELECTTO_CHAR(SYSDATE,'YYYY-MM-DD,HH24:MI:SS')FROMDUAL;查看所有表空间对应的数据文件名:SQL>SELECTDISTINCTFILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLEFROMDBA_DATA_FILES;查看表空间的使用情况:SQL>SELECTSUM(BYTES)/(1024*1024)ASFREE_SPACE,TABLESPACE_NAMEFROMDBA_FREE_SPACEGROUPBYTABLESPACE_NAME;SQL>SELECTA.TABLESPACE_NAME,A.BYTESTOTAL,B.BYTESUSED,C.BYTESFREE,
(B.BYTES*100)/A.BYTES"%USED",(C.BYTES*100)/A.BYTES"%FREE"FROMSYS.SM$TS_AVAILA,SYS.SM$TS_USEDB,SYS.SM$TS_FREECWHEREA.TABLESPACE_NAME=B.TABLESPACE_NAMEANDA.TABLESPACE_NAME=C.TABLESPACE_NAME;COLUMNTABLESPACE_NAMEFORMATA18;COLUMNSUM_MFORMATA12;COLUMNUSED_MFORMATA12;COLUMNFREE_MFORMATA12;COLUMNPTO_MFORMAT9.99;SELECTS.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))*100FREEPERCENTRATIOFROMSYS.DBA_FREE_SPACEA,SYS.DBA_DATA_FILESBWHEREA.FILE_ID(+)=B.FILE_IDGROUPBYB.FILE_ID,B.TABLESPACE_NAME,B.BYTESORDERBYB.TABLESPACE_NAME)SGROUPBYS.TABLESPACE_NAMEORDERBYSUM(S.FREESPACE)/SUM(S.BYTES)DESC;查看数据文件的hwm(可以resize的最小空间)和文件头大小:SELECTV1.FILE_NAME,V1.FILE_ID,NUM1TOTLE_SPACE,NUM3FREE_SPACE,NUM1-NUM3"USED_SPACE(HWM)",NVL(NUM2,0)DATA_SPACE,NUM1-NUM3-NVL(NUM2,0)FILE_HEADFROM(SELECTFILE_NAME,FILE_ID,SUM(BYTES)NUM1FROMDBA_DATA_FILESGROUPBYFILE_NAME,FILE_ID)V1,(SELECTFILE_ID,SUM(BYTES)NUM2FROMDBA_EXTENTSGROUPBYFILE_ID)V2,(SELECTFILE_ID,SUM(BYTES)NUM3FROMDBA_FREE_SPACEGROUPBYFILE_ID)V3WHEREV1.FILE_ID=V2.FILE_ID(+)ANDV1.FILE_ID=V3.FILE_ID(+);数据文件大小及头大小:SELECTV1.FILE_NAME,V1.FILE_ID,NUM1TOTLE_SPACE,NUM3FREE_SPACE,NUM1-NUM3USED_SPACE,NVL(NUM2,0)DATA_SPACE,NUM1-NUM3-NVL(NUM2,0)FILE_HEADFROM(SELECTFILE_NAME,FILE_ID,SUM(BYTES)NUM1FROMDBA_DATA_FILESGROUPBYFILE_NAME,FILE_ID)V1,(SELECTFILE_ID,SUM(BYTES)NUM2FROMDBA_EXTENTSGROUPBYFILE_ID)V2,(SELECTFILE_ID,SUM(BYTES)NUM3FROMDBA_FREE_SPACEGROUPBYFILE_ID)V3WHEREV1.FILE_ID=V2.FILE_ID(+)ANDV1.FILE_ID=V3.FILE_ID(+);(运行以上查询,我们可以如下信息:Totle_pace:该数据文件的总大小,字节为单位Free_space:该数据文件的剩于大小,字节为单位Used_space:该数据文件的已用空间,字节为单位Data_space:该数据文件中段数据占用空间,也就是数据空间,字节为单位File_Head:该数据文件头部占用空间,字节为单位)
数据库各个表空间增长情况的检查:SQL>SELECTA.TABLESPACE_NAME,(1-(A.TOTAL)/B.TOTAL)*100USED_PERCENTFROM(SELECTTABLESPACE_NAME,SUM(BYTES)TOTALFROMDBA_FREE_SPACEGROUPBYTABLESPACE_NAME)A,(SELECTTABLESPACE_NAME,SUM(BYTES)TOTALFROMDBA_DATA_FILESGROUPBYTABLESPACE_NAME)BWHEREA.TABLESPACE_NAME=B.TABLESPACE_NAME;SQL>SELECTUPPER(F.TABLESPACE_NAME)"表空间名",D.TOT_GROOTTE_MB"表空间大小(M)",D.TOT_GROOTTE_MB-F.TOTAL_BYTES"已使用空间(M)",TO_CHAR(ROUND((D.TOT_GROOTTE_MBF.TOTAL_BYTES)/D.TOT_GROOTTE_MB*100,2),'990.99')"使用比",F.TOTAL_BYTES"空闲空间(M)",F.MAX_BYTES"最大块(M)"FROM(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2)TOTAL_BYTES,ROUND(MAX(BYTES)/(1024*1024),2)MAX_BYTESFROMSYS.DBA_FREE_SPACEGROUPBYTABLESPACE_NAME)F,(SELECTDD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES)/(1024*1024),2)TOT_GROOTTE_MBFROMSYS.DBA_DATA_FILESDDGROUPBYDD.TABLESPACE_NAME)DWHERED.TABLESPACE_NAME=F.TABLESPACE_NAMEORDERBY4DESC;查看各个表空间占用磁盘情况:SQL>COLTABLESPACE_NAMEFORMATA20;SQL>SELECTB.FILE_IDFILE_ID,B.TABLESPACE_NAMETABLESPACE_NAME,B.BYTESBYTES,(B.BYTES-SUM(NVL(A.BYTES,0)))USED,SUM(NVL(A.BYTES,0))FREE,SUM(NVL(A.BYTES,0))/(B.BYTES)*100PERCENTFROMDBA_FREE_SPACEA,DBA_DATA_FILESBWHEREA.FILE_ID=B.FILE_IDGROUPBYB.TABLESPACE_NAME,B.FILE_ID,B.BYTESORDERBYB.FILE_ID;数据库对象下一扩展与表空间的free扩展值的检查:SQL>SELECTA.TABLE_NAME,A.NEXT_EXTENT,A.TABLESPACE_NAMEFROMALL_TABLESA,(SELECTTABLESPACE_NAME,MAX(BYTES)ASBIG_CHUNKFROMDBA_FREE_SPACEGROUPBYTABLESPACE_NAME)FWHEREF.TABLESPACE_NAMEA.TABLESPACE_NAMEANDA.NEXT_EXTENT>F.BIG_CHUNKUNIONSELECTA.INDEX_NAME,A.NEXT_EXTENT,A.TABLESPACE_NAMEFROMALL_INDEXESA,(SELECTTABLESPACE_NAME,MAX(BYTES)ASBIG_CHUNKFROMDBA_FREE_SPACEGROUPBYTABLESPACE_NAME)FWHEREF.TABLESPACE_NAMEA.TABLESPACE_NAMEANDA.NEXT_EXTENT>F.BIG_CHUNK;DiskRead最高的SQL语句的获取:SQL>SELECTSQL_TEXTFROM(SELECT*FROMV$SQLAREAORDERBYDISK_READS)WHEREROWNUM<=5;查找前十条性能差的sqlSELECT*FROM(SELECTPARSING_USER_ID
=
=
EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,SQL_TEXTFROMV$SQLAREAORDERBYDISK_READSDESC)WHEREROWNUM<10;等待时间最多的5个系统等待事件的获取:SQL>SELECT*FROM(SELECT*FROMV$SYSTEM_EVENTWHEREEVENTNOTLIKE'SQL%'ORDERBYTOTAL_WAITSDESC)WHEREROWNUM<=5;查看当前等待事件的会话:COLUSERNAMEFORMATA10SETLINE120COLEVENTFORMATA30SELECTSE.SID,S.USERNAME,SE.EVENT,SE.TOTAL_WAITS,SE.TIME_WAITED,SE.AVERAGE_WAITFROMV$SESSIONS,V$SESSION_EVENTSEWHERES.USERNAMEISNOTNULLANDSE.SID=S.SIDANDS.STATUS='ACTIVE'ANDSE.EVENTNOTLIKE'%SQL*NET%';SELECTSID,EVENT,P1,P2,P3,WAIT_TIME,SECONDS_IN_WAIT,STATEFROMV$SESSION_WAITWHEREEVENTNOTLIKE'%MESSAGE%'ANDEVENTNOTLIKE'SQL*NET%'ANDEVENTNOTLIKE'%TIMER%'ANDEVENT!='WAKEUPTIMEMANAGER';找到与所连接的会话有关的当前等待事件:SELECTSW.SID,S.USERNAME,SW.EVENT,SW.WAIT_TIME,SW.STATE,SW.SECONDS_IN_WAITSEC_IN_WAITFROMV$SESSIONS,V$SESSION_WAITSWWHERES.USERNAMEISNOTNULLANDSW.SID=S.SIDANDSW.EVENTNOTLIKE'%SQL*NET%'ORDERBYSW.WAIT_TIMEDESC;Oracle所有回滚段状态的检查:SQL>SELECTSEGMENT_NAME,OWNER,TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,DBA_ROLLBACK_SEGS.STATUSFROMDBA_ROLLBACK_SEGS,V$DATAFILEWHEREFILE_ID=FILE#;Oracle回滚段扩展信息的检查:COLNAMEFORMATA10SETLINESIZE140SELECTNAME,EXTENTS,RSSIZE,OPTSIZE,AVEACTIVE,EXTENDS,WRAPS,SHRINKS,HWMSIZEFROMV$ROLLNAMERN,V$ROLLSTATRSWHERE(RN.USN=RS.USN);
SUBSTR(NAME,1,40)
EXTENTS:回滚段中的盘区数量.Rssize:以字节为单位的回滚段的尺寸.optsize:为optimal参数设定的值.Aveactive:从回滚段中删除盘区时释放的以字节为单位的平均空间的大小.Extends:系统为回滚段增加的盘区的次数.Shrinks:系统从回滚段中清除盘区(即回滚段收缩)的次数.回滚段每次清除盘区时,系统可能会从这个回滚段中消除一个或多个盘区.Hwmsize:回滚段尺寸的上限,即回滚段曾经达到的最大尺寸.(如果回滚段平均尺寸接近OPTIMAL的值,那么说明OPTIMAL的值设置正确,如果回滚段动态增长次数或收缩次数很高,那么需要提高OPTIMAL的值)
查看回滚段的使用情况,哪个用户正在使用回滚段的资源:SELECTS.USERNAME,U.NAMEFROMV$TRANSACTIONT,V$ROLLSTATR,V$ROLLNAMEU,V$SESSIONSWHERES.TADDR=T.ADDRANDT.XIDUSN=R.USNANDR.USN=U.USNORDERBYS.USERNAME;如何查看一下某个shared_server正在忙什么:SELECTA.USERNAME,A.MACHINE,A.PROGRAM,A.SID,A.SERIAL#,A.STATUS,C.PIECE,C.SQL_TEXTFROMV$SESSIONA,V$PROCESSB,V$SQLTEXTCWHEREB.SPID=13161ANDB.ADDR=A.PADDRANDA.SQL_ADDRESS=C.ADDRESS(+)ORDERBYC.PIECE;数据库共享池性能检查:SELECTNAMESPACE,GETS,GETHITRATIO,PINS,PINHITRATIO,RELOADS,INVALIDATIONSV$LIBRARYCACHEWHERENAMESPACEIN('SQLAREA','TABLE/PROCEDURE','BODY','TRIGGER');检查数据重载比率:SELECTSUM(RELOADS)/SUM(PINS)*100"RELOADRATIO"FROMV$LIBRARYCACHE;检查数据字典的命中率:SELECT1-SUM(GETMISSES)/SUM(GETS)"DATADICTIONARYHITRATIO"FROMV$ROWCACHE;(对于librarycache,gethitratio和pinhitratio应该大于90%,对于数据重载比率,reloadratio应该小于1%,对于数据字典的命中率,datadictionaryhitratio应该大于85%)
FROM
检查共享内存的剩余情况:SELECTREQUEST_MISSES,REQUEST_FAILURESFROMV$SHARED_POOL_RESERVED;(对于共享内存的剩余情况,request_misses和request_failures应该接近0)
数据高速缓冲区性能检查:SELECT1-P.VALUE/(B.VALUE+C.VALUE)"DBBUFFERCACHEHITRATIO"FROMV$SYSSTATP,V$SYSSTATB,V$SYSSTATCWHEREP.NAME='PHYSICALREADS'ANDB.NAME='DBBLOCKGETS'ANDC.NAME='CONSISTENTGETS';检查bufferpoolHIT_RATIO执行SELECTNAME,(PHYSICAL_READS/(DB_BLOCK_GETS+CONSISTENT_GETS))"MISS_HIT_RATIO"V$BUFFER_POOL_STATISTICSWHERE(DB_BLOCK_GETS+CONSISTENT_GETS)>0;(正常时dbbuffercachehitratio应该大于90%,正常时bufferpoolMISS_HIT_RATIO应该小于10%)
FROM
数据库回滚段性能检查:检查Ratio执行
SELECTSUM(WAITS)*100/SUM(GETS)"RATIO",SUM(WAITS)"WAITS",SUM(GETS)"GETS"FROMV$ROLLSTAT;检查count/value执行:SELECTCLASS,COUNTFROMV$WAITSTATWHERECLASSLIKE'%UNDO%';SELECTVALUEFROMV$SYSSTATWHERENAME='CONSISTENTGETS';(两者的value值相除)检查average_wait执行:SELECTEVENT,TOTAL_WAITS,TIME_WAITED,AVERAGE_WAITFROMV$SYSTEM_EVENTWHEREEVENTLIKE'%UNDO%';检查RBSheadergetratio执行:SELECTN.NAME,S.USN,S.WRAPS,DECODE(S.WAITS,0,1,1-S.WAITS/S.GETS)"RBSHEADERGETRATIO"FROMV$ROLLSTATS,V$ROLLNAMENWHERES.USN=N.USN;(正常时Ratio应该小于1%,count/value应该小于0.01%,average_wait最好为0,该值越小越好,RBSheadergetratio应该大于95%)
杀会话的脚本:SELECTA.SID,B.SPID,A.SERIAL#,A.LOCKWAIT,A.USERNAME,A.OSUSER,A.LOGON_TIME,A.LAST_CALL_ET/3600LAST_HOUR,A.STATUS,'ORAKILL'||SID||''||SPIDHOST_COMMAND,'ALTERSYSTEMKILLSESSION'''||A.SID||','||A.SERIAL#||''''SQL_COMMANDFROMV$SESSIONA,V$PROCESSBWHEREA.PADDR=B.ADDRANDSID>6;查看排序段的性能:SQL>SELECTNAME,VALUEFROMV$SYSSTATWHERENAMEIN('SORTS(MEMORY)','SORTS(DISK)');7,查看数据库库对象:SELECTOWNER,OBJECT_TYPE,STATUS,COUNT(*)COUNT#FROMALL_OBJECTSGROUPBYOWNER,OBJECT_TYPE,STATUS;8,查看数据库的版本:SELECT*FROMV$VERSION;9,查看数据库的创建日期和归档方式:SELECTCREATED,LOG_MODE,LOG_MODEFROMV$DATABASE;
10,捕捉运行很久的SQL:COLUMNUSERNAMEFORMATA12COLUMNOPNAMEFORMATA16COLUMNPROGRESSFORMATA8SELECTUSERNAME,SID,OPNAME,ROUND(SOFAR*100/TOTALWORK,0)||'%'ASPROGRESS,TIME_REMAINING,SQL_TEXTFROMV$SESSION_LONGOPS,V$SQLWHERETIME_REMAINING<>0ANDSQL_ADDRESS=ADDRESSANDSQL_HASH_VALUE=HASH_VALUE;
11,查看数据表的参数信息:SELECTPARTITION_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_PARTITIONS--WHERETABLE_NAME=:TNAMEANDTABLE_OWNER=:TOWNERORDERBYPARTITION_POSITION;12,查看还没提交的事务:SELECT*FROMV$LOCKED_OBJECT;SELECT*FROMV$TRANSACTION;13,查找object为哪些进程所用:SELECTP.SPID,S.SID,S.SERIAL#SERIAL_NUM,S.USERNAMEUSER_NAME,A.TYPEOBJECT_TYPE,S.OSUSEROS_USER_NAME,A.OWNER,A.OBJECTOBJECT_NAME,DECODE(SIGN(48COMMAND),1,TO_CHAR(COMMAND),'ACTIONCODE#'||TO_CHAR(COMMAND))ACTION,P.PROGRAMORACLE_PROCESS,S.TERMINALTERMINAL,S.PROGRAMPROGRAM,S.STATUSSESSION_STATUSFROMV$SESSIONS,V$ACCESSA,V$PROCESSPWHERES.PADDR=P.ADDRANDS.TYPE='USER'ANDA.SID=S.SIDANDA.OBJECT='SUBSCRIBER_ATTR'ORDERBYS.USERNAME,S.OSUSER;14,查看回滚段:SQL>COLNAMEFORMATA10SQL>SETLINESIZE100SQL>SELECTROWNUM,SYS.DBA_ROLLBACK_SEGS.SEGMENT_NAMENAME,V$ROLLSTAT.EXTENTSEXTENTS,V$ROLLSTAT.RSSIZESIZE_IN_BYTES,V$ROLLSTAT.XACTSXACTS,V$ROLLSTAT.GETSGETS,V$ROLLSTAT.WAITSWAITS,V$ROLLSTAT.WRITESWRITES,SYS.DBA_ROLLBACK_SEGS.STATUSSTATUSFROMV$ROLLSTAT,SYS.DBA_ROLLBACK_SEGS,V$ROLLNAMEWHEREV$ROLLNAME.NAME(+)=SYS.DBA_ROLLBACK_SEGS.SEGMENT_NAMEANDV$ROLLSTAT.USN(+)=V$ROLLNAME.USNORDERBYROWNUM;15,耗资源的进程(topsession):SELECTS.SCHEMANAMESCHEMA_NAME,DECODE(SIGN(48-COMMAND),1,TO_CHAR(COMMAND),'ACTIONCODE#'||TO_CHAR(COMMAND))ACTION,STATUSSESSION_STATUS,S.OSUSEROS_USER_NAME,S.SID,P.SPID,S.SERIAL#SERIAL_NUM,NVL(S.USERNAME,'[ORACLEPROCESS]')USER_NAME,S.TERMINALTERMINAL,S.PROGRAMPROGRAM,ST.VALUECRITERIA_VALUEFROMV$SESSTATST,V$SESSIONS,V$PROCESSPWHEREST.SID=S.SIDANDST.STATISTIC#=TO_NUMBER('38')AND('ALL'='ALL'ORS.STATUS='ALL')ANDP.ADDR=S.PADDRORDERBYST.VALUEDESC,P.SPIDASC,S.USERNAMEASC,S.OSUSERASC;根据PID查找相应的语句:SELECTA.USERNAME,A.MACHINE,A.PROGRAM,A.SID,A.SERIAL#,A.STATUS,C.PIECE,C.SQL_TEXTFROMV$SESSIONA,V$PROCESSB,V$SQLTEXTCWHEREB.SPID=SPIDANDB.ADDR=A.PADDRANDA.SQL_ADDRESS=C.ADDRESS(+)ORDERBYC.PIECE;根据SID找ORACLE的某个进程:SQL>SELECTPRO.SPIDFROMSES.PADDR=PRO.ADDR;
V$SESSION
SES,V$PROCESS
PRO
WHERE
SES.SID=21
AND
监控当前数据库谁在运行什么SQL语句:SQL>SELECTOSUSER,USERNAME,SQL_TEXTFROMV$SESSIONA,V$SQLTEXTBWHEREA.SQL_ADDRESS=B.ADDRESSORDERBYADDRESS,PIECE;如何查看数据库中某用户,正在运行什么SQL语句SQL>SELECTSQL_TEXTFROMV$SQLTEXTT,V$SESSIONSWHERET.ADDRESS=S.SQL_ADDRESSANDT.HASH_VALUE=S.SQL_HASH_VALUEANDS.MACHINE='XXXXX'ORUSERNAME='WACOS';如何查出前台正在发出的sql语句:SQL>SELECTUSER_NAME,SQL_TEXTFROMV$OPEN_CURSORWHERESIDIN(SELECTSIDFROM(SELECTSID,SERIAL#FROMV$SESSIONWHERESTATUS='ACTIVE'));查询当前所执行的SQL语句:SQL>SELECTPROGRAM,SQL_ADDRESSFROMV$SESSIONWHEREPADDRIN(SELECTADDRFROMV$PROCESSWHERESPID=3556);PROGRAMSQL_ADDRESS---------------------------------------------------------------SQLPLUS@CTC20(TNSV1-V3)000000038FCB1A90SQL>SELECTSQL_TEXTFROMV$SQLAREAWHEREADDRESS='000000038FCB1A90';找出消耗CPU最高的进程对应的SQL语句:SETLINE240SETVERIFYOFFCOLUMNSIDFORMAT999COLUMNPIDFORMAT999COLUMNS_#FORMAT999COLUMNUSERNAMEFORMATA9HEADING"ORAUSER"COLUMNPROGRAMFORMATA29COLUMNSQLFORMATA60COLUMNOSNAMEFORMATA9HEADING"OSUSER"SELECTP.PIDPID,S.SIDSID,P.SPIDSPID,S.USERNAMEUSERNAME,S.OSUSEROSNAME,P.SERIAL#S_#,P.TERMINAL,P.PROGRAMPROGRAM,P.BACKGROUND,S.STATUS,RTRIM(SUBSTR(A.SQL_TEXT,1,80))SQLFROMV$PROCESSP,V$SESSIONS,V$SQLAREAAWHEREP.ADDR=S.PADDRANDS.SQL_ADDRESS=A.ADDRESS(+)ANDP.SPIDLIKE'%&;1%';ENTERVALUEFOR1:PID(这里输入占用CPU最高的进程对应的PID)SETTERMOUTOFFSPOOLMAXCPU.TXTSELECT'++'||S.USERNAMEUSERNAME,RTRIM(REPLACE(A.SQL_TEXT,CHR(10),''))||';'FROMV$PROCESSP,V$SESSIONS,V$SQLAREAAWHEREP.ADDR=S.PADDRANDS.SQL_ADDRESS=A.ADDRESS(+)ANDP.SPIDLIKE'%&;&;1%';Entervaluefor1:PID(这里输入占用CPU最高的进程对应的PID)spooloff(这句放在最后执行)
CPU用率最高的2条SQL语句的获取执行:top,通过top获得CPU占用率最高的进程的pid.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.ADDRAND
V$PROCESS.SPIDIN(PID);COLMACHINEFORMATA30COLPROGRAMFORMATA40SETLINE200SQL>SELECTSID,SERIAL#,USERNAME,OSUSER,MACHINE,PROGRAM,PROCESS,TO_CHAR(LOGON_TIME,'YYYY/MM/DDHH24:MI:SS')FROMV$SESSIONWHEREPADDRIN(SELECTADDRFROMV$PROCESSWHERESPIDIN([$SPID]));SELECTSQL_TEXTFROMV$SQLTEXT_WITH_NEWLINESWHEREHASH_VALUE=(SELECTSQL_HASH_VALUEFROMV$SESSIONWHERESID=&;SID)ORDERBYPIECE;16,查看锁(lock)情况:SQL>SELECT/*+RULE*/LS.OSUSEROS_USER_NAME,LS.USERNAMEUSER_NAME,DECODE(LS.TYPE,'RW','ROWWAITENQUEUELOCK','TM','DMLENQUEUELOCK','TX','TRANSACTIONENQUEUELOCK','UL','USERSUPPLIEDLOCK')LOCK_TYPE,O.OBJECT_NAMEOBJECT,DECODE(LS.LMODE,1,NULL,2,'ROWSHARE',3,'ROWEXCLUSIVE',4,'SHARE',5,'SHAREROWEXCLUSIVE',6,'EXCLUSIVE',NULL)LOCK_MODE,O.OWNER,LS.SID,LS.SERIAL#SERIAL_NUM,LS.ID1,LS.ID2FROMSYS.DBA_OBJECTSO,(SELECTS.OSUSER,S.USERNAME,L.TYPE,L.LMODE,S.SID,S.SERIAL#,L.ID1,L.ID2FROMV$SESSIONS,V$LOCKLWHERES.SID=L.SID)LSWHEREO.OBJECT_ID=LS.ID1ANDO.OWNER<>'SYS'ORDERBYO.OWNER,O.OBJECT_NAME;
SQL>SELECTSYS.V_$SESSION.OSUSER,SYS.V_$SESSION.MACHINE,V$LOCK.SID,SYS.V_$SESSION.SERIAL#,DECODE(V$LOCK.TYPE,'MR','MEDIARECOVERY','RT','REDOTHREAD','UN','USERNAME','TX','TRANSACTION','TM','DML','UL','PL/SQLUSERLOCK','DX','DISTRIBUTEDXACTION','CF','CONTROLFILE','IS','INSTANCESTATE','FS','FILESET','IR','INSTANCERECOVERY','ST','DISKSPACETRANSACTION','TS','TEMPSEGMENT','IV','LIBRARYCACHEINVALIDA-TION','LS','LOGSTARTORSWITCH','RW','ROWWAIT','SQ','SEQUENCENUMBER','TE','EXTENDTABLE','TT','TEMPTABLE','UNKNOWN')LOCKTYPE,RTRIM(OBJECT_TYPE)||''||RTRIM(OWNER)||'.'||OBJECT_NAMEOBJECT_NAME,DECODE(LMODE,0,'NONE',1,'NULL',2,'ROW-S',3,'ROW-X',4,'SHARE',5,'S/ROW-X',6,'EXCLUSIVE','UNKNOWN')LOCKMODE,DECODE(REQUEST,0,'NONE',1,'NULL',2,'ROW-S',3,'ROW-X',4,'SHARE',5,'S/ROW-X',6,'EXCLUSIVE','UNKNOWN')REQUESTMODE,CTIME,BLOCKBFROMV$LOCK,ALL_OBJECTS,SYS.V_$SESSIONWHEREV$LOCK.SID>6ANDSYS.V_$SESSION.SID=V$LOCK.SIDANDV$LOCK.ID1=ALL_OBJECTS.OBJECT_ID;
以DBA角色,查看当前数据库里锁的情况可以用如下SQL语句:COLOWNERFORA12COLOBJECT_NAMEFORA16SELECTB.OWNER,B.OBJECT_NAME,L.SESSION_ID,L.LOCKED_MODEFROMV$LOCKED_OBJECTL,DBA_OBJECTSBWHEREB.OBJECT_ID=L.OBJECT_ID;SQL>SELECTT2.USERNAME,T2.SID,T2.SERIAL#,T2.LOGON_TIMEFROMV$LOCKED_OBJECTT1,V$SESSIONT2WHERET1.SESSION_ID=T2.SIDORDERBYT2.LOGON_TIME;
SQL>SELECTSQL_ADDRESSFROMV$SESSIONWHERESID=;
SQL>SELECT*FROMV$SQLTEXTWHEREADDRESS=;
SQL>SELECTCOMMAND_TYPE,PIECE,SQL_TEXTSQL_ADDRESSFROMV$SESSIONAWHERESID=18);
FROM
V$SQLTEXT
WHERE
ADDRESS=(SELECT
SQL>SELECTOBJECT_IDFROMV$LOCKED_OBJECT;
SQL>SELECTOBJECT_NAME,OBJECT_TYPEFROMDBA_OBJECTSWHEREOBJECT_ID='';如果有长期出现的一列,可能是没有释放的锁.我们可以用下面SQL语句杀掉长期没有释放非正常的锁:SQL>ALTERSYSTEMKILLSESSION'SID,SERIAL#';17,查看等待(wait)情况:SQL>SELECTV$WAITSTAT.CLASS,V$WAITSTAT.COUNTCOUNT,SUM(V$SYSSTAT.VALUE)SUM_VALUEFROMV$WAITSTAT,V$SYSSTATWHEREV$SYSSTAT.NAMEIN('DBBLOCKGETS','CONSISTENTGETS')GROUPBYV$WAITSTAT.CLASS,V$WAITSTAT.COUNT;18,查看sga情况:SQL>SELECTNAME,BYTESFROMSYS.V_$SGASTATORDERBYNAMEASC;19,查看catchedobject:SQL>SELECTOWNER,NAME,DB_LINK,NAMESPACE,TYPE,SHARABLE_MEM,LOADS,EXECUTIONS,LOCKS,PINS,KEPTFROMV$DB_OBJECT_CACHE;20,查看V$SQLAREA:SQL>SELECTSQL_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_PROCESSEDFROMV$SQLAREA;21,查看object分类数量:SELECTDECODE(O.TYPE#,1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE','OTHER')OBJECT_TYPE,COUNT(*)QUANTITYFROMSYS.OBJ$OWHEREO.TYPE#>1GROUPBY
DECODE(O.TYPE#,1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE','OTHER')UNIONSELECT'COLUMN',COUNT(*)FROMSYS.COL$UNIONSELECT'DBLINK',COUNT(*)FROMALL_OBJECTS;22,有关connection的相关信息:1)查看有哪些用户连接SELECTS.OSUSEROS_USER_NAME,DECODE(SIGN(48-COMMAND),1,TO_CHAR(COMMAND),'ACTIONCODE#'||TO_CHAR(COMMAND))ACTION,P.PROGRAMORACLE_PROCESS,STATUSSESSION_STATUS,S.TERMINALTERMINAL,S.PROGRAMPROGRAM,S.USERNAMEUSER_NAME,S.FIXED_TABLE_SEQUENCEACTIVITY_METER,''QUERY,0MEMORY,0MAX_MEMORY,0CPU_USAGE,S.SID,S.SERIAL#SERIAL_NUMFROMV$SESSIONS,V$PROCESSPWHERES.PADDR=P.ADDRANDS.TYPE='USER'ORDERBYS.USERNAME,S.OSUSER;2)根据v.sid查看对应连接的资源占用等情况SELECTN.NAME,V.VALUE,N.CLASS,N.STATISTIC#FROMV$STATNAMEN,V$SESSTATVWHEREV.SID=18ANDV.STATISTIC#=N.STATISTIC#ORDERBYN.CLASS,N.STATISTIC#;3)根据sid查看对应连接正在运行的sqlSELECT/*+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,'>'||ADDRESSSQL_ADDRESS,'N'STATUSFROMV$SQLAREAWHEREADDRESS=(SELECTSQL_ADDRESSFROMV$SESSIONWHERESID=8);根据pid查看sql语句:SELECTSQL_TEXTFROMV$SQLWHEREADDRESSIN(SELECTSQL_ADDRESSFROMV$SESSIONWHERESIDIN(SELECTSIDFROMV$SESSIONWHEREPADDRIN(SELECTADDRFROMV$PROCESSWHERESPID=&;PID)));
23,查询表空间使用情况:SELECTA.TABLESPACE_NAME"空间名称",100-ROUND((NVL(B.BYTES_FREE,0)/A.BYTES_ALLOC)*100,2)"占用率(%)",ROUND(A.BYTES_ALLOC/1024/1024,2)"容量(M)",ROUND(NVL(B.BYTES_FREE,0)/1024/1024,2)空闲(M)",ROUND((A.BYTES_ALLOC-NVL(B.BYTES_FREE,0))/1024/1024,2)"使用(M)",LARGEST"最大扩展段(M)",TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS')"采样时间"FROM(SELECTF.TABLESPACE_NAME,SUM(F.BYTES)BYTES_ALLOC,SUM(DECODE(F.AUTOEXTENSIBLE,'YES',F.MAXBYTES,'NO',F.BYTES))MAXBYTESFROMDBA_DATA_FILESFGROUPBYTABLESPACE_NAME)A,(SELECTF.TABLESPACE_NAME,SUM(F.BYTES)BYTES_FREEFROMDBA_FREE_SPACEFGROUPBYTABLESPACE_NAME)B,(SELECTROUND(MAX(FF.LENGTH)*16/1024,2)LARGEST,TS.NAMETABLESPACE_NAMEFROMSYS.FET$FF,SYS.FILE$TF,SYS.TS$TSWHERETS.TS#=FF.TS#ANDFF.FILE#=TF.RELFILE#ANDTS.TS#=TF.TS#GROUPBYTS.NAME,TF.BLOCKS)CWHEREA.TABLESPACE_NAME=B.TABLESPACE_NAMEANDA.TABLESPACE_NAME=C.TABLESPACE_NAME;SELECTUPPER(F.TABLESPACE_NAME)"表空间名",D.TOT_GROOTTE_MB"表空间大小(M)",D.TOT_GROOTTE_MB-F.TOTAL_BYTES"已使用空间(M)",TO_CHAR(ROUND((D.TOT_GROOTTE_MB-F.TOTAL_BYTES)/D.TOT_GROOTTE_MB*100,2),'990.99')"
使用比",F.TOTAL_BYTES"空闲空间(M)",F.MAX_BYTES"最大块(M)"FROM(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2)TOTAL_BYTES,ROUND(MAX(BYTES)/(1024*1024),2)MAX_BYTESFROMSYS.DBA_FREE_SPACEGROUPBYTABLESPACE_NAME)F,(SELECTDD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES)/(1024*1024),2)TOT_GROOTTE_MBFROMSYS.DBA_DATA_FILESDDGROUPBYDD.TABLESPACE_NAME)DWHERED.TABLESPACE_NAME=F.TABLESPACE_NAMEORDERBY4DESC;24,查询表空间的碎片程度:SQL>SELECTTABLESPACE_NAME,COUNT(TABLESPACE_NAME)FROMDBA_FREE_SPACEGROUPBYTABLESPACE_NAMEHAVINGCOUNT(TABLESPACE_NAME)>10;SQL>ALTERTABLESPACENAMECOALESCE;SQL>ALTERTABLETABLE_NAMEDEALLOCATEUNUSED;SQL>CREATEORREPLACEVIEWTS_BLOCKS_VASSELECTTABLESPACE_NAME,BLOCK_ID,BYTES,BLOCKS,'FREESPACE'SEGMENT_NAMEFROMDBA_FREE_SPACEUNIONALLSELECTTABLESPACE_NAME,BLOCK_ID,BYTES,BLOCKS,SEGMENT_NAMEFROMDBA_EXTENTS;SQL>SELECT*FROMTS_BLOCKS_V;SQL>SELECTTABLESPACE_NAME,SUM(BYTES),MAX(BYTES),COUNT(BLOCK_ID)FROMDBA_FREE_SPACEGROUPBYTABLESPACE_NAME;SQL>SELECT'ALTERTABLESPACE'||TABLESPACE_NAME||'COALESCE;'FROMDBA_FREE_SPACE_COALESCEDWHEREPERCENT_EXTENTS_COALESCED<100ORPERCENT_BLOCKS_COALESCED<100;由于自由空间碎片是由几部分组成,如范围数量,最大范围尺寸等,我们可用fsfi---FREESPACEFRAGMENTATIONINDEX(自由空间碎片索引)值来直观体现:FSFI=100*SQRT(MAX(EXTENT)/SUM(EXTENTS))*1/SQRT(SQRT(COUNT(EXTENTS)))REMFSFIVALUECOMPUTEREMFSFI.SQLCOLUMNFSFIFORMAT999,99SELECTTABLESPACE_NAME,SQRT(MAX(BLOCKS)/SUM(BLOCKS))*(100/SQRT(SQRT(COUNT(BLOCKS))))FSFIFROMDBA_FREE_SPACEGROUPBYTABLESPACE_NAMEORDERBY1;SPOOLFSFI.REP;/SPOOLOFF;可以看出,fsfi的最大可能值为100(一个理想的单文件表空间).随着范围的增加,fsfi值缓慢下降,而随着最大范围尺寸的减少,fsfi值会迅速下降.比如,在某数据库运行脚本fsfi.sql,得到以下fsfi值:TABLESPACE_NAMEFSFI------------------------------------RBS74.06SYSTEM100.00TEMP22.82TOOLS75.79USERS100.00USER_TOOLS100.00YDCX_DATA47.34YDCX_IDX57.19
YDJF_DATA33.80YDJF_IDX75.55----统计出了数据库的fsfi值,就可以把它作为一个可比参数.在一个有着足够有效自由空间,且fsfi值超过30的表空间中,很少会遇见有效自由空间的问题.当一个空间将要接近可比参数时,就需要做碎片整理了.
25,查询有哪些数据库实例在运行:selectinst_namefromv$active_instances;
26,以DBA角色,查看当前数据库里锁的情况:SELECTOBJECT_ID,SESSION_ID,LOCKED_MODEFROMV$LOCKED_OBJECT;SELECTT2.USERNAME,T2.SID,T2.SERIAL#,T2.LOGON_TIMEFROMV$LOCKED_OBJECTT1,V$SESSIONT2WHERET1.SESSION_ID=T2.SIDORDERBYT2.LOGON_TIME;27,查看表是否是分区表:SELECTTABLE_NAME,PARTITIONEDFROMUSER_TABLESWHERETABLE_NAME='LOCALUSAGE';TABLE_NAMEPAR----------------------------------------LOCALUSAGEYES28,查看分区表的分区名和相应的表空间名:SELECTTABLE_NAME,PARTITION_NAME,TABLESPACE_NAMEFROMUSER_TAB_PARTITIONSWHERETABLE_NAMELIKE'%USAGE%';29,查看索引是否是分区索引:SELECTINDEX_NAME,TABLE_NAME,STATUS,PARTITIONEDFROMUSER_INDEXESWHERETABLE_NAMELIKE'%USAGE';30,如果返回的PATITIONED为YES,请再执行如下语句来查询分区索引的类型:SELECTINDEX_NAME,TABLE_NAME,LOCALITYFROMUSER_PART_INDEXES;31,Dual是Oracle中的一个实际存在的表,任何用户均可读取,常用在没有目标表的Select中.查看系统时间:SELECTTO_CHAR(SYSDATE,'YY-MM-DDHH24:MI:SS')SHIJIANFROMDUAL;32,查看索引段中extent的数量:SELECTSEGMENT_NAME,COUNT(*)FROMDBA_EXTENTSWHERESEGMENT_TYPE='INDEX'ANDOWNER='SCOTT'GROUPBYSEGMENT_NAME;33,查看系统表中的用户索引(用来检查在system表空间内其他用户索引的存在):SQL>SELECTCOUNT(*)FROMDBA_INDEXESWHERETABLESPACE_NAME='SYSTEM'ANDOWNERNOTIN('SYS','SYSTEM');
34,查看wacos表空间内的索引的扩展情况:SELECTSUBSTR(SEGMENT_NAME,1,20)"SEGMENTNAME",BYTES,COUNT(BYTES)FROMDBA_EXTENTSWHERESEGMENT_NAMEIN(SELECTINDEX_NAMEFROMDBA_INDEXESWHERETABLESPACE_NAME='WACOS')GROUPBYSEGMENT_NAME,BYTESORDERBYSEGMENT_NAME;35,查看表空间数据文件的读写性能:SQL>SELECTNAME,PHYRDS,PHYWRTS,AVGIOTIM,MINIOTIM,MAXIOWTM,MAXIORTMV$FILESTAT,V$DATAFILEWHEREV$FILESTAT.FILE#=V$DATAFILE.FILE#;
FROM
SQL>SELECTFS.NAMENAME,F.PHYRDS,F.PHYBLKRD,F.PHYWRTS,F.PHYBLKWRT,F.READTIM,F.WRITETIMFROMV$FILESTATF,V$DATAFILEFSWHEREF.FILE#=FS.FILE#ORDERBYFS.NAME;(注意:如果phyblkrd与phyrds很接近的话,则表明这个表空间中存在全表扫描的表,这些表需要调整索引或优化SQL语句)36,转换表空间为local方式管理:SQL>EXECSYS.DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TBS_TEST');37,查看一下哪个用户在用临时段:SELECTUSERNAME,SID,SERIAL#,SQL_ADDRESS,MACHINE,PROGRAM,TABLESPACE,SEGTYPE,CONTENTSFROMV$SESSIONSE,V$SORT_USAGESUWHERESE.SADDR=SU.SESSION_ADDR;38,查看占io较大的正在运行的session:SELECTSE.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_CHANGESFROMV$SESSIONSE,V$SESSION_WAITST,V$SESS_IOSI,V$PROCESSPRWHEREST.SID=SE.SIDANDST.SID=SI.SIDANDSE.PADDR=PR.ADDRANDSE.SID>6ANDST.WAIT_TIME=0ANDST.EVENTNOTLIKE'%SQL%'ORDERBYPHYSICAL_READSDESC;
39,查找前十条性能差的sql:SELECT*FROM(SELECTPARSING_USER_IDEXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,SQL_TEXTFROMV$SQLAREAORDERBYDISK_READSDESC)WHEREROWNUM<10;40,删除用户下所有表的语句:SELECT'DROPTABLE'||TABLE_NAME||'CASCADECONSTRAINTS;'FROMUSER_TABLES;41,查看LOCK,并杀掉会话:SETLINESIZE132PAGESIZE66BREAKONKILLONUSERNAMEONTERMINALCOLUMNKILLHEADING'KILLSTRING'FORMATA13COLUMNRESHEADING'RESOURCETYPE'FORMAT999COLUMNID1FORMAT9999990COLUMNID2FORMAT9999990COLUMNLMODEBEADING'LOCKHELD'FORMATA20COLUMNREQUESTHEADING'LOCKREQUESTED'FORMATA20COLUMNSERIAL#FORMAT99999COLUMNUSERNAMEFORMATA10HEADING"USERNAME"COLUMNTERMINALHEADINGTERMFORMATA6
COLUMNTABFORMATA35HEADING"TABLENAME"COLUMNOWNERFORMATA9COLUMNADDRESSFORMATA18SELECTNVL(S.USERNAME,'INTERNAL')USERNAME,NVL(S.TERMINAL,'NONE')TERMINAL,L.SID||','||S.SERIAL#KILL,U1.NAME||','||SUBSTR(T1.NAME,1,20)TAB,DECODE(L.LMODE,1,'NOLOCK',2,'ROWSHARE',3,'ROWEXCLUSIVE',4,'SHARE',5,'SHAREROWEXCLUSIVE',6,'EXCLUSIVE',NULL)LMODE,DECODE(L.REQUEST,1,'NOLOCK',2,'ROWSHARE',3,'ROWEXCLUSIVE',4,'SHARE',5,'SHAREROWEXCLUSIVE',6,'EXCLUSIVE',NULL)REQUESTFROMV$LOCKL,V$SESSIONS,SYS.USER$U1,SYS.OBJ$T1WHEREL.SID=S.SIDANDT1.OBJ#=DECODE(L.ID2,0,L.ID1,L.ID2)ANDU1.USER#=T1.OWNER#ANDS.TYPE!='BACKGROUND'ORDERBY1,2,5;--ALTERSYSTEMKILLSESSION',';COLUMNUSERNAMEFORMATA15COLUMNSIDFORMAT9990HEADINGSIDCOLUMNTYPEFORMATA4COLUMNLMODEFORMAT990HEADING'HELD'COLUMNREQUESTFORMAT990HEADING'REQ'COLUMNID1FORMAT9999990COLUMNID2FORMAT9999990BREAKONID1SKIP1DUPSPOOLTFSLCKWT.LSTSELECTSN.USERNAME,M.SID,M.TYPE,DECODE(M.LMODE,0,'NONE',1,'NULL',2,'ROWSHARE',3,'ROWEXCL.',4,'SHARE',5,'S/ROWEXCL.',
6,'EXCLUSIVE',LMODE,LTRIM(TO_CHAR(LMODE,'990')))LMODE,DECODE(M.REQUEST,0,'NONE',1,'NULL',2,'ROWSHARE',3,'ROWEXCL.',4,'SHARE',5,'S/ROWEXCL.',6,'EXCLUSIVE',REQUEST,LTRIM(TO_CHAR(M.REQUEST,'990')))REQUEST,M.ID1,M.ID2FROMV$SESSIONSN,V$LOCKMWHERE(SN.SID=M.SIDANDM.REQUEST!=0)OR(SN.SID=M.SIDANDM.REQUEST=0ANDLMODE!=4AND(ID1,ID2)IN(SELECTS.ID1,S.ID2FROMV$LOCKSWHEREREQUEST!=0ANDS.ID1=M.ID1ANDS.ID2=M.ID2))ORDERBYID1,ID2,M.REQUEST;SPOOLOFFCLEARBREAKS
