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

create or replace procedure p_drop_dblinks(p_owner varchar,

p_dblink_namevarchar)isv_sqlvarchar2(4000);

curnumber;

uidnumber;

rcnumber;begin

beginv_sql := 'grant create database link to' ||p_owner;executeimmediate v_sql;end;begin

select u.user_id

intouidfromdba_users uwhere 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;beginv_sql := 'revoke create database link from' ||p_owner;executeimmediate v_sql;end;end;/执行方法execp_drop_dblinks(dblink_owner,dblink_name);create or replace procedure p_create_dblinks(p_owner varchar,

p_dblink_namevarchar,

r_uservarchar,

r_passwdvarchar,

r_hostvarchar2) isv_sqlvarchar2(4000);

curnumber;

uidnumber;

rcnumber;

v_backtracevarchar2(2000);begin

beginv_sql := 'grant create database link to' ||p_owner;executeimmediate v_sql;end;begin

select u.user_id

intouidfromdba_users uwhere 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;beginv_sql := 'revoke create database link from' ||p_owner;executeimmediate v_sql;end;beginv_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 THENdbms_output.put_line('DBLINK is ok');END IF;

exceptionwhen others thenv_backtrace :=dbms_utility.format_error_backtrace;if sqlcode = '-12154' thenDBMS_OUTPUT.put_line('ORA' || sqlcode ||

':TNS or remote host ip is erro,please exec p_drop_dblinks(''' ||p_owner|| '''.''' || p_dblink_name ||

''')!!!');elseDBMS_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);