During this article we will discuss with you about a way to find the list of Character set of the Oracle database.
Notes :
Review characterset options before you begin installation.
After a database creation is done then, changing its Character set is typically very expensive in terms of your time and resources. Such type of tasks may required changing over all character information by export and import the full database and bring it back. Therefore, it’s important that you simply carefully select the database Character set at installation time.
Started from Oracle Database 12cR2, the default character set of a database is Unicode AL32UTF8 when database created from the General Purpose/Transaction Processing or the Data Warehousing template. Oracle recommends, that you just simply use the Unicode AL32UTF8 at the installation time for the database Character set.
Note, The Character set information of a database is stored within the data dictionary tables named SYS.PROPS$.
Firstly, By this query you can find all nls database parameters value :
SQL>
SQL> set lines 400 pages 120
SQL> col PARAMETER for a40
SQL> col VALUE for a40
SQL> select * from nls_database_parameters;
PARAMETER VALUE
---------------------------------------- ----------------------------------------
NLS_RDBMS_VERSION 19.0.0.0.0
NLS_NCHAR_CONV_EXCP FALSE
NLS_LENGTH_SEMANTICS BYTE
NLS_COMP BINARY
NLS_DUAL_CURRENCY $
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_SORT BINARY
NLS_DATE_LANGUAGE AMERICAN
NLS_DATE_FORMAT DD-MON-RR
NLS_CALENDAR GREGORIAN
NLS_NUMERIC_CHARACTERS .,
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_CHARACTERSET AL32UTF8
NLS_ISO_CURRENCY AMERICA
NLS_CURRENCY $
NLS_TERRITORY AMERICA
NLS_LANGUAGE AMERICAN
20 rows selected.
SQL>
By this query you can find out one or more specified nls database parameter details:
SQL>
SQL> select * from nls_database_parameters where parameter='NLS_CHARACTERSET';
PARAMETER VALUE
---------------------------------------- ----------------------------------------
NLS_CHARACTERSET AL32UTF8
SQL>
Show parameter nls Details :
SQL>
SQL> show parameter nls
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_calendar string
nls_comp string BINARY
nls_currency string
nls_date_format string
nls_date_language string
nls_dual_currency string
nls_iso_currency string
nls_language string AMERICAN
nls_length_semantics string BYTE
nls_nchar_conv_excp string FALSE
nls_numeric_characters string
nls_sort string
nls_territory string AMERICA
nls_time_format string
nls_time_tz_format string
nls_timestamp_format string
nls_timestamp_tz_format string
SQL>
Lastly, By this query you can also find out one or more specified nls database parameter details:
SQL>
SQL> set lines 400 pages 120
SQL> col PROPERTY_NAME for a30
SQL> col PROPERTY_VALUE for a30
SQL> col DESCRIPTION for a30
SQL> select * from database_properties where PROPERTY_NAME in ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ------------------------------ ------------------------------
NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set
NLS_CHARACTERSET AL32UTF8 Character set
SQL>
I hope my written content will helped you. Your suggestions/feedback are most welcome from orakldba.com.
Keep learning… Have a great day!!!
Please comment here for your any query related to above content. You could write to mail me through : orakldba@gmail.com.