NLS_LENGTH_SEMENTICS from BYTE to CHAR
Hi,
For supporting multibyte character, we need to change NLS_LENGTH_SEMENTICS parameter from BYTE to CHAR. But this parameter setting will effect for new database tables created thereafter. To change the storage characteristics for existing database tables we explicitly executed Alter statements for database tables for columns having datatype as Varchar2 and Char.
Problem:
======
Since the number of database tables in PRODUCTION are very high and contains approx. 600 million of records spread over 400 database tables, we are not in a position to afford the time which will be spent in altering these database tables in PRODUCTION.
We ran the test by alter script in System Test environment and alteration of database tables covering 150 tables and 200 million of records was carried out in almost 16-20 hrs.
APPROACHES WE HAVE IN MIND
==========================
1. Alter all the table columns (We tried the same and taking too much time)
2. Export /Import with NLS_LENGTH_SEMENTICS set as CHAR(We discuss with our DBA about this approach and found that it will also take too much time and there is RISK of data inconsistency)
3. Drop the index of the table, run alter script for changing storage type BYTE to CHAR , and rebuild the index (this is also taking too much time).
All above approaches are very costly in terms of time that we cannot afford.
If any one having better solution then please suggest.
thanks in advance
Syed