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;
Halim, a Georgia Tech graduate Senior Database Engineer/Data Architect based in Atlanta, USA, is an Oracle OCP DBA and Developer, Certified Cloud Architect Professional, and OCI Autonomous Database Specialist. With extensive expertise in database design, configuration, tuning, capacity planning, RAC, DG, scripting, Python, APEX, and PL/SQL, he combines technical mastery with a passion for innovation. Notably, Halim secured 16th place worldwide in PL/SQL Challenge Cup Playoff on the year 2010.
Subscribe to:
Post Comments (Atom)
My Blog List
-
-
-
ASSM states3 weeks ago
-
UKOUG Discover 20241 month ago
-
-
-
-
-
-
-
-
Moving Sideways8 years ago
-
-
Upcoming Events...11 years ago
-
No comments:
Post a Comment