Skip to Main Content
  • Questions

  • Increasing length of varchar2 column

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Dilip.

Asked: June 28, 2004 - 1:37 pm UTC

Last updated: June 28, 2004 - 5:48 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi Tom,

We have partitioned table with each partition holding 50 million records. At present there are 5 partitions so the total number of records is 250 million. Table is in LMT tablespace.

There is new business requirement where we have to modify a column in this table from varchar2(4) to varchar2(12).

There are no indexes on this column.

When we issue, alter table table_name modify column_name varchar2(12)

will oracle simply modify table definition and update data dictionary which would be quick operation OR will oracle do some additional verification (resulting in long operation) which we need to be aware of.

Thank you,

Dilip

and Tom said...

it'll be super fast. no checks needed. simple data dictionary update.

big_table@ORA9IR2> set timing on
big_table@ORA9IR2> alter table big_table modify secondary varchar2(12);

Table altered.

Elapsed: 00:00:00.05
big_table@ORA9IR2> select count(*) from big_table;

COUNT(*)
----------
4000000

Elapsed: 00:00:13.47
big_table@ORA9IR2>


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Sr Application Developer.

Bill, June 28, 2004 - 3:58 pm UTC

This is probably a silly question, but I have never shrunk a varchar2 field to a smaller value and I don't have a database handy that I can play with. Increasing it is very quick, would shrinking it involve every row of the users 250 million row table being checked for truncation of an existing value or would it simply truncate the data when it is read?

Tom Kyte
June 28, 2004 - 4:26 pm UTC

it needs to validate the data, it would take a while.

The amazing shrinking column

David Scott, June 28, 2004 - 5:48 pm UTC

You might also consider these steps:
- add a new column of the proper length with a "_temp" name
- update the new column using SUBSTR or other function
- rename the old column with an "_old" name
- rename the "_temp" column to the proper column name
- adjust indexes and triggers
- alter table ... set unused ("_old" column); OR drop the column

Depending on your environment, you might see a speed advantage, although this increases your storage overhead.
YMMV...

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.