Here is something that I lately found out.

Changing the character set of a Database in Oracle 8i

It is possible in 8i to drectly change the character set of a database. This can only be done, however, if the new character set is a strict superset of the original character set. For instance, according to 8.1.6 documentation, the conversion can be made from the US7ASCII character set to three other character sets which are strict supersets:

current New

US7ASCII WE8ISO8859P1
US7ASCII ALT24UTFFSS
US7ASCII UTF8

to change the character set from US7ASCII to UTF8, execute the following,

SVRMGRL> startup mount
SVRMGRL> alter system enable restricted session;
SVRMGRL> alter system set job_queue_process=0;
SVRMGRL> alter system set aq_tm_process=0
SVRMGRL> alter database open;
SVRMGRL> alter database character set UTF8;
SVRMGRL> shutdown immediate; -- or normal
SVRMGRL> startup

In the cases where the new character set is not a strict superset of the original character set, a full export/import is necessary.

Tip cotributed by Adam Pope [email protected]

I hope this can solve your problem :)

Sam