Sunday, April 11, 2010

How to know the table/index uses information in oracle

How to know the table/index uses information
=======================================
1. by enabling AUDIT .

2. BY QUERY THE FOLLOWING

SELECT *
FROM v$segment_statistics
WHERE owner = 'USER_NAME'
AND object_name = 'TABLE_NAME'

select owner,object_name,sum((case when STATISTIC# = '0' then value end )) "logical reads"
,sum((case when STATISTIC# = '1' then value end )) "buffer busy waits"
,sum((case when STATISTIC# = '2' then value end )) "gc buffer busy"
,sum((case when STATISTIC# = '3' then value end )) "db block changes"
,sum((case when STATISTIC# = '4' then value end )) "physical reads"
,sum((case when STATISTIC# = '5' then value end )) "physical writes"
,sum((case when STATISTIC# = '6' then value end )) "physical reads direct"
,sum((case when STATISTIC# = '7' then value end )) "physical writes direct"
,sum((case when STATISTIC# = '8' then value end )) "null"
,sum((case when STATISTIC# = '9' then value end )) "gc cr blocks received"
,sum((case when STATISTIC# = '10' then value end )) "gc current blocks received"
,sum((case when STATISTIC# = '11' then value end )) "ITL waits"
,sum((case when STATISTIC# = '12' then value end )) "row lock waits"
,sum((case when STATISTIC# = '14' then value end )) "space used"
,sum((case when STATISTIC# = '15' then value end )) "space allocated"
,sum((case when STATISTIC# = '17' then value end )) "segment scans"
from v$segment_statistics
where owner='USER_NAME'
---and object_name='TABLE_NAME'
and object_type='TABLE'
group by owner,object_name
order by "space used" desc--object_name


3. OR ONLY FOR INSERT/UPDATE/DELETE

SELECT * FROM select * from USER_tab_modifications;