oracle怎么删除schema,使用sys用户删除其他schema下的dblink

目标数据库：

SQL> conn mytest/mytest

Connected.

SQL>

SQL> create table emp (id int,name varchar2(20));

Table created.

SQL> insert into emp values (1,'zhangsan');

1 row created.

SQL> insert into emp values (2,'wangwu');

1 row created.

SQL> commit;

Commit complete.

源库，先建立到目标数据库的连接

[oracle@r11g admin]$ vi tnsnames.ora

ecctest =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 172.24.22.135)(PORT = 1527))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = R6Q)

)

)

~

测试连接的有效性

[oracle@r11g admin]$ tnsping ecctest

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 12-DEC-2013 14:16:03

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.24.22.135)(PORT = 1527)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = R6Q)))

OK (100 msec)

[oracle@r11g admin]$

在liutya用户下建立测试用的dblink

SQL> conn liutya/liutya

Connected.

SQL>

SQL>

SQL> create database link lecctest connect to mytest identified by mytest using 'ecctest';

create database link lecctest connect to mytest identified by mytest using 'ecctest'

*

ERROR at line 1:

ORA-01031: insufficient privileges

这是缺少相应的权限。

SQL> conn / as sysdba

Connected.

SQL>

SQL> grant create database link to liutya;

Grant succeeded.

SQL> conn liutya/liutya

Connected.

创建DBLINK

SQL>  create database link lecctest connect to mytest identified by mytest using 'ecctest';

Database link created.

SQL> select * from emp@lecctest;

ID NAME

---------- --------------------

1 zhangsan

2 wangwu

下面使用sys做测试删除

SQL> conn / as sysdba

Connected.

SQL>

SQL> show user;

USER is "SYS"

SQL> set linesize 200

SQL> col owner format a20

SQL> col db_link format a20

SQL> col username format a20

SQL>

SQL> col host format a60

SQL>

SQL> select * from dba_db_links;

OWNER                DB_LINK              USERNAME             HOST                                                         CREATED

-------------------- -------------------- -------------------- ------------------------------------------------------------ ---------

LIUTYA               LECCTEST             MYTEST               ecctest                                                      12-DEC-13

SQL> drop database link liutya.lecctest;

drop database link liutya.lecctest

*

ERROR at line 1:

ORA-02024: database link not found

使用drop database link 命令是无法删除其他schema下的dblink的，是否还有其他办法呢？下面是从网上找到的两个方法：

方法一、使用下面的脚本，使用job方式删除dblink

begin

dbms_scheduler.create_job(

job_name=>'&owner..drop_database_link',

job_type=>'PLSQL_BLOCK',

job_action=>'BEGIN execute immediate ''drop database link &db_link'';END;'

);

dbms_scheduler.run_job('&owner..drop_database_link',false);

dbms_lock.sleep(2);

dbms_scheduler.drop_job('&owner..drop_database_link');

end;

/

输入变量需要使用大写。

SQL> begin

2    dbms_scheduler.create_job(

3      job_name=>'&owner..drop_database_link',

4      job_type=>'PLSQL_BLOCK',

5      job_action=>'BEGIN execute immediate ''drop database link &db_link'';END;'

6    );

7    dbms_scheduler.run_job('&owner..drop_database_link',false);

8    dbms_lock.sleep(2);

9    dbms_scheduler.drop_job('&owner..drop_database_link');

10  end;

11  /

Enter value for owner: LIUTYA

old   3:     job_name=>'&owner..drop_database_link',

new   3:     job_name=>'LIUTYA.drop_database_link',

Enter value for db_link: LECCTEST

old   5:     job_action=>'BEGIN execute immediate ''drop database link &db_link'';END;'

new   5:     job_action=>'BEGIN execute immediate ''drop database link LECCTEST'';END;'

Enter value for owner: LIUTYA

old   7:   dbms_scheduler.run_job('&owner..drop_database_link',false);

new   7:   dbms_scheduler.run_job('LIUTYA.drop_database_link',false);

Enter value for owner: LIUTYA

old   9:   dbms_scheduler.drop_job('&owner..drop_database_link');

new   9:   dbms_scheduler.drop_job('LIUTYA.drop_database_link');

PL/SQL procedure successfully completed.

SQL> select * from dba_db_links;

no rows selected

测试成功。

重建测试环境

SQL> conn liutya/liutya

Connected.

SQL>

SQL> create database link lecctest connect to mytest identified by mytest using 'ecctest';

Database link created.

SQL> conn / as sysdba

Connected.

SQL>

SQL> select * from dba_db_links;

OWNER                DB_LINK              USERNAME             HOST                                                         CREATED

-------------------- -------------------- -------------------- ------------------------------------------------------------ ---------

LIUTYA               LECCTEST             MYTEST               ecctest                                                      12-DEC-13

SQL> show user;

USER is "SYS"

方法二、使用下面的存储过程

Create or replace procedure Drop_DbLink(schemaName varchar2, dbLink varchar2 ) is

plsql   varchar2(1000);

cur     number;

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;

/

SQL> Create or replace procedure Drop_DbLink(schemaName varchar2, dbLink varchar2 ) is

2              plsql   varchar2(1000);

3              cur     number;

4              uid     number;

5              rc      number;

6      begin

7              select

8                      u.user_id into uid

9             from    dba_users u

10             where   u.username = schemaName;

11               plsql := 'drop database link "'||dbLink||'"';

12               cur := SYS.DBMS_SYS_SQL.open_cursor;

13               SYS.DBMS_SYS_SQL.parse_as_user(

14                     c => cur,

15                     statement => plsql,

16                     language_flag => DBMS_SQL.native,

17                     userID => uid

18            );

19               rc := SYS.DBMS_SYS_SQL.execute(cur);

20

21               SYS.DBMS_SYS_SQL.close_cursor(cur);

22     end;

23     /

Procedure created.

注意输入参数全部为大写。

SQL> exec drop_dblink('LIUTYA','LECCTEST');

PL/SQL procedure successfully completed.

SQL> select * from dba_db_links;

no rows selected

SQL> drop procedure drop_dblink;

Procedure dropped.

测试同样成功。

参考资料

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11590946/viewspace-1063080/，如需转载，请注明出处，否则将追究法律责任。