This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Jul 18, 2009 8:00 AM by Mark Williams-Oracle Go to original post RSS
  • 15. Re: Unable to create public synonym
    Mark Williams-Oracle Employee ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thank you, I will work on it.
  • 17. Re: Unable to create public synonym
    26741 Oracle ACE
    Currently Being Moderated
    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 Employee ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Had u tried the synatax

    create or replace public synonym......
  • 20. Re: Unable to create public synonym
    26741 Oracle ACE
    Currently Being Moderated
    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 Employee ACE
    Currently Being Moderated
    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

Legend

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