How to know if a table is used ?

[Sofiane]

Hello,

I would like to know if a table is used or not (Select order against this table). I intend to query every hour for instance the v$sqlarea and search on sql_text the name of the table...
Do you know a better way to see if a table is queried by users ?

Thanks in advance

Sofiane

[gpsingh]

Hi,

Many options:

1) 8.1.6 , u can enable table monitoring and then query dictionary views
alter table d monitoring;
You have to wait for few hours for result to show and also u have to enable it for each table.
select * from user_tab_modifications;

Very tedious.

2) query v$db_object_cache

select owner,name,loads
from v$db_object_cache
where type='TABLE'
order by owner

3) query x$bh (8.1.6) and match object type with dba_objects

Thanks
GP

[Halo]

Audit the table

[jmodic]

Rename the table and wait for the telephone to begin ringing.... ;)

[sreddy]

Thats funny ! Can be done if its Development...