| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Identification of tables NOT being used in the System.
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). Received on Fri Mar 15 2002 - 15:49:24 CST
 |
 |