Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
If a user want to drop a DB link belonging to a schema in Oracle databases, it searches for that DB link under the schema of the user who want to drop it. If a use want to drop a DB link in another schema, the error “DB link not found” will be received.
To avoid this error, you can create a simple procedure in the schema that owns the db link and grant execute to user who will drop the db link.
The procedure can be created as follows. The procedure should be created with a DBA authorized user or SYS user in the schema where the db link is to be dropped.
In the example below, the necessary procedure was created to drop the db link named “ahmet_dblink”, in the schema of the user “ahmet”. Now we can drop this db link with another user.
1 2 3 4 | CREATE PROCEDURE ahmet.drop_db_link AS BEGIN EXECUTE IMMEDIATE 'drop database link ahmet_dblink'; END drop_db_link; |
You must grant execute privilege to user who will execute the procedure.
1 | SQL> grant execute on ahmet.drop_db_link to relavant_user_name; |
The db link will be dropped when we execute the procedure as follows with the corresponding user.
1 | SQL> exec ahmet.drop_db_link; |
You can see whether the DB link has been dropped by the following query.
1 2 3 | SQL> select * from dba_db_links where db_link='ahmet_dblink'; no rows selected |
