Object Usage in Oracle schema

suhinrasheed
suhinrasheed used Ask the Experts™
on
I have a  Oracle schema which owns a couple of packages.My Client has requested to check whether those objects have been used recently that is in the last couple of months and if not then to drop the Schema.


How can i identify whether they were used.

Rgds,
Suhin
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You can only check when this package was last modified by:
SELECT OBJECT_NAME AS PACKAGENAME,
       CREATED,
         LAST_DDL_TIME
FROM   ALL_OBJECTS
WHERE  OBJECT_TYPE LIKE 'PACKAGE%'

To check when the package was last used i suppose you must enhance the methods of the packages ...
Senior Technologist
Commented:
You can also check if they have been loaded into the shared pool. If they have, then they are definately been used.

SELECT   doc.owner, doc.NAME, doc.TYPE, doc.loads, doc.sharable_mem,
         upper(ins.instance_name) instance_name
FROM     v$db_object_cache doc, v$instance ins
WHERE    doc.loads > 2
AND      doc.TYPE IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')
ORDER BY doc.loads DESC
Forced accept.

Computer101
EE Admin

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial