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 |
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.
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 18:55:24 CDT 2024
|