How To Drop a Database Link in Another Schema

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.

You must grant execute privilege to user who will execute the procedure.

The db link will be dropped when we execute the procedure as follows with the corresponding user.

You can see whether the DB link has been dropped by the following query.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *