Oracle DBA - How to drop a private database link in an Oracle database using a procedure

 

Oracle DBA - How to drop a private database link in an Oracle database using a procedure

For instance, USER1, USER2, and USER3 are the database accounts that contain private links, and executing the procedure will result in the removal of those private database links.

Run below procedure as SYS user.

SET SERVEROUTPUT ON
SET FEEDBACK OFF
SET ECHO OFF
DECLARE
  l_sql_stmt VARCHAR2(1000);
BEGIN
  -- Iterate through all private database links
  FOR link_rec IN (SELECT owner, db_link
                   FROM dba_db_links
                   WHERE owner IN ('USER1', 'USER2', 'USER3')
   ORDER BY owner, db_link)
  LOOP
    -- Create a temporary procedure to drop the link
    l_sql_stmt := 'CREATE OR REPLACE PROCEDURE ' || link_rec.owner || '.drop_temp_link AS
                   BEGIN
                       EXECUTE IMMEDIATE ''DROP DATABASE LINK "' || link_rec.db_link || '"'';
                   END;';
    EXECUTE IMMEDIATE l_sql_stmt;
    -- Execute the temporary procedure
    l_sql_stmt := 'BEGIN ' || link_rec.owner || '.drop_temp_link; END;';
    EXECUTE IMMEDIATE l_sql_stmt;
    -- Drop the temporary procedure
    l_sql_stmt := 'DROP PROCEDURE ' || link_rec.owner || '.drop_temp_link';
    EXECUTE IMMEDIATE l_sql_stmt;
    DBMS_OUTPUT.PUT_LINE('Dropped database link ' || link_rec.owner || '."' || link_rec.db_link || '"');
  END LOOP;
END;
/
SET FEEDBACK ON
SET ECHO ON