要先执行监控命令alter index *** monitoring usage;
,如果使用了索引 就会再select * from v$object_usage
出现,试验
SQL> select * from v$object_usage
;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
------------------------------ ------------------------------ ---------- ---- ------------------- -------------------
SQL> select * from user_tables;
TABLE_NAME TABLESPACE_NAME CLUSTER_NAME IOT_NAME STATUS PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS LOGGING BACKED_UP NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS DEGREE INSTANCES CACHE TABLE_LOCK SAMPLE_SIZE LAST_ANALYZED PARTITIONED IOT_TYPE TEMPORARY SECONDARY NESTED BUFFER_POOL ROW_MOVEMENT GLOBAL_STATS USER_STATS DURATION SKIP_CORRUPT MONITORING CLUSTER_OWNER DEPENDENCIES COMPRESSION DROPPED
------------------------------ ------------------------------ ------------------------------ ------------------------------ -------- ---------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- ------- --------- ---------- ---------- ------------ ---------- ---------- ----------- ------------------------- ------------------- ------------------------------ ------------------------------ --------------- ---------- ----------- ------------- ----------- ------------ --------- --------- ------ ----------- ------------ ------------ ---------- --------------- ------------ ---------- ------------------------------ ------------ ----------- -------
TEST100 USERS VALID 10 1 255 65536 1048576 1 2147483645 YES N 46 8 0 0 0 110 0 0 1 1 N ENABLED 46 2011-3-7 22:0 NO N N NO DEFAULT DISABLED YES NO DISABLED YES DISABLED DISABLED NO
SQL> create index pp_test
2 on test100(username);
Index created
SQL> alter index pp_test monitoring usage;
Index altered
SQL> select * from test100 a where a.username='PP';;
select * from test100 a where a.username='PP';
ORA-00911: invalid character
SQL> select * from test100 a where a.username='PP';
USERNAME USER_ID PASSWORD ACCOUNT_STATUS LOCK_DATE EXPIRY_DATE DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED PROFILE INITIAL_RSRC_CONSUMER_GROUP EXTERNAL_NAME
------------------------------ ---------- ------------------------------ -------------------------------- ----------- ----------- ------------------------------ ------------------------------ ----------- ------------------------------ ------------------------------ --------------------------------------------------------------------------------
PP 58 0B905FF5968A1E1A OPEN USERS TEMP 2011-2-28 1 DEFAULT DEFAULT_CONSUMER_GROUP
PP 58 0B905FF5968A1E1A OPEN USERS TEMP 2011-2-28 1 DEFAULT DEFAULT_CONSUMER_GROUP
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
------------------------------ ------------------------------ ---------- ---- ------------------- -------------------
PP_TEST TEST100 YES YES 03/09/2011 10:29:57
SQL>