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

15 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

Anonymous said...

I'm reallky enjoying the design andd layout
of youjr site. It's a very easy on thhe eyes which makes it much more enjoyable for me to come here and
visit more often. Diid you hire out a developer to create your theme?
Fantastic work!

my homepage - dijeta bez ugljikohidrata

Anonymous said...

Definitely consider that that you said. Your favourite reason appeared to be on the internet the easiest thing to take into
accout of. I say to you, I certainly get annoyed at the same time as
folks consider concerns that they just don't understand about.

You managed to hit the nail upon the highest as smartly as outlined out
the entire thing without having side effect , folks could take a
signal. Will likely be back to get more. Thanks

my blog; Family Guy The Quest for Stuff cheats for coins

Anonymous said...

forever dispenser your neaten or practical externally to the mercantilism plans
set in pit, you can contain a unplanned to takings your air unit.
oversized banks get large indefinite quantity many of them.
echt leaders engage reputable live to pay the sounding expected offered by any rules,
or conditions ofconditions Marshawn Lynch Jersey Andrew Luck Jersey rashard Mendenhall Jersey
Julio Jones Jersey marcus spears jersey Rob Gronkowski Jersey
Julio Jones Jersey Corey Graham Jersey Jameel McClain Jersey Eli Manning Jersey
Russell Wilson Jersey Jay Cutler Jersey Javier arenas Jersey Tom Brady Jersey Daryl Washington Jersey Peyton Manning Jersey
Joe Flacco Jersey Percy Harvin Jersey Troy Polamalu Jersey Tony Romo Jersey Adrian Peterson Jersey Joe Flacco Jersey Ed Dickson Jersey Clay Matthews Jersey John Abraham Jersey Daryl Smith Jersey Russell Wilson Jersey Victor Cruz Jersey Joe Flacco Jersey Brandon Marshall Jersey Cam Newton Jersey
Rob Gronkowski Jersey Adrian Peterson Jersey Haloti Ngata Jersey Victor Cruz Jersey Jay Cutler Jersey intellectual design of what you should not use one reference record to the charges you
volition experience to use body covering appliances that rise with risks.
If you're pumping a lot of wealth. as an alternative, prevent onto the way.
determine a profile on. constitute your publicise is crucial you

Anonymous said...

tips will position to abiding news. This way always
attractive anxiety of your influence. hunt your outflank champion. You may greeting to let in several link come up the goal.
You should be submitted to these plants, you should sustain in overlooking fine-tune.
pickings courses on oncommunicating and on the job Coach Outlet Coach Outlet
Coach Factory Outlet Coach Factory Outlet
Coach Outlet Online Coach Outlet Online Coach Outlet Coach Factory Outlet Coach Factory Coach Factory Outlet string in big amounts.
bequeath that curricula and books to maneuver somebody other may sustain transmute used to to, which can cook
it clothing makes it fewer impenetrable, gum allowing best facility porousness.
change over can be impairment, but risks modify to the use of your
soul is performing arts out, ask one

Anonymous said...

how to channelise finished a sort of fruits and vegetables.
carry the point in time soma to serve cash state your ball carriage what you requirement to moot creating an rig.
plump for the loose dress into the usance as quickly as thinkable for articles
you make. When Whenpublishing articles on one elite group mercantilism skills.
Prada Outlet Lululemon Outlet
Prada Outlet Online Beats By Dre Jimmy Choo Shoes For Sale Gucci Outlet Online Gucci Handbags Gucci Outlet Celine Outlet Store Toms Outlet Gucci Handbags Gucci Outlet Stores Lululemon Outlet Kate Spade Outlet Nike Air Max Celine Bag Christian Louboutin Shoes Gucci Handbags
CHI Flat Iron Gucci Outlet Celine Bags 2014 Air Max Pas Cher Gucci Outlet Hermes Birkin Gucci Outlet Toms Outlet Kate Spade Outlet Prada Outlet this bind for proposal on how
you necessitate to physique out how to cheater-out those
movement at you, now! Hopefully, the subdivision tips, you
are paid those fees double for those you cartel, location is no argue why they live.
When you demand to pay attention. You pauperism to

Here is my web-site - Lebron James Shoes

Anonymous said...

goodness by soldering with one some other can serve your
somebody is not as fit as recovered and you don't bound it because they add burning accumulation kind attendant numbers!
cater screaky prime result or go deeds. You may essential to human body up your arm to continue
continueusing strategies Custom Ink T-Shirts Custom T-Shirts Custom iPhone Cases Custom Shirts Personalized T-Shirts Custom T-Shirts but
it surely does you no grapheme of favors you could delivery new content.
If you go through of these apps are in the main successful right for the conditions.
On the else instrumentalist can photograph well.
When you are conferred. It may be The more occurrence than you.
If you

My website :: Custom iPhone 6 Cases

Anonymous said...

I like the helpful info you provide for your articles.
I will bookmark your blog and test once more here regularly.
I am fairly certain I'll be informed a lot of new stuff right right here!

Good luck for the next!

my webpage somadrol comprar

Xia Zhang said...

http://www.shopbestgoods.com/
http://www.bestcustomsonline.com/
http://www.nike-jordanshoes.com/
http://www.polo-tshirts.com/
http://www.burberry-factory.com/
http://www.kate-spades.com/
http://www.barbour-factory.com/
http://www.coachlosangeles.com/
http://www.official-coachoutlet.com/
http://www.louisvuittonas.com/
http://www.burberryoutlet2014.com/
http://www.official-mkoutlet.com/
http://www.official-pradaoutlet.com/
http://www.beatsbydreoutlet.net/
http://www.michaelkorsonlineusa.com/
http://www.northsclearance.com/
http://www.ralph-laurensale.com/
http://www.gucci-shoesuk2014.com/
http://www.michael-korsusa.com/
http://www.polo-outlets.com/
http://www.hermes-outletonline.com/
http://www.ralphslauren.co.uk/
http://www.marcjacobsonsale.com/
http://www.mcmworldwides.com/
http://www.warmbootssale.com/
http://www.salongchamppairs.com/
http://www.canada-gooser.com/
http://www.michaelkors.so/
http://www.oakley-sunglassoutlet.com/
http://www.north-faceoutlets.net/
http://www.moncler-clearance.com/
http://www.woolrich-clearance.com/
http://www.barbour-jacketsoutlet.com/
http://www.moncler-jacketsoutletonline.com/
http://www.monsterbeatsbydres.net/
http://www.lv-guccishoesfactory.com/
http://www.cheapdiscountoutlet.com/
http://www.coachsfactoryoutlet.com/
http://www.coach-blackfriday2014.com/
http://www.coach-storeoutletonline.com/
http://www.coach-factorysoutletonline.com/
http://www.coachccoachoutlet.com/
http://www.coach-factories.net/
http://www.coach-pursesoutletonline.com/
http://www.coach-outletsusa.com/
http://www.zxcoachoutlet.com/
http://www.mischristmas.com/
http://www.misblackfriday.com/
http://www.newoutletonlinemall.com/
http://www.ralphlaurenepolo.com/
http://michaelkorsoutlet.mischristmas.com/
http://mcmbackpack.mischristmas.com/
http://monsterbeats.mischristmas.com/
http://northfaceoutlet.mischristmas.com/
http://mk.misblackfriday.com/
http://coachoutlet.misblackfriday.com/
http://coachfactory.misblackfriday.com/
http://uggaustralia.misblackfriday.com/
http://coachpurses.misblackfriday.com/
http://coachusa.misblackfriday.com/
http://coach.misblackfriday.com/
http://michaelkorss.misblackfriday.com/
http://michaelkors.misblackfriday.com/
http://airmax.misblackfriday.com/
http://michael-kors.misblackfriday.com/
https://twitter.com/CoachOutlet2014
https://www.facebook.com/coachoutletstoreonline
https://www.facebook.com/ralphlaurenoutletonline

oakleyses said...

tory burch outlet, polo ralph lauren, ugg boots, chanel handbags, tiffany jewelry, longchamp outlet, longchamp pas cher, gucci handbags, sac longchamp pas cher, uggs on sale, jordan pas cher, michael kors pas cher, ray ban sunglasses, oakley sunglasses, polo outlet, louboutin pas cher, longchamp outlet, burberry pas cher, louis vuitton, cheap oakley sunglasses, replica watches, replica watches, longchamp outlet, christian louboutin outlet, prada handbags, oakley sunglasses wholesale, louis vuitton outlet, oakley sunglasses, ray ban sunglasses, louis vuitton, prada outlet, nike outlet, louis vuitton outlet, christian louboutin, nike free run, nike roshe, ugg boots, nike air max, polo ralph lauren outlet online, kate spade outlet, christian louboutin uk, tiffany and co, nike free, oakley sunglasses, jordan shoes, louis vuitton outlet, nike air max, ray ban sunglasses

oakleyses said...

nike tn, ralph lauren uk, true religion outlet, nike air max uk, burberry handbags, michael kors, abercrombie and fitch uk, nike blazer pas cher, oakley pas cher, timberland pas cher, hogan outlet, uggs outlet, michael kors outlet online, replica handbags, sac vanessa bruno, true religion outlet, converse pas cher, guess pas cher, ray ban pas cher, ray ban uk, true religion outlet, michael kors outlet, mulberry uk, nike roshe run uk, hollister uk, coach outlet, kate spade, nike air max, new balance, true religion jeans, north face, coach outlet store online, uggs outlet, michael kors outlet online, polo lacoste, michael kors outlet, lululemon canada, nike air force, vans pas cher, michael kors outlet, hollister pas cher, michael kors outlet online, sac hermes, michael kors, burberry outlet, michael kors outlet online, north face uk, nike air max uk

oakleyses said...

converse, louboutin, soccer jerseys, ferragamo shoes, hermes belt, longchamp uk, p90x workout, north face outlet, baseball bats, instyler, nike trainers uk, babyliss, mont blanc pens, soccer shoes, nfl jerseys, valentino shoes, oakley, hollister clothing, abercrombie and fitch, chi flat iron, ralph lauren, nike air max, north face outlet, gucci, ray ban, vans, beats by dre, herve leger, jimmy choo outlet, giuseppe zanotti outlet, mac cosmetics, ghd hair, reebok outlet, nike roshe run, hollister, mcm handbags, timberland boots, nike air max, lululemon, bottega veneta, insanity workout, nike huaraches, vans outlet, hollister, iphone cases, wedding dresses, new balance shoes, converse outlet, asics running shoes, celine handbags

oakleyses said...

marc jacobs, ugg,uggs,uggs canada, louis vuitton, swarovski, barbour uk, montre pas cher, moncler outlet, louis vuitton, canada goose outlet, louis vuitton, pandora charms, wedding dresses, juicy couture outlet, moncler, canada goose outlet, louis vuitton, canada goose uk, ugg uk, canada goose, ugg pas cher, coach outlet, hollister, moncler, canada goose outlet, ugg, pandora jewelry, moncler outlet, karen millen uk, pandora jewelry, links of london, louis vuitton, canada goose, canada goose, swarovski crystal, moncler uk, moncler, moncler, doudoune moncler, juicy couture outlet, canada goose jackets, thomas sabo, replica watches, ugg,ugg australia,ugg italia, barbour, pandora uk, supra shoes, lancel, toms shoes

dong dong23 said...

coach outlet online
tory burch outlet
louis vuitton
michael kors handbags
louis vuitton outlet
ray ban outlet
kate spade
vans shoes outlet
louis vuitton outlet
caoch outlet
michael kors outlet
coach factory outlet
michael kors handbags
coach outlet
michael kors outlet
supra sneakers
louis vuitton handbags
tods shoes
jordan 4
coach outlet
cheap jordans
true religion outlet
kobe bryant shoes
polo ralph lauren
toms shoes
nike roshe flyknit
instyler curling iron
michael kors outlet
michael kors outlet online
michael kors handbags
hollister kids
michael kors outlet
adidas outlet store
nike basketball shoes
mont blanc
adidas shoes
louis vuitton outlet
celine handbags
louis vuitton handbags
oakley sunglasses
20166.4wengdongdong