RE: Identification of tables NOT being used in the System.

There was a note previously on a similar theme which I have posted below. The dba_tab_modifications does not show when a table has been selected from which could be a problem for reference data type table which can easily be overlooked.

+++++++++ Previous notes
Tony is thinking along the same lines I was. Go ahead and capture the outlines and then querying the DD where the indexes aren't found in the OL$HINTS.HINT_TEXT column could tell you what indices haven't been used. Something like:

select i.owner, i.table_name, i.index_name from dba_indexes i, outln.OL$HINTS h
where index_name not like ('%'||hint_text||'%')   and owner not in ('SYS','SYSTEM','PORTAL30','DES6I')

Just add the schema's to exclude. Only as good as the code coverage from your collection timeframe but still seems like a pretty solid approach.

Anyone tried this? I've kicked it around but have never actually tried it in anything other than a test environment.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781
-----Original Message-----
Sent: Monday, February 04, 2002 4:31 PM
To: Multiple recipients of list ORACLE-L

Another option you have if your Oracle version is high enough is to use Stored Outlines. Enable automatic generation of stored outlines for a full processing cycle as defined by the application (full month, Qtr, etc.) Then extract all of the indexes used during that cycle from OL$HINTS.HINT_TEXT
(i.e.. WHERE HINT_TEXT LIKE 'INDEX%'). If all of your application code has
been traversed in the cycle then this list will be pretty darn close to real usage. Even if you can't wait until year-end processing, you can eliminate the bulk of code to be mined for embedded SQL and focus on those once-a-year programs.
HTH
Tony Aponte
-----Original Message-----
Sent: Monday, February 04, 2002 12:55 PM To: Multiple recipients of list ORACLE-L

Hi
I there any view which can tell us which indexes are not in use? Thx
-Seema

-----Original Message-----
Sent: 15 March 2002 21:49
To: Multiple recipients of list ORACLE-L

ALTER TABLE tablename MONITORING;

Every three hours or so, the SYS.DBA_TAB_MODIFICATIONS view gets updated with the tables UPDATEs, DELETEs, and INSERTs, as well as wether or not the table has been TRUNCATEd since the last time it was DBMS_STATS'd. The view also gets updated on a SHUTDOWN, except for SHUTDOWN ABORT. I believe that if there's no activity on the table you set for MONITORING, that there will not be a row for it in this view.

And if you use CBO, you'll want to save the rows from DBA_TAB_MODIFICATIONS to your own table before using DBMS_STATS. It will zero the counters in the view for the tables it's run against.

Also, there's very little overhead, at least according to Oracle.

HTH! You might want to look this up in Metalink or the Oracle docs, too. Enjoy! :)

Rich Jesse                           System/Database Administrator
Rich.Jesse_at_qtiworld.com              Quad/Tech International, Sussex, WI USA

-----Original Message-----
Sent: Friday, March 15, 2002 3:08 PM
To: Multiple recipients of list ORACLE-L

In our production database environment, I have a list of about 1000 tables ,for which we want to find if these tables are being used by anyone. How it can be done. One of the ideas is that we start database auditing on these tables for a considerable period of time say one month. Then for those tables for which there is nothing in database audit, we assume that tables are not being used. For this option I would like to know if we put auditing on these 1000 tables, how much extra burden it is add onto the system (CPU, Memory etc). We are using Oracle 8.1.6 on HP-UX 11.00. If there are some other alternatives, please let me know. Thanks.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).


=========================================================
This electronic message contains information from the mmO2 plc Group 
which may be privileged or confidential. The information is intended to be 
for the use of the individual(s) or entity named above. If you are not the 
intended recipient be aware that any disclosure, copying, distribution or 
use of the contents of this information is prohibited. If you have received 
this electronic message in error, please notify us by telephone or email 


(to the numbers or address above) immediately.

=========================================================


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Hallas John
  INET: John.Hallas_at_o2.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).