HOWTO Create DBLINKs (database links) in Another Schema

So, you've decided to create DBLINKs in a few application schemas. As with every other CREATE statement in Oracle 9i+, you'll do this from a DBA account to save some hassle. But when you try to create your first link from your DBA account:

CREATE DATABASE LINK joeblow.joes_db_link
    CONNECT TO joes_remote_user
    IDENTIFIED BY joes_remote_password
    USING 'JOESREMDB.JOE.COM';

...instead of creating a DBLINK for user joeblow, you get a DBLINK in your DBA account called JOEBLOW.JOES_DB_LINK. Since Oracle allows, and in some cases, enforces, periods in the DBLINK name, the normal syntax used to create objects in other schemas will not work. We must resort to the days of Oracle 7 (and 8) and log in as that user to create our objects.

• DBA account
• Two test Oracle databases, one designated as "local" and one as "remote"
• Test Users created in local and remote database.
• Some knowledge of how the GLOBAL_NAMES and other init.ora parameters affect DBLINKs. See http://tahiti.oracle.com

• Fire up MOE in Toad. We'll let you use the SQL Editor if you're not up to Toad 9 yet.
• CONNECT <local DBA username>@<local DB>;
• Run the following and save the output:

select PASSWORD
from dba_users
where username = '<local username>'

• ALTER USER <local username> IDENTIFIED BY "TEMP2TEMP";
• GRANT CREATE DATABASE LINK TO <local username>;
• CONNECT <local username>/TEMP2TEMP@<local DB>;
• CREATE DATABASE LINK "REMDB" CONNECT TO <remote username> IDENTIFIED BY "<remote password>" USING '<remote DB>';
• CONNECT <local DBA username>@<local DB>;
• REVOKE CREATE DATABASE LINK FROM <local username>;
• ALTER USER <local username> IDENTIFIED BY VALUES '<password>'; where <password> is the password values saved from the script above.

I'm sure you realize the issues with changing a password on an application account, even if only for a few seconds. Plan accordingly!

A generic untested version of the procedure outlined above may also work:

COLUMN PASSWORD NEW_VALUE ORIG_PASS 
COLUMN USER NEW_VALUE ORIG_DBA_USER

SELECT user
FROM dual;

SELECT password
FROM dba_users
WHERE username = '&&MY_SCHEMA';

ALTER USER &&MY_SCHEMA IDENTIFIED BY "CHANG3M3";
GRANT CREATE DATABASE LINK TO &&MY_SCHEMA;

CONNECT &&MY_SCHEMA/CHANG3M3@&&LOCAL_DB;

CREATE DATABASE LINK "&&REMOTE_DB"
        CONNECT TO &&MY_SCHEMA
        IDENTIFIED BY "&&REMOTE_PWD"
        USING '&&REMOTE_DB';

CONNECT &&ORIG_DBA_USER@&&LOCAL_DB;

REVOKE CREATE DATABASE LINK FROM &&MY_SCHEMA;
ALTER USER &&MY_SCHEMA IDENTIFIED BY VALUES '&&ORIG_PASS';

Also, I used this on Oracle 10.2.0.2.0 with Toad 9.0. As with everything, Your Mileage May Vary. TEST!!!

Enjoy!

Note that the variables and their respective values used in the example in the Epilogue are stored in plain text by Toad in the User Files/Variables.dat file found in the Toad Application Data Directory. Click on View→Toad Options→General in Toad for the exact location of that directory on your PC.