Dynamic drop database link

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

yeah, I forgot dot.

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;
/

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

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.