how to find unused tables...

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

Many Thanks,

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

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.

Quote:

---

Originally Posted by badrinathn

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?

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.

can you send the trigger script ....

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


MAny Thanks,

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)

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).

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

well they only change if you analyze them, and that value could change even if you dont use it

also what if you just update the table records..the number doesn't grow..but the table is still used.

The whole thing seems rather silly to me. Before we discuss it any further, I think the original poster should first prowide a very concise definition of what "tables that are not used" actualy means.

For me, a table on which an application merely performs SELECTs is no less used as the table in which application performs INSERTS/UPDATES/DELETES. And as allready said, if SELECTS on a table count as "table is used", then forget about triggers. In fact, forget about anything, except auditing. Audit trail is the only way to get that information from.

You can also collect and manage user-defined statistics for tables and domain indexes using thie DBMS_STATS package. ;)