Home » SQL & PL/SQL » SQL & PL/SQL » How to reduce the column length (alternative way) ? (oracle 9i,windows xp)
How to reduce the column length (alternative way) ? [message #302562] Tue, 26 February 2008 02:54 Go to next message
mm_kanish05
Messages: 493
Registered: January 2007
Location: Chennai
Senior Member
Hello,

I have column in a table having 9000000 records, that is column length 14 and i want to reduct the coloumn length 8 ,

is this the only way?

1. make a copy of column with values
2. drop the column from the table
3. create a new column in the tbale
4. copy the correspoinding values from backup.


is there possible without drop the column.


wbr
kanish

Report message to a moderator

Re: How to reduce the column length (alternative way) ? [message #302564 is a reply to message #302562] Tue, 26 February 2008 02:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
 
SQL> create table t (col varchar2(14));

Table created.

SQL> insert into t select substr(object_name,1,8) from dba_objects where rownum<10;

9 rows created.

SQL> alter table t modify col varchar2(8);

Table altered.

Regards
Michel

Report message to a moderator

Re: How to reduce the column length (alternative way) ? [message #302570 is a reply to message #302564] Tue, 26 February 2008 03:12 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Not being complete Michel Wink

SQL> create table t ( col varchar2(14) ) ;

Table created.

SQL> insert into t select substr(object_name,1,14) from dba_objects where rownum < 10 ;

9 rows created.

SQL> alter table t modify col varchar2(8) ;
alter table t modify col varchar2(8)
                     *
ERROR at line 1:
ORA-01441: cannot decrease column length because some value is too big


As long as the content in the column you're about to reduce fits in the new length the alteration will succeed.

On the other hand: if the data doesn't fit in the new specification, then there is something wrong with the data ( at that point ) or with the specification

Report message to a moderator

Re: How to reduce the column length (alternative way) ? [message #302577 is a reply to message #302570] Tue, 26 February 2008 03:35 Go to previous message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
On the other hand: if the data doesn't fit in the new specification, then there is something wrong with the data ( at that point ) or with the specification

I assumed the spec and data are correct, maybe I was wrong. Wink

Regards
Michel

Report message to a moderator

Previous Topic: dbms_jobs failed with ORA-06550
Next Topic: query regarding value
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Dec 05 14:06:26 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.