how to find unused tables...
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: how to find unused tables...

Hybrid View

  1. #1
    Join Date
    Jan 2003
    Posts
    141

    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,

  2. #2
    Join Date
    Jan 2001
    Posts
    642
    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.

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Jan 2001
    Posts
    642
    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.

  5. #5
    Join Date
    Jan 2003
    Posts
    141
    can you send the trigger script ....

  6. #6
    Join Date
    Jan 2003
    Posts
    141
    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.

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  8. #8
    Join Date
    Jan 2001
    Posts
    642
    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.

  9. #9
    Join Date
    Oct 2005
    Location
    Indianapolis
    Posts
    100
    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

  10. #10
    Join Date
    Oct 2005
    Posts
    1
    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
  •  



Click Here to Expand Forum to Full Width