다물8

SQL> conn hr/hr

Connected.

SQL> col column_name for a20

SQL> select index_name, column_name, column_position from user_ind_columns

where table_name = 'EMPLOYEES' order by 1;

INDEX_NAME COLUMN_NAME COLUMN_POSITION

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

EMP_DEPARTMENT_IX DEPARTMENT_ID 1

EMP_EMAIL_UK EMAIL 1

EMP_EMP_ID_PK EMPLOYEE_ID 1

EMP_EMP_ID_SAL_IX SALARY 2

EMP_EMP_ID_SAL_IX EMPLOYEE_ID 1

EMP_JOB_IX JOB_ID 1

EMP_MANAGER_IX MANAGER_ID 1

EMP_NAME_IX LAST_NAME 1

EMP_NAME_IX FIRST_NAME 2

9 rows selected.

인덱스 찾기 : ALL_IND_COLUMNS

SQL> select * from ALL_ind_columns where table_name = 'EMPLOYEES' ORDER BY INDEX_NAME;

SQL> alter index hr.EMP_NAME_IX monitoring usage;

Index altered.

SQL> select index_name, start_monitoring, end_monitoring, used from v$object_usage;

INDEX_NAME START_MONITORING END_MONITORING USE

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

EMP_NAME_IX 01/02/2013 14:48:47 NO

Select * from employees where last_name = 'Whalen';

Select * from employees where first_name = 'Susan';

SQL> alter index EMP_NAME_IX nomonitoring usage;

Index altered.

① 인덱스 사용에 대한 정보를 조회

SQL> select index_name, start_monitoring, end_monitoring, used from v$object_usage;

INDEX_NAME START_MONITORING END_MONITORING USE

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

EMP_NAME_IX 01/02/2013 14:48:47 01/02/2013 20:31:52 YES

② Sys에서 조회

SQL> conn /as sysdba

Connected.

SQL> select index_name, start_monitoring, end_monitoring, used from v$object_usage;

no rows selected

è 데이터가 조회되지 않는다.

SQL> set long 30000

SQL> select text from dba_views where view_name = upper('v$object_usage');

TEXT

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

select io.name, t.name,

decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),

decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),

ou.start_monitoring,

ou.end_monitoring

from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou

where io.owner# = userenv('SCHEMAID')

and i.obj# = ou.obj#

and io.obj# = ou.obj#

and t.obj# = i.bo#

è 조회되지 않음. 이유는 v$object_usage는 userenv('SCHEMAID')로 조회하는데 index의 owner와 userenv('SCHEMAID')는 다르기 때문

è 해결하기 위해 새로운 view 생성

SQL> create view V$ALL_OBJECT_USAGE

(owner, INDEX_NAME, TABLE_NAME, MONITORING, USED, START_MONITORING, END_MONITORING)

as select u.name, io.name, t.name,

decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),

decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),

ou.start_monitoring,

ou.end_monitoring

from sys.user$ u, sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou

where i.obj# = ou.obj#

and io.obj# = ou.obj#

and t.obj# = i.bo#

and io.owner# = u.user#;

View created.

SQL> select * from v$all_object_usage;

OWNER INDEX_NAME TABLE_NAME MONUSE START_MONITORING END_MONITORING

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

HR EMP_NAME_IX EMPLOYEES NO YES 01/02/2013 14:48:47 01/02/2013 20:31:52

③ hr에서 조회

SQL> conn hr/hr

Connected.

SQL> select * from v$all_object_usage;

select * from v$all_object_usage

*

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> conn /as sysdba

Connected.

SQL> grant select on sys.v$all_object_usage to public;

Grant succeeded.

SQL> create public synonym v$all_object_usage for sys.v$all_object_usage;

Synonym created.

SQL> conn hr/hr

Connected.

SQL> select * from v$all_object_usage;

OWNER INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING

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

HR EMP_NAME_IX EMPLOYEES NO YES 01/02/2013 14:48:47 01/02/2013 20:31:52

è hr에서도 v$all_object_usage로 조회 가능