Wednesday, July 7, 2010

How to change CHARACTER SET parameter not to a superset


This is a method to change CHARACTER SET not to a superset as UTF-8, but to one at the same level,
e.g. from WE8ISO8859P1 to EL8ISO8859P7 (Greek).

For Oracle 9 and up, make sure you are connected "AS SYSDBA" in sqlplus.
For Oracle 8/8i, make sure you are connected as INTERNAL in svrmgrl.
Then follow these steps:
SHUTDOWN IMMEDIATE;

Make sure there is a database backup you can rely on, or create one.
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET INTERNAL_USE EL8ISO8859P7;

An alter database takes typically only a few minutes or less.
It depends on the number of columns in the database, not the amount of data.
SHUTDOWN;

If you use Oracle 8, then also do:
STARTUP RESTRICT;
SHUTDOWN;

The extra restart/shutdown is necessary in Oracle8(i) because of a SGA initialization bug which is fixed in Oracle9i.
Restore the parallel_server parameter in INIT.ORA, if necessary.
Restart the database:
STARTUP;