실행계획을 볼때 insufficient privileges 에러관련 해결방법

원인 : 해당 테이블에 접근할 수 없는 권한이 없을 때
해결 : 적합한 권한 제공

select a.TABLE_NAME, a.CONSTRAINT_NAME, b.COLUMN_NAME, a.CONSTRAINT_TYPE, a.R_CONSTRAINT_NAME
from user_constraints a, user_cons_columns b
where a.constraint_name = b.constraint_name;

...생략....

Execution Plan
----------------------------------------------------------
ERROR:
ORA-01039: insufficient privileges on underlying objects of the view


SP2-0612: Error generating AUTOTRACE EXPLAIN report

.... 생략....

해당 유저가 SELECT ANY DICTIONARY 권한을 가지고 있지 않아서 실행계획을 보지 못함.
GRANT SELECT ANY DICTIONARY TO "KOO"; 한 후 다시 실행

GRANT SELECT ANY DICTIONARY TO "KOO";

SQL> l
  1  select a.TABLE_NAME, a.CONSTRAINT_NAME, b.COLUMN_NAME, a.CONSTRAINT_TYPE, a.R_CONSTRAINT_NAME
  2  from user_constraints a, user_cons_columns b
  3* where a.constraint_name = b.constraint_name
SQL>/

TABLE_NAME      CONSTRAINT_NAME                COLUMN_NAME     C R_CONSTRAINT_NAME
--------------- ------------------------------ --------------- - ------------------------------
DEPARTMENTS     DEPARTMENTS_LOCATION_ID_FK     LOCATION_ID     R LOCATIONS_LOCATION_ID_PK
....생략....

Execution Plan
----------------------------------------------------------
Plan hash value: 4097523627

--------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name       | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |   346 | 80618 |   555   (2)| 00:00:07 |
|   1 |  NESTED LOOPS                       |            |   346 | 80618 |   555   (2)| 00:00:07 |
.... 생략 ....
|  34 |    TABLE ACCESS CLUSTER             | USER$      |     1 |     3 |     1   (0)| 00:00:01 |
|* 35 |     INDEX UNIQUE SCAN               | I_USER#    |     1 |       |     0   (0)| 00:00:01 |
|* 36 |   INDEX UNIQUE SCAN                 | I_OBJ1     |     1 |     5 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("C"."ENABLED"="OI"."OBJ#"(+))
   5 - access("CC"."OBJ#"="COL"."OBJ#" AND "CC"."INTCOL#"="COL"."INTCOL#")
   7 - access("CD"."CON#"="CC"."CON#")
   
.... 생략 ....

  35 - access("OI"."OWNER#"="UI"."USER#"(+))
  36 - access("CC"."OBJ#"="O"."OBJ#")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2480  consistent gets
          0  physical reads
          0  redo size
       1014  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

** 꼭 위와 똑같은 상황이 아니라도,
  실행계획검색 중 ORA-01039: insufficient privileges 가 난다면,
  권한 문제이므로, 관련된 오브젝트들의 권한을 검사해보세요