ORA-01440 trying to convert FLOATs to NUMBERs
I recently came across a legacy database that was using Oracle FLOAT datatype for Primary and Foreign Keys. As the database needed some attention I was asked to get data types in order and get rid of the FLOATs in favor of NUMBER data types.
My first attempt was not pretty. I used exp/imp to make a copy of the schema of interest and hit my head on ORA-01440 "column to be modified must be empty to decrease precision or scale" when trying to alter the columns directly from FLOAT(126) to NUMBER(38). Even though the data I found in the FLOAT columns could have fit inside of a NUMBER(6) Oracle was taking the safe approach and not allowing me to proceed.
Survey the data
To be safe I eyeballed the data in the FLOAT columns. I found only integers and the values were all less than 1,000,000 so I felt safe proceeding with the conversion. If I found real FLOATs I would need to evaluate for any potential rounding issues.
-- Survey the data -- This SQL statement will generate SELECT statements on data in each FLOAT column in a schema SELECT 'select '||column_name||' from '||OWNER||'.'||TABLE_NAME||' where '||COLUMN_NAME||' is not null;' FROM DBA_TAB_COLS WHERE OWNER='MYSCHEMA' AND data_type='FLOAT';
Export the data
As we don't want to mess with this data in place, export it.
export NLS_LANG=.UTF8 exp dba/password userid=dba/pass owner=MYSCHEMA file=out.dmp log=out.log feedback=100000 buffer=10000000 statistics=none
Import tables and data - no constraints or indexes
export NLS_LANG=.UTF8 userid=dba/password file=out.dmp log=import1.log fromuser=MYSCHEMA touser=MYSCHEMA feedback=100000 constraints=N indexes=N
Change FLOAT to NUMBER on target schema
First attempt to convert any columns that will convert without errors.
SELECT 'alter table '||OWNER||'.'||TABLE_NAME||' modify ('||column_name||' NUMBER(38));' FROM DBA_TAB_COLS WHERE owner='MYSCHEMA' and data_type='FLOAT';
Second, create a new column, copy the values, then drop original column.
-- Add new temp column SELECT 'alter table '||OWNER||'.'||TABLE_NAME||' add ('||column_name||'X NUMBER(38));' FROM dba_tab_cols WHERE owner='MYSCHEMA' and data_type='FLOAT'; -- Update the temp column with original column value SELECT 'update '||OWNER||'.'||TABLE_NAME||' set '||column_name||'X = '||column_name||';' FROM dba_tab_cols WHERE owner='MYSCHEMA' and data_type='FLOAT';
Now generate these two sets of statements and save the text before running. I usually execute in SQL Developer so I can copy out columns of text results and paste in another SQL Worksheet window or text editor window if I want to save the results:
SELECT 'alter table '||OWNER||'.'||TABLE_NAME||' drop column '||column_name||';' as stmt1, 'alter table '||OWNER||'.'||TABLE_NAME||' rename column '||column_name||'X to '||COLUMN_NAME||';' as stmt2 FROM DBA_TAB_COLS WHERE OWNER='MYSCHEMA' AND DATA_TYPE='NUMBER';
Import index and constraint definitions
imp dba/password file=out.dmp log=import2.log fromuser=MYSCHEMA touser=MYSCHEMA feedback=100000 rows=n indexes=Y constraints=Y ignore=y
After this import you should have all indexes and constraints available again but no more FLOAT columns!