[ORACLE] 세션 및 SQL 실행 정보 조회

세션을 조회하는 쿼리는 다음과 같다.

SELECT * FROM V$SESSION;

SQL 실행 정보를 조회하는 쿼리는 다음과 같다.

SELECT * FROM V$SQLAREA;

system 계정에서는 별도의 설정 없이도 조회가 가능하지만,
일반 사용자 계정으로는 조회가 불가하다.
일반 사용자 계정으로 상기 쿼리들을 조회했을 때 다음과 같은 에러를 확인할 수 있다.

ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"

그렇다면 세션 관련 권한을 일반 사용자 계정에 부여해야 한다.
V_$SESSION 권한을 부여하는 쿼리는 다음과 같다.

GRANT SELECT ON V_$SESSION TO 사용자계정명;

V_$SQLAREA 권한을 부여하는 쿼리는 다음과 같다.

GRANT SELECT ON V_$SQLAREA TO 사용자계정명;

system 계정으로 권한을 부여할 수 없고 sys 계정을 사용해야 한다.
system 계정으로 상기 쿼리를 입력했을 때 다음과 같은 에러를 확인할 수 있다.

ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"

이제 다음과 같은 과정이 필요하다.
1. SYS 계정으로 일반 사용자 계정에 V_$SESSION 권한 부여
2. 일반 사용자 계정에서 세션 조회

1. CMD에 접속해서 sqlplus를 실행시킨다.

C:\Users\Admin>sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on 토 9월 24 00:25:12 2022

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

2. sys계정으로 접속한다.

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

3. V$SESSION과 V$SQLAREA 권한을 부여한다.

방법1.
V_$SESSION 조회 권한 부여

SQL> GRANT SELECT ON V_$SESSION TO 사용자계정명;

Grant succeeded.

V_$SQLAREA 조회 권한 부여

SQL> GRANT SELECT ON V_$SQLAREA TO 사용자계정명;

Grant succeeded.

방법2. 한번에 모든 딕셔너리에 대한 조회 권한 부여

SQL> GRANT SELECT ANY DICTIONARY TO TESTER;

Grant succeeded.

4. 일반 사용자 계정에서 세션 및 SQL 실행 정보 조회 쿼리를 실행해 확인한다.

세션 정보 조회

SELECT * FROM V$SESSION;

SQL 실행 정보 조회

SELECT * FROM V$SQLAREA;

세션과 쿼리 함께 조회

SELECT
    A.USERNAME AS 계정명,
    A.SCHEMANAME AS 스키마명,
    A.STATUS AS 활성화여부,
    A.SID AS 세션식별자,
    A.MACHINE AS OS머신명,
    A.PROGRAM AS OS프로그램명,
    A.TYPE AS 세션타입,
    A.LOGON_TIME AS 세션로그인날짜,
    B.FIRST_LOAD_TIME AS 처음실행한시각,
    B.SQL_TEXT  AS 실행쿼리
FROM
    V$SESSION A, V$SQLAREA B
WHERE
    (A.SQL_HASH_VALUE = B.HASH_VALUE  --SQL_HASH_VALUE : 가장 최근 실행된 SQL의 HASH값
    OR A.PREV_HASH_VALUE = B.HASH_VALUE) --PREV_HASH_VALUE : 이전에 실행된 SQL의 HASH값
    AND
    A.SQL_ADDRESS = B.ADDRESS
    --AND A.USERNAME = '계정명' -- 특정 계정의 세션만 조회
    --AND A.STATUS = 'ACTIVE' --활성화된 세션만 조회
;