Home » RDBMS Server » Server Administration » Re: DATABASE LINK & SELECT AND THEN ONTO COPY S TATEMENTS
Re: DATABASE LINK & SELECT AND THEN ONTO COPY S TATEMENTS [message #51052] Tue, 23 April 2002 03:38
Abdullah Gilani
Messages: 18
Registered: October 2001
Junior Member
#create a database link with the same name as the database it connects to, OR set global_names=false.
######################################## at Remote Server #################
SQL> alter database rename global_name TO test.com;

Database altered.

SQL> select * from global_name;

GLOBAL_NAME
-----------------------------------------------------
TEST.COM

######################################## at Local Server #################
SQL> Create Public Database Link TEST.COM connect to SYSTEM identified by MANAGER using 'ORCL';

Database link created.

### Note: where 'ORCL' is the Tnsnames String for Remote DB Server and must be exists in the Local DB tnsnames.ora file.
'TEST.COM' is the Remote Database Global Name.

SQL> select count(*) from scott.emp@test.com;

COUNT(*)
----------
8

#########################################################################

###--------- Procedure for LoopBack DB Link -------------###
declare
dbName varchar2(30);
begin
select name
into dbName
from v$database;

begin
execute immediate 'drop public database link loopback';
exception
when others then
null;
end;
execute immediate 'create public database link loopback connect to admin identified by admin using '''||dbName||'''';
end;
###--------- End Procedure for LoopBack DB Link ------------###

Written by,
Abdullah Gilani
Email: syedgilani@hotmail.com
Previous Topic: Re: Regarding Database Link
Next Topic: Re: ORA-01536: space quota exceeded for tablespace 'USERS'
Goto Forum:
  


Current Time: Wed Dec 25 09:29:12 CST 2024