My Oracle Support Banner

SCRIPT: Changing columns to CHAR length semantics ( NLS_LENGTH_SEMANTICS ) (Doc ID 313175.1)

Last updated on OCTOBER 11, 2023

Applies to:

Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database - Standard Edition - Version 9.0.1.0 to 11.2.0.1 [Release 9.0.1 to 11.2]
Oracle Database - Enterprise Edition - Version 9.0.1.0 to 11.2.0.1 [Release 9.0.1 to 11.2]
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 9.0.1.0 to 11.2.0.1.0


Goal

NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample or bulit-in schema(s), Public Documentation delivered with an Oracle database product or other training material.  Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.

This script can be used if you want to change columns from BYTE semantics to CHAR semantics.

Use DMU tool from 12c and above to scan and identify the columns, do not use this script from 12c and above.

Usually you want to do this when you move from a database with a single-byte character set, to a database with a multi-byte character set. However, when columns with BYTE semantics are exported, these are recreated in the same way in the target database during the import. Setting the NLS_LENGTH_SEMANTICS parameter does not change this. The columns will always have to be changed "manually" to use CHAR semantics. Therefore you can use this script to help you achieve this.

Typical usage of this script is:

Note that Csalter/alter database has some limitations with using CHAR semantics columns when going from single to multi byte (to AL32UTF8 bvb), hence if you plan to change the NLS_CHARACTERSET, change the columns to CHAR semantics *after* the change of the NLS_CHARACTERSET


* when using exp/imp to go to a new database with a multibyte characterset like UTF8/AL32UTF8

  • Export the original database.
  • Import only the table definitions into the new database, without inserting the rows (ROWS=N import).
  • Run (a version of) this script to change the columns to CHAR semantics.
  • Import the rows into the new database using IGNORE=Y parameter for imp or the TABLE_EXISTS_ACTION=TRUNCATE option for Impdp.

* when using Csalter/alter database together with partial exp/imp to change a database to a multibyte characterset like UTF8/AL32UTF8:

  • Export the "convertible" data
  • truncate the "convertible" data
  • change the characterset using Csalter/alter database
  • Run (a version of) this script to change the columns to CHAR semantics.
  • Import the exported rows back into the database using IGNORE=Y parameter for imp or the TABLE_EXISTS_ACTION=TRUNCATE option for Impdp


* When changing the semantics of existing columns from BYTE to CHAR in a database (this can be a multibyte or single byte database) without changing the  NLS_CHARACTERSET simply run (a version of) the script.

More information on NLS_LENGTH_SEMANTICS is found in <Note 144808.1> Examples and limits of BYTE and CHAR semantics usage (NLS_LENGTH_SEMANTICS)

Note that running direct DDL against certain tables is not allowed, and therefore the length semantics of the columns of these tables cannot be changed.
An example of this are AQ tables, where ORA-24005 is expected when running DDL against these tables.

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
Goal
Solution
 Requirements
 Configuring
 Instructions
 Script
 Sample Output
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.