Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 9379 invoked from network); 21 Oct 2008 13:24:10 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by air964.startdedicated.com with SMTP; 21 Oct 2008 13:23:46 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 71240A90360; Tue, 21 Oct 2008 14:23:31 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 13663-02; Tue, 21 Oct 2008 14:23:31 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id DCE88A903B5; Tue, 21 Oct 2008 14:23:30 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 21 Oct 2008 14:21:29 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5CAE8A90097 for ; Tue, 21 Oct 2008 14:21:29 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 13227-01 for ; Tue, 21 Oct 2008 14:21:29 -0400 (EDT) Received: from IE1EHSOBE006.bigfish.com (outbound-dub.frontbridge.com [213.199.154.16]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 14B9DA8FBDD for ; Tue, 21 Oct 2008 14:21:25 -0400 (EDT) Received: from mail118-dub-R.bigfish.com (10.5.252.3) by IE1EHSOBE006.bigfish.com (10.5.252.26) with Microsoft SMTP Server id 8.1.291.1; Tue, 21 Oct 2008 18:21:23 +0000 Received: from mail118-dub (localhost.localdomain [127.0.0.1]) by mail118-dub-R.bigfish.com (Postfix) with ESMTP id B0660E7088B; Tue, 21 Oct 2008 18:21:23 +0000 (UTC) X-BigFish: VPS-44(zz62a3L98dRaf6W936fO9371Pzzzzz2fh6bh61h) X-Spam-TCS-SCL: 0:0 Received: by mail118-dub (MessageSwitch) id 1224613275675648_23205; Tue, 21 Oct 2008 18:21:15 +0000 (UCT) Received: from mailc.nysemail.state.ny.us (mailc.nysemail.state.ny.us [198.22.236.83]) (using TLSv1 with cipher RC4-MD5 (128/128 bits)) (No client certificate requested) by mail118-dub.bigfish.com (Postfix) with ESMTP id 3CEE11990084; Tue, 21 Oct 2008 18:21:14 +0000 (UTC) Received: from EXCNYSM0A1AJ.nysemail.nyenet ([10.66.81.139]) by mailc.nysemail.state.ny.us with Microsoft SMTPSVC(6.0.3790.3959); Tue, 21 Oct 2008 14:21:12 -0400 X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-Class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C933A9.CC203321" Subject: RE: [Q] shrink varchar2 size and truncate data???? Date: Tue, 21 Oct 2008 14:21:12 -0400 Message-ID: In-Reply-To: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [Q] shrink varchar2 size and truncate data???? References: <320659.71380.qm@web65704.mail.ac4.yahoo.com> <785A4E1EF4D9E745BAC909B7941BEC009BF66F@usplm201.amer.corp.eds.com> From: "Mercadante, Thomas F (LABOR)" To: , CC: , X-OriginalArrivalTime: 21 Oct 2008 18:21:12.0974 (UTC) FILETIME=[CC78FAE0:01C933A9] X-archive-position: 11846 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: Thomas.Mercadante@labor.state.ny.us Precedence: normal Reply-to: Thomas.Mercadante@labor.state.ny.us List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain ------_=_NextPart_001_01C933A9.CC203321 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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 =20 =20 ________________________________ From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Bradd Piontek Sent: Tuesday, October 21, 2008 2:07 PM To: guillermo.bort@eds.com Cc: mccdba1@yahoo.com; oracle-l@freelists.org Subject: Re: [Q] shrink varchar2 size and truncate data???? =20 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=20 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,=20 the greatest joy is in having someone=20 else do a first-class job under your =20 direction." -- William Feather On Tue, Oct 21, 2008 at 12:50 PM, Bort, Guillermo wrote: How about sth like: =20 update functions set instructions=3Dsubstr(instructions,0,1000) where lentgh(instructions) > 1000; =20 (where clause is not necessary, though it could improve performance by using an index should instructions have one) =20 Then alter the table. =20 hth =20 Guillermo Alan Bort DBA / DBA Main Team =20 EDS, an HP company ITO Arias 1851 Ciudad Autonoma de Buenos Aires (C1429DXC) Argentina =20 Tel: +54 11 4704-3132 E-mail: guillermo.bort@eds.com =20 =20 We deliver on our commitments so you can deliver on yours. =20 From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of dba1 mcc Sent: Tuesday, October 21, 2008 3:17 PM To: oracle-l@freelists.org Subject: [Q] shrink varchar2 size and truncate data???? =20 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. =20 =20 I can use following sql statemnet to find which records > 1000 characters, but how can I truncate data larger than 1000 characters? =20 select id, length(INSTRUCTIONS) from functions where length(INSTRUCTIONS) >=3D1000; =20 =20 ------_=_NextPart_001_01C933A9.CC203321 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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@freelists.org [mailto:oracle-l-bounce@freelists.org] = On Behalf Of Bradd Piontek
Sent: Tuesday, October = 21, 2008 2:07 PM
To: = guillermo.bort@eds.com
Cc: mccdba1@yahoo.com; oracle-l@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@eds.com> = wrote:

How about sth like:

 

update functions set = instructions=3Dsubstr(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@eds.com

 

We deliver on our = commitments

so you can deliver on = yours.

 

From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of dba1 mcc
Sent: Tuesday, October = 21, 2008 3:17 PM


To: oracle-l@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) = >=3D1000;

 

 

------_=_NextPart_001_01C933A9.CC203321-- -- http://www.freelists.org/webpage/oracle-l