R12.2 Apps DBA. Powered by Blogger.

DB Link Between Two Databases in Oracle APPS

, , , , , No comments :
CREATE PUBLIC DATABASE LINK dev_db CONNECT TO apps IDENTIFIED BY apps
USING 'HOST:PORT/SID';  

select * from dba_db_links;
ping 123.45.6789
ping remoreserv
TNSping to ping the TNS service name
tnsping mydb
query using the database link:
select * from dual@mydb;
select count(*) from remtab@mydb;

List Database Links.

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300

COLUMN owner FORMAT A30
COLUMN db_link FORMAT A50
COLUMN username FORMAT A30
COLUMN host FORMAT A30

SELECT owner,
       db_link,
       username,
       host
FROM   dba_db_links
ORDER BY owner, db_link


A database link (DBlink) is a definition of how to establish a connection from one
Oracle database to another.
The following link types are supported:
Private database link - belongs to a specific schema of a database. Only the owner of a private database link can use it.
Public database link - all users in the database can use it.
Global database link - defined in an OID or Oracle Names Server. Anyone on the network can use it.

Create a DBlink:
CREATE DATABASE LINK remotedb CONNECT TO scott IDENTIFIED BY tiger USING 'tns_conn_str';
Drop a DBlink:
DROP DATABASE LINK remotedb;
To close a DBlink:
ALTER SESSION CLOSE DATABASE LINK <link_name>;
When you are owner of the link you can drop it without problem but if you are a DBA and want to purge database,
let's say after restoring test database from live database,
you can use the proxy user feature so you can connect to the database link owner without knowing or changing its password (below apdba is the DBA).
SQL> CONNECT test/test
Connected.
TEST> CREATE DATABASE LINK mika@loop CONNECT TO test IDENTIFIED BY test USING 'mika';
Database link created.
TEST> CONNECT apdba/apdba
Connected.
apdba> ALTER USER test GRANT CONNECT THROUGH apdba ;
User altered.
apdba> -- Connect to TEST through apdba account and so with apdba's password
apdba> CONNECT apdba[test]/apdba
Connected.
TEST> SHOW USER
USER is "TEST"
TEST> DROP DATABASE LINK mika@loop;
Database link dropped.
TEST> CONNECT apdba/apdba
Connected.
apdba> ALTER USER test REVOKE CONNECT THROUGH apdba ;
User altered.

No comments :

Post a Comment

Note: only a member of this blog may post a comment.

Subscribe to: Post Comments ( Atom )