Thursday, October 20, 2011

Alter Database Link


We can alter a DATABASE Link in order modify password (password changed recently for remote schema) without dropping & recreating.

Here is a simple test.

1. Create a DBLINK with wrong password.

CREATE DATABASE LINK TEST_LINK
 CONNECT TO TIGER
 IDENTIFIED BY "tiger1"
 USING 'TESTENV';

SQL> select * from dual@TEST_LINK;
select * from dual@TEST_LINK
                  *
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from TEST_LINK


1.    Grant link alter permission to scott.

GRANT ALTER DATABASE LINK TO SCOTT;
Access Granted.

2.    Alter the link with correct password.

ALTER DATABASE LINK TEST_LINK
  CONNECT TO TIGER IDENTIFIED BY "tiger123";

SQL> select * from dual@TEST_LINK;

D
-
X

No comments: