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.
2 comments
Laurent Schneider
30 November, 2007 at 21:24 (UTC 1) Link to this comment
Well, the view DATABASE_PROPERTIES select from this base table. Default temporary tablespace for example is a 9i new feature. I think this view appeared in 9iR1
Marco Gralike
30 November, 2007 at 23:47 (UTC 1) Link to this comment
Instead of only NLS properties it now also contains some “rest bits” / data, as if they needed a table to put some leftovers in. Also it looks like some values are dynamically filled (default_temp_tablespace without also default user tablespace)?