Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Migration to AL32UTF8 from WE8ISO8859P15

Re: Migration to AL32UTF8 from WE8ISO8859P15

From: Luc Demanche <lucdemanche_at_gmail.com>
Date: Mon, 15 Jan 2007 14:42:15 -0500
Message-ID: <4b3687720701151142j727eac06x62f498ed9686cf6e@mail.gmail.com>


Luke,

I forgot to ask you.
So, you have change every vachar2 for CHAR. And after have you changed the database to use CHAR (NLS_LENGTH_SEMANTICS = CHAR) ?

Thanks
Luc

On 1/11/07, Luke Davies <luke.davies_at_hansard.com> wrote:
>
> Luc
>
> We did this same upgrade some time ago and what we did was alter all the
> varchar2 fields from BYTE to CHAR which then did not involve an increase in
> the size field (although, of course, may actually increase the storage of
> the column).
>
> After running csscan to identify truncated or lossy columns - we then
> exported those tables and then after changing the character set and
> modifying the columns, deleting the data (truncating) (which involved
> turning off foreign keys) and then importing the data back in.
>
> All went quite smoothly in the end after a few practice runs!
>
> HTH
> Luke
>
> Luc Demanche wrote:
>
> Nigel,
>
> If I understand correctly, I only have to change the existing column size
> ? Should I multiple by 4 the size of my varchar2 ? Even if the column
> is not mentionned in the report of csscan ?
>
> After that, I recompile every storedprocs, views, etc manually.
>
> Then, I set the NLS_LENGTH_SEMANTICS=CHAR, so I don't have to worry about
> the size of my varchar2 anymore ?
>
> Thank you
> Luc
>
>
> On 1/10/07, Nigel Thomas <nigel_cl_thomas_at_yahoo.com> wrote:
> >
> > Luc
> >
> >
> > Q1/Q3: If you set the parameter NLS_LENGTH_SEMANTICS=CHAR then any
> > ambiguous *new* column/variable definitions will be made long enough for
> > characters rather than bytes. That seems a lot easier than changing every
> > single table and package definition.
> >
> > But existing columns won't be affected, as I recall. You would need to
> > MODIFY table/column definitions according (and then recompile affected
> > views, dependent packages etc).
> >
> > You should beware of the possible implications on index key sizes (as
> > the
> >
> > VARCHAR2 / NVARCHAR2 declarations in PL/SQL will be fixed as the
> > packages are recompiled (mostly this will happen implicitly because of your
> > DDL on the underlying tables - but you should check that any packages that
> > aren't automatically recompiled are manually recompiled.
> >
> > Q2: of course Oracle limits SQL types (NOT in PL/SQL) to 4000 bytes - so
> > up to 1000 * 4 byte chars (of course, most of your characters will occupy
> > just 1 or 2 bytes - your exact mileage will vary depending on language). You
> > can use CLOB instead, and many string functions work directly against
> > 'small' CLOBS - but remember that CLOBs also have some drawbacks (LOB space
> > management etc), so only convert if you are sure you have to.
> >
> > HTH
> >
> > Regards Nigel
> >
> >
>
>
>
> --
> Luc Demanche
> Oracle DBA
> (514) 867-9977
>
> The contents of this message and any attachments are confidential and
> are
> intended for the use of the persons to whom it is addressed.
> If you are not the intended recipient, you should not copy, forward, use
> or
> alter the message in any way, nor disclose its contents to any other
> person.
> Please notify the sender immediately and delete the e-mail from your
> system.
> The sender is not responsible for any alterations that may have occurred
> without
> authorisation. Any files attached to this email will have been checked by
> us
> with virus detection software before transmission.
> You should carry out your own virus checks before opening any attachments,
> as we
> do not accept any liability for loss or damage which may be caused by
> viruses.
>

-- 
Luc Demanche
Oracle DBA
(514) 867-9977

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jan 15 2007 - 13:42:15 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US