describe on remote synonym fails
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: describe on remote synonym fails

  1. #1
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233

    describe on remote synonym fails

    Hi guys,

    We've built on a remote database special user with synonym to a table of the main schema in the remote database.
    In the current database,we've built a remote synonym to the synonym of the special remote database's user.
    The strane thing is :

    SQL> desc VW_HADAS_KABLANIM;
    ERROR:
    ORA-04043: object OVDIM.VW_HADAS_KABLANIM does not exist

    SQL> select count (*) from VW_HADAS_KABLANIM;

    COUNT(*)
    ----------
    148


    DESCRIBE fails and regular select works.

    Does anyone face with this kind of problem?

    Both databases are 8.1.7.3 on HP-UX.

    Regards,

    Nir

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Are the 2 statements executed under same user?

    If not therz permission issue.

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    Both statements are issued by the same user!

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by nir_s
    Both statements are issued by the same user!
    Again Oracle going crazy.

    I hope not, guess some moderators here give some light on this.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    Surely,it is a big mystery.....
    May be it's a bug in sql plus.

  6. #6
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    what is the output of

    select owner,object_name,object_type from al_objects where object_name like 'VW_HADAS_KABL';

    Run this as sys user.
    Last edited by adewri; 04-14-2003 at 05:54 AM.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by adewri
    what is the output of

    select owner,object_name,object_type from al_objects where object_name like 'VW_HADAS_KABL';

    Run this as sys user.
    Amar :

    See his post

    In the current database,we've built a remote synonym to the synonym of the special remote database's user.
    so o/p would be public(if defined so), 'VW_HADAS_KABL', Synonym.

    Rather you could have asked for

    Select * from dba_synonyms where synonym_name like 'VW_HADAS_KABL'

    or rather u really wanted to know the owner and type of object

    u could try somthing like this ( if its DB Link )

    select owner,object_name,object_type from all_objects@db_link_name where object_name='VW_HADAS_KABL';


    Thanks
    Abhay.
    Last edited by abhaysk; 04-14-2003 at 06:07 AM.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    The query as user "HADAS", the owner of the remote synonym:

    SQL> select owner,object_name,object_type from all_objects@ovdim
    2 where object_name like 'VW_HADAS_KABL%';
    OWNER OBJECT_NAME OBJECT_TYPE
    ------------------------------ ------------------------------ ------------------
    OVDIM VW_HADAS_KABLANIM VIEW
    OVDIM_HADAS VW_HADAS_KABLANIM SYNONYM

    The query ,as user sys:
    SQL> show user
    USER is "SYS"
    SQL>
    SQL> select owner,object_name,object_type from all_objects
    where object_name like 'VW_HADAS_KABL';


    OWNER OBJECT_NAME OBJECT_TYPE
    ------------------------------ ------------------------------ ------------------
    HADAS VW_HADAS_KABLANIM SYNONYM


    As user HADAS:
    SQL> show user
    USER is "HADAS"
    SQL> desc VW_HADAS_KABLANIM
    ERROR:
    ORA-02019: connection description for remote database not found


    SQL> select count (*) from VW_HADAS_KABLANIM ;

    COUNT(*)
    ----------
    158

    SQL> select
    SYNONYM_NAME,TABLE_OWNER ,TABLE_NAME ,DB_LINK
    2 from user_synonyms
    3 where SYNONYM_NAME ='VW_HADAS_KABLANIM';

    SYNONYM_NAME TABLE TABLE_NAME DB_LINK
    ------------------------------ ----- ---------------------- ----------
    VW_HADAS_KABLANIM VW_HADAS_KABLANIM OVDIM

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Now this is again crazy
    First post
    SQL> desc VW_HADAS_KABLANIM;
    ERROR:
    ORA-04043: object OVDIM.VW_HADAS_KABLANIM does not exist
    second post
    SQL> desc VW_HADAS_KABLANIM
    ERROR:
    ORA-02019: connection description for remote database not found
    All i can say is drop the synonym in current DB and also DB link

    and recreate the sysnonym as public and also the DB link as public.

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  10. #10
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    Still does not working:

    SQL> connect system@hp64_dev1
    Enter password: *****
    Connected.

    SQL> drop synonym HADAS.VW_HADAS_KABLANIM;

    Synonym dropped.



    SQL> connect hadas/hadas@hp64_dev1
    Connected.
    SQL> drop database link OVDIM.WORLD;

    Database link dropped.

    SQL> drop database link OVDIM;

    Database link dropped.

    SQL> connect system@hp64_dev1
    Enter password: *****
    Connected.

    SQL> create public database link ovdim_hadas
    2 connect to OVDIM_HADAS identified by OVDIM_HADAS using 'hp65_dwprod1';

    Database link created.

    SQL> select count (*) from user_objects@OVDIM_HADAS;

    COUNT(*)
    ----------
    12

    SQL> create public synonym VW_HADAS_KABLANIM for VW_HADAS_KABLANIM@OVDIM_HADAS;

    Synonym created.

    SQL> show user
    USER is "SYSTEM"
    SQL> select count (*) from VW_HADAS_KABLANIM;

    COUNT(*)
    ----------
    158

    SQL> desc VW_HADAS_KABLANIM;
    ERROR:
    ORA-02019: connection description for remote database not found

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width