Partition Clause Over The Dblink Is Not Supported, Generates ORA-2070 or ORA-14100 Error

(Doc ID 734342.1)

Last updated on MAY 10, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 8.1.7.0 to 11.1.0.6 [Release 8.1.7 to 11.1]
Information in this document applies to any platform.

Symptoms


Select the remote table with the partition clause gives the following error :

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

 Inserting into remote table, but selecting from local table with the partition clause gives the following error :

 

ORA-02070: database <"name"> does not support capabilit<y> <name >in this context



Same can be verified from the following example: 

 

 

-- Small example of how to reproduce our problem
Connect to the test schema SCOTT/TIGER
-- Create table with interval partitioning at the Primary DB
CREATE TABLE org
(
COL1 varchar2(6 CHAR),
period varchar2(6 CHAR),
saved timestamp DEFAULT SYSTIMESTAMP NOT NULL
)
PARTITION BY RANGE (saved)
INTERVAL (numtodsinterval(1,'DAY') )
(partition p_first values less than (TO_DATE('20010101','YYYYMMDD')));

-- Create table with interval partitioning at the Secondary DB
CREATE TABLE copy
(
COL1 varchar2(6 CHAR),
period varchar2(6 CHAR),
saved timestamp DEFAULT SYSTIMESTAMP NOT NULL,
partitioncol DATE AS (TO_DATE(period ||'01','YYYYMMDD'))
)
PARTITION BY RANGE (PARTITIONCOL)
INTERVAL (numtoyminterval(1,'MONTH') )
(partition p_first values less than (TO_DATE('20010101','YYYYMMDD')));

-- Load the data into source table
insert into org (col1, period) values ('hej', '200808');
commit;

-- Create a databaselink from Source to Target DB.
CREATE DATABASE LINK TESTLINK CONNECT TO scott IDENTIFIED BY tiger USING 'your_instance';

-- Check the table
SELECT col1, period, saved FROM org partition for (to_date('2008-08-21','yyyy-mm-dd'));

-- Insert the records into remote Table using dblink with partition caluse.

INSERT INTO scott.copy @ TESTLINK (col1, period, saved )
(SELECT col1, period, saved FROM org
partition for(to_date('2008-08-21','yyyy-mm-dd')));

INSERT INTO scott.copy @ TESTLINK (col1, period, saved ) (SELECT col1, period, saved FROM org
*
ERROR at line 1:
ORA-02070: database TESTLINK does not support extended partition name in this context

-- If you try it without partition clause.
INSERT INTO scott.copy @ TESTLINK (col1, period, saved )
(SELECT col1, period, saved FROM org);

1 row created.

-- If you try to Select the table over the Dblink with partition clause.

select * from scott.copy@ TESTLINK
2 partition (to_date('2008-08-21','yyyy-mm-dd'));
select * from scott.copy @ TESTLINK
*
ERROR at line 1:
ORA-14100: partition extended table name cannot refer to a remote object.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms