ORA-02024: database link not found

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.