oracle中的经典SQL.docx

1、oracle中的经典SQL1、查看表空间的名称及大小SELECTT.TABLESPACE_NAME,ROUND(SUM(BYTES/(1024*1024),0)TS_SIZE FROMDBA_TABLESPACEST,DBA_DATA_FILESD WHERET.TABLESPACE_NAME=D.TABLESPACE_NAME GROUPBYT.TABLESPACE_NAME; 2、查看表空间物理文件的名称及大小SELECTTABLESPACE_NAME,FILE_ID,FILE_NAME, ROUND(BYTES/(1024*1024),0)TOTAL_SPACE FROMDBA_DATA

2、_FILES ORDERBYTABLESPACE_NAME; 3、查看回滚段名称及大小SELECTSEGMENT_NAME,TABLESPACE_NAME,R.STATUS, (INITIAL_EXTENT/1024)INITIALEXTENT,(NEXT_EXTENT/1024)NEXTEXTENT, MAX_EXTENTS,V.CUREXTCUREXTENT FROMDBA_ROLLBACK_SEGSR,V$ROLLSTATV WHERER.SEGMENT_ID=V.USN(+) ORDERBYSEGMENT_NAME; 4、查看控制文件SELECTNAMEFROMV$CONTROLFIL

3、E; 5、查看日志文件SELECTMEMBERFROMV$LOGFILE; 6、查看表空间的使用情况SELECTSUM(BYTES)/(1024*1024)ASFREE_SPACE,TABLESPACE_NAME FROMDBA_FREE_SPACE GROUPBYTABLESPACE_NAME; 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_USE

4、DB,SYS.SM$TS_FREEC WHEREA.TABLESPACE_NAME=B.TABLESPACE_NAMEANDA.TABLESPACE_NAME=C.TABLESPACE_NAME; 7、查看数据库库对象SELECTOWNER,OBJECT_TYPE,STATUS,COUNT(*)COUNT#FROMALL_OBJECTSGROUPBYOWNER,OBJECT_TYPE,STATUS; 8、查看数据库的版本SELECTVERSIONFROMPRODUCT_COMPONENT_VERSION WHERESUBSTR(PRODUCT,1,6)=ORACLE; 9、查看数据库的创建日期

5、和归档方式SELECTCREATED,LOG_MODE,LOG_MODEFROMV$DATABASE; 10、捕捉运行很久的SQL COLUMNUSERNAMEFORMATA12 COLUMNOPNAMEFORMATA16 COLUMNPROGRESSFORMATA8 SELECTUSERNAME,SID,OPNAME, ROUND(SOFAR*100/TOTALWORK,0)|%ASPROGRESS, TIME_REMAINING,SQL_TEXT FROMV$SESSION_LONGOPS,V$SQL WHERETIME_REMAINING0 ANDSQL_ADDRESS=ADDRESS

6、ANDSQL_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,C

7、HAIN_CNT,AVG_ROW_LEN,SAMPLE_SIZE, LAST_ANALYZED FROMDBA_TAB_PARTITIONS -WHERETABLE_NAME=:TNAMEANDTABLE_OWNER=:TOWNER ORDERBYPARTITION_POSITION 12.查看还没提交的事务SELECT*FROMV$LOCKED_OBJECT; SELECT*FROMV$TRANSACTION; 13。查找OBJECT为哪些进程所用SELECT P.SPID, S.SID, S.SERIAL#SERIAL_NUM, S.USERNAMEUSER_NAME, A.TYPEOBJ

8、ECT_TYPE, S.OSUSEROS_USER_NAME, A.OWNER, A.OBJECTOBJECT_NAME, DECODE(SIGN(48-COMMAND), 1, TO_CHAR(COMMAND),ACTIONCODE#|TO_CHAR(COMMAND)ACTION, P.PROGRAMORACLE_PROCESS, S.TERMINALTERMINAL, S.PROGRAMPROGRAM, S.STATUSSESSION_STATUS FROMV$SESSIONS,V$ACCESSA,V$PROCESSP WHERES.PADDR=P.ADDRAND S.TYPE=USERA

9、ND A.SID=S.SIDAND A.OBJECT=SUBSCRIBER_ATTR ORDERBYS.USERNAME,S.OSUSER 14。回滚段查看SELECTROWNUM,SYS.DBA_ROLLBACK_SEGS.SEGMENT_NAMENAME,V$ROLLSTAT.EXTENTS EXTENTS,V$ROLLSTAT.RSSIZESIZE_IN_BYTES,V$ROLLSTAT.XACTSXACTS, V$ROLLSTAT.GETSGETS,V$ROLLSTAT.WAITSWAITS,V$ROLLSTAT.WRITESWRITES, SYS.DBA_ROLLBACK_SEGS.

10、STATUSSTATUSFROMV$ROLLSTAT,SYS.DBA_ROLLBACK_SEGS, V$ROLLNAMEWHEREV$ROLLNAME.NAME(+)=SYS.DBA_ROLLBACK_SEGS.SEGMENT_NAMEAND V$ROLLSTAT.USN(+)=V$ROLLNAME.USNORDERBYROWNUM 15。耗资源的进程（TOPSESSION）SELECTS.SCHEMANAMESCHEMA_NAME, DECODE(SIGN(48-COMMAND),1, TO_CHAR(COMMAND),ACTIONCODE#|TO_CHAR(COMMAND)ACTION,

11、STATUS SESSION_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$PROCESSP WHEREST.SID=S.SIDAND ST.STATISTIC#=TO_NUMBER(38)AND (ALL=ALL ORS.STATUS=ALL)ANDP.

12、ADDR=S.PADDRORDERBYST.VALUEDESC,P.SPIDASC,S.USERNAMEASC,S.OSUSERASC 16。查看锁（LOCK）情况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

13、,ROWSHARE,3, ROWEXCLUSIVE,4,SHARE,5,SHAREROWEXCLUSIVE,6,EXCLUSIVE,NULL) LOCK_MODE, O.OWNER, LS.SID, LS.SERIAL#SERIAL_NUM, LS.ID1, LS.ID2 FROMSYS.DBA_OBJECTSO,( SELECTS.OSUSER, S.USERNAME, L.TYPE, L.LMODE, S.SID, S.SERIAL#, L.ID1, L.ID2 FROMV$SESSIONS, V$LOCKL WHERES.SID=L.SID)LSWHEREO.OBJECT_ID=LS.I

14、D1AND O.OWNER SYSORDERBYO.OWNER,O.OBJECT_NAME 17。查看等待（WAIT）情况SELECTV$WAITSTAT.CLASS,V$WAITSTAT.COUNTCOUNT,SUM(V$SYSSTAT.VALUE)SUM_VALUE FROMV$WAITSTAT,V$SYSSTATWHEREV$SYSSTAT.NAMEIN(DBBLOCKGETS, CONSISTENTGETS)GROUPBYV$WAITSTAT.CLASS,V$WAITSTAT.COUNT 18。查看SGA情况SELECTNAME,BYTESFROMSYS.V_$SGASTATORDER

15、BYNAMEASC 19。查看CATCHEDOBJECT SELECTOWNER,NAME,DB_LINK,NAMESPACE, TYPE,SHARABLE_MEM,LOADS,EXECUTIONS, LOCKS,PINS,KEPTFROMV$DB_OBJECT_CACHE 20。查看V$SQLAREASELECTSQL_TEXT,SHARABLE_MEM,PERSISTENT_MEM,RUNTIME_MEM,SORTS, VERSION_COUNT,LOADED_VERSIONS,OPEN_VERSIONS,USERS_OPENING,EXECUTIONS, USERS_EXECUTING,

16、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(*)QUANTITYFROM SYS.OBJ$OWHEREO.TYPE#1GROUPBYDECODE(O.TYPE#,1,INDEX,2,TABLE,3 ,CLUSTER

17、,4,VIEW,5,SYNONYM,6,SEQUENCE,OTHER)UNIONSELECT COLUMN,COUNT(*)FROMSYS.COL$UNIONSELECTDBLINK,COUNT(*)FROM 22。按用户查看OBJECT种类SELECTU.NAMESCHEMA, SUM(DECODE(O.TYPE#,1,1,NULL)INDEXES, SUM(DECODE(O.TYPE#,2,1,NULL)TABLES, SUM(DECODE(O.TYPE#,3,1,NULL) CLUSTERS, SUM(DECODE(O.TYPE#,4,1,NULL)VIEWS, SUM(DECODE(O

18、.TYPE#,5,1, NULL)SYNONYMS, SUM(DECODE(O.TYPE#,6,1,NULL)SEQUENCES, SUM(DECODE(O.TYPE#,1,NULL,2,NULL,3,NULL,4,NULL,5,NULL,6,NULL,1) OTHERSFROMSYS.OBJ$O,SYS.USER$UWHEREO.TYPE#=1AND U.USER#= O.OWNER#AND U.NAMEPUBLICGROUPBYU.NAMEORDERBY SYS.LINK$UNIONSELECTCONSTRAINT,COUNT(*)FROMSYS.CON$ 23。有关CONNECTION的

19、相关信息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

20、_USAGE, S.SID, S.SERIAL#SERIAL_NUM FROMV$SESSIONS, V$PROCESSPWHERES.PADDR=P.ADDRAND S.TYPE=USER ORDERBYS.USERNAME,S.OSUSER 2）根据V.SID查看对应连接的资源占用等情况 SELECTN.NAME, V.VALUE, N.CLASS, N.STATISTIC# FROMV$STATNAMEN, V$SESSTATV WHEREV.SID=71AND V.STATISTIC#=N.STATISTIC# ORDERBYN.CLASS,N.STATISTIC# 3）根据SID查看

21、对应连接正在运行的SQL 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, SYSDATES

22、TART_TIME, SYSDATEFINISH_TIME, |ADDRESSSQL_ADDRESS, NSTATUS FROMV$SQLAREA WHEREADDRESS=(SELECTSQL_ADDRESSFROMV$SESSIONWHERESID=71) 24查询表空间使用情况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

23、/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)MAXBYTES FROMDBA_DATA_FILESF GROUPBYTABLESPACE_NAME)A, (SE

24、LECTF.TABLESPACE_NAME, SUM(F.BYTES)BYTES_FREE FROMDBA_FREE_SPACEF GROUPBYTABLESPACE_NAME)B, (SELECTROUND(MAX(FF.LENGTH)*16/1024,2)LARGEST, TS.NAMETABLESPACE_NAME FROMSYS.FET$FF,SYS.FILE$TF,SYS.TS$TS WHERETS.TS#=FF.TS#ANDFF.FILE#=TF.RELFILE#ANDTS.TS#=TF.TS# GROUPBYTS.NAME,TF.BLOCKS)C WHEREA.TABLESPAC

25、E_NAME=B.TABLESPACE_NAMEANDA.TABLESPACE_NAME=C.TABLESPACE_NAME 25.查询表空间的碎片程度 SELECTTABLESPACE_NAME,COUNT(TABLESPACE_NAME)FROMDBA_FREE_SPACEGROUPBYTABLESPACE_NAME HAVINGCOUNT(TABLESPACE_NAME)10; ALTERTABLESPACENAMECOALESCE; ALTERTABLENAMEDEALLOCATEUNUSED; CREATEORREPLACEVIEWTS_BLOCKS_VAS SELECTTABLES

26、PACE_NAME,BLOCK_ID,BYTES,BLOCKS,FREESPACESEGMENT_NAMEFROMDBA_FREE_SPACE UNIONALL SELECTTABLESPACE_NAME,BLOCK_ID,BYTES,BLOCKS,SEGMENT_NAMEFROMDBA_EXTENTS; SELECT*FROMTS_BLOCKS_V; SELECTTABLESPACE_NAME,SUM(BYTES),MAX(BYTES),COUNT(BLOCK_ID)FROMDBA_FREE_SPACE GROUPBYTABLESPACE_NAME; 26.查看有哪些实例在运行： SELEC

27、T*FROMV$ACTIVE_INSTANCES; :EM02:EM02:EM03:EM03:1、 表空间统计A、 脚本说明：这是我最常用的一个脚本，用它可以显示出数据库中所有表空间的状态，如表空间的大小、已使用空间、使用的百分比、空闲空间数及现在表空间的最大块是多大。B、脚本原文:SELECT upper(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_