Oracle Character set identification

character-setimportoracle-11g-r2

In the given export(Normal Export) log file i see following:

Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

Is this multi-byte or single byte database char-set.

Can you please explain me a bit

What is difference between multi-byte and single byte character set?
Can we import multibyte into Single byte and vice versa?
How can we identify weather a character set is multi-byte or single byte by looking into its name?

Please provide any other useful information on this topic?

Best Answer

Ok, this basically means:

The National character set of the database you exported from was AL16UTF16. This is a multi-byte character set and, being the national character set, would have been used for any NCHAR, NVARCHAR and NCLOB columns (the N in the name stands for "national").
The "normal" character set of the database you exported from was WE8ISO8859P1. This is an 8-bit character set (the Western European ISO variant) and would have been used for any CHAR, VARCHAR and CLOB columns.

You can see which character sets are being used by a particular database by querying the NLS_DATABASE_PARAMETERS data dictionary view. Your export would have been done from a database with the parameter NLS_CHARACTERSET set to WE8ISO8859P1, and the NLS_NCHAR_CHARACTERSET parameter set to AL16UTF16.

The difference between multi-byte and single-byte character sets is explained in the name. Single-byte characters are stored in a single byte, i.e. 8 bits. Multi-byte characters are stored in one or more (multiple) bytes.

As far as importing multi-byte into single-byte (and vice versa) is concerned, it really depends on the character encodings and whether or not the source character actually exists in the target character set. By way of example, a Japanese multi-byte character is not going to exist in the US7ASCII character set, and you'd end up with a database full of ? signs.

I strongly suggest you read the Oracle Globalization Support Guide, as this goes into the topic of internationalisation in great detail.

Related Solutions

Oracle Import problem caused by different character sets

If that is the actual DDL you are using to create the table, you could use the NLS_LENGTH_SEMANTICS parameter. If you set that to CHAR rather than the default of BYTE, a VARCHAR2(5) will be allocated enough space to store 5 characters in the database character set (potentially up to 20 bytes) rather than 5 bytes (which could allow just 1 character).

Unfortunately, changing the NLS_LENGTH_SEMANTICS probably won't be terribly helpful if you're relying on the import process to create the table-- the dump file will inherently add the CHAR or BYTE keyword so it would actually issue the statement

CREATE TABLE BDATA.Artikel(
    Key                   VARCHAR2(3 BYTE)  NOT NULL,
    Name                  VARCHAR2(60 BYTE) NOT NULL,
    Abkuerzung            VARCHAR2(5 BYTE)  NOT NULL
);

Determine Oracle session client character set

I am a little doubtful that this is exactly what you are looking for, but

host echo %nls_lang%;

ENGLISH_UNITED KINGDOM.WE8ISO8859P1

shows the client nls_lang environment variable on the client.

I don't think there will be a SQL query you can run to give the 'current' setting because AFAIK the server is not aware of what translation is done client-side, so any command to show the current setting will have to be native to the client - I used SQL Developer for the above command, but I assume it will work the same in SQL*Plus

--edit

from AskTom:

only the client knows their character set as well -- it is not available "in the database"

and

the character set describes what is stored in database.

the client makes their desired translated to character know [sic] to the database via the NLS_LANG settting.

If you were on 11.1+, you might have some joy with v$session_connect_info, because:

This information is pushed by OCI to the server ats login time.

But I discovered it would still depend on how you are connecting, eg from the JDBC Thin Driver you aren't using OCI and so the information isn't pushed

Best Answer

Related Solutions

Oracle Import problem caused by different character sets

Determine Oracle session client character set

Related Question