RE: [Q] shrink varchar2 size and truncate data????
Date: Tue, 21 Oct 2008 14:21:12 -0400
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.
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Bradd Piontek
Sent: Tuesday, October 21, 2008 2:07 PM
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.
"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.
Guillermo Alan Bort
DBA / DBA Main Team
EDS, an HP company
ITO Arias 1851
Ciudad Autonoma de Buenos Aires (C1429DXC)
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.
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of dba1 mcc
Sent: Tuesday, October 21, 2008 3:17 PM
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;Received on Tue Oct 21 2008 - 13:21:12 CDT