I would like to make 2 columns in a 3

[mrmac228]

I would like to make 2 columns in a database larger, is this possible or can they only be made smaller (without having to recreate the table of course)

 

[sem]

I suppose that only decreasing column size may be a problem.

Regards, Dima

 

[SantaMufasa]

Mr. Mac,

As Dima mentions, you can only increase the size of a column without table re-creation. If you do not have the syntax, here it is:

ALTER TABLE <table_name> MODIFY <column_name> <data_type>(<larger_max_size>);

Dave

 

[Turkbear]

Hi,
Dima, as usual, is correct:

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Sep 3 09:32:19 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.5.1.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.1.0 - Production

SQL> desc drop_me
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 F1                                                 VARCHAR2(10)
 DT_FIELD                                           DATE


  1* alter table drop_me modify F1 varchar2(200)
SQL> /

Table altered.

SQL> desc drop_me
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 F1                                                 VARCHAR2(200)
 DT_FIELD                                           DATE

SQL> alter table drop_me modify F1 varchar2(23);
alter table drop_me modify F1 varchar2(23)
                           *
ERROR at line 1:
ORA-01441: column to be modified must be empty to decrease column length


SQL>

 

[carp]

And yet the column width CAN be decreased - as the error message says, the column has to be empty.

If you really need to decrease the width of the table and can afford to have the data unavailable while you're doing this, here's how:

1. Create a table to store the column(s) you want to shrink, along with the table's primary key column:
CREATE TABLE my_table
AS SELECT pk_column, shrinking_column;

2. Empty out the columns you need to shrink:
UPDATE original_table
SET shrinking_column = NULL;
COMMIT;

3. Shrink the column:
ALTER TABLE original_table
MODIFY shrinking_column VARCHAR2(5);

4. Repopulate the column:
UPDATE original_table o
SET shrinking_column = (SELECT shrinking_column
FROM my_table i
WHERE i.primary_key = o.primary_key);
COMMIT;

5. Verify your results and drop the temporary table.

I would recommend you go through this drill a couple of times just to familiarize yourself with the process before you start messing with your production data!

Also note that this can be quite a bit of work to shrink the column width, so you'll have to weigh the benefits against the effort required. For instance, if you're looking at a VARCHAR2 column, you're not going to save any space with this exercise - you will only be limiting how large a given data element can be. On the other hand, if you're doing something like storing social security numbers as numbers, but now want to store them as VARCHAR2(11), this is probably the least intrusive way to do it.

 

[mrmac228]

Thanks for all the information guys.

A star for carp for the answer and steps.

 

[sevenhvn]

Folks,

The ideas above are good; I also wanted to point out another method, similar to carp's, but without any table creation being involved (this is good for very large tables especially) -

Let's call the table my_table -
col_pk number(10),
col_shrink_this char(50)

-- add another column to temporarily hold the data in the
-- column to be decreased, with same precision as reqd. for
-- old col.
SQL> alter table my_table add column col_shrunk char(25);

-- copy the data to the new column
SQL> update my_table set col_shrunk = trim(col_shrink_this);
SQL> commit;

-- empty the old column (this col. can't be part of pri.key)
SQL> update my_table set col_shrnk_this = NULL;
SQL> commit;

-- decrease the old col.
SQL> alter table my_table modify col_shrink_this char(25);

-- copy the data back to the old col.
SQL> update my_table set col_shrink_this = col_shrunk;
SQL> commit;

-- drop the &quot;temporary&quot; col. using ***TWO*** DDL commands
SQL> alter table my_table set unused column col_shrunk;
SQL> alter table my_table drop unused columns;


This method avoids having to build a table from scratch - good especially for really large tables which could possibly max out on space, and also the copy-back process is simpler, since it's an internal copy, no indexes are required (for very large tables).

My $0.02...


sevenhvn@hotmail.com

 

[mrmac228]

I like that one too seven.

 

[carp]

Sevenhvn -
I like yours better too! I've actually used this approach before, but had forgotten it - but it's a lot cleaner. Thank you for refreshing my memory!

 

[SantaMufasa]

When you have more data in the original column than the maxsize of the new column, then your options are:

1) Do a select on the original column that displays just the entries that are wider than the new, desired max width to determine if truncation causes loss of valuable data. If truncation is acceptable, then write a truncated version of the original data to the holding column.

2) If the data are important and you cannot abbreviate them to a narrower width, the your de facto decision is not to revise to such a narrow width, right?

Dave