Migrating NLS_LENGTH_SEMANTICS from BYTE to CHAR

[Lwik]

ORACLE RDBMS Version: 10.2.0.2.0

1. My dumps are in BYTE length semantics.
2. Altering system NLS_LENGTH_SEMANTICS=CHAR has a limited effect (only
new objects are created with new semantics).
3. Trying to alter existing schema objects to CHAR semantics
efficiently render schema unusable - from my experience.
4. Any export/import operation retains length semantics of the source
without options to alter.
5. One of the possibilities of altering data successfully is to create
a new schema with definitions altered to CHAR semantics, e.g. by
extracting and changing DDL from dump file, and then importing data
onto newly created schema skeleton.
7. Other methods are data pump, and dblink, with the former failing to
import all of the SQL data, and latter... I didn't try.

[epilip]

Post by Lwik
ORACLE RDBMS Version: 10.2.0.2.0

Mine was 9.2.0.6 / AIX5L

Post by Lwik
1. My dumps are in BYTE length semantics.

Normal

Post by Lwik
2. Altering system NLS_LENGTH_SEMANTICS=CHAR has a limited effect (only
new objects are created with new semantics).

It's better isn't it ?

Post by Lwik
3. Trying to alter existing schema objects to CHAR semantics
efficiently render schema unusable - from my experience.

From mine, it's so easy. I did it 2 weeks ago with Note:313175.1.
Columns changed need to be empty and not indexed. I changed the script
to work on a schema basis not on the whole database
1° imp ...rows=n constraints=n indexes=n
2° the script of Note:313175.1.
3° imp ... ignore=y

Post by Lwik
4. Any export/import operation retains length semantics of the source
without options to alter.

That's true but it's a conversion problem not an import problem.

Post by Lwik
5. One of the possibilities of altering data successfully is to create
a new schema with definitions altered to CHAR semantics, e.g. by
extracting and changing DDL from dump file, and then importing data
onto newly created schema skeleton.
7. Other methods are data pump, and dblink, with the former failing to
import all of the SQL data, and latter... I didn't try.