OraFAQ Forum: Server Administration » find table and index are using or not

Home » RDBMS Server » Server Administration » find table and index are using or not

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

find table and index are using or not [message #152982]

Wed, 28 December 2005 04:57

gjayasekar
Messages: 45
Registered: October 2005

Member

Hi,

I want to find out Which tables and indexes are not using by the user (or) last used date from 9.2.0 Version

Thanks
Jai

Report message to a moderator

Re: find table and index are using or not [message #152990 is a reply to message #152982]

Wed, 28 December 2005 05:32

tarundua
Messages: 1080
Registered: June 2005
Location: India

Senior Member

SQL> select last_ddl_time , object_name 
  2  from dba_objects
  3  where owner='SCOTT';
LAST_DDL_ OBJECT_NAME
--------- --------------------
08-DEC-05 BONUS
08-DEC-05 DEPT
08-DEC-05 EMP
20-DEC-05 EMP1
08-DEC-05 PK_DEPT
08-DEC-05 PK_EMP

Report message to a moderator

Re: find table and index are using or not [message #153003 is a reply to message #152990]

Wed, 28 December 2005 06:01

Frank
Messages: 7901
Registered: March 2000

Senior Member

I think the OP wants to know when the last DML statements were fired using the table/index.
This will require auditing (for tables) or index-monitoring.
Search the board to find out more about auditing; look in the docs for the index-monitoring.

hth

Report message to a moderator

Re: find table and index are using or not [message #153082 is a reply to message #152982]

Wed, 28 December 2005 23:17

wwllzpz
Messages: 11
Registered: December 2005
Location: shanghai china

Junior Member

hi ,you can use the following sql to monitor index.
alter index index_name monitoring usage;

Report message to a moderator

Re: find table and index are using or not [message #153195 is a reply to message #152982]

Thu, 29 December 2005 20:48

dbaxchangedba
Messages: 26
Registered: November 2005

Junior Member

A few dictionary views / tables from where you can get information on whether the tables and indexes were used during the duration of the instance include:

V_$segstat
v_$segment_statistics

statistics_level parameter needs to be TYPICAL or ALL for the above dictionary views to provide segment statistics. They however do not give you timestamp information.

v$db_object_cache is another view that provides details on object usage.

In addition, if you enable table monitoring then you can query dba_tab_modifications view for DML operations performed on the table. This view also provides you with timestamp information.

v$object_usage view will provide you with information on index access / usage if index monitoring is enabled.

As stated by others in this thread, auditing will give you much more detailed information on objects usage.

Good Luck!

http://www.dbaxchange.com

Report message to a moderator

Previous Topic:	scripts for creating Instance
Next Topic:	Database Slows down after some time

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 26 13:09:27 CST 2025