Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 466 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Dynamic drop database link

User341075
Member Posts: 83 Blue Ribbon
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
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 15Thanks
Answers
-
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. -
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 -
Paul wrote: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;
/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; /
-
yeah, I forgot dot.
-
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; /
-
791550 wrote: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?
Yet it doest work please see bellow:
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. -
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. -
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.