This discussion is archived
5 Replies Latest reply: Mar 19, 2013 9:25 PM by Justin_Mungal RSS

Drop database link in another schema as DBA is not working

dba-hyd Pro
Currently Being Moderated
How to drop a database object for example, "MYDB.MYDBLINK" that is present in SCOTT schema.

When I try the following options connected as DBA it is giving error,

DROP DATABASE LINK "SCOTT"."MYDB.MYDBLINK1";
ORA-2024: Database link not found.

DROP DATABASE LINK SCOTT.MYDB.MYDBLINK1;
ORA-2024: Database link not found.

DROP DATABASE LINK 'SCOTT'.'MYDB.MYDBLINK1';
ORA-2024: Database link not found.

DROP DATABASE LINK SCOTT.MYDB.MYDBLINK1;
ORA-2024: Database link not found.

Always I have to connect as SCOTT to drop the database link MYDB.MYDBLINK1 that is present in SCOTT's schema.

Please provide the way to drop the database link in another schema without connecting as that schema owner, while I can connect as a DBA (SYSTEM or SYS).
  • 1. Re: Drop database link in another schema as DBA is not working
    Justin_Mungal Journeyer
    Currently Being Moderated
    From the documentation:

    http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_8010.htm#SQLRF01514

    Restriction on Dropping Database Links You cannot drop a database link in another user's schema, and you cannot qualify dblink with the name of a schema, because periods are permitted in names of database links. Therefore, Oracle Database interprets the entire name, such as ralph.linktosales, as the name of a database link in your schema rather than as a database link named linktosales in the schema ralph.

    Setting your CURRENT_SCHEMA to the user won't work either. I hope this helps.
  • 2. Re: Drop database link in another schema as DBA is not working
    Veeresh.S Pro
    Currently Being Moderated
    DatabaseAdministrator wrote:
    How to drop a database object for example, "MYDB.MYDBLINK" that is present in SCOTT schema.
    you cant delete database links of other schema even if you are super user.
    When I try the following options connected as DBA it is giving error,

    DROP DATABASE LINK "SCOTT"."MYDB.MYDBLINK1";
    ORA-2024: Database link not found.
    in above case database consider '' SCOTT"."MYDB.MYDBLINK1 '' as a dblink name, so the error is expected.
  • 3. Re: Drop database link in another schema as DBA is not working
    TSharma-Oracle Guru
    Currently Being Moderated
    You cannot drop a database link in another user's schema. One workaround is:


    SQL> CREATE PROCEDURE scott.drop_db_link AS
    BEGIN
    EXECUTE IMMEDIATE 'drop database link LINK1';
    END drop_db_link; 2 3 4
    5 /

    Procedure created.

    SQL> exec scott.drop_db_link

    PL/SQL procedure successfully completed.

    source: http://dbaoracletips.blogspot.com/2011/11/how-to-dropcreate-database-link-from.html
    http://laurentschneider.com/wordpress/2012/10/drop-database-link-in-another-schema.html
  • 4. Re: Drop database link in another schema as DBA is not working
    SalmanQureshi Expert
    Currently Being Moderated
    Hi,

    Restriction on Dropping Database Links You cannot drop a database link in another user's schema, and you cannot qualify dblink with the name of a schema, because periods are permitted in names of database links. Therefore, Oracle Database interprets the entire name, such as ralph.linktosales, as the name of a database link in your schema rather than as a database link named linktosales in the schema ralph.*

    http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_8010.htm

    Salman
  • 5. Re: Drop database link in another schema as DBA is not working
    Justin_Mungal Journeyer
    Currently Being Moderated
    Wow, lots of help for the OP... :D

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points