Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2015
    Posts
    1

    Unanswered: Privileges for TABLE, GV$ Function

    Hi,

    In Oracle 11.2.0.3 I have created a user and given select permissions on V$TIMER, V$ACTIVE_SESSION_HISTORY.

    But, when I execute the below query (This query is generated from DBMS_SQLTUNE) it throws an error saying ORA-00942 table or view does not exist. Only objects that are used in the below query are

    1. V$TIMER
    2. V$ACTIVE_SESSION_HISTORY
    3. TABLE function
    4. GV$ Undocumented function (Oracle executes the query inside GV$ function on all the instances of the object).

    Below is the query.

    SELECT * FROM TABLE(GV$(CURSOR((SELECT USERENV('INSTANCE') INST_ID FROM
    (SELECT * FROM ( SELECT (CASE WHEN :B24 > 1 THEN 0 ELSE 1 END) BUCKET_NUM FROM
    (SELECT 1 C1, NULL C2 FROM V$TIMER UNION ALL SELECT 1 C1,1 C2 FROM V$TIMER) DUP,
    (SELECT /*+ no_merge */ 1 C1,ASH000.WAIT_CLASS, ASH000.QC_INSTANCE_ID INSTANCE_ID FROM (SELECT ASH0.* FROM
    (SELECT * FROM V$ACTIVE_SESSION_HISTORY ASH ) ASH0 ) ASH000 ) ASH00 ) ASH1
    GROUP BY USERENV('INSTANCE'),ASH1.BUCKET_NUM) ASH2)))) GVTF ;


    Is there any privileges that I need to give?

    The same sql gets executed without any issues if i give "SELECT ANY DICTIONARY" privilege to the user but can't give those permissions to that user.

    Can you help me?

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down

    Quote Originally Posted by kirankumarpv View Post
    Hi,
    . . . e t c
    The same sql gets executed without any issues if i give "SELECT ANY DICTIONARY" privilege to the user but can't give those permissions to that user.
    Why not?
    There is nothing in the GV$ and V$ tables that needs to be hidden.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •