Operating System - HP-UX
1748134 Members
3437 Online
108758 Solutions
New Discussion юеВ

Doubt to find which tables are absolete.

 
Nirmalkumar
Frequent Advisor

Doubt to find which tables are absolete.

Hi,

we use to run dbstats for the entire schema whenever change in structure of schema happens.i want to find out in that schema which tables are absolete.if i found which tables are absolete.i will run dbstats for the tables only.

Help appreciated..

Thanks,
Nirmal.
4 REPLIES 4
Yogeeraj_1
Honored Contributor

Re: Doubt to find which tables are absolete.

hi,

Why version of database are you running?

You can use dbms_stats package to collect the statistics.
e.g
dbms_stats.gather_schema_stats( ownname => user, options => 'GATHER STALE' );

for it to work properly, you should enable "monitoring" on all tables.

if you need any further clarifications, please let us know.

kind regards
yogeeraj

No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Nirmalkumar
Frequent Advisor

Re: Doubt to find which tables are absolete.

Hi yogeeraj,

Thank u for your suggestions..

we use to run the DBSTATS with the commands u r mentioned in this forum thread.MY question is any Data Dictionary view or dynamic performance view are availbale to find out which tables are ABSOLETE.


Thanks,
Nirmal.
Yogeeraj_1
Honored Contributor

Re: Doubt to find which tables are absolete.

HI nirmal,

there are many such data dictionary views.

one that i can think right now is col_usage$

see also:
http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#sthref7885


hope this helps!

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Bill Thorsteinson
Honored Contributor

Re: Doubt to find which tables are absolete.

try

l_objList dbms_stats.objectTab;

dbms_stats.gather_schema_stats(
ownname => l_schema_owner,
options => 'LIST AUTO',
objlist => l_objList

Iterate over the list and use dbms_output to
list the stale (obsolete) tables.