Re: [Q] shrink varchar2 size and truncate data????

You can change the data all you want, but unless you have the column completely empty (All Nulls) you won't be able to shrink the VARCHAR2(2000) to VARCHAR2(1000). The two options I know (one was already given) is to a. CTAS the pk and new column with the a substr to get the data smaller, NULL out the existing table column, and then update the data back in. b. avoid the CTAS altogether, add a new temporary column, update witht he smaller data, then drop the existing column and rename the temporary to the existing name.

Bradd Piontek
  "Next to doing a good job yourself,

        the greatest joy is in having someone
        else do a first-class job under your
        direction."

• William Feather

On Tue, Oct 21, 2008 at 12:50 PM, Bort, Guillermo <guillermo.bort_at_eds.com>wrote:

> How about sth like:
>
>
>
> update functions set instructions=substr(instructions,0,1000) where
> lentgh(instructions) > 1000;
>
>
>
> (where clause is not necessary, though it could improve performance by
> using an index should instructions have one)
>
>
>
> Then alter the table.
>
>
>
> hth
>
>
>
> *Guillermo Alan Bort*
>
> DBA / DBA Main Team
>
>
>
> *EDS*, an HP company
>
> ITO
>
> Arias 1851
>
> Ciudad Autonoma de Buenos Aires (C1429DXC)
>
> Argentina
>
>
>
> Tel: +54 11 4704-3132
>
> E-mail: guillermo.bort_at_eds.com <name.name_at_eds.com>
>
>
>
> We deliver on our commitments
>
> so you can deliver on yours.
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *dba1 mcc
> *Sent:* Tuesday, October 21, 2008 3:17 PM
> *To:* oracle-l_at_freelists.org
> *Subject:* [Q] shrink varchar2 size and truncate data????
>
>
>
> we have a table has a field define as "varchar2(2000)", we tried to change
> size from varchar2(2000) TO VARCHAR2(1000). dUE TO SOME RECORDS DATA LARGE
> THAN 1000.
>
>
>
> I can use following sql statemnet to find which records > 1000 characters,
> but how can I truncate data larger than 1000 characters?
>
>
>
> select id, length(INSTRUCTIONS) from functions where length(INSTRUCTIONS)
> >=1000;
>
>
>

--
http://www.freelists.org/webpage/oracle-l