-
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.
There is always a better way to do the things.
-
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?
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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.
There is always a better way to do the things.
-
can you send the trigger script ....
-
Can you pls send the triggers....which will really help to start of with
MAny Thanks,
Last edited by castlerock; 10-27-2005 at 10:08 AM.
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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)
There is always a better way to do the things.
-
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).
"False data can act only as a distraction. Therefore. I shall refuse to perceive you." - Bomb #20
-
you could monitor ALL_TABLES.NUM_ROWS to keep a watch on how the rows are growing
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|