Remote Select from Partitions

Carsten Kaftan

select * from TABLE_NAME partition(PARTITION_NAME);

is correct.

select * from [email protected] partition(PARTITION_NAME);

throws "ORA-14100: partition extended table name cannot refer to a remote object"

Please enable direct referencing of partitions over database links.

Lothar Flatz

I missed that syntax too in one of my projects.

Mr.Shah

You cannot query a particular partition over db link.

Workaround: Use where clause on partitioned column.

William Robertson

Unless there is some conceptual or architectural obstacle I am missing, it does seem a rather arbitrary limitation. My guess is it is there to limit the amount of data that needs to be pulled from the remote site in order to parse the query. Perhaps it could be reviewed though.

Marek Läll

> Workaround: Use where clause on partitioned column

Oracle has made that complex as well.

I could use high_value(s) in where clause but the column type of "dba_tab_partitions.high_value" is LONG. Retrieving it over database link and concatenating (to build up sql query) can not be done using SQL.

Another use case I miss is referencing to subpartitions over database link:

     select * from [email protected] subpartition(SUB_PARTITION_NAME);

Can you build a where clause for hash subpartitions based on data in view dba_tab_subpartitions?

user5679830

I have implemented a technique using views (1 view per partition) which works in oracle 12.2. 

On the source system, create the view to select from single table partition -

create or replace view mike.mike_test_p1 as select * from test_table partition(P1);

select count(*) from mike_test_p1;

10025

On the destination system, select from the view across db link -

select count(*) from [email protected]_link;

10025