Oracle - exchange partitions with a table?

Question

I try to create a table2 on Oracle 11g.2.0.3 with: CREATE table2 LOGGING TABLESPACE TS_table1_2014 PCTFREE 10 INITRANS 1 STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS UNLIMITED BUFFER_POOL DEFAULT ) NOCOMPRESS as (select * from table1 where date_text <= '2015-12-31'); and I have received error below when I try to exchange this table2 with a partitioned table3: alter table table3 exchange partition partition_name WITH TABLE table2; Error report - SQL Error: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION 14097. 00000 - "column type or size mismatch in ALTER TABLE EXCHANGE PARTITION" *Cause: The corresponding columns in the tables specified in the ALTER TABLE EXCHANGE PARTITION are of different type or size *Action: Ensure that the two tables have the same number of columns with the same type and size. I have test diferences with query below: Select a.COLUMN_NAME , a.DATA_TYPE, b.DATA_TYPE , a.data_length, b.data_length , a.data_precision, b.data_precision , a.data_scale, b.data_scale , a.nullable, b.nullable from ALL_TAB_COLUMNS a full outer join ALL_TAB_COLUMNS b on a.column_name=b.column_name and b.owner=user and b.table_name='&table2' where a.owner=user and a.table_name='&table1' and ( nvl(a.data_type,'#')!=nvl(b.data_type,'#') or nvl(a.data_length,-1)!=nvl(b.data_length,-1) or nvl(a.data_precision,-100)!=nvl(b.data_precision,-100) or nvl(a.data_scale,-100)!=nvl(b.data_scale,-100) or nvl(a.nullable,'#')!=nvl(b.nullable,'#') ) ; Some differences resulted are in a column size. This syntax "create as select" didn't keep order and size for new table created. How can I create table2 **as select** from table1 with force keep same size columns as primary table1 source? Thanks!

Answer 1

You couple of options:

1. Use CAST to force the datatype . You'll have to find the mismatch columns and use cast to align it with the table1 datatype. Example create table table2 as select cast( col1 as varchar2(20) ) as col1 etc.... where date_text <= '2015-12-31' . Refrain from using * and list out all columns in the source table table1.

Beside CTAS above , you may try :

1. Using the "FOR PARTITION EXCHANGE" option of the create table command. This is a two steps process
2. Create table2 tablespace TS_table1_2014 for exchange with table1;
3. Insert into table2 select * from table1;
4. dbms_metadata.get_ddl('TABLE', 'TABLE2');

then get the ddl code and execute it, when table2 is created,

insert the data with

insert into TABLE2 select * from TABLE1;