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

Bradd,

I believe it was true in Oracle 9. But I just tested this in Oracle 10 and it worked just fine once the data was truncated past the new end point.

Tom    

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Bradd Piontek
Sent: Tuesday, October 21, 2008 2:07 PM
To: guillermo.bort_at_eds.com
Cc: mccdba1_at_yahoo.com; oracle-l_at_freelists.org Subject: 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 <mailto: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