oracle创建删除其他用户dblink 存储过程

create or replace  procedure p_drop_dblinks(p_owner       varchar,
                                                            p_dblink_name varchar
                                                           ) is
  v_sql varchar2(4000);
  cur   number;
  uid   number;
  rc    number;
begin
  begin
    v_sql := 'grant create database link to  ' || p_owner;
    execute immediate v_sql;
  end;
  begin
    select u.user_id
      into uid
      from dba_users u
     where u.username = upper(p_owner);
    v_sql := 'drop database link ' || p_dblink_name;
    dbms_output.put_line(v_sql);
    cur := SYS.DBMS_SYS_SQL.open_cursor;
    SYS.DBMS_SYS_SQL.parse_as_user(C             => cur,
                                   STATEMENT     => v_sql,
                                   LANGUAGE_FLAG => DBMS_SQL.native,
                                   USERID        => uid);
    rc := SYS.DBMS_SYS_SQL.execute(cur);
    SYS.DBMS_SYS_SQL.close_cursor(cur);
  end;
begin
  v_sql := 'revoke create database link from  ' || p_owner;
  execute immediate v_sql;
end;
end;
/

执行方法
exec p_drop_dblinks(dblink_owner,dblink_name);

create or replace procedure p_create_dblinks(p_owner       varchar,
                                             p_dblink_name varchar,
                                             r_user        varchar,
                                             r_passwd      varchar,
                                             r_host        varchar2) is
  v_sql       varchar2(4000);
  cur         number;
  uid         number;
  rc          number;
  v_backtrace varchar2(2000);
begin
  begin
    v_sql := 'grant create database link to  ' || p_owner;
    execute immediate v_sql;
  end;
  begin
    select u.user_id
      into uid
      from dba_users u
     where u.username = upper(p_owner);
    v_sql := 'create database link ' || p_dblink_name || ' connect to ' ||
             r_user || ' identified by "' || r_passwd || '"
    using ''' || r_host || '''';
    cur   := SYS.DBMS_SYS_SQL.open_cursor;
    SYS.DBMS_SYS_SQL.parse_as_user(C             => cur,
                                   STATEMENT     => v_sql,
                                   LANGUAGE_FLAG => DBMS_SQL.native,
                                   USERID        => uid);
    rc := SYS.DBMS_SYS_SQL.execute(cur);
    SYS.DBMS_SYS_SQL.close_cursor(cur);
  end;
  begin
    v_sql := 'revoke create database link from  ' || p_owner;
    execute immediate v_sql;
  end;
  begin
    v_sql := 'select * from dual@' || p_dblink_name;
  
    cur := SYS.DBMS_SYS_SQL.open_cursor;
    SYS.DBMS_SYS_SQL.parse_as_user(C             => cur,
                                   STATEMENT     => v_sql,
                                   LANGUAGE_FLAG => DBMS_SQL.native,
                                   USERID        => uid);
    rc := SYS.DBMS_SYS_SQL.execute(cur);
    IF rc = 0 THEN
      dbms_output.put_line('DBLINK is ok');
    END IF;
  exception
    when others then
      v_backtrace := dbms_utility.format_error_backtrace;
      if sqlcode = '-12154' then
        DBMS_OUTPUT.put_line('ORA' || sqlcode ||
                             ':TNS or remote host ip is erro,please exec p_drop_dblinks(''' ||
                             p_owner || '''.''' || p_dblink_name ||
                             ''')!!!');
      else
        DBMS_OUTPUT.put_line(v_backtrace);
      end if;
      rollback;
      SYS.DBMS_SYS_SQL.close_cursor(cur);
  end;
end;
/
执行方法
exec p_create_dblinks(dblink_owner,dblink_name,remote_user,remote_password,remote_host);