Character set in Oracle 9i database.

mukeshsaini
mukeshsaini used Ask the Experts™
on
Hi guys,

We are in process of upgrading databases from 8.1.7.3 to 9.2.0 on Solaris8. I tried to upgrade one of the database using Database upgrade assistant and I got the following warning message:

Your database is using US7ASCII as the NCHAR type.In Oracle9i, the NCHAR datatypes are limited to the Unicode character set encoding(UTF8 and AL16UTF16) only.So change NCHAR columns in user tables to one of the supported types.

Curently we are using US7ASCII both for database character set and for NCHAR and we don't have any plan to support any other language in future except English.

So I want to know that do I really have to change database character set and National Character set from US7ASCII to any other if I want to upgrade from 8.1.7.3 to 9.2.0? If yes, is there any effect on our existing applications? Do I have to make any changes?

Thanks a lot in advance.

Regrads,
msaini
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi Msaini,


i attached you an excerpt from the official oracle documentation.

Regards,
Stefan




Changing the Character Set After Database Creation

In some cases, you may wish to change the existing database character set. For instance, you may find that the number of languages that need to be supported in your database have increased. In most cases, you will need to do a full export/import to properly convert all data to the new character set. However, if and only 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.

The target character set is a strict superset if and only if each and every codepoint in the source character set is available in the target character set, with the same corresponding codepoint value. For instance the following migration scenarios can take advantage of the ALTER DATABASE CHARACTER SET command since US7ASCII is a strict subset of WE8ISO8859P1, AL24UTFFSS, and UTF8:

Current Character Set  
 New Character Set  
 New Character Set is strict superset?  
 
US7ASCII  
 WE8ISO8859P1  
 yes  
 
US7ASCII  
 ALT24UTFFSS  
 yes  
 
US7ASCII  
 UTF8  
 yes  
 
 


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 back. The syntax is:

ALTER DATABASE [<db_name>] CHARACTER SET <new_character_set>;
ALTER DATABASE [<db_name>] NATIONAL CHARACTER SET <new_NCHAR_character_set>;


The database name is optional. The character set name should be specified without quotes, for example:

ALTER DATABASE CHARACTER SET WE8ISO8859P1;


To change the database character set, perform the following steps. Not all of them are absolutely necessary, but they are highly recommended:

SQL> SHUTDOWN IMMEDIATE;   -- or NORMAL
    <do a full backup>

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;


To change the national character set, replace the ALTER DATABASE CHARACTER SET statement with ALTER DATABASE NATIONAL CHARACTER SET. You can issue both commands together if desired.

Hi there,

- It depend on whether your applications are using NCHAR, NVARCHAR2, NCLOB datatypes or not. Check first if you are using these types.

Author

Commented:
Hi marcelectro,

None of our applications are using NCHAR, NVARCHAR2, NCLOB datatypes. So what that means?

Regards,
Mukesh




That you can change it without having to worry about anything to break ;-)!


SQL> SHUTDOWN IMMEDIATE;   -- or NORMAL <do a full backup>
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE NATIONAL CHARACTER SET UTF8;
SQL> SHUTDOWN IMMEDIATE;   -- or NORMAL
SQL> STARTUP;

Cheers,
Stefan

Commented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Accept stemu2000's comment as answer
Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
jpkemp
EE Cleanup Volunteer

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial