Re: Resize Columns

From: Karen Abgarian <karen.abgarian_at_fmr.com>
Date: Fri, 17 Sep 1999 11:24:27 -0400
Message-ID: <37E25D2B.7D3405E3@fmr.com>

Sir,

I hate to insist that much, but I was right. Your columns must be empty, not the table.
Would you consider this example:

SQL> select * from v$version;

BANNER

---

Oracle8 Enterprise Edition Release 8.0.5.1.0 - Production PL/SQL Release 8.0.5.1.0 - Production
CORE Version 4.0.5.0.0 - Production
TNS for Solaris: Version 8.0.5.0.0 - Production NLSRTL Version 3.3.2.0.0 - Production

SQL> create table karen( v varchar2(10));

Table created.

SQL> insert into karen values( &x );
Enter value for x: null
old 1: insert into karen values( &x ) new 1: insert into karen values( null )

1 row created.

SQL> alter table karen modify( v varchar2(5));

Table altered.

SQL> insert into karen values( &x );
Enter value for x: 'DD'
old 1: insert into karen values( &x ) new 1: insert into karen values( 'DD' )

1 row created.

SQL> alter table karen modify( v varchar2(4) ); alter table karen modify( v varchar2(4) )

                          *

ERROR at line 1:
ORA-01441: column to be modified must be empty to decrease column length

Regards,
Karen Abgarian.

davidho99_at_my-deja.com wrote:

> To increase column size (easier!):
> =================================
>
> o ALTER TABLE mytable MODIFY(mycolumn varchar2(100));
>
> To reduce column size :
> ===============================
>
> o user same ALTER TABLE statement as above, if you have NO DATA in the
> column
>
> o However if you have data, then follow the steps below:
>
> Assume DEPT has a column DEPT_ID of varchar2(10), and you want to change
> to char(2):
>
> Steps
> =====
> 1. Create another table, say DEPT2 with DEPT_ID of char(2)
>
> sqlplus > create table DEPT2 (char(2));
>
> 2. Copy the data from DEPT to DEPT2
>
> sqlplus > insert into DEPT2
> select DEPT_ID from DEPT;
>
> 3. Rename both tables
>
> sqlplus > rename DEPT to DEPT_OLD;
> sqlplus > rename DEPT2 to DEPT;
>
> David Ho
>
> In article <37DE9B2E.98E4F944_at_fmr.com>,
> Karen Abgarian <karen.abgarian_at_fmr.com> wrote:
> > alter table xx modify(n varchar2(100));
> >
> > You can even make it shorter if you have all NULLs in the column.
> >
> > Regards,
> > Karen Abgarian.
> >
> > In2Home User wrote:
> >
> > > I'm sure this is an age-old question, but
> > > can I resize the columns of an Oracle 8.0.5 table
> > > without having to drop and recreate the table?
> > >
> > > Thanks,
> > >
> > > Chris
> >
> >
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Fri Sep 17 1999 - 10:24:27 CDT