Oracle: Create or drop database link for/from different schema
You can't create a database link for another user than the one you logged in. Neither you can drop it. Here's a workaround...
Creating a database link from one Oracle database to another can be very helpful if you want to transfer data between them.
There are two kinds of database links:
- public database links
- private database links
The public ones can be created or deleted by anyone who has these privileges. But the private ones can be only created when logging on the schema/user where they have to be created. And if you don't know it's password, you can't do it.
But as users like SYS are able to create procedures for other users, and these procedures can execute anything within this user context, this can be a way to solve it
Create a database link
This is how a database link can be created for another user:
SQL >
CREATE PROCEDURE <my user>.create_database_link AS
BEGIN
EXECUTE IMMEDIATE 'create database link <database link name> ...';
END ;
/Drop the database link
The same way can be used to drop the database link:
SQL >
CREATE PROCEDURE <my user>.drop_database_link AS
BEGIN
EXECUTE IMMEDIATE 'drop database link <database link name>';
END ;
/