2 Ora Instances with Different CharacterSets?

[Sumedha Bhosekar]

Hello,

I know its possible on ORacle to have multiple instanaces running. But what i would like to know is that, is it possible for these different instances to have different charactersets?.

As a limitation of hardware i need to install 2 products with different charactersets using a single oracle installation. So was wondering if this is possible and more so supported. If yes then could you let me know the steps or probably a doc explaining the steps.

Thanks and Regards
Sumedha

[Indira Aramandla]

Hi Sumedha,

Database character set and how is it set :-

The database character set is specified when the database is created and is stored in the data dictionary table, sys.props$. It is used to determine which characters can be stored in the database. It is also used to determine the character set to be used for object identifiers and PL/SQL variables and for storing PL/SQL program source.

Client character set how is it set?

The client character set used for Oracle should be equivalent to the character set supported for your terminal. This character set should also be equivalent to or a subset of the character set used for your database so that every character input through the terminal has a matching character to map to in the database.

It is set by the environment variable NLS_LANG. This has the format:
LANGUAGE_TERRITORY.CHARACTERSET and is set as a local environment variable in Unix

From Oracle 9i onwards the NLS_NCHAR_CHARACTERSET can have only 2 values:
UTF8 or AL16UTF16.

Indira A

[Sumedha Bhosekar]

Hello Indira,

So do you think the Database character set can be different for the 2 instances if the ORACLE_HOME value is the same for both instances.

Thanks
Sumedha

[Indira Aramandla]

Hi Sumedha,

Yes you can have different character sets for different databases as it is specified when the database is created and is stored in the data dictionary table of the database.



Indira A

[Sumedha Bhosekar]

Hi,

So i guess its just to add the 2 Charactersets details in the data dictionary table, sys.props$. to use different sets? Am i right here?.

Regards,
Sumedha

[Indira Aramandla · Accepted solution]

Hi Sumedha,

I ment that there can co-exist two databases with different charactersets on the same server.

Characterset for a database is defined when the database is initially created. i.e if you are using the dbassist to create the database it will prompt you to select the characterset. or if you are using the script to create the database you specify the characterset.
eg: -
CREATE DATABASE "rprd"
maxdatafiles 2048
maxinstances 8
maxlogfiles 64
character set US7ASCII
national character set US7ASCII

After this when the database is created this characterset is stored in the data dictionary table, sys.props$.

For an existing database changing character set takes a very long time and uses lots of rollback space. Changing the character set is done by either export / import or by using the Alter database character set commnad. Remember that changing the database character set only changes the way characters are stored on the database. This should NOT have any effect on the clients and you should not have to change the NLS_LANG for example. The NLS_LANG on the clients should simply reflect the setup of that machine.

I hope this is clear now.


Indira A

[Indira Aramandla]

Sumedha,

Attached is a document about changing character set for an existing database from metalink.


Indira A

[Sumedha Bhosekar]

Hello Indira,

Yes i get it now. Thx for the descriptive explantion. Appreciate it.

Regards
Sumedha

[Jean-Luc Oudart]

Sumedha,

to the excellent post from Indira I will add this document.

Regards
Jean-Luc

[Jean-Luc Oudart]

I also like to point to these Metlink documents :

Note:227330.1 Character Sets & Conversion - Frequently Asked Questions

Note:60134.1 Globalization (NLS) - Frequently Asked Questions

Regards
Jean-Luc

[Sumedha Bhosekar]

Hi Jean,

Thx for the update, appreciate it.

Regards
Sumedha