I am using 'SYS' to drop the database link of another owner 'ALEE'.
But, the error have been prompted.
There are 2 suggestions from Oracle experts to create drop db link procedure.
1) Procedure with delete single database links with owner. and db link name.
SQL> CREATE OR REPLACE PROCEDURE Drop_DbLink(schemaName VARCHAR2, dbLink VARCHAR2 ) IS
plsql VARCHAR2(1000);
cur NUMBER;
UID NUMBER;
rc NUMBER;
BEGIN
SELECT
u.user_id INTO UID
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 => UID
);
rc := SYS.Dbms_Sys_Sql.EXECUTE(cur);
SYS.Dbms_Sys_Sql.close_cursor(cur);
END;
/
Procedure created.
SQL> EXEC Drop_DbLink( 'ALEE', 'LCNPDB_CRP' );
PL/SQL procedure successfully completed.
2) Procedure with delete all database links with single owner.
SQL> CREATE OR REPLACE PROCEDURE DropSchema_DbLinks(schemaName VARCHAR2 ) IS
BEGIN
FOR LINK IN(
SELECT
l.db_link
FROM dba_db_links l
WHERE l.owner = schemaName
) LOOP
Drop_DbLink(
schemaName => schemaName,
dbLink => LINK.db_link
);
END LOOP;
END;
/
Procedure created.
SQL> EXEC dropschema_dblinks('ALEE');
PL/SQL procedure successfully completed.
As conclusion, in order to drop private db_link is require for owner password and now we have another way to drop db links using procedures. It is consider very efficient and good to handle.
