Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Krishnaprasad.

Asked: October 14, 2018 - 3:49 am UTC

Last updated: October 15, 2018 - 4:00 am UTC

Version: 12.1.2.0

Viewed 1000+ times

You Asked

hi Team,

currently, we are doing partition activity , in brief about the activity is we are using exchange partition with other table.

While performing exchange we are facing issue of ORA-14097 : column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

we identified that their is one unused column in partition table due to which it is failing .on UAT environment we drop the unused column and later it succeeded.

in PROD env , we fear that unused column might have any dependencies on other object/segment , so wanted to know is their is any way out to identified it ?

Does it is safe to drop unused column ?

Please suggest ....


regards,
Krishna

and Connor said...

Here's a couple of blog posts explaining possible causes (and solutions):

https://connor-mcdonald.com/2013/01/20/exchange-partition-those-pesky-columns/

https://connor-mcdonald.com/2016/02/16/exchange-partition-revisited/

I would be hesitant about dropping the columns, because that is a very resource intensive activity, and I'm guessing you have partitions because its a large table.

If the column is unused, then there are no longer dependencies on it.

Once you are on 12.2, things get a lot easier, you can do:

create table TEMPLATE *for exchange"

which will copy the column structure (including unused etc) from the source table.

Details on that here

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/vldbg/maintenance-partition-tables-indexes.html#GUID-2DE24EE4-00EB-4D58-A428-8E6CBD7E7F34

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Krishnaprasad Yadav, October 15, 2018 - 2:52 am UTC

Hi Connor

I see, this post is very useful.

But one more confusion over here , i see you create temporary table , then you add column and going forward you make it unused , so does datatype of unused column in main table i.e source needs to be same as of temporary ??

if yes , then how do we identify datatype of unused columns of source table
Connor McDonald
October 15, 2018 - 4:00 am UTC

Its all there in the dictionary

SQL> create table t ( x int, y date, z varchar2(10));

Table created.

SQL>
SQL> alter table t set unused column y;

Table altered.

SQL> alter table t set unused column z;

Table altered.

SQL>
SQL> select *
  2  from user_tab_cols
  3  where table_name = 'T'
  4  @pr
==============================
TABLE_NAME                    : T
COLUMN_NAME                   : X
DATA_TYPE                     : NUMBER
DATA_TYPE_MOD                 :
DATA_TYPE_OWNER               :
DATA_LENGTH                   : 22
DATA_PRECISION                :
DATA_SCALE                    : 0
NULLABLE                      : Y
COLUMN_ID                     : 1
DEFAULT_LENGTH                :
DATA_DEFAULT                  :
NUM_DISTINCT                  :
LOW_VALUE                     :
HIGH_VALUE                    :
DENSITY                       :
NUM_NULLS                     :
NUM_BUCKETS                   :
LAST_ANALYZED                 :
SAMPLE_SIZE                   :
CHARACTER_SET_NAME            :
CHAR_COL_DECL_LENGTH          :
GLOBAL_STATS                  : NO
USER_STATS                    : NO
AVG_COL_LEN                   :
CHAR_LENGTH                   : 0
CHAR_USED                     :
V80_FMT_IMAGE                 : NO
DATA_UPGRADED                 : YES
HIDDEN_COLUMN                 : NO
VIRTUAL_COLUMN                : NO
SEGMENT_COLUMN_ID             : 1
INTERNAL_COLUMN_ID            : 1
HISTOGRAM                     : NONE
QUALIFIED_COL_NAME            : X
USER_GENERATED                : YES
DEFAULT_ON_NULL               : NO
IDENTITY_COLUMN               : NO
EVALUATION_EDITION            :
UNUSABLE_BEFORE               :
UNUSABLE_BEGINNING            :
COLLATION                     :
COLLATED_COLUMN_ID            :
==============================
TABLE_NAME                    : T
COLUMN_NAME                   : SYS_C00002_18101512:00:28$
DATA_TYPE                     : DATE
DATA_TYPE_MOD                 :
DATA_TYPE_OWNER               :
DATA_LENGTH                   : 7
DATA_PRECISION                :
DATA_SCALE                    :
NULLABLE                      : Y
COLUMN_ID                     :
DEFAULT_LENGTH                :
DATA_DEFAULT                  :
NUM_DISTINCT                  :
LOW_VALUE                     :
HIGH_VALUE                    :
DENSITY                       :
NUM_NULLS                     :
NUM_BUCKETS                   :
LAST_ANALYZED                 :
SAMPLE_SIZE                   :
CHARACTER_SET_NAME            :
CHAR_COL_DECL_LENGTH          :
GLOBAL_STATS                  : NO
USER_STATS                    : NO
AVG_COL_LEN                   :
CHAR_LENGTH                   : 0
CHAR_USED                     :
V80_FMT_IMAGE                 : NO
DATA_UPGRADED                 : YES
HIDDEN_COLUMN                 : YES
VIRTUAL_COLUMN                : NO
SEGMENT_COLUMN_ID             : 2
INTERNAL_COLUMN_ID            : 2
HISTOGRAM                     : NONE
QUALIFIED_COL_NAME            : SYS_C00002_18101512:00:28$
USER_GENERATED                : NO
DEFAULT_ON_NULL               : NO
IDENTITY_COLUMN               : NO
EVALUATION_EDITION            :
UNUSABLE_BEFORE               :
UNUSABLE_BEGINNING            :
COLLATION                     :
COLLATED_COLUMN_ID            :
==============================
TABLE_NAME                    : T
COLUMN_NAME                   : SYS_C00003_18101512:00:28$
DATA_TYPE                     : VARCHAR2
DATA_TYPE_MOD                 :
DATA_TYPE_OWNER               :
DATA_LENGTH                   : 10
DATA_PRECISION                :
DATA_SCALE                    :
NULLABLE                      : Y
COLUMN_ID                     :
DEFAULT_LENGTH                :
DATA_DEFAULT                  :
NUM_DISTINCT                  :
LOW_VALUE                     :
HIGH_VALUE                    :
DENSITY                       :
NUM_NULLS                     :
NUM_BUCKETS                   :
LAST_ANALYZED                 :
SAMPLE_SIZE                   :
CHARACTER_SET_NAME            : CHAR_CS
CHAR_COL_DECL_LENGTH          : 10
GLOBAL_STATS                  : NO
USER_STATS                    : NO
AVG_COL_LEN                   :
CHAR_LENGTH                   : 10
CHAR_USED                     : B
V80_FMT_IMAGE                 : NO
DATA_UPGRADED                 : YES
HIDDEN_COLUMN                 : YES
VIRTUAL_COLUMN                : NO
SEGMENT_COLUMN_ID             : 3
INTERNAL_COLUMN_ID            : 3
HISTOGRAM                     : NONE
QUALIFIED_COL_NAME            : SYS_C00003_18101512:00:28$
USER_GENERATED                : NO
DEFAULT_ON_NULL               : NO
IDENTITY_COLUMN               : NO
EVALUATION_EDITION            :
UNUSABLE_BEFORE               :
UNUSABLE_BEGINNING            :
COLLATION                     : USING_NLS_COMP
COLLATED_COLUMN_ID            :

PL/SQL procedure successfully completed.


SQL>


Krishnaprasad Yadav, October 15, 2018 - 4:10 am UTC

Thank you Connor !!!

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.