decrease size of column 3

[shantanu125]

Hi Gurus,
I want to decrease size of a not null column of a table from 7 to 6.
We have 70,000 rows in that table and in each row column length is always 6. How can we decrease the size of column.
I thought exporting the table data, then deleting all the rows from the table,then decreasing the size and then importing the data back should help.
Any suggestions.

 

[rcurva]

You are in the right direction.
Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[Turkbear]

Hi,
It might not be 'worth the candle'; If the field is defined as a varchar2(7) it will only use as much space as is needed to hold the contents..
Even if defined as a Char(7)
( not a good idea if you plan to use this field to join to others ) the extra space for the 7 vs the 6 in a table that size should have practically no effect on performance..

Just my 2c ( also, as soon as you change the size, someone will want to store 7 characters in it )

 

[shantanu125]

Hey guys
I got the answer from a site:
Decreasing size of column (which contains data)

Let's say you want to decrease ENAME from VARCHAR2(10) to VARCHAR2(7)
Oracle allows you to increase the size of a column but does not allow you to decrease the size of a column.
Here's a way to decrease the size of a column which contains data.

SQL> desc emp
Name Null? Type
------------------------------- -------- ----
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)


drop table FIX_IT;

create table FIX_IT as select rowid rowid_xx, ENAME from EMP;
create index FIX_IT on FIX_IT (rowid_xx);

alter table EMP modify (ENAME NULL);
update EMP set ENAME = NULL;
alter table EMP modify (ENAME VARCHAR2(7));
update EMP set ENAME = (
select ENAME from FIX_IT where rowid_xx = EMP.rowid);

drop table FIX_IT;


shantanu

 

[carp]

"Oracle allows you to increase the size of a column but does not allow you to decrease the size of a column" - this is flat-out wrong!

Oracle lets you resize columns as well as redefine the datatype - and has done so since at least V7.3! The catch is - if you want to decrease the size of the column or change datatypes, the column can contain no data. So the trick is to empty out the column:

CREATE TABLE holder AS (SELECT primary_key_column, column_you_want_to_shrink FROM your_table);

UPDATE your_table SET column_you_want_to_shrink = NULL;

ALTER your_table modify column_you_want_to_shrink VARCHAR2(5);

UPDATE your_table y SET column_you_want_to_shrink = (SELECT column_you_want_to_shrink FROM holder h WHERE y.primary_key = h.primary_key);

COMMIT;

With that said, as Turkbear has already pointed out - this is NOT going to save you any space. The only thing this will accomplish is to prevent anybody from entering a value that contains more than 5 characters.