Hi,

I am using EXCHANGE PARTITION command on my database, so the live table has the least unavailable time.

I have a partitioned table, with approximately 20 columns; 5 of them are nested tables.
I also have a non-partitioned table which has exactly the same structure as the partitioned one, including nested tables.

This is how I'm issuing the command

ALTER TABLE TABLE_PART EXCHANGE PARTITION TABLE_PART_P1 WITH TABLE TABLE_UNPART WITHOUT VALIDATION

Once I create the tables and have data on the unpartitioned table, I'm able to successfully execute the above command and the partitioned one ends up with the data correctly.

I also have a job that runs every 1/2 hour and refreshes data on the unpartitioned table. Then it issues the EXCHANGE PARTITION command and the live table will get updated information, which is ok.

If I let the job execute for some days, it ends up failing on the EXCHANGE PARTITION command. This is the error I get:

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 is a temporary 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.

I've tried marking the partition indexes as unusable, then rebuilding them, but witout success.

I've also created a pair of partitioned/unpartitioned tables (test tables), inserted data from the original pair tables and then run the EXCHANGE PARTITION command successfully on the test tables. Then I compared the DDL on all objects (tables, nested tables, types, etc...) but didn't find any difference. I've also compared the table partition and index partitions (on original and test tables), but didn't find any differnces.
Then I moved on to the segments, but no differences either.

Now, if I remove the nested tables, it will work properly. Then, if I add them once again, it will stop working.
So the only way to get it working is by dropping both tables and re-creating them. It won't work if I only drop the partitioned or unpartitioned one. I have to do this every other week, once it starts failing.


My questions are:
Why am I able to run the command, even if the error clearly states the non-partitioned cannot have nested tables?
What could have changed internally, that is not allowing me to run the command?
If I were never allowed to execute this command, I would understand as it simply wasn't supported. But the behaviour is not linear, I can do it successfully until "something" happens, then I can't do it anymore.

Here are the DDL/DML instructions for creating objects/populating tables/exchange partitions:

--Drops
DROP TABLE TABLE_PART
/
DROP TABLE TABLE_UNPART
/
DROP TYPE APPLICATION_TYPE
/
DROP TYPE APPLICATION_OBJECT
/
--Types
CREATE TYPE APPLICATION_OBJECT AS OBJECT (
  application_id     NUMBER,
  application_name   VARCHAR2 (2000 CHAR)
)
/
CREATE TYPE APPLICATION_TYPE AS TABLE OF APPLICATION_OBJECT
/
--Partitioned table
CREATE TABLE TABLE_PART
   (    Id             NUMBER, 
        name           VARCHAR2(500 CHAR) CONSTRAINT TABLE_PART_NAME NOT NULL,
        applications   APPLICATION_TYPE
   ) NOLOGGING
  NESTED TABLE APPLICATIONS STORE AS APPLICATION_PART_NT
 PARTITION BY RANGE (name) 
 (PARTITION TP_P1  VALUES LESS THAN (MAXVALUE))
/
CREATE INDEX TABLE_PART_I ON TABLE_PART (name) 
  TABLESPACE /*YOUR_INDEX_TABLESPACE*/  LOCAL
 (PARTITION TP_P1 TABLESPACE /*YOUR_INDEX_TABLESPACE*/ ) 
/
CREATE INDEX I_APPLICATIONS_PART ON APPLICATION_PART_NT (NESTED_TABLE_ID, application_name)
/
--Unpartitioned table
CREATE TABLE TABLE_UNPART
   (    Id             NUMBER, 
        name           VARCHAR2(500 CHAR) CONSTRAINT TABLE_UNPART_NAME NOT NULL,
        applications   APPLICATION_TYPE
   ) NOLOGGING
 NESTED TABLE APPLICATIONS STORE AS APPLICATION_UNPART_NT
/
CREATE INDEX I_APPLICATIONS_UNPART ON APPLICATION_UNPART_NT (NESTED_TABLE_ID, application_name)
/
CREATE INDEX TABLE_UNPART_I ON TABLE_UNPART (name)
/
--DMLs
insert into TABLE_UNPART values (1 , 'Scott', APPLICATION_TYPE(APPLICATION_OBJECT(1,'ORACLE'), APPLICATION_OBJECT(2,'APACHE'), APPLICATION_OBJECT(3,'TOMCAT')))
/
insert into TABLE_UNPART values (2 , 'Henry', APPLICATION_TYPE(APPLICATION_OBJECT(1,'APACHE'), APPLICATION_OBJECT(2,'SAP'), APPLICATION_OBJECT(3,'ORACLE')))
/
insert into TABLE_UNPART values (3 , 'Alice', APPLICATION_TYPE(APPLICATION_OBJECT(3,'TOMCAT'), APPLICATION_OBJECT(4,'TOMCAT'), APPLICATION_OBJECT(666, 'SAP')))
/
commit
/
--data on unpartitioned table
select  tu.id,
        tu.name,
        app.*
from TABLE_UNPART tu, table(applications) app
/
--EXCHANGE PARTITION
ALTER TABLE TABLE_PART EXCHANGE PARTITION TP_P1 WITH TABLE TABLE_UNPART WITHOUT VALIDATION
/
--data on partitioned table
select  tu.id,
        tu.name,
        app.*
from TABLE_PART tu, table(applications) app
/

Thanks in advance,
Carlos

ALTER TABLE TABLE_PART EXCHANGE PARTITION TP_P1 WITH TABLE TABLE_UNPART WITHOUT VALIDATION
/

select  tu.id,
        tu.name,
        app.*
from TABLE_PART tu, table(applications) app
/

ID   NAME    APPLICATION_ID   APPLICATION_NAME   
   1 Scott                  1 ORACLE             
   1 Scott                  2 APACHE             
   1 Scott                  3 TOMCAT    

select DBMS_STATS.create_extended_stats(ownname   => user,
                                        tabname   => 'TABLE_PART',
                                        extension => '(ID,NAME)')
from   dual;

DBMS_STATS.CREATE_EXTENDED_STATS(OWNNAME=>USER,TABNAME=>'TABLE_PART',EXTENSION=>'(ID,NAME)')   
SYS_STUIA0V924QODN5R5SCAKM60G#   

ALTER TABLE TABLE_PART EXCHANGE PARTITION TP_P1 WITH TABLE TABLE_UNPART WITHOUT VALIDATION
/

ORA-14132: table cannot be used in EXCHANGE

TABLE_NAME     COLUMN_NAME                      INTERNAL_COLUMN_ID   HIDDEN_COLUMN   
TABLE_PART     ID                                                  1 NO              
TABLE_PART     NAME                                                2 NO              
TABLE_PART     APPLICATIONS                                        3 NO              
TABLE_PART     SYS_NC0000300004$                                   4 YES             
TABLE_PART     SYS_STUIA0V924QODN5R5SCAKM60G#                      5 YES             
TABLE_UNPART   ID                                                  1 NO              
TABLE_UNPART   NAME                                                2 NO              
TABLE_UNPART   APPLICATIONS                                        3 NO              
TABLE_UNPART   SYS_NC0000300004$                                   4 YES 

select * from user_stat_extensions;

TABLE_NAME   EXTENSION_NAME                   EXTENSION       CREATOR   DROPPABLE   
TABLE_PART   SYS_STUIA0V924QODN5R5SCAKM60G#   ("ID","NAME")   USER      YES

exec dbms_stats.drop_extended_stats( user, 'table_part', '(id, name)' );

ALTER TABLE TABLE_PART EXCHANGE PARTITION TP_P1 WITH TABLE TABLE_UNPART WITHOUT VALIDATION;

select  tu.id,
        tu.name,
        app.*
from    TABLE_PART tu, table(applications) app
/

no rows selected