OraFAQ Forum: SQL & PL/SQL » Problem in partition exchange

Home » SQL & PL/SQL » SQL & PL/SQL » Problem in partition exchange (Oracle 12 C 2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Problem in partition exchange [message #686318]

Wed, 27 July 2022 09:09

sss111ind
Messages: 628
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

Report message to a moderator

Re: Problem in partition exchange [message #686319 is a reply to message #686318]

Wed, 27 July 2022 09:16

John Watson
Messages: 8804
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.

Report message to a moderator

Re: Problem in partition exchange [message #686320 is a reply to message #686319]

Wed, 27 July 2022 09:21

sss111ind
Messages: 628
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.

Report message to a moderator

Re: Problem in partition exchange [message #686321 is a reply to message #686318]

Wed, 27 July 2022 09:34

John Watson
Messages: 8804
Registered: January 2010
Location: Global Village

Senior Member

There are some other examples here,
https://www.orafaq.com/forum/t/191402/

Report message to a moderator

Re: Problem in partition exchange [message #686323 is a reply to message #686321]

Wed, 27 July 2022 12:00

sss111ind
Messages: 628
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.

Report message to a moderator

Re: Problem in partition exchange [message #686324 is a reply to message #686323]

Wed, 27 July 2022 12:21

John Watson
Messages: 8804
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.

Report message to a moderator

Previous Topic:	Plsql function testing years
Next Topic:	Create comma delimited function

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Mar 28 09:28:18 CDT 2023