3 server side levels: DATABASE, INSTANCE, SESSION DATABASE - these are as specified when the database is created - NLS_CHARACTERSET comes from the create database command itself; all other settings are taken as from the 'current session'. View the database settings and importantly database characterset with:
SELECT * FROM NLS_DATABASE_PARAMETERS; eg: PARAMETER VALUE ------------------------------ ------------------------------ NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_DATE_FORMAT DD-MON-YY NLS_DATE_LANGUAGE AMERICAN NLS_CHARACTERSET US7ASCII NLS_SORT BINARY
INSTANCE - As specified in the init.ora file. Note you cannot override the the database characterset. View settings with:
SELECT * FROM NLS_INSTANCE_PARAMETERS; eg: PARAMETER VALUE ------------------------------ ------------------------------ NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_SORT NLS_DATE_LANGUAGE NLS_DATE_FORMAT NLS_CURRENCY NLS_NUMERIC_CHARACTERS NLS_ISO_CURRENCY
This view will only display values for parameters that have been explicitly set in the init.ora file.
SESSION - These are the settings that are in force for the current session. They are derived as follows: a) from the database settings, overridden by b) the instance settings, overridden by c) any alter session type commands. Session parameters are viewed as below. Note again there is no concept of a session level characterset - this belongs only to the database.
SELECT * FROM NLS_SESSION_PARAMETERS;
eg: PARAMETER VALUE ------------------------------ ------------------------------ NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_DATE_FORMAT DD-MON-YY NLS_DATE_LANGUAGE AMERICAN NLS_SORT BINARY