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)
    )
  )


nobleitAsked:
Who is Participating?
 
martin_seaConnect With a Mentor Commented:
you have an entry in the tnsname.ora

JTAC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbd95078)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jtac)
    )
  )

re check it
# tnsping jtac

it should say ok

SQL> CREATE Public DATABASE LINK dblink5 CONNECT TO scott IDENTIFIED BY tiger USING 'jtac';

SQL> select count(*) from emp@dblink5;

so tables inside the scott schema in the database jtac can be accessed using the dblink 'dblink5'

note there are two types of dblink public (accessed by every one) and private (access within the schema only)


0
 
OP_ZaharinCommented:
- the syntax is:
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'
0
 
slightwv (䄆 Netminder) Commented:
To add the USING piece needs to be an entry in the tnsnames.ora file.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
arturrrroCommented:
wait ...
you are creating dblink names dblink2  and using db_link2 .
Remove the underscore from the name.
0
 
OP_ZaharinCommented:
- the syntax to call :
SQL> select * from tablename@thedatabaselinkname

- 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
0
 
nobleitAuthor Commented:
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..

0
 
slightwv (䄆 Netminder) Commented:
For grins:
Select sysdate from dual@dblink11;

Not sure it matters but I always use upper case in my using syntax: ... Using 'ORCL';
0
 
OP_ZaharinCommented:
ORA-00942: table or view does not exist
- check also if the table/view call newtab is exist for scott
0
 
slightwv (䄆 Netminder) Commented:
Thought that was covered with "where newtab is a table in scott/scott in orcl"
0
 
OP_ZaharinCommented:
- then add the schema to the tablename:

SELECT * FROM scott.newtab@dblink11
0
 
slightwv (䄆 Netminder) Commented:
Schema should be implied with the create link connect to syntax?
0
 
OP_ZaharinCommented:
- 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.
0
 
nobleitAuthor Commented:
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
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
OP_ZaharinCommented:
- 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.
0
All Courses

From novice to tech pro — start learning today.