This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,747 Users
  • 2,269,776 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 Syed Zeshan 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 London, UKMember Posts: 9,579 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;