Difference between revisions of "Oracle"
Jump to navigation
Jump to search
| Line 7: | Line 7: | ||
where owner = '?' | where owner = '?' | ||
and index_type not in ('LOB'); | and index_type not in ('LOB'); | ||
| + | </source> | ||
| + | |||
| + | Validate it has been enabled on all indexes (in case some were locked): | ||
| + | <source lang="sql"> | ||
| + | select index_name | ||
| + | from user_indexes | ||
| + | where index_type not in ('LOB') | ||
| + | minus | ||
| + | select index_name | ||
| + | from v$object_usage; | ||
</source> | </source> | ||
Revision as of 12:04, 31 March 2016
DBA / Tuning
Index monitoring
Enable monitoring:
select 'alter index ' || owner || '.' || index_name || ' monitoring usage;'
from dba_indexes
where owner = '?'
and index_type not in ('LOB');
Validate it has been enabled on all indexes (in case some were locked):
select index_name
from user_indexes
where index_type not in ('LOB')
minus
select index_name
from v$object_usage;
Verify, make sure to connect as the owner of the objects you are monitoring:
select *
from v$object_usage;