1

Topic: Sectioning ORA-14097

Good afternoon all
The following situation:

 test_ora@STHPUXBO>select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for HPUX: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
test_ora@STHPUXBO>create table test_table (num number);
Table created.
test_ora@STHPUXBO>insert into test_table
2 select rownum
3 from user_objects
4 where rownum <1000;
999 rows created.
test_ora@STHPUXBO>alter table test_table add (my_date date default to_date (' 1/1/2010 ', ' dd.mm.yyyy ') not null);
Table altered.
test_ora@STHPUXBO>rename test_table to test_table_temp;
Table renamed.
test_ora@STHPUXBO>create table test_table (num number, my_date date default to_date (' 1/1/2010 ', ' dd.mm.yyyy ') not null)
2 PARTITION BY RANGE (my_date)
3 INTERVAL (NUMTOYMINTERVAL (1, ' MONTH '))
4 (
5 PARTITION test_table_201001 VALUES LESS THAN (TIMESTAMP ' 2010-02-01 0:00:00 AM ')
6);
Table created.
test_ora@STHPUXBO>alter table test_table exchange partition test_table_201001 with table test_table_temp without validation;
alter table test_table exchange partition test_table_201001 with table test_table_temp without validation
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

Somebody can explain a cause of error?
If the column has only restriction not null or default - that all works. If they are are not present.
If to look at the description of columns of both tables request:
select c.*
from sys.col $ c join sys.obj $ o on o.obj#=c.obj#
where o.name=upper (' test_table_temp ')
order by col#;
That the unique difference at the table test_table_temp in the field property for a column my_date will be value 1073741824, and in test_table there will be a zero.
That it for a field sys.col $. Property, who can knows?

2

Re: Sectioning ORA-14097

On this subject is, for example, Bug 9598610. The Disorder any with defaults.
PS ( not especially useful):
col $. Property is a bit mask of field properties (some representation about its contents it is possible to receive,  the code  dba_tab_cols or dba_updatable_columns or.):
to_char (1073741824, ' XXXXXXXX ') = 40000000, but here that in this quadruple is ciphered - I do not know...

3

Re: Sectioning ORA-14097

Clearly, thanks, mean I will pour at first the data, and then  not null.