Forum Stats

  • 3,824,847 Users
  • 2,260,430 Discussions


Remote Select from Partitions

Carsten Kaftan
Carsten Kaftan Member Posts: 10 Bronze Badge
edited Jan 11, 2016 6:12PM in Database Ideas - Ideas

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.

Carsten KaftanManish ChaturvediUser259623 -OraclemarkmevansctriebcaadecarvalhoPrasad.Gadiraju-Oraclevinaykumar2rohanwaliaArpit Jain -OracleAparna Dutta-OraclePravin TakpireLothar FlatzberxborneselJagadekarabhagatsinghRodrigo Jorge DBAAish13top.gunBrian BakulasysassysdbaMr.ShahFranck PachotulohmannWilliam RobertsonBPeaslandDBAUser_FLNLB3280107Sven W.BEDEGerald Venzl-OracleMarek Läll
33 votes

Active · Last Updated


  • Lothar Flatz
    Lothar Flatz Member Posts: 687 Silver Badge

    I missed that syntax too in one of my projects.

  • Mr.Shah
    Mr.Shah Member Posts: 9

    You cannot query a particular partition over db link.

    Workaround: Use where clause on partitioned column.

    BrunoVromanMaciej Tokar
  • William Robertson
    William Robertson Member Posts: 9,567 Bronze Crown

    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
    Marek Läll Member Posts: 23 Blue Ribbon

    > 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?

    William Robertson
  • 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;


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

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