Skip to Main Content
  • Questions

  • Exchange Partition - Error with VIRTUAL & CLOB columns

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, muzavvir.

Asked: January 04, 2016 - 7:45 am UTC

Last updated: September 13, 2018 - 1:01 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hi Folks,

Need your expert advice on this. Could you please guide me through the below issue -

Issue : Exchange partition doesn't seem to work when both Virtual column and CLOB column exists together in a table :( . However it works fine on it individual existence.

drop table t_x purge;
drop table t purge;

-- The partitioned table
create table t 
(
   id number, 
   virt_col as (id || '-TEST'),
   clob_col clob
) tablespace warehouse_small_data
partition by list (id)
( partition p_default values (1));

-- The non-partitioned table
create table t_x
tablespace warehouse_small_data
as SELECT 1 id from dual;

alter table t_x add (clob_col clob);
alter table t exchange partition p_default with table t_x including indexes without validation;

select * from t;
select * from t_x;

and Connor said...

(Thanks for test case...makes life much easier!)

I'm not sure why you think this would work ? The tables end up with different structure:

SQL> desc T
 Name                          Null?    Type
 ----------------------------- -------- ------------
 ID                                     NUMBER
 VIRT_COL                               VARCHAR2(45)
 CLOB_COL                               CLOB

SQL> desc T_X
 Name                          Null?    Type
 ----------------------------- -------- ------------
 ID                                     NUMBER
 CLOB_COL                               CLOB


Hence...

SQL> alter table t exchange partition p_default with table t_x including indexes
  2  without validation;
alter table t exchange partition p_default with table t_x including indexes
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE
EXCHANGE PARTITION


If the columns DO match, then it seems to go through fine

SQL> drop table t_x purge;

Table dropped.

SQL> drop table t purge;

Table dropped.

SQL>
SQL> -- The partitioned table
SQL> create table t
  2  (
  3     id number,
  4     virt_col as (id || '-TEST'),
  5     clob_col clob
  6  )
  7  partition by list (id)
  8  ( partition p_default values (1));

Table created.

SQL>
SQL> -- The non-partitioned table
SQL> create table t_x
  2  (   id number,
  3     virt_col as (id || '-TEST'),
  4     clob_col clob
  5  );

Table created.

SQL>
SQL> alter table t exchange partition p_default with table t_x including indexes
  2  without validation;

Table altered.


Am I misinterpreting your question ?

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, January 04, 2016 - 8:28 am UTC

Aahh.. My bad, I must have overlooked. Thanks Connor.
Connor McDonald
January 04, 2016 - 11:27 am UTC

No probs. Glad it worked out easy.

Desc for table T_X differs

Rajeshwaran, Jeyabal, January 04, 2016 - 12:56 pm UTC 

I'm not sure why you think this would work ? The tables end up with different structure: 

SQL> desc T
 Name                          Null?    Type
 ----------------------------- -------- ------------
 ID                                     NUMBER
 VIRT_COL                               VARCHAR2(45)
 CLOB_COL                               CLOB

SQL> desc T_X
 Name                          Null?    Type
 ----------------------------- -------- ------------
 ID                                     NUMBER
 CLOB_COL                               CLOB


Not like this, but it is like this.

rajesh@ORA11G> -- The partitioned table
rajesh@ORA11G> create table t
  2  (
  3     id number,
  4     virt_col as (id || '-TEST'),
  5     clob_col clob
  6  )
  7  --tablespace warehouse_small_data
  8  partition by list (id)
  9  ( partition p_default values (1));

Table created.

rajesh@ORA11G>
rajesh@ORA11G> -- The non-partitioned table
rajesh@ORA11G> create table t_x
  2  --tablespace warehouse_small_data
  3  as SELECT 1 id from dual;

Table created.

rajesh@ORA11G> set linesize 71
rajesh@ORA11G> desc t
 Name                                Null?    Type
 ----------------------------------- -------- -------------------------
 ID                                           NUMBER
 VIRT_COL                                     VARCHAR2(45)
 CLOB_COL                                     CLOB

rajesh@ORA11G> desc t_x
 Name                                Null?    Type
 ----------------------------------- -------- -------------------------
 ID                                           NUMBER

rajesh@ORA11G>

Connor McDonald
January 05, 2016 - 1:56 am UTC

yes, but then the poster did:

"alter table t_x add (clob_col clob);"

(which still didnt get a matching set)

HARI PRASAD kurapati, September 11, 2018 - 12:58 pm UTC

Hi Tom,

I am working on converting Oracle queue table to partition table.
Database version is 12C.

Queue Table is - QUEUE_TABLE
interim table -- QUEUE_TABLE_11092018
Queue payload is TYPE object --USER_DATA

create table QUEUE_TABLE_11092018
partition by range (ENQ_TIME)
interval(numtodsinterval(1,'DAY'))
(
partition PART_01 values less than (TO_DATE('10/09/2018', 'DD/MM/YYYY'))) ENABLE ROW MOVEMENT
as select * from QUEUE_TABLE where 1=2;


When i created interim table as CTAS and tried to do exchange partition getting
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

When i verified the data type and data length in sys.col$ or user_tab_cols it is different data length for type object.

select col#, name, type#, length, precision# from sys.col$
where obj# = (select object_id from dba_objects where object_name='QUEUE_TABLE_11092018' and object_type='TABLE' )

28 USER_DATA 121 3
(Length is 3)

When i verify the same query for Queue table it is showing data length as 1.

In other database's it is working fine, only in 12C or 11G for few schema's it is showing different data length and exchange partition is failing.

Can you please advise on this.

Thank You.

Connor McDonald
September 13, 2018 - 1:01 am UTC

You already asked this as a separate question
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.