Home » RDBMS Server » Server Administration » Partition Exchange Error On Table With Nested Table (Oracle 11g, 11.2.0.1.0 - 64bit, windows)
Partition Exchange Error On Table With Nested Table [message #633455] Thu, 19 February 2015 03:36 Go to next message
bibek.dada.dada@gmail.com
Messages: 2
Registered: February 2015
Location: India
Junior Member
Dear all,

Two ORACLE schemas (test1 and test2) have same table.
test1 has partitioned Table With Nested Table.
test2 has non-partitioned Table With Nested Table.
test1.CUSTOMERS table is empty.
test2.CUSTOMERS table has data with dated 2012-12-19.
Now I am trying to exchange partition test1.CUSTOMERS with test2.CUSTOMERS and getting error.

any workaround?

--for schemas test1
create or replace
TYPE address_t AS OBJECT (
street VARCHAR2(30),
city VARCHAR2(20),
state CHAR(2),
zip CHAR(5) );

create or replace
TYPE address_tab IS TABLE OF address_t;

CREATE TABLE test1.CUSTOMERS
(
CUSTID NUMBER,
DT DATE,
ADDRESS ADDRESS_TAB
)
TABLESPACE USERS NESTED TABLE ADDRESS GLOBAL STORE AS CUSTOMER_ADDRESSES
RETURN AS VALUE PARTITION BY RANGE
(
DT
)
(
PARTITION P_20121219 VALUES LESS THAN (TO_DATE(' 2012-12-20 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
PARTITION P_20121220 VALUES LESS THAN (TO_DATE(' 2012-12-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
PARTITION P_20121221 VALUES LESS THAN (TO_DATE(' 2012-12-22 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
PARTITION P_MAXVALUE VALUES LESS THAN (MAXVALUE)
) ;

--for schemas test2
create or replace
TYPE address_t AS OBJECT (
street VARCHAR2(30),
city VARCHAR2(20),
state CHAR(2),
zip CHAR(5) );

create or replace
TYPE address_tab IS TABLE OF address_t;

CREATE TABLE test2.CUSTOMERS
(
CUSTID NUMBER,
DT DATE,
ADDRESS ADDRESS_TAB
)
TABLESPACE USERS NESTED TABLE ADDRESS GLOBAL STORE AS CUSTOMER_ADDRESSES;

ALTER TABLE test1.CUSTOMERS
EXCHANGE PARTITION P_20121219
WITH TABLE test2.CUSTOMERS
INCLUDING INDEXES
WITHOUT VALIDATION
UPDATE GLOBAL INDEXES;

Error report:
SQL Error: ORA-14132: table cannot be used in EXCHANGE
14132. 00000 - "table cannot be used in EXCHANGE"
*Cause: An attempt was made to issue an ALTER TABLE EXCHANGE PARTITION |
SUBPARTITION command, but the non-partitioned table cannot be used
in the EXCHANGE because one or more of the following apply:
- it is a typed table
- it contains ADT columns
- it contains nested-table columns
- it contains REF columns
- it contains array columns
- it is an index-organized table
- it contains LOB columns
- it is a nested table
- it is created with row dependency and the partitioned table is not
- it is created without row dependency and the partitioned table is
*Action: Make sure the non-partitioned table does not violate any
of the above restrictions for the ALTER TABLE EXCHANGE PARTITION |
SUBPARTITION command.
Re: Partition Exchange Error On Table With Nested Table [message #633456 is a reply to message #633455] Thu, 19 February 2015 03:56 Go to previous messageGo to next message
cookiemonster
Messages: 13017
Registered: September 2008
Location: Rainy Manchester
Senior Member
Since the error message is very clear that what you are attempting is illegal I very much doubt there's a workaround.
Re: Partition Exchange Error On Table With Nested Table [message #633457 is a reply to message #633455] Thu, 19 February 2015 03:57 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

One more reason to NEVER use non relational datatype in a rdbms.

The only workaround is to use "insert select"/"truncate" and no partition operation.

[Updated on: Thu, 19 February 2015 03:58]

Report message to a moderator

Re: Partition Exchange Error On Table With Nested Table [message #633795 is a reply to message #633457] Tue, 24 February 2015 22:59 Go to previous message
bibek.dada.dada@gmail.com
Messages: 2
Registered: February 2015
Location: India
Junior Member
Thanks
Previous Topic: Changing SGA_MAX_SIZE
Next Topic: killing all sessions
Goto Forum:
  


Current Time: Sat Feb 24 09:11:34 CST 2018

Total time taken to generate the page: 0.01188 seconds