COLUMN name FORMAT A40
COLUMN value FORMAT A40
SELECT name,
value
FROM v$parameter
WHERE SUBSTR(name, 1, 1) = '_'
ORDER BY name;
COLUMN FORMAT DEFAULT
Tuesday, May 19, 2009
Monday, May 18, 2009
Displays high water mark statistics.
COLUMN name FORMAT A40
COLUMN highwater FORMAT 999999999999
COLUMN last_value FORMAT 999999999999
SET PAGESIZE 24
SELECT hwm1.name,
hwm1.highwater,
hwm1.last_value
FROM dba_high_water_mark_statistics hwm1
WHERE hwm1.version = (SELECT MAX(hwm2.version)
FROM dba_high_water_mark_statistics hwm2
WHERE hwm2.name = hwm1.name)
ORDER BY hwm1.name;
COLUMN FORMAT DEFAULT
COLUMN highwater FORMAT 999999999999
COLUMN last_value FORMAT 999999999999
SET PAGESIZE 24
SELECT hwm1.name,
hwm1.highwater,
hwm1.last_value
FROM dba_high_water_mark_statistics hwm1
WHERE hwm1.version = (SELECT MAX(hwm2.version)
FROM dba_high_water_mark_statistics hwm2
WHERE hwm2.name = hwm1.name)
ORDER BY hwm1.name;
COLUMN FORMAT DEFAULT
Displays information on the current wait states for all active database sessions.
SET LINESIZE 250
SET PAGESIZE 1000
COLUMN username FORMAT A15
COLUMN osuser FORMAT A15
COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 9999999
COLUMN wait_class FORMAT A15
COLUMN state FORMAT A19
COLUMN logon_time FORMAT A20
SELECT NVL(a.username, '(oracle)') AS username,
a.osuser,
a.sid,
a.serial#,
d.spid AS process_id,
a.wait_class,
a.seconds_in_wait,
a.state,
a.blocking_session,
a.blocking_session_status,
a.module,
TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session a,
v$process d
WHERE a.paddr = d.addr
AND a.status = 'ACTIVE'
ORDER BY 1,2;
SET PAGESIZE 14
SET PAGESIZE 1000
COLUMN username FORMAT A15
COLUMN osuser FORMAT A15
COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 9999999
COLUMN wait_class FORMAT A15
COLUMN state FORMAT A19
COLUMN logon_time FORMAT A20
SELECT NVL(a.username, '(oracle)') AS username,
a.osuser,
a.sid,
a.serial#,
d.spid AS process_id,
a.wait_class,
a.seconds_in_wait,
a.state,
a.blocking_session,
a.blocking_session_status,
a.module,
TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session a,
v$process d
WHERE a.paddr = d.addr
AND a.status = 'ACTIVE'
ORDER BY 1,2;
SET PAGESIZE 14
Different ways to set up NLS parameters
The word NLS means National Language Support. The NLS_* parameters determine the
locale-specific behavior on both the client and the server; where * of NLS_* is for various strings which make various NLS parameters.
There are many NLS_* parameters like NLS_SORT, NLS_LANGUAGE, NLS_CHARACTERSET, NLS_DATE_LANGUAGE etc. In this post I will show how the NLS parameters can be set based on their setting of priority.
1)In SQL functions:
If you set NLS_* parameters inside SQL functions then that setting has the highest priority.
You can set in SQL functions like,
TO_CHAR(sysdate, 'DD/MON/YYYY', 'nls_date_language = FRENCH')
Below is an example. Note that in my client machine FRENCH language is not installed so it might not display properly.
SQL> select sysdate from dual;
SYSDATE
---------
07-FEB-09
SQL> select TO_CHAR(sysdate, 'DD/MON/YYYY', 'nls_date_language = FRENCH') from dual;
TO_CHAR(SYSDA
-------------
07/FEB./2009
Setting in this way (inside sql functions) overrides the default values that are set for the session in the initialization parameter file, set for the client with environment variables, or set for the session by the ALTER SESSION statement.
2)With the ALTER SESSION statement:
Setting through ALTER SESSION parameter has the second highest priority. Setting by an ALTER SESSION statement override the default values that are set for the session in the initialization parameter file or set by the client with environment variables.
Below is an example. As in my client machine Japanese language is not installed so displaying in Japanese character might not work properly.
SQL> select sysdate from dual;
SYSDATE
---------
07-FEB-09
SQL> alter session set NLS_DATE_LANGUAGE=JAPANESE;
Session altered.
SQL> select sysdate from dual;
SYSDATE
----------
07-02 -09
3)Through Environmental variable on the client machine:
This setting has the third highest priority. Through OS environmental variable you can set NLS_* parameters. Setting of environmental variable is platform specific. On windows machine you can set by,
C:>set NLS_*=value;
On unix machine
$export NLS_*=value (bash shell)
$setenv NLS_*=value (c shell)
Below is an example on my windows client machine.
C:\>set NLS_SORT=FRENCH
4)As initialization parameters on the server:
You can set the NLS_* parameters in the server machine inside the initialization parameter file. Setting in the initialization parameter specify a default session NLS environment. Setting in this way has no effect on the client side, they control only the server's behavior.
For example, if you use spfile then you can set NLS_TERRITORY parameter by below,
SQL> ALTER SYSTEM SET NLS_TERRITORY = "CZECH REPUBLIC" scope=spfile;
System altered.
Then in order to effect bounce database.
If I draw a table based on priority and ways to do then it will be like,
Priority Ways to do the task.
----------- -----------------------------------------
1 (highest) Set in SQL functions
2 Set by an ALTER SESSION statement
3 Set as an environment variable
4 Specified in the initialization parameter file
5 (lowest) Default
Related Documents
Unicode characterset in Oracle database.
What is NLS_LANG environmental variable?
What is database character set and how to check it
What is national character set / NLS_NCHAR_CHARACTERSET?
Which datatypes use the National Character Set?
What is character set and character set encoding
locale-specific behavior on both the client and the server; where * of NLS_* is for various strings which make various NLS parameters.
There are many NLS_* parameters like NLS_SORT, NLS_LANGUAGE, NLS_CHARACTERSET, NLS_DATE_LANGUAGE etc. In this post I will show how the NLS parameters can be set based on their setting of priority.
1)In SQL functions:
If you set NLS_* parameters inside SQL functions then that setting has the highest priority.
You can set in SQL functions like,
TO_CHAR(sysdate, 'DD/MON/YYYY', 'nls_date_language = FRENCH')
Below is an example. Note that in my client machine FRENCH language is not installed so it might not display properly.
SQL> select sysdate from dual;
SYSDATE
---------
07-FEB-09
SQL> select TO_CHAR(sysdate, 'DD/MON/YYYY', 'nls_date_language = FRENCH') from dual;
TO_CHAR(SYSDA
-------------
07/FEB./2009
Setting in this way (inside sql functions) overrides the default values that are set for the session in the initialization parameter file, set for the client with environment variables, or set for the session by the ALTER SESSION statement.
2)With the ALTER SESSION statement:
Setting through ALTER SESSION parameter has the second highest priority. Setting by an ALTER SESSION statement override the default values that are set for the session in the initialization parameter file or set by the client with environment variables.
Below is an example. As in my client machine Japanese language is not installed so displaying in Japanese character might not work properly.
SQL> select sysdate from dual;
SYSDATE
---------
07-FEB-09
SQL> alter session set NLS_DATE_LANGUAGE=JAPANESE;
Session altered.
SQL> select sysdate from dual;
SYSDATE
----------
07-02 -09
3)Through Environmental variable on the client machine:
This setting has the third highest priority. Through OS environmental variable you can set NLS_* parameters. Setting of environmental variable is platform specific. On windows machine you can set by,
C:>set NLS_*=value;
On unix machine
$export NLS_*=value (bash shell)
$setenv NLS_*=value (c shell)
Below is an example on my windows client machine.
C:\>set NLS_SORT=FRENCH
4)As initialization parameters on the server:
You can set the NLS_* parameters in the server machine inside the initialization parameter file. Setting in the initialization parameter specify a default session NLS environment. Setting in this way has no effect on the client side, they control only the server's behavior.
For example, if you use spfile then you can set NLS_TERRITORY parameter by below,
SQL> ALTER SYSTEM SET NLS_TERRITORY = "CZECH REPUBLIC" scope=spfile;
System altered.
Then in order to effect bounce database.
If I draw a table based on priority and ways to do then it will be like,
Priority Ways to do the task.
----------- -----------------------------------------
1 (highest) Set in SQL functions
2 Set by an ALTER SESSION statement
3 Set as an environment variable
4 Specified in the initialization parameter file
5 (lowest) Default
Related Documents
Unicode characterset in Oracle database.
What is NLS_LANG environmental variable?
What is database character set and how to check it
What is national character set / NLS_NCHAR_CHARACTERSET?
Which datatypes use the National Character Set?
What is character set and character set encoding
What is character set and character set encoding
The term character set indicates the set of characters used by a particular encoding system. It does not represent the numeric assignments of the characters nor the order of the characters but just the set of characters under an encoding system.
The term character set encoding refers how each character of a character set is represented under an encoding system. In order words character set encoding is the mapping of characters to binary values. It pairs the character from the character set with a natural number. Suppose, in 8-bit character set encoding each character from the character set is mapped to the values range from 0-255.
For example capital A will be encoded to 65. Every time you press A from keyboard it will be interpreted as 65. This system also named as encoding scheme.
Related Documents
Unicode characterset in Oracle database.
What is NLS_LANG environmental variable?
What is database character set and how to check it
Different ways to set up NLS parameters
What is national character set / NLS_NCHAR_CHARACTERSET?
Which datatypes use the National Character Set?
The term character set encoding refers how each character of a character set is represented under an encoding system. In order words character set encoding is the mapping of characters to binary values. It pairs the character from the character set with a natural number. Suppose, in 8-bit character set encoding each character from the character set is mapped to the values range from 0-255.
For example capital A will be encoded to 65. Every time you press A from keyboard it will be interpreted as 65. This system also named as encoding scheme.
Related Documents
Unicode characterset in Oracle database.
What is NLS_LANG environmental variable?
What is database character set and how to check it
Different ways to set up NLS parameters
What is national character set / NLS_NCHAR_CHARACTERSET?
Which datatypes use the National Character Set?
Which datatypes use the National Character Set?
There are three datatypes which can store data in the national character set.
1)NCHAR: It is fixed length national character set- character datatype. This datatype uses CHAR length semantics, that is, the length of the NCHAR datatype column is defined in characters.
2)NVARCHAR2: It is variable length national character set- character datatype. This datatype uses CHAR length semantics, that is, the length of the NVARCHAR2 datatype column is defined in characters.
3)NCLOB: It stores national character set data up to four gigabytes. Data is always stored in UCS2 or AL16UTF16, even if the NLS_NCHAR_CHARACTERSET is UTF8.
If you use NCHAR/NVARCHAR2/NCLOB data type then, use the (N'...') syntax when coding these data type so that literals are denoted as being in the national character set by prefixing letter 'N'.
Below is an example.
1)NCHAR: It is fixed length national character set- character datatype. This datatype uses CHAR length semantics, that is, the length of the NCHAR datatype column is defined in characters.
2)NVARCHAR2: It is variable length national character set- character datatype. This datatype uses CHAR length semantics, that is, the length of the NVARCHAR2 datatype column is defined in characters.
3)NCLOB: It stores national character set data up to four gigabytes. Data is always stored in UCS2 or AL16UTF16, even if the NLS_NCHAR_CHARACTERSET is UTF8.
If you use NCHAR/NVARCHAR2/NCLOB data type then, use the (N'...') syntax when coding these data type so that literals are denoted as being in the national character set by prefixing letter 'N'.
Below is an example.
SQL> create table t_test(col1 NVARCHAR2(30));
Table created.
SQL> insert into t_test values(N'This is NLS_NCHAR_CHARACTERSET');
1 row created.
Related Documents
Unicode characterset in Oracle database.
What is NLS_LANG environmental variable?
What is database character set and how to check it
Different ways to set up NLS parameters
What is national character set / NLS_NCHAR_CHARACTERSET?
What is character set and character set encoding
What is national character set / NLS_NCHAR_CHARACTERSET?
- The national character set is the character set which is defined in oracle database in addition to normal character set.
- The normal character set is defined by the parameter NLS_CHARACTERSET and the national character set is defined by the parameter NLS_NCHAR_CHARACTERSET.
- The national character set is used for data stored in NCHAR, NVARCHAR2 and NCLOB columns while the normal character set is used for data stored in CHAR, VARCHAR2, CLOB columns.
- You can get the value of national character set or NLS_NCHAR_CHARACTERSET by,
SQL> select value from nls_database_parameters where parameter='NLS_NCHAR_CHARACTERSET';
VALUE
----------------------------------------
AL16UTF16
SQL> select value$ from sys.props$ where name='NLS_NCHAR_CHARACTERSET';
VALUE$
--------------------------------------------------------------------------------
AL16UTF16
SQL> select property_value from database_properties where property_name
='NLS_NCHAR_CHARACTERSET';
PROPERTY_VALUE
--------------------------------------------------------------------------------
AL16UTF16
- NLS_NCHAR_CHARACTERSET is defined when the database is created and specified with the CREATE DATABASE command.
- The default value of NLS_NCHAR_CHARACTERSET is AL16UTF16.
- From Oracle 9i onwards the NLS_NCHAR_CHARACTERSET can have only 2 values, either UTF8 or AL16UTF16 and both are unicode character sets.
- National character set are always defined in CHAR length semantics and you cannot define them in BYTE. That means if you defines NCHAR(5) then 5 maximum characters can be stored regardless of how many bytes they can hold.
- Many one thinks that they need to use the NLS_NCHAR_CHARACTERSET to have UNICODE support in oracle but this is not true. One can always use UNICODE in either two ways. Storing data into NCHAR, NVARCHAR2 or NCLOB columns or you can perfectly use "normal" CHAR and VARCHAR2 columns for storing unicode in a database who has a AL32UTF8 / UTF8 NLS_CHARACTERSET.
Unicode characterset in Oracle database.
What is NLS_LANG environmental variable?
What is database character set and how to check it
Different ways to set up NLS parameters
Which datatypes use the National Character Set?
What is character set and character set encoding
What is database character set and how to check it
Note that database character set refers to the term character set encoding and in oracle database the terms character set and character set encoding are often used interchangeably.
The database character set in oracle determines the set of 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.
The database character set information is stored in the data dictionary tables named SYS.PROPS$.
You can get the character set used in the database by SYS.PROPS$ table or any other views (like database_properties/ nls_database_parameters) exist in the database. The parameter NLS_CHARACTERSET value contains the database character set name. Get it from,
The database character set in oracle determines the set of 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.
The database character set information is stored in the data dictionary tables named SYS.PROPS$.
You can get the character set used in the database by SYS.PROPS$ table or any other views (like database_properties/ nls_database_parameters) exist in the database. The parameter NLS_CHARACTERSET value contains the database character set name. Get it from,
SQL> select value$ from sys.props$ where name='NLS_CHARACTERSET';
VALUE$
--------------------------------------------------------------------------------
WE8MSWIN1252
SQL> select property_value from database_properties where property_name=
'NLS_CHARACTERSET';
PROPERTY_VALUE
--------------------------------------------------------------------------------
WE8MSWIN1252
SQL> select value from nls_database_parameters where parameter='NLS_CHARACTERSET';
VALUE
----------------------------------------
WE8MSWIN1252
Related Documents
Unicode characterset in Oracle database.
What is NLS_LANG environmental variable?
Different ways to set up NLS parameters
What is national character set / NLS_NCHAR_CHARACTERSET?
Which datatypes use the National Character Set?
What is character set and character set encoding
What is NLS_LANG environmental variable?
NLS_LANG is a client side environmental variable. To specify the locale behavior- setting the NLS_LANG environment parameter is the simplest way.
With the setting of NLS_LANG parameter on client machine it is specified the language, territory and character set used by the client application. As through NLS_LANG parameter, client character set is also specified so oracle has an idea which is the character set for data entered or displayed by a client program as well as Oracle can do (if needed) conversion from the client's character set to the database character set.
On UNIX machine NLS_LANG parameter is an environmental variable and on windows machine this value comes from registry settings.
The parameter NLS_LANG holds the following format.
NLS_LANG=[Language]_[Territory].[clients character set]
The default value of NLS_LANG is AMERICAN_AMERICA.US7ASCII which indicates that
The language is AMERICAN,
the territory is AMERICA, and
the character set is US7ASCII.
The first part of NLS_LANG parameter is language and it is used for Oracle Database messages, sorting, day names, and month names. Each language has a unique name.
The language specifies default values for territory and character set so if language is specified then the other two arguments can be omitted. Language can have the value like AMERICAN, GERMAN, FRENCH, JAPANESE etc. The default value is AMERICAN.
The second part of NLS_LANG parameter is territory and it is used for default date, monetary, and numeric formats. Each territory has a unique name. Territory can have the value like AMERICA, FRANCE, JAPAN, CANADA etc. If the territory is not specified, then the value is derived from the language value.
The third part of NLS_LANG parameter is the client character set. It specifies the character set that is used by the client application. The client character set used for Oracle should be equivalent to the character set supported for the client machine. 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. Example of client character set is US7ASCII, WE8ISO8859P1, WE8DEC, WE8MSWIN1252 etc.
It is important to note that all three parts of NLS_LANG environmental variable/parameter are optional. This means if any of the parts are not specified then default value is used- may be the default value is derived value. You can specify Territory and/or character set without language value; in this case your must include the preceding delimiter -underscore (_) for territory and period (.) for character set. If you don't include the delimiter then the whole value is parsed as a language name.
For example you can only set territory portion by,
NLS_LANG=_FRANCE
You can only set client character set portion by,
NLS_LANG=.WE8MSWIN1252
The three parts of NLS_LANG can be specified in many combination but all of the combination may not work properly. Like,
NLS_LANG = JAPANESE_JAPAN.WE8ISO8859P1
This combination can be will not work properly. Beacuse the specification will try to support Japanese by using a Western European character set but WE8ISO8859P1 character set does not support any Japanese characters.
So if you set your NLS_LANG environmental variable above then you can't store or display Japanese character.
Some logical combination,
NLS_LANG = AMERICAN_AMERICA.WE8MSWIN1252
NLS_LANG = FRENCH_CANADA.WE8ISO8859P1
NLS_LANG = JAPANESE_JAPAN.JA16EUC
In server machine there is no need to set NLS_LANG environmental variable. This variable is only needed for client machine. The character set defined for NLS_LANG environmental variable should be the subset or equal to the database character set so that oracle can aware of each character set and thus can convert client character set correctly. It is also important that character set value of NLS_LANG variable should reflect client machine supported character set so that client machine can display that properly. For example if japanese character set is not installed in client machine but NLS_LANG parameter is set as JAPANESE_JAPAN.JA16EUC then client will not be able to see JAPANESE characters properly.
Important Notes About NLS_LANG Parameter
1)NLS_LANG is used to let Oracle know what character set client's OS is using so that Oracle can do (if needed) conversion from the client's character set to the database characterset.
2)Don't think that NLS_LANG needs to be the same as the database characterset.
3)The characterset defined with the NLS_LANG parameter does not change your client's character set. You cannot change the characterset of your client by using a different NLS_LANG setting. NLS_LANG is used to let Oracle know what characterset you are using on the client side.
4)Don't think that, if you don't set the NLS_LANG on the client it uses the NLS_LANG of the server (which is not true). If you don't set it then default NLS_LANG as described earlier in this post is used.
5)If the NLS_LANG variable match with database character set then oracle will perform no validation on the character set; and thus incorrect NLS_LANG settings may cause to enter garbage data into the database.
Related Documents
Unicode characterset in Oracle database.
What is database character set and how to check it
Different ways to set up NLS parameters
What is national character set / NLS_NCHAR_CHARACTERSET?
Which datatypes use the National Character Set?
What is character set and character set encoding
With the setting of NLS_LANG parameter on client machine it is specified the language, territory and character set used by the client application. As through NLS_LANG parameter, client character set is also specified so oracle has an idea which is the character set for data entered or displayed by a client program as well as Oracle can do (if needed) conversion from the client's character set to the database character set.
On UNIX machine NLS_LANG parameter is an environmental variable and on windows machine this value comes from registry settings.
The parameter NLS_LANG holds the following format.
NLS_LANG=[Language]_[Territory].[clients character set]
The default value of NLS_LANG is AMERICAN_AMERICA.US7ASCII which indicates that
The language is AMERICAN,
the territory is AMERICA, and
the character set is US7ASCII.
The first part of NLS_LANG parameter is language and it is used for Oracle Database messages, sorting, day names, and month names. Each language has a unique name.
The language specifies default values for territory and character set so if language is specified then the other two arguments can be omitted. Language can have the value like AMERICAN, GERMAN, FRENCH, JAPANESE etc. The default value is AMERICAN.
The second part of NLS_LANG parameter is territory and it is used for default date, monetary, and numeric formats. Each territory has a unique name. Territory can have the value like AMERICA, FRANCE, JAPAN, CANADA etc. If the territory is not specified, then the value is derived from the language value.
The third part of NLS_LANG parameter is the client character set. It specifies the character set that is used by the client application. The client character set used for Oracle should be equivalent to the character set supported for the client machine. 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. Example of client character set is US7ASCII, WE8ISO8859P1, WE8DEC, WE8MSWIN1252 etc.
It is important to note that all three parts of NLS_LANG environmental variable/parameter are optional. This means if any of the parts are not specified then default value is used- may be the default value is derived value. You can specify Territory and/or character set without language value; in this case your must include the preceding delimiter -underscore (_) for territory and period (.) for character set. If you don't include the delimiter then the whole value is parsed as a language name.
For example you can only set territory portion by,
NLS_LANG=_FRANCE
You can only set client character set portion by,
NLS_LANG=.WE8MSWIN1252
The three parts of NLS_LANG can be specified in many combination but all of the combination may not work properly. Like,
NLS_LANG = JAPANESE_JAPAN.WE8ISO8859P1
This combination can be will not work properly. Beacuse the specification will try to support Japanese by using a Western European character set but WE8ISO8859P1 character set does not support any Japanese characters.
So if you set your NLS_LANG environmental variable above then you can't store or display Japanese character.
Some logical combination,
NLS_LANG = AMERICAN_AMERICA.WE8MSWIN1252
NLS_LANG = FRENCH_CANADA.WE8ISO8859P1
NLS_LANG = JAPANESE_JAPAN.JA16EUC
In server machine there is no need to set NLS_LANG environmental variable. This variable is only needed for client machine. The character set defined for NLS_LANG environmental variable should be the subset or equal to the database character set so that oracle can aware of each character set and thus can convert client character set correctly. It is also important that character set value of NLS_LANG variable should reflect client machine supported character set so that client machine can display that properly. For example if japanese character set is not installed in client machine but NLS_LANG parameter is set as JAPANESE_JAPAN.JA16EUC then client will not be able to see JAPANESE characters properly.
Important Notes About NLS_LANG Parameter
1)NLS_LANG is used to let Oracle know what character set client's OS is using so that Oracle can do (if needed) conversion from the client's character set to the database characterset.
2)Don't think that NLS_LANG needs to be the same as the database characterset.
3)The characterset defined with the NLS_LANG parameter does not change your client's character set. You cannot change the characterset of your client by using a different NLS_LANG setting. NLS_LANG is used to let Oracle know what characterset you are using on the client side.
4)Don't think that, if you don't set the NLS_LANG on the client it uses the NLS_LANG of the server (which is not true). If you don't set it then default NLS_LANG as described earlier in this post is used.
5)If the NLS_LANG variable match with database character set then oracle will perform no validation on the character set; and thus incorrect NLS_LANG settings may cause to enter garbage data into the database.
Related Documents
Unicode characterset in Oracle database.
What is database character set and how to check it
Different ways to set up NLS parameters
What is national character set / NLS_NCHAR_CHARACTERSET?
Which datatypes use the National Character Set?
What is character set and character set encoding
Unicode characterset in Oracle database.
Before starting this post let's have an idea about unicode. Unicode is a Universal encoding scheme which is designed to include far more characters than the normal character set, in fact, Unicode wants to be able to list ALL characters. So, with unicode support in oracle data from any languages can be stored and retrieved from oracle.
Oracle supports unicode within many of the character sets starting from Oracle 7.
Below is the list of character sets that is used to support unicode in oracle.
1)AL24UTFFSS: This character set was the first Unicode character set supported by Oracle. The AL24UTFFSS encoding scheme was based on the Unicode 1.1 standard, which is now obsolete. This unicode character set was used between oracle version 7.2 to 8.1.
2)UTF8: UTF8 was the UTF-8 encoded character set in Oracle8 and 8i. It followed the
Unicode 2.1 standard between Oracle 8.0 and 8.1.6, and was upgraded to Unicode
version 3.0 for oracle versions 8.1.7, 9i, 10g and 11g. If supplementary characters are inserted into in a UTF8 database encoded with Unicode version 3.0, then the actual data will be treated as 2 separate undefined characters, occupying 6 bytes in storage. So for fully support of supplementary characters use AL32UTF8 character set instead of UTF8.
3)UTFE: UTFE has the same properties as UTF8 on ASCII based platforms. As of UTF8 it is used in different oracle versions.
4)AL32UTF8: This is the UTF-8 encoded character set introduced in Oracle9i.
In Oracle 9.2 AL32UTF8 implemented unicode 3.1,
in 10.1 it implemented the Unicode 3.2 standard,
in Oracle 10.2 it supports the Unicode 4.01 standard and
in Oracle 11.1 it supports the Unicode 5.0.
AL32UTF8 was introduced to provide support for the newly defined supplementary characters. All supplementary characters are stored as 4 bytes in AL32UTF8. As while designed UTF-8 there was no concept of supplementary characters therefore UTF8 has a maximum of 3 bytes per character.
5)AL16UTF16: This is the first UTF-16 encoded character set in Oracle. It was introduced in Oracle9i as the default national character set (NLS_NCHAR_CHARACTERSET). It also provides support for the newly defined supplementary characters. All supplementary characters are stored as 4 bytes.
As with AL32UTF8, the plan is to keep enhancing AL16UTF16 as
necessary to support future version of the Unicode standard.
AL16UTF16 cannot be used as a database character set (NLS_CHARACTERSET), it is only used as the national character set (NLS_NCHAR_CHARACTERSET).
Like, AL32UTF8
In Oracle 9.0 AL16UTF16 implemented unicode 3.0,
in Oracle 9.2 it implemented unicode 3.1,
in 10.1 it implemented the Unicode 3.2 standard,
in Oracle 10.2 it supports the Unicode 4.01 standard and
in Oracle 11.1 it supports the Unicode 5.0.
Related Documents
What is NLS_LANG environmental variable?
What is database character set and how to check it
Different ways to set up NLS parameters
What is national character set / NLS_NCHAR_CHARACTERSET?
Which datatypes use the National Character Set?
What is character set and character set encoding
Oracle supports unicode within many of the character sets starting from Oracle 7.
Below is the list of character sets that is used to support unicode in oracle.
1)AL24UTFFSS: This character set was the first Unicode character set supported by Oracle. The AL24UTFFSS encoding scheme was based on the Unicode 1.1 standard, which is now obsolete. This unicode character set was used between oracle version 7.2 to 8.1.
2)UTF8: UTF8 was the UTF-8 encoded character set in Oracle8 and 8i. It followed the
Unicode 2.1 standard between Oracle 8.0 and 8.1.6, and was upgraded to Unicode
version 3.0 for oracle versions 8.1.7, 9i, 10g and 11g. If supplementary characters are inserted into in a UTF8 database encoded with Unicode version 3.0, then the actual data will be treated as 2 separate undefined characters, occupying 6 bytes in storage. So for fully support of supplementary characters use AL32UTF8 character set instead of UTF8.
3)UTFE: UTFE has the same properties as UTF8 on ASCII based platforms. As of UTF8 it is used in different oracle versions.
4)AL32UTF8: This is the UTF-8 encoded character set introduced in Oracle9i.
In Oracle 9.2 AL32UTF8 implemented unicode 3.1,
in 10.1 it implemented the Unicode 3.2 standard,
in Oracle 10.2 it supports the Unicode 4.01 standard and
in Oracle 11.1 it supports the Unicode 5.0.
AL32UTF8 was introduced to provide support for the newly defined supplementary characters. All supplementary characters are stored as 4 bytes in AL32UTF8. As while designed UTF-8 there was no concept of supplementary characters therefore UTF8 has a maximum of 3 bytes per character.
5)AL16UTF16: This is the first UTF-16 encoded character set in Oracle. It was introduced in Oracle9i as the default national character set (NLS_NCHAR_CHARACTERSET). It also provides support for the newly defined supplementary characters. All supplementary characters are stored as 4 bytes.
As with AL32UTF8, the plan is to keep enhancing AL16UTF16 as
necessary to support future version of the Unicode standard.
AL16UTF16 cannot be used as a database character set (NLS_CHARACTERSET), it is only used as the national character set (NLS_NCHAR_CHARACTERSET).
Like, AL32UTF8
In Oracle 9.0 AL16UTF16 implemented unicode 3.0,
in Oracle 9.2 it implemented unicode 3.1,
in 10.1 it implemented the Unicode 3.2 standard,
in Oracle 10.2 it supports the Unicode 4.01 standard and
in Oracle 11.1 it supports the Unicode 5.0.
Related Documents
What is NLS_LANG environmental variable?
What is database character set and how to check it
Different ways to set up NLS parameters
What is national character set / NLS_NCHAR_CHARACTERSET?
Which datatypes use the National Character Set?
What is character set and character set encoding
Subscribe to:
Posts (Atom)