12 Replies Latest reply: Jul 7, 2013 8:39 PM by sb92075 RSS

    Compiling a view

    yxes2013

      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


          query USER_ERRORS

           

          select *

            from user_errors

          where type = 'VIEW'

             and name = <your view name>;

          • 2. Re: Compiling a view
            sybrand_b

            By Reading The Fabulous Manual?

             

            ----------

            Sybrand Bakker

            Senior Oracle DBA

            • 3. Re: Compiling a view
              34MCA2K2

              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

                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

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

                  • 6. Re: Compiling a view
                    yxes2013

                    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

                      how can we reproduce what you report?

                      • 8. Re: Compiling a view
                        yxes2013

                        Hi Sb,

                         

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

                        • 9. Re: Compiling a view
                          DK2010

                          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

                            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

                              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

                                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]