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: 487
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



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: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
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
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: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: dbms_jobs failed with ORA-06550
Next Topic: query regarding value
Goto Forum:
  


Current Time: Sun Dec 04 06:23:27 CST 2016

Total time taken to generate the page: 0.19296 seconds