I didnt get any intelligent replies or good suggestions for this task. i’m very sure very exp dbas have from experience, known that SA creates tables, indexes etc objects that are no longer used, but never do any cleanup.

Q1: I need to find a list of objects (tables, indexes, etc) that have not been used for > 6 mths and delete these
Q2: Before i delete, i need to find the dependency (eg any views based on indexes, tables)

Hope someone w this sort of cleanup experience help me

Many thanks in advance

Background: Oracle 9.2.0 running in Win2k

For Q1, not quite sure but one way would be for auditing to be enabled for the objects you are interested in checking.
For other questions (aside from C++ objects), you can utilize Oracle’s Enterprise Manager, (Schema Browser) to look at dependencies as well as TOAD (TOAD is great). As well, here’s an old script that I used to run in SQL*Plus if you don’t have access to the other tools:

accept object_owner char prompt “Enter Object Owner: ”
accept object_name char prompt “Enter Object Name: ”
accept run_control char prompt “Run Control: A)ll, I)nter-App only, B)oth: ”
prompt
prompt

set feed off
rem set termout off

column sqlline newline
set pages 0
spool c:tempx1.sql
select decode(upper(‘&run_control’),’A',’set termout on’
,’B',’set termout on’
,’rem ‘) sqlline
,decode(upper(‘&run_control’),’A',’prompt Running All Dependency Report…’
,’B',’prompt Running All Dependency Report…’
,’rem ‘) sqlline
,decode(upper(‘&run_control’),’A',’set termout off’
,’B',’set termout off’
,’rem A)ll or B)oth not selected.’) sqlline
,decode(upper(‘&run_control’),’A',’r’
,’B',’r’
,’rem ‘) sqlline
from dual;
spool off

spool c:tempx2.sql
select decode(upper(‘&run_control’),’I',’set termout on’
,’B',’set termout on’
,’rem ‘) sqlline
,decode(upper(‘&run_control’),’I',’prompt Running Inter-Application Only Report…’
,’B',’prompt Running Inter-Application Only Report…’
,’rem ‘) sqlline
,decode(upper(‘&run_control’),’I',’set termout off’
,’B',’set termout off’
,’rem I)nter-Aplication Only or B)oth not selected.’) sqlline
,decode(upper(‘&run_control’),’I',’r’
,’B',’r’
,’rem ‘) sqlline
from dual;
spool off

column curr_date_time new_value curr_date_time

column object_name format a45 heading “Object”;
column ref_object_name format a53 heading “Referenced Object”;
column referenced_link_name format a20 heading “Link Name”;

break on object_name skip 2 on report
compute count of ref_object_name on object_name report;

set lines 120
set pages 58
set newpage 0

set termout off;
select to_char(sysdate,’dd-Mon-yyyy hh24:mi:ss’) curr_date_time from dual;
set termout on;

ttitle left ‘Date: &curr_date_time’ -
center “Dependencies to/from (owner.table): &object_owner . &object_name ” -
right ‘Page: ‘ format 999 sql.pno skip 3

spo c:tempdb_impact.lst
select owner||’.'||name||’ (‘||type||’)’ object_name
,referenced_owner||’.'||referenced_name||’ (‘||referenced_type||’)’ ref_object_name
,referenced_link_name
from dba_dependencies
where (name like upper(‘&object_name’) or
referenced_name like upper(‘&object_name’))
and (owner like upper(‘&object_owner’) or
referenced_owner like upper(‘&object_owner’))
order by 1,2
.

@c:tempx1

set lines 120
set pages 58
set newpage 0

set termout off;
select to_char(sysdate,’dd-Mon-yyyy hh24:mi:ss’) curr_date_time from dual
.

@c:tempx2

set termout on;

ttitle left ‘Date: &curr_date_time’ -
right ‘Page: ‘ format 999 sql.pno -
center ‘Dependencies to/from (owner.table): &object_owner . &object_name ‘ skip 1 -
center “*** Inter-Application Dependencies Only ***” skip 3

select owner||’.'||name||’ (‘||type||’)’ object_name
,referenced_owner||’.'||referenced_name||’ (‘||referenced_type||’)’ ref_object_name
,referenced_link_name
from dba_dependencies
where (name like upper(‘&object_name’) or
referenced_name like upper(‘&object_name’))
and (owner like upper(‘&object_owner’) or
referenced_owner like upper(‘&object_owner’))
and referenced_owner not like ‘SYS%’
and owner != referenced_owner
order by 1,2
.

@c:tempx2

spool off

set termout on
prompt
prompt Done. Output in c:tempdb_impact.lst
prompt