Home » SQL & PL/SQL » SQL & PL/SQL » Problem in partition exchange (Oracle 12 C 2)
Problem in partition exchange [message #686318] Wed, 27 July 2022 09:09 Go to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member

Hi All,

I have a table for example emp which is partitioned but i can see a column present with name as SYS_NC00077$ with data type as RAW. So When I am doing as follows I am getting ORA-14097 , please help on this regards.

create table temp_emp for exchange with table emp;
alter table temp_emp  add constraint xempno primary key (empno);

alter table emp exchange partition p1 with table temp_emp;

(here i am getting error ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION )

I found that 1 extra column SYS_NC00077$ found in emp table , how can i get rid of this column so that i can perform partition.

Thanks


Re: Problem in partition exchange [message #686319 is a reply to message #686318] Wed, 27 July 2022 09:16 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Could be a column used for a function based index. Check the DATA_DEFAULT for the column, as shown in dba_tab_cols.
Re: Problem in partition exchange [message #686320 is a reply to message #686319] Wed, 27 July 2022 09:21 Go to previous messageGo to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member

Thank you for your reply,

I have checked there it shows null as there is no function based index built on it. So any other way to drop this.
Re: Problem in partition exchange [message #686321 is a reply to message #686318] Wed, 27 July 2022 09:34 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
There are some other examples here,
https://www.orafaq.com/forum/t/191402/
Re: Problem in partition exchange [message #686323 is a reply to message #686321] Wed, 27 July 2022 12:00 Go to previous messageGo to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member

Actually I had gone through this but could not understand how to find why that column is created by Oracle and how to remove it.
Re: Problem in partition exchange [message #686324 is a reply to message #686323] Wed, 27 July 2022 12:21 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Come on, man. You could at least run the query I used in the last post in that other topic to show what the column is. And try to interpret the result. How else do you expect to get any insight?

You could also extract the DDL for the table with dbms_metadata, that might show you what is going on.

As for removing the column, why do you want to do that? It will be there for a reason.
Previous Topic: Plsql function testing years
Next Topic: Create comma delimited function
Goto Forum:
  


Current Time: Thu Mar 28 15:10:33 CDT 2024