Recreate/drop Oracle database link for another schema/user
Have you ever needed to recreate/drop a database link for another user/schema owner in an Oracle database? Here's how I did it. -- recreate Database link CREATE PROCEDURE schema_owner.create_db_lnk AS BEGIN EXECUTE IMMEDIATE 'CREATE DATABASE LINK COGNOS.WORLD ' ||'CONNECT TO CRUD IDENTIFIED BY "password123" ' ||'USING ''(description=(address=(protocol=tcp)(host=host-scan)(port=1521))(connect_data=(service_name=test.artisdba.com)))'''; END create_db_lnk; / exec schema_owner.create_db_lnk; drop procedure schema_owner.create_db_lnk; -- Drop database link CREATE PROCEDURE schema_owner.drop_db_lnk AS BEGIN EXECUTE IMMEDIATE 'DROP DATABASE LINK COGNOS.WORLD'; END drp_db_lnk; / exec schema_owner.drop_db_lnk; drop procedure schema_owner.drop_db_lnk;