Home » SQL & PL/SQL » SQL & PL/SQL » ORA-14097 (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 )
ORA-14097 [message #662515] Thu, 04 May 2017 15:37 Go to next message
scottwmackey
Messages: 515
Registered: March 2005
Senior Member
Hi all,

I am trying to do a partition exchange and seeing this

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 
 
SQL> ALTER TABLE user_portfolios EXCHANGE PARTITION FOR (2368)  WITH TABLE n_155569_c1;
 
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

I am creating the n_155569_c1 by getting the definition from

SELECT dbms_metadata.get_ddl(object_type => 'TABLE', name => 'USER_PORTFOLIOS') FROM dual;

and just changing the name. When I've googled this, all I could find is something about dropped columns being a problem. They say to look at dba_tab_cols to find "hidden" columns. I ran this comparison SQL to see if there are any differences between the two tables.

WITH q1 AS (
         SELECT owner,
                column_name,
                data_type,
                data_type_mod,
                data_type_owner,
                data_length,
                data_precision,
                data_scale,
                nullable,
                column_id,
                default_length,
                num_distinct,
                low_value,
                high_value,
                density,
                num_nulls,
                num_buckets,
                last_analyzed,
                sample_size,
                character_set_name,
                char_col_decl_length,
                global_stats,
                user_stats,
                avg_col_len,
                char_length,
                char_used,
                v80_fmt_image,
                data_upgraded,
                hidden_column,
                virtual_column,
                segment_column_id,
                internal_column_id,
                histogram,
                qualified_col_name
           FROM dba_tab_cols
          WHERE table_name = 'USER_PORTFOLIOS'
            AND owner = USER)
    ,q2 AS (
         SELECT owner,
                column_name,
                data_type,
                data_type_mod,
                data_type_owner,
                data_length,
                data_precision,
                data_scale,
                nullable,
                column_id,
                default_length,
                num_distinct,
                low_value,
                high_value,
                density,
                num_nulls,
                num_buckets,
                last_analyzed,
                sample_size,
                character_set_name,
                char_col_decl_length,
                global_stats,
                user_stats,
                avg_col_len,
                char_length,
                char_used,
                v80_fmt_image,
                data_upgraded,
                hidden_column,
                virtual_column,
                segment_column_id,
                internal_column_id,
                histogram,
                qualified_col_name
           FROM dba_tab_cols
          WHERE table_name = 'N_155569_C1'
            AND owner = USER)
SELECT 'q1' q, v.* FROM (SELECT * FROM q1 MINUS SELECT * FROM q2) v
UNION ALL
SELECT 'q2' q, v.* FROM (SELECT * FROM q2 MINUS SELECT * FROM q1) v;

It returned nothing, i.e. there are no differences. I checked indexes too

WITH q1 AS (
         SELECT column_name,
                column_position,
                column_length,
                char_length,
                descend
           FROM dba_ind_columns
          WHERE table_name = 'USER_PORTFOLIOS'
            AND table_owner = USER)
    ,q2 AS (
         SELECT column_name,
                column_position,
                column_length,
                char_length,
                descend
           FROM dba_ind_columns
          WHERE table_name = 'N_155569_C1'
            AND table_owner = USER)
SELECT 'q1' q, v.* FROM (SELECT * FROM q1 MINUS SELECT * FROM q2) v
UNION ALL
SELECT 'q2' q, v.* FROM (SELECT * FROM q2 MINUS SELECT * FROM q1) v;

There are no difference there either.

Anybody have any guesses as to what i should look for?

Thanks in advance.
Re: ORA-14097 [message #662517 is a reply to message #662515] Thu, 04 May 2017 15:47 Go to previous messageGo to next message
scottwmackey
Messages: 515
Registered: March 2005
Senior Member
Hmm, I think I might have found the answer. I should have read the next few articles google linked.
Re: ORA-14097 [message #662518 is a reply to message #662517] Thu, 04 May 2017 15:53 Go to previous message
scottwmackey
Messages: 515
Registered: March 2005
Senior Member
The solution at http://blog.trivadis.com/b/danischnider/archive/2014/08/07/sherlock-holmes-and-partition-exchange.aspx worked. I had a column that was added with a NOT NULL and a DEFAULT. I just changed it to nullable and everything is fine.
Previous Topic: Inserting data in multiple tables
Next Topic: Using Virtual Column
Goto Forum:
  


Current Time: Wed Nov 21 10:40:38 CST 2018