nobleit
asked on
created dblink
sys has " new " table
scott also has a table name "new"
SQL> CREATE DATABASE LINK dblink2 CONNECT TO sys IDENTIFIED BY sys USING 'dbd95078/orcl';
Database link created.
SQL> select name from v$database;
NAME
---------
JTAC
SQL> select * from new@db_link2;
select * from new@db_link2
*
ERROR at line 1:
ORA-02019: connection description for remote database not found
SQL> CREATE DATABASE LINK dblink2 CONNECT TO scott IDENTIFIED BY tiger USING 'dbd95078/orcl';
CREATE DATABASE LINK dblink2 CONNECT TO scott IDENTIFIED BY tiger USING 'dbd95078/orcl'
*
ERROR at line 1:
ORA-02011: duplicate database link name
SQL> CREATE DATABASE LINK dblink3 CONNECT TO scott IDENTIFIED BY tiger USING 'dbd95078/orcl';
Database link created.
SQL> select * from new@db_link3;
select * from new@db_link3
*
ERROR at line 1:
ORA-02019: connection description for remote database not found
tnsnames.ora is here
JTAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbd95078)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jtac)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbd95078)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
scott also has a table name "new"
SQL> CREATE DATABASE LINK dblink2 CONNECT TO sys IDENTIFIED BY sys USING 'dbd95078/orcl';
Database link created.
SQL> select name from v$database;
NAME
---------
JTAC
SQL> select * from new@db_link2;
select * from new@db_link2
*
ERROR at line 1:
ORA-02019: connection description for remote database not found
SQL> CREATE DATABASE LINK dblink2 CONNECT TO scott IDENTIFIED BY tiger USING 'dbd95078/orcl';
CREATE DATABASE LINK dblink2 CONNECT TO scott IDENTIFIED BY tiger USING 'dbd95078/orcl'
*
ERROR at line 1:
ORA-02011: duplicate database link name
SQL> CREATE DATABASE LINK dblink3 CONNECT TO scott IDENTIFIED BY tiger USING 'dbd95078/orcl';
Database link created.
SQL> select * from new@db_link3;
select * from new@db_link3
*
ERROR at line 1:
ORA-02019: connection description for remote database not found
tnsnames.ora is here
JTAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbd95078)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jtac)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbd95078)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
To add the USING piece needs to be an entry in the tnsnames.ora file.
wait ...
you are creating dblink names dblink2 and using db_link2 .
Remove the underscore from the name.
you are creating dblink names dblink2 and using db_link2 .
Remove the underscore from the name.
- the syntax to call :
SQL> select * from tablename@thedatabaselinkn
- arturrro is right. you have created the database link named as dblink2 and dblink3, therefore call it using that name:
SQL> select * from new@dblink2
OR
SQL> select * from new@dblink3
SQL> select * from tablename@thedatabaselinkn
- arturrro is right. you have created the database link named as dblink2 and dblink3, therefore call it using that name:
SQL> select * from new@dblink2
OR
SQL> select * from new@dblink3
ASKER
SQL> CREATE DATABASE LINK dblink11 CONNECT TO scott IDENTIFIED BY scott USING 'orcl';
Database link created.
SQL> select * from newtab@dblink11;
select * from newtab@dblink11
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-02063: preceding line from DBLINK11
where
newtab is a table in scott/scott
in orcl
I did not change anything for tnsnames.ora
currently it is
JTAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbd95078)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jtac)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbd95078)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
where should we change if we want..
Database link created.
SQL> select * from newtab@dblink11;
select * from newtab@dblink11
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-02063: preceding line from DBLINK11
where
newtab is a table in scott/scott
in orcl
I did not change anything for tnsnames.ora
currently it is
JTAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbd95078)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jtac)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbd95078)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
where should we change if we want..
For grins:
Select sysdate from dual@dblink11;
Not sure it matters but I always use upper case in my using syntax: ... Using 'ORCL';
Select sysdate from dual@dblink11;
Not sure it matters but I always use upper case in my using syntax: ... Using 'ORCL';
ORA-00942: table or view does not exist
- check also if the table/view call newtab is exist for scott
- check also if the table/view call newtab is exist for scott
Thought that was covered with "where newtab is a table in scott/scott in orcl"
- then add the schema to the tablename:
SELECT * FROM scott.newtab@dblink11
SELECT * FROM scott.newtab@dblink11
Schema should be implied with the create link connect to syntax?
- i agree. however the error "ORA-00942: table or view does not exist" might implies to two reason. either the table is not there or no privilege to select the table.
- as for the case suggestion, i think its not implied. i have all my entry in tnsnames in uppercase and i create all my dblink 'using' smallcase and it works as expected.
- as for the case suggestion, i think its not implied. i have all my entry in tnsnames in uppercase and i create all my dblink 'using' smallcase and it works as expected.
ASKER
please advice whatever I am saying is right.
1) when I created the table is was not commited by me..now I committed now it is woriking fine
select * from newtab@dblink11;
is working fine
2) I did not do any modifications in tnsnames.ora
then how it is working fine...
"commit" was the issue
1) when I created the table is was not commited by me..now I committed now it is woriking fine
select * from newtab@dblink11;
is working fine
2) I did not do any modifications in tnsnames.ora
then how it is working fine...
"commit" was the issue
Creating a dblink does not need a 'commit'. You only need to 'commit' DML or procedures/packages that do DML that don't automatically commit.
- i don't remember if a commit is needed during creation of a table? only DML (insert-update-delete) operation needs commit.
- uppercase or lowercase of the tnsnames.ora is not the issue. dblink... using 'orcl' will work regardless of the case.
- uppercase or lowercase of the tnsnames.ora is not the issue. dblink... using 'orcl' will work regardless of the case.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
CREATE DATABASE LINK dblink CONNECT TO user INDENTIFIED BY password USING remote_database;
- so for your dblink, try this:
CREATE DATABASE LINK dblink2 CONNECT TO scott IDENTIFIED BY tiger USING 'orcl'