-
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"
-
Thanks!
I'll check it tommorow again and i'll let you know.
Regards,
Nir
-
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
-
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?
-
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"
-
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??
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|