1. 현재 세션 조회(SYS)

SELECT  SID
,       SERIAL#
,       USERNAME
,       STATUS AS "STATUS OF SESSION"
,       SERVER
,       OSUSER
,       MACHINE
,       PROGRAM
,       PROCESS
,       TYPE
,       SQL_ID
,       SQL_CHILD_NUMBER 
FROM   V$SESSION
WHERE USERNAME IS NOT NULL;

2. 현재 사용자의 최근 쿼리 조회(SYS)

SELECT  SQL_FULLTEXT
,       SQL_ID
,       CHILD_NUMBER
,       OPTIMIZER_MODE
,       OPTIMIZER_COST
,       PARSING_SCHEMA_NAME
,       MODULE
,       CPU_TIME
,       PROGRAM_ID
,       TO_CHAR(LAST_ACTIVE_TIME, 'YYYY-MM-DD HH24:MI:SS') AS LAST_ACTIVE_TIME
FROM V$SQL
WHERE PARSING_SCHEMA_NAME = (SELECT USERNAME FROM USER_USERS)
ORDER BY LAST_ACTIVE_TIME DESC;


SELECT  TO_CHAR(LAST_ACTIVE_TIME, 'YYYY-MM-DD HH24:MI:SS') AS LAST_ACTIVE_TIME
,       SQL_FULLTEXT
,       SQL_ID
,       CHILD_NUMBER
,       HASH_VALUE
,       OLD_HASH_VALUE
,       PLAN_HASH_VALUE
,       SERVICE_HASH
,       LITERAL_HASH_VALUE
,       OPTIMIZER_MODE
,       OPTIMIZER_COST
,       PARSING_SCHEMA_NAME
,       MODULE
,       CPU_TIME
,       PROGRAM_ID
FROM V$SQL
WHERE HASH_VALUE IN (SELECT PREV_HASH_VALUE 
                  FROM V$SESSION 
                  WHERE USERNAME = (SELECT USERNAME 
                                    FROM USER_USERS) 
                  AND MACHINE = 'GSIS-DEV')
AND LAST_ACTIVE_TIME >= TRUNC(SYSDATE)
ORDER BY LAST_ACTIVE_TIME DESC;


SELECT  TO_CHAR(LAST_ACTIVE_TIME, 'YYYY-MM-DD HH24:MI:SS') AS LAST_ACTIVE_TIME
,       SQL_FULLTEXT
,       SQL_ID
,       CHILD_NUMBER
,       HASH_VALUE
,       OLD_HASH_VALUE
,       PLAN_HASH_VALUE
,       SERVICE_HASH
,       LITERAL_HASH_VALUE
,       OPTIMIZER_MODE
,       OPTIMIZER_COST
,       PARSING_SCHEMA_NAME
,       MODULE
,       CPU_TIME
,       PROGRAM_ID
FROM V$SQL
WHERE (SQL_ID, CHILD_NUMBER) IN (SELECT PREV_SQL_ID, PREV_CHILD_NUMBER
                                FROM V$SESSION 
                                WHERE USERNAME =    (SELECT USERNAME 
                                                    FROM USER_USERS) 
                                AND MACHINE = 'GSIS-DEV')
AND LAST_ACTIVE_TIME >= TRUNC(SYSDATE)                    
ORDER BY LAST_ACTIVE_TIME DESC;

3. 특정세션에서 최근 쿼리 조회(9버전) (예제는 iis 프로세서로 접근한 최근쿼리)

SELECT  LAST_LOAD_TIME
,       PARSING_SCHEMA_ID
,       PARSING_USER_ID
,       (SELECT USERNAME FROM DBA_USERS WHERE USER_ID = PARSING_USER_ID) AS USER_NAME
,       SQL_TEXT
FROM V$SQL 
WHERE HASH_VALUE IN (SELECT PREV_HASH_VALUE FROM V$SESSION WHERE PROGRAM = 'w3wp.exe') 
ORDER BY LAST_LOAD_TIME DESC;


SELECT   *
FROM V$SQLTEXT
WHERE HASH_VALUE IN (SELECT PREV_HASH_VALUE FROM V$SESSION WHERE PROGRAM = 'w3wp.exe')
order by address, hash_value, piece asc;

4. 모든 사용자 정보 조회(SYS)

SELECT USERNAME
,      USER_ID
,      ACCOUNT_STATUS
,      DEFAULT_TABLESPACE
,      TEMPORARY_TABLESPACE
,      CREATED
FROM DBA_USERS;

5. 사용자 정보 조회

SELECT * FROM USER_USERS;

6. 사용자에게 부여된 롤 조회

SELECT * FROM USER_ROLE_PRIVS;

7. 사용자에게 부여된 모든 권한 조회

SELECT * FROM USER_TAB_PRIVS;

SELECT  P.*
,       S.*
FROM V$PROCESS P
LEFT OUTER JOIN V$SESSION S ON S.PADDR = P.ADDR;