How to drop a private database link

Problem: You are refreshing the database from production and need to drop the private database link owned by a user. You do not know the password for the schema (As a DBA the chances that you are not aware of the password is slim, but it can happen). You wish to leave the password unchanged. You cannot login as system or any other user with dba privilege and drop the link because "drop database link" does not allow you to qualify the db link name with the schema name. In other words you cannot do 'drop database link scott.testdblink;' .
Solution: The easiest way is to login as system or any other privileged user and change the password for the schema owning the db link. Then login as the schema owning the db link and drop the db link. For whatever reason if you need to keep the password untouched, you can follow these steps:
1. Login as system or any user that has DBA role.
2. Note down the encrypted password for the schema owning the db link using the DBA_USERS view. The password will be in the form of hexadecimal literals.
3. Change the password of the schema which owns the db link. for eg alter user scott identified by test;
4. login to the database as scott/test. Drop the private db link.
5. Log back in to the database as system user and issue the following command:Alter user scott identified by values ''; . Use the hexadecimal value you noted down in step 2 here. The password will be set back to what it was when you started this exercise.


The same approach can be used whenever you need to see things from a specific database user perspective for which you do not have the password.


- Aravind Kamath