#25 DROP DATABASE LINK error

Released in 1.4x
closed-works-for-me
Drzewiecki T.
Interface (12)
7
2014-10-14
2005-06-16
Drzewiecki T.
No

CREATE DATABASE LINK test_xyz
CONNECT TO user_xyz IDENTIFIED BY pass_xyz
USING 'db_xyz.world';

Refresh 'object list' and try to drop it. You can't:
'ORA-02024 : database link not found'.

In the object list my dblink is showed as:
'test_xyz.mycompany.com.pl'.

Extract DDL also created (quite proper) statement:
CREATE DATABASE LINK
test_xyz2.mycompany.com.pl
CONNECT TO user_xyz IDENTIFIED BY pass_xyz
USING 'db_xyz.world';

But DROP doesn't know anything about:
test_xyz2.mycompany.com.pl

Proper DROP statement:
DROP DATABASE LINK test_xyz2

Solution:
If we drop dblink, truncate domain.
If we create DDL, also probably will be better to truncate
domain.

Discussion

  • Logged In: YES
    user_id=423922

    I can drop db link using its full name (with domain). It
    works at least for 10g (I'll check it for 8i and 9i). The
    root cause is that sqltools generates a drop statement with
    "SCHEMA"."NAME" but oracle does not support that - sql
    manual says that only an owner can delete a private synonym.

     
  • Drzewiecki T.
    Drzewiecki T.
    2005-06-29

    Logged In: YES
    user_id=747155

    We also shouldn't drop database link as:
    DROP DATABASE LINK owner.link_name.

    Oracle documentation:
    "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 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.
    "

     
  • Drzewiecki T.
    Drzewiecki T.
    2005-06-29

    • assigned_to: nobody --> tmk_pl
    • status: open --> closed-works-for-me
     
  • Drzewiecki T.
    Drzewiecki T.
    2005-06-29

    Logged In: YES
    user_id=747155

    Fixed in:
    CDbSourceWnd::DoDelete()

     
    • status: closed-works-for-me --> open-works-for-me
     
  • Logged In: YES
    user_id=423922

    Drop does not work in the very simple case:

    CREATE DATABASE LINK test.test.com USING '';
    -- look for the db link name
    SELECT * FROM user_db_links;
    -- then substitute it
    DROP DATABASE LINK "TEST.TEST.COM";
    -- works fine for me but CDbSourceWnd::DoDelete() does not
    handle it

    BTW drop with domain always work for me

     
  • Drzewiecki T.
    Drzewiecki T.
    2005-09-29

    • status: open-works-for-me --> closed-works-for-me
     
  • Drzewiecki T.
    Drzewiecki T.
    2005-09-29

    Logged In: YES
    user_id=747155

    Forgave my sins...
    You were right in your fist post - SCHEMA created the
    problem. I cut this and it is working now!

     
  • Drzewiecki T.
    Drzewiecki T.
    2006-01-31

    • milestone: 455736 --> Released in 1.4x