OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-14097 (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 )

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

ORA-14097 [message #662515]

Thu, 04 May 2017 15:37

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.

Report message to a moderator

Re: ORA-14097 [message #662517 is a reply to message #662515]

Thu, 04 May 2017 15:47

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.

Report message to a moderator

Re: ORA-14097 [message #662518 is a reply to message #662517]

Thu, 04 May 2017 15:53

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.

Report message to a moderator

Previous Topic:	Inserting data in multiple tables
Next Topic:	Using Virtual Column

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 02:24:04 CDT 2024