Content of base table SYS.PROPS$

A long long time ago (during my Oracle 7 days), I once needed to update base table SYS.PROPS$. This action was needed to change the database NLS characterset of US7ASCII to a characterset that would support GERMAN. Based on a metalink note, updating the SYS.PROPS$ base table, was the only way to achieve this (or completely rebuild the environment) in those Oracle 7 days.

This procedure was tricky. If you updated it with the wrong, an unsupported character set or with a typo in the string, the database would be corrupted and could not be started up again (so be warned if you want to fiddle around with the method)

Since those days, I always lookup NLS settings via a quick select on that table. The last time I did this, was a long time ago and to my surprise, while looking up settings, I noticed that this table does contain more data then only NLS parameters these days…

Output of a full (demo) clean database Oracle 11g installation gives:

SQL> select * from sys.props$;

NAME                         VALUE$           COMMENT$
---------------------------- ---------------- ----------------------------------
DICT.BASE                    2                dictionary base tables version #
DEFAULT_TEMP_TABLESPACE      TEMP             Name of default temporary tablespa
                                              ce

DEFAULT_PERMANENT_TABLESPACE USERS            Name of default permanent tablespa
                                              ce

DEFAULT_EDITION              ORA$BASE         Name of the database default editi
                                              on

Flashback Timestamp TimeZone GMT              Flashback timestamp created in GMT
TDE_MASTER_KEY_ID
DBTIMEZONE                   +01:00           DB time zone
DEFAULT_TBS_TYPE             SMALLFILE        Default tablespace type
NLS_LANGUAGE                 AMERICAN         Language
NLS_TERRITORY                AMERICA          Territory
NLS_CURRENCY                 $                Local currency
NLS_ISO_CURRENCY             AMERICA          ISO currency
NLS_NUMERIC_CHARACTERS       .,               Numeric characters
NLS_CHARACTERSET             AL32UTF8         Character set
NLS_CALENDAR                 GREGORIAN        Calendar system
NLS_DATE_FORMAT              DD-MON-RR        Date format
NLS_DATE_LANGUAGE            AMERICAN         Date language
NLS_SORT                     BINARY           Linguistic definition
NLS_TIME_FORMAT              HH.MI.SSXFF AM   Time format
NLS_TIMESTAMP_FORMAT         DD-MON-RR HH.MI. Time stamp format
                             SSXFF AM

NLS_TIME_TZ_FORMAT           HH.MI.SSXFF AM T Time with timezone format
                             ZR

NLS_TIMESTAMP_TZ_FORMAT      DD-MON-RR HH.MI. Timestamp with timezone format
                             SSXFF AM TZR

NLS_DUAL_CURRENCY            $                Dual currency symbol
NLS_COMP                     BINARY           NLS comparison
NLS_LENGTH_SEMANTICS         BYTE             NLS length semantics
NLS_NCHAR_CONV_EXCP          FALSE            NLS conversion exception
NLS_NCHAR_CHARACTERSET       AL16UTF16        NCHAR Character set
NLS_RDBMS_VERSION            11.1.0.6.0       RDBMS version for NLS parameters
GLOBAL_DB_NAME               homework.nl      Global database name
EXPORT_VIEWS_VERSION         8                Export views revision #
WORKLOAD_CAPTURE_MODE                         CAPTURE implies workload capture i
                                              s in progress

WORKLOAD_REPLAY_MODE                          PREPARE implies external replay cl
                                              ients can connect; REPLAY implies
                                              workload replay is in progress


32 rows selected.

Output of a not so clean Oracle 10g installation gives, for example, something like this:

SQL> select * from sys.props$;

NAME                         VALUE$           COMMENT$
---------------------------- ---------------- ----------------------------------
DICT.BASE                    2                dictionary base tables version #
DEFAULT_TEMP_TABLESPACE      TEMP             Name of default temporary tablespa
                                              ce

DBTIMEZONE                   +02:00           DB time zone
NLS_LANGUAGE                 AMERICAN         Language
NLS_TERRITORY                AMERICA          Territory
NLS_CURRENCY                 $                Local currency
NLS_ISO_CURRENCY             AMERICA          ISO currency
NLS_NUMERIC_CHARACTERS       .,               Numeric characters
NLS_CHARACTERSET             WE8ISO8859P1     Character set
NLS_CALENDAR                 GREGORIAN        Calendar system
NLS_DATE_FORMAT              DD-MON-RR        Date format
NLS_DATE_LANGUAGE            AMERICAN         Date language
NLS_SORT                     BINARY           Linguistic definition
NLS_TIME_FORMAT              HH.MI.SSXFF AM   Time format
NLS_TIMESTAMP_FORMAT         DD-MON-RR HH.MI. Time stamp format
                             SSXFF AM

NLS_TIME_TZ_FORMAT           HH.MI.SSXFF AM T Time with timezone format
                             ZR

NLS_TIMESTAMP_TZ_FORMAT      DD-MON-RR HH.MI. Timestamp with timezone format
                             SSXFF AM TZR

NLS_DUAL_CURRENCY            $                Dual currency symbol
NLS_COMP                     BINARY           NLS comparison
NLS_LENGTH_SEMANTICS         BYTE             NLS length semantics
NLS_NCHAR_CONV_EXCP          FALSE            NLS conversion exception
NLS_NCHAR_CHARACTERSET       AL16UTF16        NCHAR Character set
NLS_RDBMS_VERSION            9.2.0.7.0        RDBMS version for NLS parameters
GLOBAL_DB_NAME               HOMEWORK.LOCAL   Global database name
EXPORT_VIEWS_VERSION         8                Export views revision #
MAILHOST                     homework.nl      mailhost for email notfns.
MAILPORT                     25               mail port for email notfns.

27 rows selected.

Does anyone have clue? Does anyone have some more information about this?

M.