Oracle DBA

To drop database links from multiple user schemas in Oracle, the typical method is to log in as each user and manually execute the command:

DROP DATABASE LINK <database_link>;

However, there’s a more efficient way to achieve this by creating a PL/SQL procedure. This procedure allows you to drop database links across various schemas without having to log in as each individual user.

Here’s how it works:

1. Define the PL/SQL Procedure
   You can create a procedure that iterates through user schemas and executes the DROP DATABASE LINK command for each specified database link. This centralizes the process, saving time and reducing the need for multiple logins.
2. Execute the Procedure
   Once the procedure is created, you simply run it once, and it will handle dropping the specified link from relevant schema.

Here’s an example of the PL/SQL procedure:

CREATE OR REPLACE PROCEDURE DROP_DBLINKS(SCHEMANAME VARCHAR2, DBLINK VARCHAR2) IS
		PLSQL	VARCHAR2(1000),
		CUR		NUMBER,
		U_ID	NUMBER,
		RC		NUMBER;
	BEGIN
		SELECT
			U.USER_ID INTO U_ID
		FROM
			DBA_USERS U
		WHERE
			U.USERNAME=SCHEMANAME;
		
		PLSQL := 'DROP DATABASE LINK ' || DBLINK;
		CUR := SYS.DBMS_SYS_SQL.OPEN_CURSOR;
		SYS.DBMS_SYS_SQL.PARSE_AS_USER(
			C => CUR,
			STATEMENT => PLSQL,
			LANGUAGE_FLAG => DBMS_SQL.NATIVE,
			USERID => U_ID
		);
		
		RC := SYS.DBMS_SYS_SQL.EXECUTE(CUR);
		SYS.DBMS_SYS_SQL.CLOSE_CURSOR(CUR);
		DBMS_OUTPUT.PUT_LINE('DB_LINK '|| SCHEMANAME || ' , ' || DBLINK || ' DROPPED.');
	END;
/

Using this approach allows you to drop database links across multiple schemas quickly and efficiently, without having to log in as each user individually. This method is particularly helpful for DBAs managing numerous schemas.