常用SQL語句2

 根據SID找ORACLE的某個程序:

SQL> SELECT PRO.SPID FROM V$SESSION SES,V$PROCESS PRO WHERE SES.SID=21 AND SES.PADDR=PRO.ADDR;

監控目前資料庫誰在運作什麼SQL語句:

SQL>SELECT OSUSER, USERNAME, SQL_TEXT FROM V$SESSION A, V$SQLTEXT B

WHERE A.SQL_ADDRESS =B.ADDRESS ORDER BY ADDRESS, PIECE;

如何檢視資料庫中某使用者，正在運作什麼SQL語句

SQL>SELECT SQL_TEXT FROM V$SQLTEXT T, V$SESSION S WHERE T.ADDRESS=S.SQL_ADDRESS AND T.HASH_VALUE=S.SQL_HASH_VALUE AND S.MACHINE='XXXXX' OR USERNAME='WACOS';

如何查出前台正在發出的sql語句:

SQL> SELECT USER_NAME,SQL_TEXT FROM V$OPEN_CURSOR WHERE SID IN(SELECT SID FROM (SELECT SID,SERIAL# FROM V$SESSION WHERE STATUS='ACTIVE'));

查詢目前所執行的SQL語句：

SQL> SELECT PROGRAM ,SQL_ADDRESS FROM V$SESSION WHERE PADDR IN (SELECT ADDR FROM V$PROCESS WHERE SPID=3556);

PROGRAM                                          SQL_ADDRESS

------------------------------------------------ ----------------

SQLPLUS@CTC20 (TNS V1-V3)                        000000038FCB1A90

SQL> SELECT SQL_TEXT FROM V$SQLAREA WHERE ADDRESS='000000038FCB1A90';

找出消耗CPU最高的程序對應的SQL語句：

SET LINE 240

SET VERIFY OFF

COLUMN SID FORMAT 999

COLUMN PID FORMAT 999

COLUMN S_# FORMAT 999

COLUMN USERNAME FORMAT A9 HEADING "ORA USER"

COLUMN PROGRAM FORMAT A29

COLUMN SQL      FORMAT A60

COLUMN OSNAME FORMAT A9 HEADING "OS USER"

SELECT P.PID PID,S.SID SID,P.SPID SPID,S.USERNAME USERNAME,S.OSUSER OSNAME,P.SERIAL# S_#,P.TERMINAL,P.PROGRAM PROGRAM,P.BACKGROUND,S.STATUS,RTRIM(SUBSTR(A.SQL_TEXT, 1, 80)) SQLFROM V$PROCESS P, V$SESSION S,V$SQLAREA A WHERE P.ADDR = S.PADDR AND S.SQL_ADDRESS = A.ADDRESS (+) AND P.SPID LIKE '%&1%';

ENTER VALUE FOR 1: PID¡（這裡輸入占用CPU最高的程序對應的PID）

SET TERMOUT OFF

SPOOL MAXCPU.TXT

SELECT '++'||S.USERNAME USERNAME,RTRIM(REPLACE(A.SQL_TEXT,CHR(10),''))||';'FROM V$PROCESS P, V$SESSION S,V$SQLAREA A WHERE P.ADDR = S.PADDR AND S.SQL_ADDRESS = A.ADDRESS (+) AND P.SPID LIKE '%&&1%';

 Enter value for 1: PID（這裡輸入占用CPU最高的程序對應的PID）

spool off(這句放在最後執行)

CPU用率最高的2條SQL語句的擷取

執行：top，通過top獲得CPU占用率最高的程序的pid。

SQL>SELECT SQL_TEXT,SPID,V$SESSION.PROGRAM,PROCESS FROM V$SQLAREA,V$SESSION,V$PROCESS WHERE V$SQLAREA.ADDRESS=V$SESSION.SQL_ADDRESS AND V$SQLAREA.HASH_VALUE=V$SESSION.SQL_HASH_VALUE AND V$SESSION.PADDR=V$PROCESS.ADDR AND V$PROCESS.SPID IN (PID);

COL MACHINE FORMAT A30

COL PROGRAM FORMAT A40

SET LINE 200

SQL>SELECT SID,SERIAL# ,USERNAME,OSUSER,MACHINE,PROGRAM,PROCESS,TO_CHAR(LOGON_TIME,'YYYY/MM/DD HH24:MI:SS') FROM V$SESSION WHERE PADDR IN(SELECT ADDR FROM V$PROCESS WHERE SPID IN([$SPID]));

SELECT SQL_TEXT FROM V$SQLTEXT_WITH_NEWLINES

WHERE HASH_VALUE=(SELECT SQL_HASH_VALUE FROM V$SESSION WHERE SID=&SID)

ORDER BY PIECE;

16、檢視鎖（lock）情況:

SQL>SELECT /*+ RULE */

 LS.OSUSER OS_USER_NAME,

 LS.USERNAME USER_NAME,

 DECODE(LS.TYPE,

        'RW','ROW WAIT ENQUEUE LOCK',

        'TM','DML ENQUEUE LOCK',

        'TX','TRANSACTION ENQUEUE LOCK',

        'UL','USER SUPPLIED LOCK') LOCK_TYPE,

 O.OBJECT_NAME OBJECT,

 DECODE(LS.LMODE,

        1,NULL,

        2,'ROW SHARE',

        3,'ROW EXCLUSIVE',

        4,'SHARE',

        5,'SHARE ROW EXCLUSIVE',

        6,'EXCLUSIVE',

        NULL) LOCK_MODE,

 O.OWNER,

 LS.SID,

 LS.SERIAL# SERIAL_NUM,

 LS.ID1,

 LS.ID2

 FROM SYS.DBA_OBJECTS O,

       (SELECT S.OSUSER,

               S.USERNAME,

               L.TYPE,

               L.LMODE,

               S.SID,

               S.SERIAL#,

               L.ID1,

               L.ID2

          FROM V$SESSION S, V$LOCK L

         WHERE S.SID = L.SID) LS

 WHERE O.OBJECT_ID = LS.ID1

   AND O.OWNER <> 'SYS'

 ORDER BY O.OWNER, O.OBJECT_NAME;

SQL>SELECT SYS.V_$SESSION.OSUSER,

       SYS.V_$SESSION.MACHINE,

       V$LOCK.SID,

       SYS.V_$SESSION.SERIAL#,

       DECODE(V$LOCK.TYPE,

              'MR','MEDIA RECOVERY',

              'RT','REDO THREAD',

              'UN','USER NAME',

              'TX','TRANSACTION',

              'TM','DML',

              'UL','PL/SQL USER LOCK',

              'DX','DISTRIBUTED XACTION',

              'CF','CONTROL FILE',

              'IS','INSTANCE STATE',

              'FS','FILE SET',

              'IR','INSTANCE RECOVERY',

              'ST','DISK SPACE TRANSACTION',

              'TS','TEMP SEGMENT',

              'IV','LIBRARY CACHE INVALIDA-TION',

              'LS','LOG START OR SWITCH',

              'RW','ROW WAIT',

              'SQ','SEQUENCE NUMBER',

              'TE','EXTEND TABLE',

              'TT','TEMP TABLE',

              'UNKNOWN') LOCKTYPE,

       RTRIM(OBJECT_TYPE) || ' ' || RTRIM(OWNER) || '.' || OBJECT_NAME OBJECT_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,

              'UNKNOWN') REQUESTMODE,

       CTIME,

       BLOCK B

 FROM V$LOCK, ALL_OBJECTS, SYS.V_$SESSION

 WHERE V$LOCK.SID > 6

   AND SYS.V_$SESSION.SID = V$LOCK.SID

   AND V$LOCK.ID1 = ALL_OBJECTS.OBJECT_ID;

以DBA角色, 檢視目前資料庫裡鎖的情況可以用如下SQL語句：

COL OWNER FOR A12

COL OBJECT_NAME FOR A16

SELECT B.OWNER,B.OBJECT_NAME,L.SESSION_ID,L.LOCKED_MODE

FROM V$LOCKED_OBJECT L, DBA_OBJECTS B

WHERE B.OBJECT_ID=L.OBJECT_ID;

SQL>SELECT T2.USERNAME,T2.SID,T2.SERIAL#,T2.LOGON_TIME FROM V$LOCKED_OBJECT T1,V$SESSION T2 WHERE T1.SESSION_ID=T2.SID ORDER BY T2.LOGON_TIME;

SQL>SELECT SQL_ADDRESS FROM V$SESSION WHERE SID=;

SQL>SELECT * FROM V$SQLTEXT WHERE ADDRESS=;

SQL>SELECT COMMAND_TYPE,PIECE,SQL_TEXT FROM V$SQLTEXT WHERE ADDRESS=(SELECT SQL_ADDRESS FROM V$SESSION A WHERE SID=18);   

SQL>SELECT OBJECT_ID FROM V$LOCKED_OBJECT;

SQL>SELECT OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS WHERE OBJECT_ID='';

如果有長期出現的一列，可能是沒有釋放的鎖。我們可以用下面SQL語句殺掉長期沒有釋放非正常的鎖： SQL>ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

17、檢視等待（wait）情況:

SQL>SELECT V$WAITSTAT.CLASS,V$WAITSTAT.COUNT COUNT, SUM(V$SYSSTAT.VALUE) SUM_VALUE FROM V$WAITSTAT,V$SYSSTAT WHERE V$SYSSTAT.NAME IN('DB BLOCK GETS','CONSISTENT GETS') GROUP BY V$WAITSTAT.CLASS,V$WAITSTAT.COUNT;

18、檢視sga情況:

SQL>SELECT NAME, BYTES FROM SYS.V_$SGASTAT ORDER BY NAME ASC;

19、檢視catched object:

SQL>SELECT OWNER,NAME,DB_LINK,NAMESPACE,TYPE,SHARABLE_MEM,LOADS, EXECUTIONS,LOCKS,PINS,KEPT FROM V$DB_OBJECT_CACHE;

20、檢視V$SQLAREA:

SQL>SELECT 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 FROM V$SQLAREA;

21、檢視object分類數量:

SELECT DECODE(O.TYPE#,1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6, 'SEQUENCE','OTHER') OBJECT_TYPE , COUNT(*) QUANTITY FROM SYS.OBJ$ O WHERE O.TYPE# > 1 GROUP BY DECODE(O.TYPE#,1,'INDEX',2,'TABLE',3,'CLUSTER' ,4,'VIEW',5,'SYNONYM',6,'SEQUENCE','OTHER') UNION SELECT 'COLUMN', COUNT(*) FROM SYS.COL$ UNION SELECT 'DB LINK' , COUNT(*) FROM ALL_OBJECTS;

22、有關connection的相關資訊:

1）檢視有哪些使用者連接配接

SELECT S.OSUSER OS_USER_NAME,DECODE(SIGN(48 - COMMAND),1,TO_CHAR(COMMAND),

 '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_NUM FROM V$SESSION S,V$PROCESS P WHERE S.PADDR=P.ADDR AND S.TYPE = 'USER' ORDER BY S.USERNAME, S.OSUSER;

2）根據v.sid檢視對應連接配接的資源占用等情況

SELECT N.NAME,V.VALUE,N.CLASS,N.STATISTIC# FROM V$STATNAME N,V$SESSTAT V WHERE V.SID=18 AND V.STATISTIC# = N.STATISTIC# ORDER BY N.CLASS, N.STATISTIC#;

3）根據sid檢視對應連接配接正在運作的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,SYSDATE START_TIME,SYSDATE FINISH_TIME,'>'|| ADDRESS SQL_ADDRESS, 'N' STATUS FROM V$SQLAREA WHERE ADDRESS = (SELECT SQL_ADDRESS FROM V$SESSION WHERE SID=8);

根據pid檢視sql語句:

SELECT SQL_TEXT FROM V$SQL WHERE ADDRESS IN (SELECT SQL_ADDRESS FROM V$SESSION WHERE SID IN (SELECT SID FROM V$SESSION WHERE PADDR IN (SELECT ADDR FROM V$PROCESS WHERE SPID=&PID)));