Let us today go through the details about the Database properties time zones as they act (or) take part important role in the life of Oracle Database. Let’s see what the information we get from the above concepts and what best we can make use of information.
During the installation of Oracle usually we specify the default tablespace type, so Oracle Database assigns to this tablespace any non-system users for whom you do not explicitly specify a different permanent tablespace. As I have saw some queries in Oracle forums with respective default database properties and from where to get.
we can get the information from the DATABASE_PROPERTIES View.
1: SQL> desc DATABASE_PROPERTIES
2: Name Null? Type
3: ----------------------------------------- -------- ----------------------------
4: PROPERTY_NAME NOT NULL VARCHAR2(30)
5: PROPERTY_VALUE VARCHAR2(4000) 6: DESCRIPTION VARCHAR2(4000) 7: 8: SQL> SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES
9: 2 WHERE PROPERTY_NAME = 'DEFAULT_TBS_TYPE';
10: 11: PROPERTY_VALUE 12: -------------------------------------------------------------------------------- 13: SMALLFILEYes, if you check above sample information where we are fetching details from the view, but not the from Table Segment. Let’s see actually from which based table the query is fetching the details across.
1: SQL> select text from dba_views where view_name='DATABASE_PROPERTIES';
2: 3: TEXT4: --------------------------------------------------------------------------------
5: select name, value$, comment$
6: from props$
So, its from props$ is the table or view from where the actual details are query across, in order to display the database default parameters. Is props$ is table or view ?
1: 2: SQL> select owner,namespace,object_type,status from dba_objects where object_name='PROPS$';
3: 4: OWNER NAMESPACE OBJECT_TYPE STATUS5: ------------------------------ ---------- ------------------- -------
6: SYS 1 TABLE VALID
So, its Table. !! let us see the default database properties of the Oracle 11g Release 2 Database.
1: SQL> select * from props$;
2: 3: NAME VALUE$ COMMENT$
4: --------------------------------------------------------------------------------
5: DICT.BASE 2 dictionary base tables version #
6: DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace
7: DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace
8: DEFAULT_EDITION ORA$BASE Name of the database default edition
9: Flashback Timestamp TimeZone GMT Flashback timestamp created in GMT
10: TDE_MASTER_KEY_ID 11: DST_UPGRADE_STATE NONE State of Day Light Saving Time Upgrade
12: DST_PRIMARY_TT_VERSION 11 Version of primary timezone data file
13: DST_SECONDARY_TT_VERSION 0 Version of secondary timezone data file
14: DEFAULT_TBS_TYPE SMALLFILE Default tablespace type
15: NLS_LANGUAGE AMERICAN Language
16: NLS_TERRITORY AMERICA Territory 17: NLS_CURRENCY $ Local currency
18: NLS_ISO_CURRENCY AMERICA ISO currency 19: NLS_NUMERIC_CHARACTERS ., Numeric characters
20: NLS_CHARACTERSET AL32UTF8 Character set
21: NLS_CALENDAR GREGORIAN Calendar system 22: NLS_DATE_FORMAT DD-MON-RR Date format
23: NLS_DATE_LANGUAGE AMERICAN Date language
24: NLS_SORT BINARY Linguistic definition
25: NLS_TIME_FORMAT HH.MI.SSXFF AM Time format
26: NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM Time stamp format
27: NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR Time with timezone format
28: NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR Timestamp with timezone format
29: NLS_DUAL_CURRENCY $ Dual currency symbol 30: NLS_COMP BINARY NLS comparison
31: NLS_LENGTH_SEMANTICS BYTE NLS length semantics 32: NLS_NCHAR_CONV_EXCP FALSE NLS conversion exception
33: NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set
34: NLS_RDBMS_VERSION 11.2.0.1.0 RDBMS version for NLS parameters
35: GLOBAL_DB_NAME ORCL Global database name
36: EXPORT_VIEWS_VERSION 8 Export views revision # 37: WORKLOAD_CAPTURE_MODE CAPTURE implies workload capture is in progress
38: WORKLOAD_REPLAY_MODE PREPARE implies external replay clients can connect; REPLAY implies workload replay is in progress
39: NO_USERID_VERIFIER_SALT 57505D68AFECC3BCECE484A1C42CC8CE 40: DBTIMEZONE 00:00 DB time zone
41: 42: 43: 36 rows selected.
44: 45: SQL> spool off;
So, Default installation of Oracle comes with 36 – default parameters or values. Kindly look at the parameter of “NO_USERID_VERIFIER_SALT”, what is this parameter and what it signifies. Let’s see in upcoming future post above this parameter and what it’s significance.
Time zones
The default time zone file is ORACLE_HOME/oracore/zoneinfo/timezlrg_11.dat. A smaller time zone file with fewer time zones can be found in ORACLE_HOME/oracore/zoneinfo/timezone_11.dat.
Time zones details are available from V$TIMEZONE_NAMES.
The following query is the one which is executed in order to fetch actual details of time zones from Database.
select TZNAME, TZABBREV from X$TIMEZONE_NAMES.
Now, we check the file “D:\app\Pavan\product\11.2.0\dbhome_1\oracore\zoneinfo\timezdif.csv” – based on your ORACLE_HOME, you find the details about the time zones.
Sample data :-
America/North_Dakota/Center
America/North_Dakota/New_Salem
America/Kentucky/Monticello
America/Moncton
America/Argentina/San_Luis
Note: Don’t make any change to the current time zone file.
0 comments: on "Oracle Database Properties and Time Zones…"
Post a Comment