Forum Stats

  • 3,770,761 Users
  • 2,253,163 Discussions
  • 7,875,579 Comments

Discussions

Dynamic drop database link

User341075
User341075 Member Posts: 83 Blue Ribbon
edited Oct 5, 2012 1:50AM in SQL & PL/SQL
Hi,

As user SYS i need to Dynamically DROP all the database link in the Database.
I cant drop private database link .
For example i have few database links under APPS schema that i would like to drop.
Please note that it should be done by SYS user
SQL> declare
  2  
  3  cursor c is
  4  select * 
  5  from dba_db_links;
  6  
  7  
  8  begin
  9  for c_rec in c loop
 10  
 11      if c_rec.owner = 'PUBLIC' then
 12          execute immediate ' drop public database link '||'"'||c_rec.db_link||'"';
 13      else
 14          dbms_output.put_line (' drop database link '||'"'||c_rec.db_link||'"');
 15           execute immediate ' drop database link '||'"'||c_rec.db_link||'"';
 16      end if;      
 17  end loop;
 18  
 19  end;
 20  /

drop database link "APPS_TO_APPS"
declare
*
ERROR at line 1:
ORA-02024: database link not found
ORA-06512: at line 15
Thanks
«1

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 41,500 Red Diamond
    You need to include the owning schema name in the drop command so that it would be e.g.
    drop database link apps.app_to_app
    However, it sounds like a very dangerous procedure to me.
  • 737517
    737517 Member Posts: 49
    edited Aug 30, 2011 10:25AM
    I think, you should define owner - c_rec.owner
    DECLARE
            CURSOR c
            IS
                     SELECT * FROM dba_db_links;
    BEGIN
            FOR c_rec IN c
            LOOP
                    IF c_rec.owner = 'PUBLIC' THEN
                            DBMS_OUTPUT.PUT_LINE (' drop public database link '||'"'||c_rec.db_link||
                            '"');
                    ELSE
                            DBMS_OUTPUT.PUT_LINE (' drop database link '||c_rec.owner||'"'||C_REC.DB_LINK||'"');
                         
                    END IF;
            END LOOP;
    END;
    /
    Edited by: Paul on Aug 30, 2011 7:24 AM
  • BluShadow
    BluShadow Member, Moderator Posts: 41,500 Red Diamond
    Paul wrote:
    DECLARE
    CURSOR c
    IS
    SELECT * FROM dba_db_links;
    BEGIN
    FOR c_rec IN c
    LOOP
    IF c_rec.owner = 'PUBLIC' THEN
    DBMS_OUTPUT.PUT_LINE (' drop public database link '||'"'||c_rec.db_link||
    '"');
    ELSE
    DBMS_OUTPUT.PUT_LINE (' drop database link '||c_rec.owner||'"'||C_REC.DB_LINK||'"');

    END IF;
    END LOOP;
    END;
    /
    I think you mean more like...
    DECLARE
      CURSOR c IS
        SELECT * FROM dba_db_links;
    BEGIN
      FOR c_rec IN c
      LOOP
        IF c_rec.owner = 'PUBLIC' THEN
          DBMS_OUTPUT.PUT_LINE (' drop public database link '||'"'||c_rec.db_link||'"');
        ELSE
          DBMS_OUTPUT.PUT_LINE (' drop database link "'||c_rec.owner||'"."'||C_REC.DB_LINK||'"');
        END IF;
      END LOOP;
    END;
    /
  • 737517
    737517 Member Posts: 49
    yeah, I forgot dot.
  • User341075
    User341075 Member Posts: 83 Blue Ribbon
    Hi,
    Yet it doest work please see bellow:
    SELECT OWNER, DB_LINK from dba_db_links;
    
    
    OWNER                          DB_LINK
    ------------------------------ ------------------------------
    APPS                           EDW_APPS_TO_WH.WORLD
    APPS                           EDW_APPS_TO_WH
    APPS                           APPS_TO_APPS.WORLD
    APPS                           APPS_TO_APPS
    
    
    
    declare
      cursor c is
      select * 
      from dba_db_links;
      begin
      for c_rec in c loop
     
          if c_rec.owner = 'PUBLIC' then
              execute immediate ' drop public database link '||'"'||c_rec.db_link||'"';
          else
              DBMS_OUTPUT.PUT_LINE (' drop database link "'||c_rec.owner||'"."'||C_REC.DB_LINK||'"');
               execute immediate ' drop database link "'||c_rec.owner||'"."'||C_REC.DB_LINK||'"' ;
          end if;      
      end loop;
      
    end;
    / 
     
    
    drop database link "APPS"."APPS_TO_APPS"
    declare
    *
    ERROR at line 1:
    ORA-02024: database link not found
    ORA-06512: at line 12
    Please advice
    Thanks
  • You cannot drop a private database link owned by a another user. You have to connect as that user.

    Say you have a privileged account (with dba privileges), "privact", and you have db links owned by user "apps". You do not know the password of the "apps" user but you want to delete all it's db links. Connect to the "privact" and do the following:
    alter user apps grant connect through privact;
    
    --
    -- this now allows you to connect to apps using privact by doing the following
    --
    
    connect privact[apps]/<password>@<db_name>
    
    begin
      for rec in (select 'drop database link ' || db_link stmt from user_db_links) loop
           execute immediate rec.stmt;
      end loop;  
    end;
    /
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,603 Red Diamond
    edited Aug 31, 2011 6:50AM
    791550 wrote:

    Yet it doest work please see bellow:
    Correct. The domain name can optionally be included with the database name. So if you for example want to drop database link "ABC.DEF.WORLD.COM", does ABC refer to the link name and DEF.WORLD.COM to the domain?

    Or is ABC the schema name, DEF the link name and WORLD.COM the domain?

    And what is schema ABC exists with link DEF.WORLD.COM and in the current schema there's a link called ABC for domain DEF.WORLD.COM ?

    So there is no clean and correct way for Oracle to resolve the schema scope of a database link name.

    If you, as SYS, want to drop all database links, then you can use the DBMS_SYS_SQL interface - this is a (mostly undocumented) interface for parsing SQL as any specific schema on the database. The DBMS_SQL interface (restricted to parsing as the current schema) is documented and runs on top of this system interface.

    E.g.
    create or replace procedure DropDbLink( schemaName varchar2, dbLink varchar2 ) is
            cur     number;
            plsql   varchar2(1000);
            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;
    /
    This proc, as SYS, allows you to drop any private database link from the SYS schema.
  • pmkr
    pmkr Member Posts: 21 Blue Ribbon
    Though, it's a late post, I too have similar issues.

    Ours is an E-Biz 11i environment on 9i database. Every night we're creating a break fix environment by cloning production and the cloning process involves dropping and re-creating the database links in APPS and other schemas.

    We've a post-clone SQL script to do the above along with the other database tasks such as updating the profiles, cancelling the pending concurrent requests, recreating the database directories etc. Right now, there're a few individual SQLs that do the above. I'm consolidating them all in a single PL/SQL script. Within this, I've written a procedure to drop and re-create the links. It accepts the link name, target user/password it's connecting to and the host name and drops/re-creates the links.

    As long as APPS calls this procedure, it works very well. Since, I can't connect to a different user from within the PL/SQL block, I tried to use 'alter session set current_schema = <username>' and try calling the above procedure, but I'm getting the ORA-1031 error. I tried even using the invoker rights (by defining the procedure with "authid current_user" clause) but still the same error.

    Then I came to know about this DBMS_SYS_SQL and thought I got the solution I wanted when I read about the parse_as_user procedure. But still I've no luck. I tried to test the above from a PL/SQL block, as APPS. It works for the APPS schema and when it comes to the other schemas, problem still remains.

    I tried to run it as SYS user as well. But again, it works well for the APPS schema and for the other schemas, it throws the same ORA-1031 error. I made sure that the other schemas have the execute privilege on DBMS_SYS_SQL package and it's qualified as "sys.dbms_sys_sql.parse_as_user" when it is called.

    This really confuses me. When running this procedure as SYS, it can drop and re-create the links in APPS schema, buy why not in the other schemas ? Do the other schemas need any other privilege ?

    Thanks and regds.
    Muthu
  • >
    Though, it's a late post, I too have similar issues.
    >
    Please create your own thread for your question and provide your particulars (4 digit Oracle version) including any code you are trying to use.
  • pmkr
    pmkr Member Posts: 21 Blue Ribbon
    Thanks, but since my problem is the same as discussed in this thread, with respect to the DBMS_SYS_SQL package to create the database link, thought it would be appropriate to post in here too.
This discussion has been closed.