1 2 Previous Next 21 Replies Latest reply on Jul 18, 2009 3:00 PM by Mark Williams-Oracle

    Unable to create public synonym

    695781
      Unable to create public synonym for a temporary table of schema sample1

      create public synonym syn1 for sample1l.synval;

      ORA-00604: error occurred at recursive SQL level 1
      ORA-01422: exact fetch returns more than requested number of rows

      Please help on this issue
        • 1. Re: Unable to create public synonym
          damorgan
          sample1l.synval;
          Isn't that a single vertical bar in there?
          • 2. Re: Unable to create public synonym
            695781
            sorry typo error no bar there
            • 3. Re: Unable to create public synonym
              26741
              Do a SELECT COUNT(*) FROM DUAL to confirm that DUAL has only 1 row ('x')
              • 4. Re: Unable to create public synonym
                695781
                Hi,


                There si only 1 row. please see the output

                SQL> SELECT COUNT(*) FROM DUAL ;

                COUNT(*)
                ----------
                1

                SQL> desc dual;
                Name Null? Type
                ----------------------------------------- -------- ----------------------------
                DUMMY VARCHAR2(1)
                • 5. Re: Unable to create public synonym
                  damorgan
                  You just eliminated the most obvious possible issue. Now it gets more interesting.

                  Run the following and post the results:
                  SELECT * FROM v$version;
                  
                  SELECT comp_name, version, status
                  FROM dba_registry;
                  
                  SELECT owner, object_type, COUNT(*)
                  FROM dba_objects
                  WHERE status = 'INVALID'
                  GROUP BY owner, object_type
                  Is this a production system, dev or test?

                  Scan the alert log for anything beginning with "ORA-"
                  • 6. Re: Unable to create public synonym
                    Mark Williams-Oracle
                    Hi,

                    In addition to the other suggestions that have been given, one thing that I have found helpful in the past for these sorts of issues is to enable extended sql trace and then perform the failing action.

                    Something like the following from within SQL*Plus:

                    /* enable extended sql trace at level 4 (binds) */
                    alter session set events '10046 trace name context forever, level 4';

                    <perform the action>

                    exit SQL*Plus

                    This means you would need to have the privilege to alter your session and then have access to the trace file in the user_dump_destination - but if you are creating a public synonym you may also already have the necessary privilege for this as well. If not, coordinate with your DBA. The trace file should have the exact statement that caused the error to be raised.

                    Regards,

                    Mark

                    EDIT1: in the trace file you should find something similar to:

                    ERROR #5:err=1422

                    Where the "5" would be replaced by your cursor number. Searching for "ERROR" or "err=1422" should locate the problem statement.

                    Edited by: Mark Williams on Jul 17, 2009 1:48 PM
                    • 7. Re: Unable to create public synonym
                      695781
                      Hi This is a dev system. we get the folowing ora error in alert log

                      Errors in file /opt/oracle/product/rdbms/admin/UBASE/bdump/ubase1_j000_22472.trc:
                      ORA-01422: exact fetch returns more than requested number of rows


                      SQL> SELECT * FROM v$version;

                      BANNER
                      ----------------------------------------------------------------
                      Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
                      PL/SQL Release 10.2.0.4.0 - Production
                      CORE 10.2.0.4.0 Production
                      TNS for HPUX: Version 10.2.0.4.0 - Production
                      NLSRTL Version 10.2.0.4.0 - Production


                      SQL> SELECT comp_name, version, status
                      FROM dba_registry;


                      COMP_NAME
                      --------------------------------------------------------------------------------
                      VERSION STATUS
                      ------------------------------ -----------
                      Oracle Enterprise Manager
                      10.2.0.4.0 VALID

                      Spatial
                      10.2.0.4.0 VALID

                      Oracle interMedia
                      10.2.0.4.0 VALID


                      COMP_NAME
                      --------------------------------------------------------------------------------
                      VERSION STATUS
                      ------------------------------ -----------
                      OLAP Catalog
                      10.2.0.4.0 VALID

                      Oracle XML Database
                      10.2.0.4.0 VALID

                      Oracle Text
                      10.2.0.4.0 VALID


                      COMP_NAME
                      --------------------------------------------------------------------------------
                      VERSION STATUS
                      ------------------------------ -----------
                      Oracle Expression Filter
                      10.2.0.4.0 VALID

                      Oracle Rule Manager
                      10.2.0.4.0 VALID

                      Oracle Workspace Manager
                      10.2.0.4.3 VALID


                      COMP_NAME
                      --------------------------------------------------------------------------------
                      VERSION STATUS
                      ------------------------------ -----------
                      Oracle Data Mining
                      10.2.0.4.0 VALID

                      Oracle Database Catalog Views
                      10.2.0.4.0 VALID

                      Oracle Database Packages and Types
                      10.2.0.4.0 VALID


                      COMP_NAME
                      --------------------------------------------------------------------------------
                      VERSION STATUS
                      ------------------------------ -----------
                      JServer JAVA Virtual Machine
                      10.2.0.4.0 VALID

                      Oracle XDK
                      10.2.0.4.0 VALID

                      Oracle Database Java Packages
                      10.2.0.4.0 VALID


                      COMP_NAME
                      --------------------------------------------------------------------------------
                      VERSION STATUS
                      ------------------------------ -----------
                      OLAP Analytic Workspace
                      10.2.0.4.0 VALID

                      Oracle OLAP API
                      10.2.0.4.0 VALID

                      Oracle Real Application Clusters
                      10.2.0.4.0 VALID


                      18 rows selected.


                      SELECT owner, object_type, COUNT(*)
                      FROM dba_objects
                      WHERE status = 'INVALID'
                      GROUP BY owner, object_type
                      ;

                      OWNER OBJECT_TYPE COUNT(*)
                      ------------------------------ ------------------- ----------
                      SYS VIEW 2
                      UTIBISRC PACKAGE 1
                      UAT PACKAGE BODY 5
                      ORA_PARTITION PACKAGE BODY 7
                      UAT FUNCTION 1
                      SYS EVALUATION CONTEXT 1
                      UAT VIEW 1
                      SYS TABLE 8
                      DMADM PACKAGE BODY 1
                      SYSTEM PROCEDURE 1
                      OWF_MGR SYNONYM 1

                      OWNER OBJECT_TYPE COUNT(*)
                      ------------------------------ ------------------- ----------
                      UCONNECT SYNONYM 1
                      ORA_PARTITION TYPE 4
                      UWEB PROCEDURE 2
                      UAT TRIGGER 1
                      PUBLIC SYNONYM 2
                      UAT PROCEDURE 4
                      SYS QUEUE 1
                      • 8. Re: Unable to create public synonym
                        695781
                        Here is the trace file content

                        Cursor#7(800003ffefdc1980) state=BOUND curiob=800003ffef9a10b0
                        curflg=cf fl2=0 par=0000000000000000 ses=c00000009f6a4a68
                        sqltxt(c0000000941f7c18)=SELECT KSPPCV.KSPPSTVL FROM X$KSPPCV KSPPCV, X$KSPPI KSPPI WHERE KSPPI.INDX = KSPPCV.INDX AND KSPPI
                        .KSPPINM = 'db_block_size'
                        hash=ec0e4e2b4ead3d37078ec8f05cd6b91c
                        parent=c000000096101bc8 maxchild=01 plk=c000000097c89058 ppn=n
                        cursor instantiation=800003ffef9a10b0 used=1247781634
                        child#0(c0000000941a0c68) pcs=c0000000961017d8
                        clk=c000000097ca0278 ci=c000000096100ec0 pn=0000000000000000 ctx=c000000091bb60a0
                        kgsccflg=0 llk[800003ffef9a10b8,800003ffef9a10b8] idx=0
                        xscflg=80141036 fl2=44000001 fl3=208208c fl4=0
                        Frames pfr 800003ffef9a1048 siz=4384 efr 800003ffef9a0f90 siz=4352
                        Cursor frame dump
                        enxt: 3.0x00000010
                        pnxt: 2.0x00000010 pnxt: 1.0x00000010
                        kxscphp 800003ffefd22728 siz=984 inu=504 nps=504
                        kxscdfhp 800003ffefd22638 siz=984 inu=88 nps=0
                        ----------------------------------------
                        Cursor#10(800003ffefdc1ab8) state=BOUND curiob=800003ffef9a0560
                        curflg=cd fl2=0 par=0000000000000000 ses=c00000009f6a4a68
                        sqltxt(c0000000941f4618)=
                        • 9. Re: Unable to create public synonym
                          Mark Williams-Oracle
                          That does not look like the correct trace file - a 10046 trace file looks different from that. Are you sure that is the correct file?

                          - Mark
                          • 10. Re: Unable to create public synonym
                            695781
                            Hi, I once again rechecked and found that I have added the correct trace file.. Again pasting below for ur reference.Thank you

                            vi /opt/oracle/product/rdbms/admin/UBASE/bdump/ubase1_j000_22472.trc

                            /opt/oracle/product/rdbms/admin/UBASE/bdump/ubase1_j000_22472.trc" Line too long


                            Cursor#7(800003ffefdc1980) state=BOUND curiob=800003ffef9a10b0
                            curflg=cf fl2=0 par=0000000000000000 ses=c00000009f6a4a68
                            sqltxt(c0000000941f7c18)=SELECT KSPPCV.KSPPSTVL FROM X$KSPPCV KSPPCV, X$KSPPI KSPPI WHERE KSPPI.INDX = KSPPCV.INDX AND KSPPI
                            .KSPPINM = 'db_block_size'
                            hash=ec0e4e2b4ead3d37078ec8f05cd6b91c
                            parent=c000000096101bc8 maxchild=01 plk=c000000097c89058 ppn=n
                            cursor instantiation=800003ffef9a10b0 used=1247781634
                            child#0(c0000000941a0c68) pcs=c0000000961017d8
                            clk=c000000097ca0278 ci=c000000096100ec0 pn=0000000000000000 ctx=c000000091bb60a0
                            kgsccflg=0 llk[800003ffef9a10b8,800003ffef9a10b8] idx=0
                            xscflg=80141036 fl2=44000001 fl3=208208c fl4=0
                            Frames pfr 800003ffef9a1048 siz=4384 efr 800003ffef9a0f90 siz=4352
                            Cursor frame dump
                            enxt: 3.0x00000010
                            pnxt: 2.0x00000010 pnxt: 1.0x00000010
                            kxscphp 800003ffefd22728 siz=984 inu=504 nps=504
                            kxscdfhp 800003ffefd22638 siz=984 inu=88 nps=0
                            ----------------------------------------
                            Cursor#10(800003ffefdc1ab8) state=BOUND curiob=800003ffef9a0560
                            curflg=cd fl2=0 par=0000000000000000 ses=c00000009f6a4a68
                            sqltxt(c0000000941f4618)=
                            • 11. Re: Unable to create public synonym
                              Mark Williams-Oracle
                              /opt/oracle/product/rdbms/admin/UBASE/bdump/ubase1_j000_22472.trc

                              That is not the file that the 10046 trace would produce. The 10046 trace will create a file in the user_dump_destination as I noted above. Please check that directory on the server that hosts the instance to which you connected. You should find the file there and it should have the word "ERROR" or "err=" to make it easier to find if you want to grep the files or similar.

                              Of course you would have to perform the steps I listed above to create the file first.

                              Regards,

                              Mark
                              • 12. Re: Unable to create public synonym
                                695781
                                I got the trace file with err as suggested by you. please see below:

                                /opt/oracle/product/rdbms/admin/UBASE/udump/ubase1_ora_7850.trc
                                Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
                                With the Partitioning, Real Application Clusters, OLAP, Data Mining
                                and Real Application Testing options
                                ORACLE_HOME = /opt/oracle/product/rdbms/10.2.0/db_1
                                System name: HP-UX
                                Node name: devdb1
                                Release: B.11.23
                                Version: U
                                Machine: 9000/800
                                Instance name: UBASE1
                                Redo thread mounted by this instance: 1
                                Oracle process number: 51
                                Unix process pid: 7850, image: oracle@devdb1 (TNS V1-V3)

                                *** 2009-07-17 20:34:48.997
                                *** ACTION NAME:() 2009-07-17 20:34:48.996
                                *** MODULE NAME:(SQL*Plus) 2009-07-17 20:34:48.996
                                *** SERVICE NAME:(SYS$USERS) 2009-07-17 20:34:48.996
                                *** SESSION ID:(286.2232) 2009-07-17 20:34:48.996
                                XCTEND rlbk=0, rd_only=1
                                =====================
                                PARSING IN CURSOR #2 len=38 dep=0 uid=135 oct=19 lid=135 tim=9671106968989 hv=0 ad='efd9fc08'
                                create public synonym et_val for samp1
                                END OF STMT
                                PARSE #2:c=0,e=2788,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=9671106966187
                                BINDS #2:
                                =====================
                                PARSING IN CURSOR #1 len=94 dep=1 uid=46 oct=3 lid=46 tim=9671106991934 hv=1451648271 ad='9e0d8f88'
                                select dummy from dual where ora_dict_obj_type = 'SYNONYM' AND ora_dict_obj_owner = 'PUBLIC'
                                END OF STMT
                                PARSE #1:c=10000,e=11427,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=9671106991921
                                BINDS #1:
                                EXEC #1:c=0,e=2652,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=9671106994695
                                FETCH #1:c=0,e=3237,p=0,cr=3,cu=1,mis=0,r=1,dep=1,og=1,tim=9671106997977
                                EXEC #2:c=10000,e=22788,p=0,cr=3,cu=2,mis=0,r=0,dep=0,og=1,tim=9671107002555
                                ERROR #2:err=604 tim=990321357
                                STAT #1 id=1 cnt=2 pid=0 pos=1 obj=0 op='FILTER (cr=3 pr=0 pw=0 time=3715 us)'
                                STAT #1 id=2 cnt=2 pid=1 pos=1 obj=258 op='TABLE ACCESS FULL DUAL (cr=3 pr=0 pw=0 time=165 us)'
                                *** 2009-07-17 20:35:19.726
                                XCTEND rlbk=0, rd_only=1
                                • 13. Re: Unable to create public synonym
                                  Mark Williams-Oracle
                                  From the 10046 trace we see that the query against dual executed OK and returned 1 row (r=1 in the FETCH line).

                                  However, the parent statement (the create synonym) failed with:

                                  ERROR #2:err=604

                                  From the documentation:

                                  ORA-00604: error occurred at recursive SQL level string
                                  Cause: An error occurred while processing a recursive SQL statement (a statement applying to internal dictionary tables).
                                  Action: If the situation described in the next error on the stack can be corrected, do so; otherwise contact Oracle Support.


                                  Out of curiosity what does the following query (executed as SYSDBA user) produce:

                                  SELECT KSPPCV.KSPPSTVL FROM X$KSPPCV KSPPCV, X$KSPPI KSPPI WHERE KSPPI.INDX = KSPPCV.INDX AND KSPPI.KSPPINM = 'db_block_size';

                                  Also, in the output from the queries Dan suggested, you have invalid objects owned by SYS - can you verify what those objects are?

                                  - Mark
                                  • 14. Re: Unable to create public synonym
                                    695781
                                    Hello ,

                                    This is the following output produced

                                    SQL> SELECT KSPPCV.KSPPSTVL FROM X$KSPPCV KSPPCV, X$KSPPI KSPPI WHERE KSPPI.INDX = KSPPCV.INDX AND KSPPI.KSPPINM = 'db_block_size';

                                    KSPPSTVL
                                    --------------------------------------------------------------------------------
                                    8192
                                    1 2 Previous Next