Re: Migration to AL32UTF8 from WE8ISO8859P15

Good morning,

Last question .... do you have an "on-logon" trigger to set the NLS_LENGTH_SEMANTICS=CHAR to make sure that every table creation will use the right semantics? Can we set it by default ?

Thank you again
Luc

On 1/30/07, Luc Demanche <lucdemanche_at_gmail.com> wrote:
>
> 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
>

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

--
http://www.freelists.org/webpage/oracle-l