[20180211]dblink查询单个分区数据.txt

[20180211]dblink查询单个分区数据.txt

1.环境:

SCOTT@book> @ &r/ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

CREATE PUBLIC DATABASE LINK LOOPBACK
CONNECT TO SCOTT
IDENTIFIED BY <PWD>
USING 'localhost:1521/book:DEDICATED';

2.测试:
SCOTT@book> select count(*) from sh.sales ;
  COUNT(*)
----------
    918843

SCOTT@book> select count(*) from sh.sales partition(SALES_Q1_1998);
  COUNT(*)
----------
     43687

SCOTT@book> select count(*) from sh.sales@loopback ;
  COUNT(*)
----------
    918843

SCOTT@book> select count(*) from sh.sales@loopback partition(SALES_Q1_1998);
select count(*) from sh.sales@loopback partition(SALES_Q1_1998)
                        *
ERROR at line 1:
ORA-14100: partition extended table name cannot refer to a remote object   
    
$ oerr ora 14100
14100, 00000, "partition extended table name cannot refer to a remote object"
// *Cause:  User attempted to use partition-extended table name syntax
//          in conjunction with remote object name which is illegal
// *Action: Correct the statement and reenter

3.不知道如何实现单个分区查询通过dblink,或者语法存在问题...也许通过视图可以绕过...不过不是很好..
--//放弃!!!

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

转载于:http://blog.itpub.net/267265/viewspace-2151026/