FAQ
Hey all,

Looking at implementing Index Monitoring in 9.2.0.5.0 on HP-UX 11.11, =
primarily to make a case to drop unused indexes (one particular table =
has 17 indexes). So, I ALTER tons of indexes MONITORING USAGE, ignoring =
the LOB and Context indexes. All's well, but for some reason, Oracle =
decided to make the V$OBJECT_USAGE view so that a user can only see =
monitored indexes for their own schema (and I thought Oracle had a =
better handle on security in 9i).

So, I create my own view . I can't because the =
SYS.OBJECT_USAGE table cannot be changed, apparently including GRANTs. =
I'd paste the error here, but when I try it again, I get an ORA-600. =
Oracle's solution is to create a new V$ view in SYS. I make it a habit =
to not mess with SYS unless I have to.

Thoughts?

Rich

Rich Jesse System/Database Administrator
rich.jesse_at_quadtechworld.com QuadTech, Sussex, WI USA
----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.

Search Discussions

  • Wolfgang Breitling at May 17, 2004 at 4:02 pm
    Works for me, albeit on 9.2.0.1.0 on W2K:

    SQL*Plus: Release 9.2.0.1.0 - Production on Mon May 17 15:01:42 2004

    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

    SP2-0640: Not connected
    SQL> connect / as sysdba
    Connected.
    SQL> grant select on sys.object_usage to scott;

    Grant succeeded.

    SQL> grant select on sys.object_usage to public;

    Grant succeeded.

    SQL>

    Maybe it's another 9.2.0.5 bug? The fact that you get an ORA-600 error is
    suspicious.
    At 02:47 PM 5/17/2004, you wrote:
    Hey all,

    So, I create my own view . I can't because the =
    SYS.OBJECT_USAGE table cannot be changed, apparently including GRANTs. =
    I'd paste the error here, but when I try it again, I get an ORA-600. =
    Oracle's solution is to create a new V$ view in SYS. I make it a habit =
    to not mess with SYS unless I have to.


    Rich Jesse System/Database Administrator
    regards

    Wolfgang Breitling
    Centrex Consulting Corporation
    http://www.centrexcc.com

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Mladen Gogala at May 17, 2004 at 4:11 pm
    Which version? In 9.2.0.5 I can access object_usage from another DBA user.
    Here is how it works:

    SQL> connect mgogala
    Enter password:
    Connected.
    SQL> select * from sys.object_usage;


    no rows selected


    SQL>

    SQL> desc sys.object_usage

    Name Null? Type
    ----------------------------------------- -------- ---------------------------- OBJ# NOT NULL NUMBER
    FLAGS NOT NULL NUMBER
    START_MONITORING VARCHAR2(19)
    END_MONITORING VARCHAR2(19)

    SQL>

    All you need, besides love, is the way to interpret the "flag" column.
    This might be helpfull:

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


    VIEW_NAME

    TEXT

    V$OBJECT_USAGE

    select io.name, t.name,

    decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),

    In other words, I believe that your problem is in the version. As Rajendra and Wolfgang
    have proven, you should upgrade to the version which doesn't know how to merge the query
    and a subquery. May the force be with you.

    --
    Mladen Gogala
    Oracle DBA

    Note:
    This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks.
    Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity.

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
  • Jacques Kilchoer at May 17, 2004 at 7:06 pm
    What do you mean by "SYS.OBJECT_USAGE table cannot be changed,
    apparently including GRANTs."
    Are you signing on as SYSDBA and issuing the command
    "GRANT SELECT ON OBJECT_USAGE TO MYUSER;" ? (you could also grant MYUSER
    the system privilege SELECT ANY DICTIONARY)
    That privilege should allow you to create your own copy of
    V$OBJECT_USAGE in schema MYUSER.

    -----Original Message-----
    Jesse, Rich

    ... Oracle =
    decided to make the V$OBJECT_USAGE view so that a user can only see
    monitored indexes for their own schema

    ...

    the
    SYS.OBJECT_USAGE table cannot be changed, apparently including GRANTs.
    I'd paste the error here, but when I try it again, I get an ORA-600.
    Oracle's solution is to create a new V$ view in SYS. I make it a habit
    to not mess with SYS unless I have to.

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Boris Dali at May 18, 2004 at 7:56 am
    Worked just fine for us before 9.2.0.5

    In 9.2.0.5 "grant select on sys.object_usage to ... "
    for the first time raises ORA-701 and subsequent
    grants throw ORA-600 [17058]

    Support opened bug 3627650 for us, which apparently
    (as in not confirmed yet) can be fixed with the
    one-off patch for bug 2934068

    Thanks,
    Boris Dali.

    "Jesse, Rich"
    wrote: > Hey all,
    ....
    So, I create my own view . I can't because
    the SYS.OBJECT_USAGE table cannot be changed,
    apparently including GRANTs.
    I'd paste the error here, but when I try it again, I
    get an ORA-600. =
    Post your free ad now! http://personals.yahoo.ca

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Jesse, Rich at May 18, 2004 at 9:32 am
    Oracle Support has reproduced the error in 9.2.0.5 on other platforms:

    GRANT SELECT ON sys.object_usage TO my_dba;
    ORA-00701: object necessary for warmstarting database cannot be altered

    After that, ORA-600s galore. Joy.

    I think we need to look at removing the 9.2.0.5 patch...

    Rich

    Rich Jesse System/Database Administrator
    rich.jesse_at_quadtechworld.com QuadTech, Sussex, WI USA
    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org

    Sent: Monday, May 17, 2004 7:09 PM
    To: oracle-l_at_freelists.org
    Subject: RE: V$OBJECT_USAGE view for indexes in all schemas
    =20
    =20
    What do you mean by "SYS.OBJECT_USAGE table cannot be changed,
    apparently including GRANTs."
    Are you signing on as SYSDBA and issuing the command
    "GRANT SELECT ON OBJECT_USAGE TO MYUSER;" ? (you could also=20
    grant MYUSER
    the system privilege SELECT ANY DICTIONARY)
    That privilege should allow you to create your own copy of
    V$OBJECT_USAGE in schema MYUSER.
    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Jesse, Rich at May 18, 2004 at 12:14 pm
    Thanks, Boris! Your bug is so new the Support analyst didn't find it =
    until I redirected him to it (save the comments -- I'm giving him the =
    benefit of the doubt). For now, I've created a "QT_INDEX_USAGE" view in =
    SYS that incorporates all schemas, unlike the Oracle-supplied =
    V$OBJECT_USAGE fixed view. I'm not terribly comfortable with the idea =
    of creating user objects in SYS for a production system, but at least =
    the recommendation for it is in the TAR as well as another MetaLink =
    article, so my CYA is in place.

    Working a TAR with the help of this list makes a big difference! :)

    Thanks again!
    Rich

    Rich Jesse System/Database Administrator
    rich.jesse_at_quadtechworld.com QuadTech, Sussex, WI USA
    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    Sent: Tuesday, May 18, 2004 7:50 AM
    Subject: Re: V$OBJECT_USAGE view for indexes in all schemas
    =20
    =20
    Worked just fine for us before 9.2.0.5
    =20
    In 9.2.0.5 "grant select on sys.object_usage to ... "
    for the first time raises ORA-701 and subsequent
    grants throw ORA-600 [17058]
    =20
    Support opened bug 3627650 for us, which apparently
    (as in not confirmed yet) can be fixed with the
    one-off patch for bug 2934068
    =20
    Thanks,
    Boris Dali.
    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMay 17, '04 at 3:45p
activeMay 18, '04 at 12:14p
posts7
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase