Database cleanup

pts.
Tags:
Database
IBM DB2
Oracle
Background: Oracle 9.2.0 running on win2k q1: I need to find out which objects have not been in used for > 6 mths. How do i do this? q2: Before i delete an object, eg, an index, materialized view, view, i need a query to find what are its dependency, so that i would know whether this shld be deleted q3: i also need to know which view, index, table (any object, object-type) is being referenced in a program (PL/SQL, C++). Anyway to run a query that would list this info

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi,

Not sure about q1. But for q2 and q3, you can easily check the same using TOAD. It provides you the TAB (Referentials / Used By) for the dependant and referenced objects. You would be able to get information on procedures / packages written in database. But with reference to the proc*c or c++, i guess you will have to build some logical solution for the same.

Regards.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Mayleong
    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
    0 pointsBadges:
    report
  • Kurthp
    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
    195 pointsBadges:
    report
  • Mayleong
    [...] Address: http://itknowledgeexchange.techtarget.com/itanswers/database-cleanup/ (0) Comments Read [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following