This discussion is archived
12 Replies Latest reply: Jul 7, 2013 6:39 PM by sb92075 RSS

Compiling a view

yxes2013 Newbie
Currently Being Moderated

Hi all,

 

11gR2,

 

I am creating a view but it says:

 

Warning: View created with compilation errors.

 

SQL> show error

No errors.

SQL>

 

It did not display any error hint ?

 

How can I debug a view error?

 

Thanks a lot,

 

zxy

  • 1. Re: Compiling a view
    Karthick_Arp Guru
    Currently Being Moderated


    query USER_ERRORS

     

    select *

      from user_errors

    where type = 'VIEW'

       and name = <your view name>;

  • 2. Re: Compiling a view
    sybrand_b Guru
    Currently Being Moderated

    By Reading The Fabulous Manual?

     

    ----------

    Sybrand Bakker

    Senior Oracle DBA

  • 3. Re: Compiling a view
    34MCA2K2 Journeyer
    Currently Being Moderated

    First step I would do is execute the query of the View to check if there are any errors, then if it is running fine, you can query user_errors as suggested by Karthick.

  • 4. Re: Compiling a view
    yxes2013 Newbie
    Currently Being Moderated

    Hi all,

     

    It seems I am hit by  this DATABASE VAULT constraints making my views getting errors

    I am confused about this database vault to make it harder its coupled with oracle wallet and ASO-TDO (advance security option - transparent data encryption)

     

    Anyone here knows Vault ?

     

    1. I created a "hr" realm

    2. I added "hr" at realm secured objects, as the table owner.

    3. I added "hr" at realm authorizations as owner.

    4. I added "scott" at realm authorizations as participants.

     

    I granted select on the table HR.EMP to SCOTT.

    At user SCOTT> I created synonym SCOTT.EMP for HR.EMP.

    When logged in as SCOTT, I can select the synonym EMP.

    But when I created a view against this synonym EMP, i got ora-1031 error

     

    Please help....

  • 5. Re: Compiling a view
    sb92075 Guru
    Currently Being Moderated

    too bad for us that Copy & Paste are broken for you.

  • 6. Re: Compiling a view
    yxes2013 Newbie
    Currently Being Moderated

    What should I copy & paste

     

    Login as scott:

    Even if I test : create view xxx as select * from emp;  (emp is a synonym from hr.emp)

     

    I got ora-1031 insufficient privileges

     

    Thanks

  • 7. Re: Compiling a view
    sb92075 Guru
    Currently Being Moderated

    how can we reproduce what you report?

  • 8. Re: Compiling a view
    yxes2013 Newbie
    Currently Being Moderated

    Hi Sb,

     

    You can install database vault, it is available in 11gR2 enterprise edition

  • 9. Re: Compiling a view
    DK2010 Guru
    Currently Being Moderated

    Hi,

     

    I am creating a view but it says:

     

    Warning: View created with compilation errors.

     

    SQL> show error

    No errors.

    SQL>

    Are you trying to create Force View ?. then this warning is fine.

     

    Login as scott:

    Even if I test : create view xxx as select * from emp;  (emp is a synonym from hr.emp)

     

    I got ora-1031 insufficient privileges

    user do not have permission to create view. grant create view and then try.

    or

    As sb92075 said copy paste all the command  what you tried on your sql prompt.

  • 10. Re: Compiling a view
    yxes2013 Newbie
    Currently Being Moderated

    Thanks

     

    Before I installed the db vault the user can create a view, after I installed the user ca not anymore.... maybe I need to regrant it using the dvacctmgr or dvowner.

  • 11. Re: Compiling a view
    yxes2013 Newbie
    Currently Being Moderated

    Hi Dk,

     

    I already granted scott a "create any  view" privilege but still same error

     

    Can you help please. Try to install db vault on your test server or laptop using this docs

     

    Disabling and Enabling Oracle&amp;nbsp;Database&amp;nbsp;Vault

     

    1.And then grant select on hr.emp to scott;

    2.The create a synoym scott.emp for hr.emp;

    3.Then create view scott.emp_vew for scott.emp;

     

    Thanks

  • 12. Re: Compiling a view
    sb92075 Guru
    Currently Being Moderated

    Alternatively you could do as below to see if the error continues after Data Vault has been shutdown

     

    dvca -action disable

      -oh Oracle_home_directory

      -service service_name

      -instance Oracle_instance_name

      -dbname database_name

      -sys_passwd SYS_password

      -owner_account DV_owner_account_name

      -owner_passwd DV_owner_account_password

      [-logfile ./dvca.log]

      [-nodecrypt]

      [-racnode node]

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points