在分析测试库：edu@analysetest
①对要 被调用的包 适当授权给edu@analysetest
grant all on dbms_flashback to edu;
edu@edutest
②找出可用的db link
select db_link from dba_db_links;
---------------
DB_LINK
-----------------
EDU_FX
EDU_CLONE
EDU_XXT
TEST_YFLORC
ORA_MYSQLTEST
EDU_XXTWEB
EDU_XXTHIS.XXT.CN
EDU_XXTBAK
EDU_NMDBC.XXT.CN
EDU_MYSQL
EDU_JL
DB_LINK
-----------------
EDU_FX
EDU_BLOGTEST
EDU_BLOG
CJ1
MMS_XXT
TEST.XXT.CN
EDU_CLONE
已选择18行。
③选择可以使用DB Link进行远程和本地执行:
SQL> select count(*)
2 from organization@edu_fx;
COUNT(*)
----------
16509
④config@分析测试 edu测试库
create table o_test(name varchar2(40));
/
insert into o_test
values('涛涛不绝于耳@edu_test')
create table o_test(name varchar2(40));
/
insert into o_test
values('涛涛不绝于耳@analyse_test')
⑤测试：
SQL> select count(*)
2 from organization@edu_fx;
COUNT(*)
----------
16509
SQL> set serveroutput on
SQL> set feedback off
SQL> declare
2 test_name organization.name%type;
3 test_name1 organization.name%type;
4 begin
5 execute immediate
6 'select o.name from o_test@edu_fx o' into test_name;
7 dbms_output.put_line('测试名字 of remote:'||test_name);
8 select o.name into test_name1 from o_test o;
9 dbms_output.put_line('测试名字 of locald:'||test_name1);
10 end;
11 /
测试名字 of remote:涛涛不绝于耳@analyse_test
测试名字 of locald:涛涛不绝于耳@edu_test
⑥远程Package或Function调用也可以随之实现:
SQL> declare
2 r_scn number;
3 begin
4 execute immediate
5 'select dbms_flashback.GET_SYSTEM_CHANGE_NUMBER@edu_fx from dual' into r_scn;
6 dbms_output.put_line('scn of remote:'||r_scn);
7 end;
8 /
scn of remote:18865075287
--------------------附记 第一步的授权很重要，囿于时间所限，先写这一点。
edit on 2010-07-31
