SQL语句大全—查看数据（三）

根据 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)                         000000038 FCB1A90
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,
               0 , 'NONE' ,
               1 , 'NULL' ,
               2 , 'ROW-S' ,
               3 , 'ROW-X' ,
               4 , 'SHARE' ,
               5 , 'S/ROW-X' ,
               6 , 'EXCLUSIVE' ,
                '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)));