ORA-01440: column to be modified must be empty to decrease precision or scale

ORA-01440: column to be modified must be empty to decrease precision or scale

Example to generate the error with TEST1 table

--Created table test1
SQL> create table test1 (id number(1));
Table created.

--insert data
SQL> insert into test1 values (1);
1 row created.

--Modified the table with increase scale
SQL> alter table test1 modify id number(2);
Table altered.

--insert data
SQL> insert into test1 values(2);
1 row created.

Getting error while modified the column datatype in decrease length scale

SQL> alter table test1 modify id number(1);
alter table test1 modify id number(1)
*
ERROR at line 1:
ORA-01440: column to be modified must be empty to decrease precision or scale

Solution

1. Add a first temporary column with data type used to modified in upper statement.

SQL> alter table test1 add id_temp NUMBER(1);
Table altered.

2. Move the data from original column to first temporary column.

SQL> update test1 set id_temp= id;
2 rows updated.

3. Check the number of rows.

SQL> select count(*) from test1;

COUNT(*)
----------
2

4. Rename the original column to second new temporary column so that we will move temporary column with original name in next step.

SQL> alter table test1 rename column id to id_TEMP1;
Table altered.

Note: We can directly drop id column and rename the first temporary to original column but we add this step to make production data security.(We can skip this step to by dropping first id column)

5. Rename the column which is having original data created first.

SQL> alter table test1 rename column id_temp to id;
Table altered.

6. Drop the extra temporary column.

SQL> alter table test1 drop column id_temp1;
Table altered.

7. Verify the Number of rows is same.

SQL> select count(*) from test1;

COUNT(*)
----------
2

8. Verify the Structure with Describe command.
desc test1;
Name Null? Type
--------- -------- ---------------
ID NUMBER(1)

9. Compile the invalid objects related to that object by executing.

execute utl_recomp.recomp_serial();

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.