I have an Oracle database that has more than 1000 tables, sequences, indexes, and other object.

I want to clean up database object. Some objects are not used.

I want to list all objects that have not been use in more than 1 month so I can delete them.

  • I don't think this is possible. For tables (and other things that "store" data), you might be able to trace the usage through v$segment) but I doubt this is possible for views, stored procedures or other "non-storage" types. – a_horse_with_no_name Sep 13 '16 at 8:51
  • 2
    One option might be to switch on auditing and add BY ACCESS audits to objects that you want to check. This might results in a lot of audit data so some caution is needed if you left it for a month on busy objects so perhaps review it each day and remove audits for things that are being accessed. – user3629 Sep 13 '16 at 10:26
  • 1
    What is "cleaning up" going to achieve exactly? I've seen this asked many times, and it's usually misguided – Philᵀᴹ Sep 13 '16 at 14:44
  • my database have some object that create last year ago and forgot do delete by dba .for example we create sequence object that name is seq_user after change this to seq_users dba forgot to delete that.i want to delete them – ali akbar azizkhani Sep 13 '16 at 15:51
  • You don't want to drop objects on Oracle schemas, such as: sys, system, outln, dbsnmp, exfsys, ordsys, olapsys, sysman, wmsys, xdb, etc. But for tables that are a part of your application, it would be worth having an Entity Relationship diagram (ERD) that shows the relationship between tables. You would then know what each table does and if it is still needed. If you are running a third party app, some of the vendors don't want you to understand the data persistence layer. But if you created a table, you should be able to figure out what you are using it for and whether it should still exist. – Gandolf989 Sep 13 '16 at 17:57

There is no reliable way to do this. Even with auditing enabled, indirect object access is not recorded in some cases. For example if you have an ON DELETE CASCADE foreign key defined, and you delete rows from the parent table, the deletion of rows from the child table does not appear in the audit trail.

As far as I know, the only object type that has support for this, is the index. You can enable index monitoring by:

alter index owner.index_name monitoring usage;

Once you have enabled monitoring for an index, later you can check if the index was used in the view V$OBJECT_USAGE (when logged in as the owner of the index).

Starting with 12c, you need not to log in as the owner to query this, the new DBA_OBJECT_USAGE view can be used for this.

When you do not want the index to be monitored anymore, disable monitoring by:

alter index owner.index_name nomonitoring usage;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.