Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Lets say I have two columns of type NUMBER (without precision, and scale) and VARCHAR(300). I saw that these columns are way too large for my data, so I want to modify them to NUMBER(11) and VARCHAR(10). So If I run this SQL statement:

ALTER TABLE FOO
    MODIFY(BAR NUMBER(10));
  • Will I be able to do that on nonempty column?
  • If so, what If there is one value greater than NUMBER(10), will oracle tell me about it?
  • Will column default values remain unchanged if previously defined?
  • Will column nullable option remain unchanged?
  • Will primary, foreign, unique key on that column remain unchanged?
  • Will constraints involving that columns remain unchanged?
  • Will indexes on that columns remain unchanged?

Is there any official documentation answering my questions?

share|improve this question
add comment

1 Answer

up vote 9 down vote accepted

The Oracle Administrators Guide says the following:

Use the ALTER TABLE...MODIFY statement to modify an existing column definition. You can modify column data type, default value, column constraint, column expression (for virtual columns) and column encryption.

You can increase the length of an existing column, or decrease it, if all existing data satisfies the new length. You can change a column from byte semantics to CHAR semantics or vice versa. You must set the initialization parameter BLANK_TRIMMING=TRUE to decrease the length of a non-empty CHAR column.

If you are modifying a table to increase the length of a column of data type CHAR, realize that this can be a time consuming operation and can require substantial additional storage, especially if the table contains many rows. This is because the CHAR value in each row must be blank-padded to satisfy the new column length.

The Oracle SQL Language Reference has much more detail including the following:

You can change the data type of any column if all rows of the column contain nulls. However, if you change the data type of a column in a materialized view container table, then Oracle Database invalidates the corresponding materialized view.

You can always increase the size of a character or raw column or the precision of a numeric column, whether or not all the rows contain nulls. You can reduce the size of a data type of a column as long as the change does not require data to be modified.The database scans existing data and returns an error if data exists that exceeds the new length limit.

You can modify a DATE column to TIMESTAMP or TIMESTAMP WITH LOCAL TIME ZONE. You can modify any TIMESTAMP WITH LOCAL TIME ZONE to a DATE column.

If the table is empty, then you can increase or decrease the leading field or the fractional second value of a datetime or interval column. If the table is not empty, then you can only increase the leading field or fractional second of a datetime or interval column.

For CHAR and VARCHAR2 columns, you can change the length semantics by specifying CHAR (to indicate character semantics for a column that was originally specified in bytes) or BYTE (to indicate byte semantics for a column that was originally specified in characters). To learn the length semantics of existing columns, query the CHAR_USED column of the ALL_, USER_, or DBA_TAB_COLUMNS data dictionary view.

There is additional information and restrictions in the above documentation. Here is a demonstration of attempting to reduce the precision of a Number column and reduce the length of a Varchar2. You can try other changes so you will know what will happen.

--Setup.
DROP TABLE FOO;
CREATE TABLE FOO (BAR Number, BAR2 VARCHAR2(300));
INSERT INTO FOO (SELECT Level, RPAD(to_char(Level),10*Level,to_char(Level)) 
   FROM DUAL CONNECT BY Level <=20);
COMMIT;
SELECT * FROM FOO;

--Reduce Number to Number(10).
ALTER TABLE FOO MODIFY (BAR NUMBER (10));

--Reduce Varchar2(300) to Varchar2(100) (data would be truncated).
ALTER TABLE FOO MODIFY (BAR2 VARCHAR2(100));

--Reduce Varchar2(300) to Varchar2(200) (no data would be truncated).
ALTER TABLE FOO MODIFY (BAR2 VARCHAR2(200));

The alter statements have the following output:

ALTER TABLE FOO MODIFY (BAR NUMBER (10))
Error report:
SQL Error: ORA-01440: column to be modified must be empty to decrease precision or scale
01440. 00000 -  "column to be modified must be empty to decrease precision or scale"

ALTER TABLE FOO MODIFY (BAR2 VARCHAR2(100))
Error report:
SQL Error: ORA-01441: cannot decrease column length because some value is too big
01441. 00000 -  "cannot decrease column length because some value is too big"

table FOO altered.

Reduce precision by creating a new column.

ALTER TABLE FOO ADD (BAR3 NUMBER(10));
UPDATE FOO SET Bar3 = Bar;
ALTER TABLE FOO DROP COLUMN BAR;
ALTER TABLE FOO RENAME COLUMN BAR3 TO BAR;
share|improve this answer
    
So, conclusion is - if you want to decrease precision or scale of the column and keep things like indexes, keys etc. the only way to do it is to copy the table, truncate it, change types, copy data back to it and drop the temporary table. There is no faster, more elegant way? –  mnowotka Oct 19 '12 at 14:27
1  
Well, you could create a new column, copy the data, re-create the index, drop the old column and rename the new one. You could also use DBMS_REDEFINITION, or you could create a new table, copy the data, drop the old table and rename the new one. Or you could export the table, drop it, re-create it with the new definition and them import the data. There are lots of ways to do this, but faster/more elegant is something you'll have to decide. –  Leigh Riffel Oct 19 '12 at 14:44
    
Probably you can also create a new column, copy the data to it, set the old column to null, modify its length, copy the data from the new column back to the changed old column and drop the new column. And all of that because oracle doesn't allow reducing numeric columns even though the data would fit. 8-{ –  hstoerr Jan 28 at 12:07
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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