SQL & PL/SQL

I have a partitioned table with ~50 million rows that was setup with a number(10) instead
of a date column. All the data in the table is ALWATS in this format YYYYMMDD
Code: [Select all] [Show/ hide]CREATE TABLE T1.monthly
(
SEQ_NUM NUMBER(10) NOT NULL,
DAY_DK NUMBER(10) NOT NULL
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
LOGGING
PARTITION BY RANGE (DAY_DK)
(
PARTITION DEC_2012 VALUES LESS THAN (20130101)
LOGGING
NOCOMPRESS
TABLESPACE USERS
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 8M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION POST_2017 VALUES LESS THAN (20510101)
NOLOGGING
NOCOMPRESS
TABLESPACE USERS
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
)
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

some sample data

SEQ_NUM DAY_DK
---------- ----------
990 20121225
991 20121225
992 20121225
993 20121225
994 20121225
995 20121225
996 20121225
997 20121225
998 20121225
999 20121225

When I use the exchange partition method the parition is able to move the data from "monthly" table to "mth" table.
Code: [Select all] [Show/ hide]desc t1.mth; ### my temorary table
Name Null? Type
----------------------------------------- -------- ----------------------------
SEQ_NUM NUMBER(10)
DAY_DK NUMBER(10)

Than when I try to alter my temp table "mth". I get an error table must be empty to change
column types.
Code: [Select all] [Show/ hide]alter table n546830.mth modify (DAY_DK date);

Next I tried making my temporary table "mth" a date column. When I an the exchange partiton
command I get the following error:
Code: [Select all] [Show/ hide]alter table t1.monthly exchange partition DEC_2012
with table t1.mth without validation;
alter table n546830.monthly exchange partition DEC_2012 with table n546830.mth without validation
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

I am thinking maybe instead of the parition exchange I can use pl sql to move most of the row over to a new table (all except the number(10) coluimn, than use some procedure or function to convert the number to a date? not being a developer I was wondering if somebody can verify this is a good idea ( keep in mind I want to generate the min amount of undo as possible). In addtion, can somebody provide me a template. In particular on how to convert the number to date column row by row.

Thanks to all who answer