Thanks for the question, Micah.
Asked: October 07, 2016 - 7:28 pm UTC
Last updated: October 08, 2016 - 1:49 am UTC
Version: 12c
Viewed 10K+ times! This question is 
You Asked
I am working on a database that has nearly 3,900 tables, and 450 views, 90 packages, 740 procedures, and 325 functions. Some of those are called by apps and report servers that I do not have access to all the source code.
I have a feeling that maybe 1/20th of those objects are actually used, but I don't know how I can figure that out.
I am afraid of turning on select, execute audits because of massive amounts of data being created, and io/cpu utilization and storage issues.
Is there another place I can find out when these objects were last accessed (selected, inserted, updated, deleted, executed)?
Or, is there a slick way that I can audit everything, but have the audit for an object automatically be turned off the first time it is audited? (eg, an insert trigger on DBA_AUDIT_OBJECT that would exec NOAUDIT for the object that is being audited).
and Connor said...
How about this for a strategy:
Step 1: Eliminate as many candidates as possible
- query v$segment_statistics to identify tables/views/indexes that are in use
- query v$db_object_cache to identify other objects that are in use
- query v$sql_plan.object_name to add to the list
Step 2: Then selectively apply audit to the *other* objects
Step 3: (as you said) review the audit trail, add NOAUDIT to anything captured
A few iterations of that, and I would imagine you'll quickly get down to a manageable set of objects to track.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment