Thursday, December 20, 2007

Viewing All Indexes Being Monitored Under Another User's Schema

PURPOSE
To show how to view all indexes being monitored.


SCOPE & APPLICATION
Instructional.


Viewing All Indexes Being Monitored Under Another User's Schema:
================================================================

V$OBJECT_USAGE does not display rows for all indexes in the database whose
usage is being monitored.

'ALTER INDEX MONITORING USAGE' places an entry in V$OBJECT_USAGE for
that particular index to help determine if the index is being used or not. The
V$OBJECT_USAGE view uses the username logged into database when the 'ALTER
INDEX MONITORING USAGE' is issued. This will not enable any user other
than the user who issued the 'ALTER INDEX MONITORING USAGE' to view if
index is being monitored or not.

The view structure may be changed slightly (see below) in order to expand its
scope system-wide (see below) so that you may see all indexes being monitored.

For example:

Showing User Scott monitoring his Index on EMP table:

SQL> connect scott/tiger
SQL> set LONG 30000

SQL> select text from dba_views where view_name ='V$OBJECT_USAGE';

TEXT
select io.name, t.name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
ou.start_monitoring,
ou.end_monitoring
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
where io.owner# = userenv('SCHEMAID')
and i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#


SQL> select index_name, table_name, uniqueness, status from user_indexes
where table_name = 'EMP';

INDEX_NAME TABLE_NAME UNIQUENES STATUS
PK_EMP EMP UNIQUE VALID

SQL> alter index PK_EMP monitoring usage;

Index altered.

SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
PK_EMP EMP YES NO 10/12/2001 06:42:35


Then connect as another user to view indexes being monitored:

SQL> connect / as sysdba;

Connected.

SQL> select * from v$object_usage;

no rows selected


To be able to view them do the following:

SQL> create or replace view V$ALL_OBJECT_USAGE
(OWNER,
INDEX_NAME,
TABLE_NAME,
MONITORING,
USED,
START_MONITORING,
END_MONITORING)
as
select u.name, io.name, t.name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
ou.start_monitoring,
ou.end_monitoring
from sys.user$ u, sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
where i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#
and u.user# = io.owner#

View created.


SQL> select * from v$all_object_usage;

OWNER INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
SCOTT PK_EMP EMP YES NO 10/12/2001 06:42:35



Related Documents:
==================

Oracle9i Database Administrator's Guide Volume 1 (Managing Indexes: Monitoring
Index Usage)

Note 144070.1 Identifying unused indexes in Oracle9i

7 comments:

Anonymous said...

Excellent way of telling, and pleasant paragraph to obtain facts regarding my presentation subject, which i am going
to present in school.

Here is my web page; aloe vera - -

Anonymous said...

I pay a quick visit every day a few web pages and sites to read content, but this web site gives quality based articles.


Also visit my web site: how to make a lot of money fast

raybanoutlet001 said...

new orleans saints jerseys
cheap basketball shoes
reebok shoes
michael kors handbags
christian louboutin shoes
oakley sunglasses
pandora outlet
adidas nmd
michael kors handbags
ugg outlet

Unknown said...

air max 90
yeezy
kobe shoes
jordan shoes
chrome hearts
stephen curry shoes
adidas superstar
adidas nmd
lacoste outlet
curry 3

jeje said...

adidas eqt support adv
kobe 11
timberland shoes
true religion
air jordan 11
adidas neo online shop
yeezy sneakers
kyrie 3
cheap jordan shoes
baseball jerseys

الركن المكلي said...

نقل عفش بجدة
نقل عفش بجده
شركه نقل عفش بجده
شركة نقل عفش بجدة
شركات نقل العفش بجدة

شركة نقل عفش جدة الحمدانية
نقل عفش جدة الحمدانية
نقل عفش جده الحمدانيه
شركه نقل عفش جده الحمدانيه


شركة نقل عفش بجدة
شركة نقل عفش بجده
شركه نقل عفش بجده
شركة نقل عفش داخل جدة
افضل شركة نقل عفش بجدة
افضل شركه نقل عفش بجده
نقل عفش
شركة نقل جدة
ارقام نقل عفش
شركة نقل عفش
رقام نقل عفش
شركات نقل عفش
نقل العفش
شركات نقل العفش بجدة
نقل عفش جدة
نقل العفش جدة
نقل عفش جده
نقل العفش جده
نقل عفش بجدة
نقل عفش بجده
نقل العفش بجدة
نقل العفش بجده
نقل عفش بجده رخصيه
نقل عفش بجدةرخيص

shouseson said...

my companyanchor his comment is hereyou can look here find thisfind out here now