Query to check characterset in Oracle

Below are the queries to check characterset and NLS parameters in database.

SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;
SELECT * FROM NLS_DATABASE_PARAMETERS;

Sample output:

SQL> SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;

VALUE$
-----------------------
AL32UTF8

SQL> set linesize 200
SQL> SELECT * FROM NLS_DATABASE_PARAMETERS;

PARAMETER                                               VALUE
-----------------------------------------           -------------------------------------------------------------------------
NLS_CSMIG_SCHEMA_VERSION                  5
NLS_NCHAR_CHARACTERSET                UTF8
NLS_LANGUAGE                                         AMERICAN
NLS_TERRITORY                                         AMERICA
NLS_CURRENCY                                          $
NLS_ISO_CURRENCY                                AMERICA
NLS_NUMERIC_CHARACTERS               .,
NLS_CHARACTERSET                              AL32UTF8
NLS_CALENDAR                                        GREGORIAN
NLS_DATE_FORMAT                                 DD-MON-RR
NLS_DATE_LANGUAGE                          AMERICAN
NLS_SORT                                                   BINARY
NLS_TIME_FORMAT                                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT                  DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT                        HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT           DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY                              $
NLS_COMP                                                BINARY
NLS_LENGTH_SEMANTICS                  BYTE
NLS_NCHAR_CONV_EXCP                   FALSE
NLS_RDBMS_VERSION                         11.2.0.3.0

21 rows selected.

SQL>

Thats it.. Hope this will help you.. :)

Thanks,
Chowdari.

Query to check locally managed tablespaces

Below query to identify tablespaces are in locally or dictionary managed tablespaces:

SELECT tablespace_name,extent_management FROM dba_tablespaces;

Sample Output:

SQL> SELECT tablespace_name,extent_management FROM dba_tablespaces;

TABLESPACE_NAME                EXTENT_MAN
------------------------------ ----------
SYSTEM                         LOCAL
SYSAUX                         LOCAL
TEMP                              LOCAL
.............................
.............................
............................
XDB                                LOCAL
APPS_UNDOTS1           LOCAL

15 rows selected.

SQL>

Its possible all tablespaces may be locally managed and below query to find tablespaces are in locally managed.

SELECT tablespace_name,extent_management FROM dba_tablespaces where extent_management='LOCAL';

Same way below query to find dictionary managed tablespaces:

SELECT tablespace_name,extent_management FROM dba_tablespaces where extent_management='DICTIONARY';

Thats it.. Hopw this will help you.. :)

Regards,
Chowdari