how to find unused tables...

[castlerock]

Hi All,
Is there any way to find unused tables...(ie tables that are not used for a long time).

Many Thanks,

[badrinathn]

Easiest way would be to put the triggers- that's how we do the data transfer to identify the changes to the tables.

[jmodic]

a) If you have appropriate auditing turned on, simply find that information from your audit trail.

b) If not, turn the auditing on, wait "a long time" and then return to point a).

Realy, without some kind of audit trail, you can't get that information.

[jmodic]

Easiest way would be to put the triggers- that's how we do the data transfer to identify the changes to the tables.

How will triggers show you which tables were or were not not used for *querying* purposes?

[badrinathn]

It's not with-in oracle, but we have a list of all the tables, and whenever any DML occurs, the change date will be logged...

So it's just a round about way to know for all the interested tables in the application.

[castlerock]

can you send the trigger script ....

[castlerock]

Can you pls send the triggers....which will really help to start of with


MAny Thanks,

[badrinathn]

Just a very simple one - to log the access.
LU_ACCOUNT is the table name.
TABLE_CONTROL is the control table.

create or replace trigger LA_lu_account_trg
after insert or update or delete
on lu_account
begin
update table_control set last_access = sysdate where tab_name = 'LU_ACCOUNT';
end;

Table_control
SCH_NAME VARCHAR2(20)
TAB_NAME VARCHAR2(30)
LAST_ACCESS DATE
TAB_DROPPED CHAR(1)

[tomcat]

Why not use auditing? That's what it's there for.

Triggers are fine, depending on the circumstances - but never add them without some thought first. That code gets executed everytime a statement hits your table(s) - so use with care. If your tables really aren't used much, then no big deal.

Auditing could also show you selects, not just the DML. And also errors on sql hitting those tables (I use that a lot).

[bigd]

you could monitor ALL_TABLES.NUM_ROWS to keep a watch on how the rows are growing