1

I'm trying to decrease the precision of a column from NUMBER(25) to NUMBER(10) but when I do I got error saying column to be modified must be empty to decrease precision

I do not have any data in the column length more than 10. So I want to change this precision forcefully.

How can I update this forcefully? Is it possible?

1

As the error message states, you can't just modify the number column to reduce its precision and scale.

Here is one of the workarounds I would use for this situation.

Here is my table.

SQL> desc t1_num;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(30)

Add another column with the number data type with specified precision.

SQL> alter table t1_num add id2 number(10);

Table altered.

Update the new column with the data from the old column.

SQL> update t1_num set id2=id;

1 row updated.

SQL> commit;

Commit complete.

Set the old column to null to change its specification.

SQL> update t1_num set id=null;

1 row updated.

Modify the column

SQL> alter table t1_num modify id number(10);

Table altered.

Update the column with the original data.

SQL> update t1_num set id=id2;

1 row updated.

SQL> commit;

Commit complete.

Drop the temp column

SQL> alter table  t1_num drop column id2;

Table altered.

SQL> desc t1_num;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(10)
  • There are before dml trigger is present on the table, I cannot update from one column to other – Aravind Feb 22 '18 at 10:07
  • 1
    @Aravind Can't they be disabled while doing the changes? – Husam Mohamed Feb 22 '18 at 10:47
  • @Aravind if you're even considering trying to use a FORCE command, disabling a trigger temporarily shouldn't be of concern. – MguerraTorres Feb 22 '18 at 14:45
0

column to be modified must be empty to decrease precision

As mentioned in the error message, it is not allowed to modify the column unless the column is empty from all records.

Maybe you can try something like this, create a new column with the required specs and copy the data from the first column to the new one then drop the original column.

0

Dropping columns can be a pretty expensive operation, and the update might cause some row migration issues. You can also achieve the remapping with DBMS_REDEFINITION which also keeps the table available during the operation, and can retain the trigger/constraint/etc definitions as well

SQL> create table t1 ( x number primary key, y number(25,5));

Table created.

SQL>
SQL> insert into t1 values (1, 12.123);

1 row created.

SQL> insert into t1 values (1.1234234, 12.123123123);

1 row created.

SQL> insert into t1 values (2.99999999999, 12.121231425457433);

1 row created.

SQL> select * from t1;

         X          Y
---------- ----------
         1     12.123
 1.1234234   12.12312
         3   12.12123

3 rows selected.

SQL> create table t2 ( x number(10,2), y number(12,2));

Table created.

SQL>
SQL> EXEC DBMS_REDEFINITION.can_redef_table(user, 'T1');

PL/SQL procedure successfully completed.

SQL>
SQL> EXEC DBMS_REDEFINITION.start_redef_table(user, 'T1', 'T2');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_REDEFINITION.sync_interim_table(user, 'T1', 'T2');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_REDEFINITION.finish_redef_table(user, 'T1', 'T2');

PL/SQL procedure successfully completed.

SQL>
SQL> select * from t1;

         X          Y
---------- ----------
         1      12.12
      1.12      12.12
         3      12.12

3 rows selected.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.