DB LINK SET UP!

[amitstora]

Hi all!

Query:

We are working on Oracle v8i 8.1.6.0.0 with OS as NT.

Would like to create a DB Link from either NT to NT or from NT to any other OS or vice versa
(if possible).


I have created a DB link from Test DB to RCVCAT DB in SCOTT/TIGER@TEST as on the same server as under:


CREATE PUBLIC DATABASE LINK TESTRCVCAT
CONNECT TO scott IDENTIFIED BY tiger
USING '(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = 192.168.1.54)
(PORT = 1521))
(CONNECT_DATA = (SID=rcvcat)))';


1.Now from scott/tiger@test log in,I would like to query scott schema's tables in the RCVCAT DB.

How is it possible considering the above scenario!

2.1.I have two databases BIAC,PGVL on separate Server systems.

What would be the syntax and detailed steps to set up and create a DB LINK and query from Remote DB(PGVL) considering BIAC DB to be LOCAL DB and PGVL DB to be a Remote DB.

Why can't I use SERVICE_NAME instead of SID in the above Description list.


Consider the following:

IP ADDRESS FOR BIAC DB(LOCAL) IS: 192.168.1.51.Character Version:BIAC-01.
IP ADDRESS FOR PGVL DB(REMOTE) IS:192.168.1.201.Character Version:HIMALAYA.


I have tried using the following syntax for creating DB LINK in BALAJI schema in the local DB BIAC connecting from Local DB BIAC to Remote DB PGVL as under:

create public database Link BIAC_LINK
connect to BALAJI identified by VMS
using 'hq';


The Local Host's Tnsnames.ora File is as under:


hq = (DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = HQ)
(PORT = 1521))
(CONNECT_DATA =
(SID=PGVL)))';

Should I use HQ as the Host name in the above description list.

Is the above configuration correct?

After the above DB links gets created,what should be the syntax to query from Remote DB PGVL
Tables.

In which Schema would the above DB link be created?

Please correct me If I have gone wrong somewhere!

3.This is a simple question separate from DB links as under:

Every day I create,update new tables.

I would like to have a Sql query which would give me the creation dates of all the tables
created in a particular schema!

Is this possible!

Please forward your suggestions for the above.


Thanks&Regards,

Amit.

[moff]

Not sure at the Mo' about 1 and 2 but the answer to 3 is....

The DBA_OBJETCS table holds the date the object was created...

Cheers
Moff.

[moff]

Hi....

1. When you create your database link use the entry in your tnsnames.ora file (If you have one) to make it slightly easier....
eg:

CREATE PUBLIC DATABASE LINK TESTRCVCAT
CONNECT TO scott IDENTIFIED BY tiger
USING 'rcvcat'

where rcvcat is the entry in your tnsnames.ora file that identifies the database rcvcat.

then to query scotts schema in rcvcat while logged onto test enter
select table_name from user_tables@rcvcat;

If you want to use your example then use :

select table_name from user_tables@TESTRCVCAT;

2. Not sure on this as it looks OK to me - have defined 'hq' in the hosts file, if not then this won't work - use the hq's IP address or define hq in the hosts file.....
What error are you getting? The db_link should be created in the schema of the user that wants to execute the sql to look at the remote database - unless you create it as a public link in which case all users should be able to use it....

Cheers
Moff.





[Edited by moff on 11-16-2001 at 11:45 AM]

[Torsky]

If you already have a tnsnames address set up for 'hq' try:

CREATE DATABASE LINK hq
CONNECT TO scott IDENTIFIED BY tiger
USING ’hq’;

Name the link the same as the address.

I would have thought that if your tnsnames.ora file was for 8.1.6 it would be using service_name rather than sid. You might switch that to service_name--or use your orginal script using service_name instead of sid and hq instead of testrcvcat.

Hope this helps...