Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.4K Intelligent Advisor
- 75 Insurance
- 537.7K On-Premises Infrastructure
- 138.7K Analytics Software
- 38.6K Application Development Software
- 6.1K Cloud Platform
- 109.6K Database Software
- 17.6K Enterprise Manager
- 8.8K Hardware
- 71.3K Infrastructure Software
- 105.4K Integration
- 41.6K Security Software
Remote Select from Partitions

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.
Comments
-
I missed that syntax too in one of my projects.
-
You cannot query a particular partition over db link.
Workaround: Use where clause on partitioned column.
-
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.
-
> 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?
-
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