[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,或者語法存在問題...也許透過檢視可以繞過...不過不是很好..
--//放棄!!!