RE: [SPAM] Oracle and DST changes
- From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
- To: <Mark.Bobak@xxxxxxxxxxxxxxx>, <John.Fedock@xxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 22 Jan 2007 16:02:00 -0700
I was curious too, so I just went looking for them and it turns out
Oracle's not just pulling our leg - TSTZ is actually used pretty
extensively - 149 columns in 10.2.0.2:
SQL> select owner, table_name, column_name, data_type from
dba_tab_columns where data_type like '%ZONE%' order by 1,2,3
OWNER TABLE_NAME COLUMN_NAME
DATA_TYPE
----- ------------------------------ ------------------------------
------------------------------
SYS ALL_FILE_GROUPS CREATED
TIMESTAMP(6) WITH TIME ZONE
SYS ALL_FILE_GROUP_VERSIONS CREATED
TIMESTAMP(6) WITH TIME ZONE
SYS ALL_SCHEDULER_JOBS END_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS ALL_SCHEDULER_JOBS LAST_START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS ALL_SCHEDULER_JOBS NEXT_RUN_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS ALL_SCHEDULER_JOBS START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS ALL_SCHEDULER_JOB_LOG LOG_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS ALL_SCHEDULER_JOB_RUN_DETAILS ACTUAL_START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS ALL_SCHEDULER_JOB_RUN_DETAILS LOG_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS ALL_SCHEDULER_JOB_RUN_DETAILS REQ_START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS ALL_SCHEDULER_RUNNING_CHAINS END_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS ALL_SCHEDULER_RUNNING_CHAINS START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS ALL_SCHEDULER_SCHEDULES END_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS ALL_SCHEDULER_SCHEDULES START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS ALL_SCHEDULER_WINDOWS END_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS ALL_SCHEDULER_WINDOWS LAST_START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS ALL_SCHEDULER_WINDOWS MANUAL_OPEN_TIME
TIMESTAMP(6) WITH TIME ZONE
SYS ALL_SCHEDULER_WINDOWS NEXT_START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS ALL_SCHEDULER_WINDOWS START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS ALL_SCHEDULER_WINDOW_DETAILS ACTUAL_START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS ALL_SCHEDULER_WINDOW_DETAILS LOG_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS ALL_SCHEDULER_WINDOW_DETAILS REQ_START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS ALL_SCHEDULER_WINDOW_LOG LOG_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS ALL_TAB_STATS_HISTORY STATS_UPDATE_TIME
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_ALERT_HISTORY CREATION_TIME
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_ALERT_HISTORY TIME_SUGGESTED
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_AUDIT_EXISTS EXTENDED_TIMESTAMP
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_AUDIT_OBJECT EXTENDED_TIMESTAMP
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_AUDIT_SESSION EXTENDED_TIMESTAMP
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_AUDIT_STATEMENT EXTENDED_TIMESTAMP
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_AUDIT_TRAIL EXTENDED_TIMESTAMP
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_COMMON_AUDIT_TRAIL EXTENDED_TIMESTAMP
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_FGA_AUDIT_TRAIL EXTENDED_TIMESTAMP
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_FILE_GROUPS CREATED
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_FILE_GROUP_VERSIONS CREATED
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_OPTSTAT_OPERATIONS END_TIME
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_OPTSTAT_OPERATIONS START_TIME
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_OUTSTANDING_ALERTS CREATION_TIME
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_OUTSTANDING_ALERTS TIME_SUGGESTED
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_RESOURCE_INCARNATIONS STARTUP_TIME
TIMESTAMP(9) WITH TIME ZONE
SYS DBA_SCHEDULER_JOBS END_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_SCHEDULER_JOBS LAST_START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_SCHEDULER_JOBS NEXT_RUN_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_SCHEDULER_JOBS START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_SCHEDULER_JOB_LOG LOG_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_SCHEDULER_JOB_RUN_DETAILS ACTUAL_START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_SCHEDULER_JOB_RUN_DETAILS LOG_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_SCHEDULER_JOB_RUN_DETAILS REQ_START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_SCHEDULER_RUNNING_CHAINS END_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_SCHEDULER_RUNNING_CHAINS START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_SCHEDULER_SCHEDULES END_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_SCHEDULER_SCHEDULES START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_SCHEDULER_WINDOWS END_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_SCHEDULER_WINDOWS LAST_START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_SCHEDULER_WINDOWS MANUAL_OPEN_TIME
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_SCHEDULER_WINDOWS NEXT_START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_SCHEDULER_WINDOWS START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_SCHEDULER_WINDOW_DETAILS ACTUAL_START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_SCHEDULER_WINDOW_DETAILS LOG_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_SCHEDULER_WINDOW_DETAILS REQ_START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_SCHEDULER_WINDOW_LOG LOG_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_TAB_STATS_HISTORY STATS_UPDATE_TIME
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_TSM_DESTINATION DESTINATION_END_TIME
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_TSM_DESTINATION DESTINATION_INST_START_TIME
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_TSM_DESTINATION DESTINATION_START_TIME
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_TSM_HISTORY DESTINATION_START_TIME
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_TSM_HISTORY END_TIME
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_TSM_HISTORY SOURCE_START_TIME
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_TSM_HISTORY START_TIME
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_TSM_SOURCE SOURCE_END_TIME
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_TSM_SOURCE SOURCE_INSTANCE_START_TIME
TIMESTAMP(6) WITH TIME ZONE
SYS DBA_TSM_SOURCE SOURCE_START_TIME
TIMESTAMP(6) WITH TIME ZONE
SYS FGR$_FILE_GROUPS CREATION_TIME
TIMESTAMP(6) WITH TIME ZONE
SYS FGR$_FILE_GROUP_FILES CREATION_TIME
TIMESTAMP(6) WITH TIME ZONE
SYS FGR$_FILE_GROUP_VERSIONS CREATION_TIME
TIMESTAMP(6) WITH TIME ZONE
SYS GV_$XML_AUDIT_TRAIL EXTENDED_TIMESTAMP
TIMESTAMP(6) WITH TIME ZONE
SYS OPTSTAT_HIST_CONTROL$ SPARE6
TIMESTAMP(6) WITH TIME ZONE
SYS OPTSTAT_HIST_CONTROL$ SVAL2
TIMESTAMP(6) WITH TIME ZONE
SYS SCHEDULER$_EVENT_LOG LOG_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS SCHEDULER$_GLOBAL_ATTRIBUTE ATTR_TSTAMP
TIMESTAMP(6) WITH TIME ZONE
SYS SCHEDULER$_JOB END_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS SCHEDULER$_JOB LAST_ENABLED_TIME
TIMESTAMP(6) WITH TIME ZONE
SYS SCHEDULER$_JOB LAST_END_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS SCHEDULER$_JOB LAST_START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS SCHEDULER$_JOB NEXT_RUN_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS SCHEDULER$_JOB START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS SCHEDULER$_JOB_RUN_DETAILS LOG_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS SCHEDULER$_SCHEDULE END_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS SCHEDULER$_SCHEDULE REFERENCE_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS SCHEDULER$_STEP_STATE END_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS SCHEDULER$_STEP_STATE START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS SCHEDULER$_WINDOW ACTUAL_START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS SCHEDULER$_WINDOW END_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS SCHEDULER$_WINDOW LAST_START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS SCHEDULER$_WINDOW MANUAL_OPEN_TIME
TIMESTAMP(6) WITH TIME ZONE
SYS SCHEDULER$_WINDOW NEXT_START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS SCHEDULER$_WINDOW START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS SCHEDULER$_WINDOW_DETAILS LOG_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS SCHEDULER$_WINDOW_DETAILS REQ_START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS SCHEDULER$_WINDOW_DETAILS START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS TSM_DST$ DST_END_TIME
TIMESTAMP(6) WITH TIME ZONE
SYS TSM_DST$ DST_INST_START_TIME
TIMESTAMP(6) WITH TIME ZONE
SYS TSM_DST$ DST_START_TIME
TIMESTAMP(6) WITH TIME ZONE
SYS TSM_SRC$ SRC_END_TIME
TIMESTAMP(6) WITH TIME ZONE
SYS TSM_SRC$ SRC_INST_START_TIME
TIMESTAMP(6) WITH TIME ZONE
SYS TSM_SRC$ SRC_START_TIME
TIMESTAMP(6) WITH TIME ZONE
SYS USER_AUDIT_OBJECT EXTENDED_TIMESTAMP
TIMESTAMP(6) WITH TIME ZONE
SYS USER_AUDIT_SESSION EXTENDED_TIMESTAMP
TIMESTAMP(6) WITH TIME ZONE
SYS USER_AUDIT_STATEMENT EXTENDED_TIMESTAMP
TIMESTAMP(6) WITH TIME ZONE
SYS USER_AUDIT_TRAIL EXTENDED_TIMESTAMP
TIMESTAMP(6) WITH TIME ZONE
SYS USER_FILE_GROUPS CREATED
TIMESTAMP(6) WITH TIME ZONE
SYS USER_FILE_GROUP_VERSIONS CREATED
TIMESTAMP(6) WITH TIME ZONE
SYS USER_SCHEDULER_JOBS END_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS USER_SCHEDULER_JOBS LAST_START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS USER_SCHEDULER_JOBS NEXT_RUN_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS USER_SCHEDULER_JOBS START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS USER_SCHEDULER_JOB_LOG LOG_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS USER_SCHEDULER_JOB_RUN_DETAILS ACTUAL_START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS USER_SCHEDULER_JOB_RUN_DETAILS LOG_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS USER_SCHEDULER_JOB_RUN_DETAILS REQ_START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS USER_SCHEDULER_RUNNING_CHAINS END_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS USER_SCHEDULER_RUNNING_CHAINS START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS USER_SCHEDULER_SCHEDULES END_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS USER_SCHEDULER_SCHEDULES START_DATE
TIMESTAMP(6) WITH TIME ZONE
SYS USER_TAB_STATS_HISTORY STATS_UPDATE_TIME
TIMESTAMP(6) WITH TIME ZONE
SYS V_$XML_AUDIT_TRAIL EXTENDED_TIMESTAMP
TIMESTAMP(6) WITH TIME ZONE
SYS WRI$_ALERT_HISTORY CREATION_TIME
TIMESTAMP(6) WITH TIME ZONE
SYS WRI$_ALERT_HISTORY TIME_SUGGESTED
TIMESTAMP(6) WITH TIME ZONE
SYS WRI$_ALERT_OUTSTANDING CREATION_TIME
TIMESTAMP(6) WITH TIME ZONE
SYS WRI$_ALERT_OUTSTANDING TIME_SUGGESTED
TIMESTAMP(6) WITH TIME ZONE
SYS WRI$_OPTSTAT_AUX_HISTORY SAVTIME
TIMESTAMP(6) WITH TIME ZONE
SYS WRI$_OPTSTAT_AUX_HISTORY SPARE6
TIMESTAMP(6) WITH TIME ZONE
SYS WRI$_OPTSTAT_HISTGRM_HISTORY SAVTIME
TIMESTAMP(6) WITH TIME ZONE
SYS WRI$_OPTSTAT_HISTGRM_HISTORY SPARE6
TIMESTAMP(6) WITH TIME ZONE
SYS WRI$_OPTSTAT_HISTHEAD_HISTORY SAVTIME
TIMESTAMP(6) WITH TIME ZONE
SYS WRI$_OPTSTAT_HISTHEAD_HISTORY SPARE6
TIMESTAMP(6) WITH TIME ZONE
SYS WRI$_OPTSTAT_IND_HISTORY SAVTIME
TIMESTAMP(6) WITH TIME ZONE
SYS WRI$_OPTSTAT_IND_HISTORY SPARE6
TIMESTAMP(6) WITH TIME ZONE
SYS WRI$_OPTSTAT_OPR END_TIME
TIMESTAMP(6) WITH TIME ZONE
SYS WRI$_OPTSTAT_OPR SPARE6
TIMESTAMP(6) WITH TIME ZONE
SYS WRI$_OPTSTAT_OPR START_TIME
TIMESTAMP(6) WITH TIME ZONE
SYS WRI$_OPTSTAT_TAB_HISTORY SAVTIME
TIMESTAMP(6) WITH TIME ZONE
SYS WRI$_OPTSTAT_TAB_HISTORY SPARE6
TIMESTAMP(6) WITH TIME ZONE
SYS _ALL_FILE_GROUPS CREATED
TIMESTAMP(6) WITH TIME ZONE
SYS _ALL_FILE_GROUP_FILES CREATED
TIMESTAMP(6) WITH TIME ZONE
SYS _ALL_FILE_GROUP_VERSIONS CREATED
TIMESTAMP(6) WITH TIME ZONE
SYS _USER_FILE_GROUPS CREATED
TIMESTAMP(6) WITH TIME ZONE
149 rows selected.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.2.0 - Productio
NLSRTL Version 10.2.0.2.0 - Production
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Bobak, Mark
Note that 10g uses timezone aware datatypes in the data dictionary in
10g (or so Oracle claims, I haven't gone looking for them)
Privileged/Confidential Information may be contained in this message or
attachments hereto. Please advise immediately if you or your employer do not
consent to Internet email for messages of this kind. Opinions, conclusions and
other information in this message that do not relate to the official business
of this company shall be understood as neither given nor endorsed by it.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
