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

Thread: describe on remote synonym fails

  1. #11
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by nir_s
    Still does not working:
    SQL> desc VW_HADAS_KABLANIM;
    ERROR:
    ORA-02019: connection description for remote database not found
    I got the reason why its behaving like this. PS below demo
    Code:
    SQL> alter table emp3 rename to emp;
    
    Table altered.
    
    SQL> select * from dba_synonyms where synonym_name='TEST_DESC'; -- Synonym Test_Desc created as test_desc@oemrep
    
    OWNER                          SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME                     DB_LINK
    ------------------------------ ------------------------------ ------------------------------ ------------------------------ -------------------------
    PUBLIC                         TEST_DESC                                                     TEST_DESC                      OEMREP
    
    SQL> desc emp -- Current DB
     Name                                                  Null?    Type
     ----------------------------------------------------- -------- ------------------------------------
     ID                                                             NUMBER
     EMP_NAME                                                       VARCHAR2(11)
     ADDRESS                                                        VARCHAR2(100)
     NICK_NAME                                                      VARCHAR2(15)
    
    SQL> desc test_desc -- this is describing current DB's emp table ( but table name taking from remote DB )
     Name                                                  Null?    Type
     ----------------------------------------------------- -------- ------------------------------------
     ID                                                             NUMBER
     EMP_NAME                                                       VARCHAR2(11)
     ADDRESS                                                        VARCHAR2(100)
     NICK_NAME                                                      VARCHAR2(15)
    
    SQL> select * from dba_synonyms@oemrep where synonym_name='TEST_DESC'; -- Remote DB's synonym Test_Desc
    
    OWNER                          SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME                     DB_LINK
    ------------------------------ ------------------------------ ------------------------------ ------------------------------ -------------------------
    SCOTT                          TEST_DESC                      SCOTT                          EMP
    
    SQL> desc test_desc@oemrep -- This will give you correct description coz u r using @
     Name                                                                                                                                                   
     ----------------------------------------------------------------------------------------------------------------------------------------------------
     EMPNO                                                                                                                                                  
     ENAME                                                                                                                                                  
     JOB                                                                                                                                                    
     MGR                                                                                                                                                    
     HIREDATE                                                                                                                                               
     SAL                                                                                                                                                    
     COMM                                                                                                                                                   
     DEPTNO                                                                                                                                                 
    
    SQL> desc emp@oemrep -- Table on remote DB.
     Name                                                                                                                                                   
     ----------------------------------------------------------------------------------------------------------------------------------------------------
     EMPNO                                                                                                                                                  
     ENAME                                                                                                                                                  
     JOB                                                                                                                                                    
     MGR                                                                                                                                                    
     HIREDATE                                                                                                                                               
     SAL                                                                                                                                                    
     COMM                                                                                                                                                   
     DEPTNO           
    
    SQL> select count(*) from emp; -- Current DB count
    
                COUNT(*)
    --------------------
                 1000001
    
    SQL> select count(*) from test_desc; -- Giving correct results as that of remote DB.
    
                COUNT(*)
    --------------------
                      14
                                 
    
    SQL> select count(*) from emp@oemrep;
    
                COUNT(*)
    --------------------
                      14
    
    SQL> alter table emp rename to emp3;
    
    Table altered.
    
    -- Now i desc the synonym
    
    SQL> desc test_desc 
    
    -- Other oracle DB goes down...saying windows has generated errors for Oracle.exe.
    Actually Desc is taking the table name from the datadictionary of the remote DB and describing the current DB table.

    Atleast U r lucky u r not getting other DB down as in my case....

    regards
    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"

  2. #12
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    Thanks!

    I'll check it tommorow again and i'll let you know.

    Regards,

    Nir

  3. #13
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    Hi,

    Unfortunately,it still does not work!!

    The current DB
    ---------------
    SQL> select * from dba_synonyms
    2 where SYNONYM_NAME='VW_HADAS_KURSIM';
    OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
    ---------- --------------- --------------- --------------- -----------
    HADAS VW_HADAS_KURSIM VW_HADAS_KURSIM OVDIM
    PUBLIC VW_HADAS_KURSIM VW_HADAS_KURSIM OVDIM_HADAS

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

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

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


    SQL> select count (*) from VW_HADAS_KURSIM;

    COUNT(*)
    ----------
    97918

    SQL> desc VW_HADAS_KURSIM@OVDIM_HADAS;
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    ZEUT NOT NULL NUMBER(9)
    KOURSE NOT NULL NUMBER(3)
    MITAARICH NOT NULL DATE
    ADTAARICH DATE
    KOD_MOSAD NUMBER(3)
    TEUR_KOURSE VARCHAR2(15)
    TEUR_MOSAD VARCHAR2(15)
    KOD_AGAF NUMBER(2)

    The DESC works only with the adding of the db link!

    The remote DB
    --------------
    SQL> select * from dba_synonyms
    2 where SYNONYM_NAME like 'VW_HADAS_KUR%';
    OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
    ----------- --------------- --------------- --------------- -----------
    KAMPUS VW_HADAS_KURSIM OVDIM VW_HADAS_KURSIM
    OVDIM_WEB VW_HADAS_KURSIM OVDIM VW_HADAS_KURSIM
    OVDIM_BO VW_HADAS_KURSIM OVDIM VW_HADAS_KURSIM
    OVDIM_HADAS VW_HADAS_KURSIM OVDIM VW_HADAS_KURSIM

    SQL> connect OVDIM_HADAS/OVDIM_HADAS
    Connected.
    SQL> desc VW_HADAS_KURSIM
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    ZEUT NOT NULL NUMBER(9)
    KOURSE NOT NULL NUMBER(3)
    MITAARICH NOT NULL DATE
    ADTAARICH DATE
    KOD_MOSAD NUMBER(3)
    TEUR_KOURSE VARCHAR2(15)
    TEUR_MOSAD VARCHAR2(15)
    KOD_AGAF NUMBER(2)

    SQL> select count (*) from VW_HADAS_KURSIM;

    COUNT(*)
    ----------
    97918

    Regards,
    Nir

  4. #14
    Join Date
    Nov 2002
    Posts
    25
    Which user are you doing the DESC as on the local DB? Could it be that you are doing it as the user HADAS and so using the private synonym instead of the PUBLIC synonym? If so does the OVDIM database link exists?

  5. #15
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Nir :

    the object VW_HADAS_KURSIM is a synonym to the view VW_HADAS_KURSIM right? ( remote DB )

    Now wat is the underlying table for the view VW_HADAS_KURSIM ? ( remote DB )

    Build same type of structure (inculding View) on current DB with the user where you created DB Link.

    Then let me know.

    Thanks
    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"

  6. #16
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    Hi abhaysk,

    The mystery is still huge:

    The current hostname is hp64
    The current db is dev1

    I've created a demo user in another db (dev2) on the same host.
    Under the domo user i've created the same synonym and the same db link.
    And.... DESC works fine!!

    So,maybe there is a problem with dev1 db?

    I've checked several suspicious init*.ora parameters which appear in dev1 and not in dev2:
    db_domain = world
    service_names = dev1.world
    remote_os_authent = true

    I've changed them as dev2 , but the problem is still valid!

    I've also created the demo user on another host on another db and the things work fine.

    Mistery,isn't it??

  7. #17
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Nir :

    I will giv up.

    Might be mistery...or might be something we are missing.

    Well in my case it was describing the current DB table structure & more interestingly if current DB did not have Table name as that of Remote one then Oracle.exe of Remote DB would geterate errors and Windows will kill that process, hence shuting down remote DB ( Its more Stranger(Misterious) than Urs ).

    Might be Moderators(Pando,Marist89,Jmodic,ChisLong) can throw some light.

    Thanks
    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"

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