1 2 Previous Next 21 Replies Latest reply: Jul 18, 2009 10:00 AM by Mark Williams-Oracle Go to original post RSS
      • 15. Re: Unable to create public synonym
        Mark Williams-Oracle
        OK, thanks - that was the query from the j000 process trace file - just wanted to make sure it returned correct results (i.e. single row).

        I'd concentrate on the invalid SYS objects mentioned above at this stage... you might also run the "healthcheck" script described in My Oracle Support Note:136697.1 ("hcheck.sql" script to check for known problems in Oracle8i, Oracle9i, Oracle10g and Oracle 11g).

        - Mark
        • 16. Re: Unable to create public synonym
          695781
          Thank you, I will work on it.
          • 17. Re: Unable to create public synonym
            26741
            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)'
            indicates that DUAL returned 2 rows.

            You could query :
            connect / as sysdba
            select * from sys.dual;
            select * from sys.x$dual;
            select synonym_name from dba_synonyms where table_owner = 'SYS' and table_name = 'DUAL';
            Recreating SYS.DUAL may be an option -- but you should log an SR with Oracle Support on that.
            • 18. Re: Unable to create public synonym
              Mark Williams-Oracle
              Hi Hemant,

              I'm not following you on the STAT lines and returning 2 rows.

              In the FETCH line we see this:

              FETCH #1:c=0,e=3237,p=0,cr=3,cu=1,mis=0,r=1,dep=1,og=1,tim=9671106997977

              showing 1 row fetched (emphasis added).

              Can you explain what you mean about the STAT lines and "DUAL returned 2 rows"?

              Thanks,

              Mark
              • 19. Re: Unable to create public synonym
                Aman
                Had u tried the synatax

                create or replace public synonym......
                • 20. Re: Unable to create public synonym
                  26741
                  Mark,

                  See that trace output again :
                  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
                  Cursor#1 is the "select dummy from dual where ora_dict_obj_type = 'SYNONYM' AND ora_dict_obj_owner = 'PUBLIC'" which shows "cnt=2" in the STAT call.

                  Cursor#2 is the "create public synonym et_val for samp1" which I am not concerned about right now.
                  • 21. Re: Unable to create public synonym
                    Mark Williams-Oracle
                    Hi Hemant,

                    Oh, dear. Yes, you are quite right - I don't know why I didn't see that correctly last night. I guess I was so focused on the FETCH #1 line (which is also for the select from dual)... anyway, thank you for correcting/pointing that out. That is definitely something to investigate as you have already pointed out with your suggested queries.

                    - Mark
                    1 2 Previous Next