Wednesday, July 23, 2008

Changing the Character Set in Oracle

if, the new character set is a strict superset of the current character set, it is possible to use the ALTER DATABASE CHARACTER SET to expedite the change in the database character set.

WARNING: Attempting to change the database character set to a character set that is not a strict superset can result in data loss and data corruption. To ensure data integrity, whenever migrating to a new character set that is not a strict superset, you must use export/import. It is essential to do a full backup of the database before using the ALTER DATABASE [NATIONAL] CHARACTER SET statement, since the command cannot be rolled.

The syntax is:

ALTER DATABASE [] CHARACTER SET ;
ALTER DATABASE [] NATIONAL CHARACTER SET ;

for example:
ALTER DATABASE CHARACTER SET WE8ISO8859P1;

But before changing the character set, its reccomended to use the Character Set Scanner utility. To install the CSSCAN schema.
$sqlplus / as sysdba
sql>$ORACLE_HOME/rdbms/admin/csminst.sql
To execute the utility:
$$ORACLE_HOME/bin/csscan
Here we can get the error like,
ld.so.1: csscan: fatal: libclntsh.so.10.1: open failed: No such file or directory Killed
The, we need to do,
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
After the successful completion of the scan three report files will be created scan.txt,scan.out and scan.err.In my case I have exported the database objects with exceptions as specified in the scan.out and plan to import it back after the database conversion is done.
Now,

SQL> SHUTDOWN IMMEDIATE; -- or NORMAL
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET new _character_set_name;
SQL> SHUTDOWN IMMEDIATE; -- or NORMAL
SQL> STARTUP;

No comments:

search engine

Custom Search