ORA-01039: insufficient privileges on underlying objects of the view
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: ORA-01039: insufficient privileges on underlying objects of the view

  1. #1
    Join Date
    Jul 2000
    Location
    Pune, India
    Posts
    80

    ORA-01039: insufficient privileges on underlying objects of the view

    Hello,

    We have written function which is used in our application to check cost of queries. Although not helpful, but application has passed queries on data dictionary to this function and this has raised error "ORA-01039: insufficient privileges on underlying objects of the view". Our user has required privileges on view DBA_TAB_COLUMNS, on oracle 8i it was working, but when we migrated from 8i to 10g it is giving this error. In USER_TAB_PRIVS following privileges exist

    On 8i: INSERT, UPDATE, DELETE, SELECT
    On 10g: INSERT, UPDATE, DELETE, SELECT, REFERENCES, ON COMMIT REFRESH, FLASHBACK, DEBUG, QUERY REWRITE

    Query and sample function code given below please help us to solve this issue.

    SELECT Getcost('SELECT * FROM SYS.Dba_Tab_Columns WHERE Table_Name = ''TABLENAME''') FROM Dual;

    -----------------------------------------------------
    FUNCTION GetCost(Sqltext In Varchar2) RETURN NUMBER is
    PRAGMA AUTONOMOUS_TRANSACTION;
    nCost NUMBER;
    cSeqName Varchar2(30) := NULL;
    Stmt Varchar2 (4000) := NULL;

    Type Cur1 IS REF Cursor;
    C1 Cur1;
    Begin

    Execute Immediate 'SELECT SeqExplainPlan.Nextval FROM Dual' Into nPlanVal;

    Name := 'I1PLAN'||nPlanVal;

    Stmt:= 'Explain Plan Set Statement_Id=' ;
    Stmt:= Stmt||''''||Name||''''||' For '||Sqltext;
    Execute Immediate Stmt;
    Commit;

    Open C1 for
    SELECT Cost
    FROM Plan_Table
    WHERE Statement_Id = Name
    AND Id = 0;
    Fetch C1 into nCost ;
    Close C1;
    RETURN nCost;

    End;
    /


    Thanks & Regards,

    Shailesh
    Share on Google+

  2. #2
    Join Date
    Jul 2000
    Location
    Pune, India
    Posts
    80

    Thumbs up

    GRANT SELECT ANY DICTIONARY TO ;

    has solved my problem.

    Regards,

    Shailesh
    Share on Google+

Posting Permissions

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


Click Here to Expand Forum to Full Width