2

We are converting a Oracle Single Byte instance into Multi-Byte. Following is the summery of steps we have following:

  • Take an export of Single Byte schema
  • Generate DDL file from exported Dump in step 1(Only table Definitions).
  • Run these table creation scripts in Multi Byte(AL32UTF8) Instance
  • Import data by excluding table definitions

Its working as expected. Help me if we need to set nls_length_semantics to CHAR for target multi-byte instance? Currently this is set to BYTE

Maddy
  • 3,726
  • 9
  • 41
  • 55
  • 1
    Yes, as you have said it works as expected for the existing data because they were all equal to single byte. But after migration if you try to insert multi byte characters in the table it will return errors. So set the `nls_semantics` to `CHAR`. – atokpas Nov 16 '16 at 05:38
  • @JSapkota How to set this? ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=CHAR SCOPE=BOTH – Maddy Nov 16 '16 at 05:46
  • Yes, that's that statement makes it permanent. – atokpas Nov 16 '16 at 05:50
  • @JSapkota, Even after execute this, at session level NLS_LENGTH_SEMANTICS are shown as BYTE. Can we ignore this? – Maddy Nov 16 '16 at 05:51
  • How have you got that value in your session? If you are querying `nls_database_parameters` that values are set during the database creation, you can see it using `show parameter nls_length_semantics`. – atokpas Nov 16 '16 at 06:01
  • 1
    I would recommend to read this My Oracle Support Document. **Examples and limits of BYTE and CHAR semantics usage (NLS_LENGTH_SEMANTICS) (Doc ID 144808.1)** – atokpas Nov 16 '16 at 06:05
  • @JSapkota, I am understanding better now. At session level i cant alter as the connection created by Application Server. But am explicitly updating all VARCHAR2(x byte) columns with VARCHAR2(CHAR). Hope this is sufficient. Why are we needed to set this again at instance level? – Maddy Nov 16 '16 at 06:59
  • 1
    You don't need to change it at instance level once you have modified your table definition. – atokpas Nov 16 '16 at 07:05
  • @JSapkota, Sory but last question.. By default all sessions NLS_LENGTH_SEMANTICS are set to BYTE? Can we modify this behaviour? – Maddy Nov 16 '16 at 07:42
  • 1
    Yes, the default value for `NLS_LENGTH_SEMANTICS` is `BYTE`. Generally we don't change it database wide. If you need to `CHAR` semantics then you can use it explicitly(`VARCHAR2(5 CHAR`) or using `ALTER SESSION`. – atokpas Nov 16 '16 at 07:50
  • @JSapkota One more question, What is impact of changing NLS_LENGTH_SEMANTICS at instance level? – Maddy Nov 16 '16 at 07:55
  • Did you consult this guideline from Oracle: [Character Set Migration](https://docs.oracle.com/database/121/NLSPG/ch11charsetmig.htm#NLSPG011) – Wernfried Domscheit Nov 16 '16 at 08:12
  • @WernfriedDomscheit, I have gone through similar documentation. My Source char set is WE8ISO8859P15 and moving into AL32UTF8. Here we are changing all VARCHAR definitions to VARCHAR(CHAR) to accommodate size changes if any. But tables created new are defaulting to VARCHAR(BYTE) unless the session NLS_LENGTH_SEMANTICS are changed. – Maddy Nov 16 '16 at 08:30
  • @WernfriedDomscheit, Please caution me if am missing anything here!! – Maddy Nov 16 '16 at 08:44

0 Answers0