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: Tue, 30 Jan 2007 10:07:31 -0500
Message-ID: <4b3687720701300707w56a4ab2cx98eb61c0cf794edc@mail.gmail.com>


Luke,

We will have a brand new server and a brand new database. So I will create it in Unicode and I will set NLS_LENGTH_SEMANTICS to CHAR and I will import into it.

I will also make sure the storedproc are valid.

Regarding the varchar2 that will convert to CLOB, we have few columns that the content is more then 1000 characters. So they will have to be converted in CLOB.

Thank you everyone
Luc

On 1/16/07, Luke Davies <luke.davies_at_hansard.com> wrote:
>
> Luc
>
> Yes, we changed every varchar2 column from BYTE to CHAR which I admit was
> probably overkill, it may be that the majority of columns will only ever
> contain ASCII characters. But that required some analysis by the
> application developers, so instead of having to rely on them we just did the
> sledge-hammer approach and did them all.
>
> We did change the NLS_LENGTH_SEMANTICS to CHAR as well but be warned that
> you may have to change it back temporarily to BYTE when doing upgrades.
>
> Also 1 point that I forgot to mention that you *must* modify the database
> characterset first before altering the columns otherwise you still get
> trunacated columns on import, at least that was our experience.
>
> Cheers
> Luke
>
> Luc Demanche wrote:
>
> 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
>
> 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 Tue Jan 30 2007 - 09:07:31 CST

Original text of this message

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