1

In V$LOGMNR_CONTENTS dictionary view the TIMESTAMP and COMMIT_TIMESTAMP columns are of DATE data type - without any timezone information. So which timezone are they in - database timezone, host timezone, or UTC? Is there a database parameter to configure their timezone?

Raihan
  • 10,095
  • 5
  • 27
  • 45

2 Answers2

2

I guess it is the time zone of database server's operating system. Simply because SYSDATE which might be used for insert is also returned in the time zone of database server's operating system.

Perhaps Oracle uses DATE data type instead of TIMESTAMP data type for historical reasons. I don't know when TIMESTAMP was introduced but certainly DATE came earlier.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • After performing some random DMLs, I read them back from V$LOGMNR_CONTENTS. I verified that the value I am getting from the COMMIT_TIMESTAMP column is in fact correct only in the DB server OS timezone! I have different host timezone and DB timezone. This is very counter-intuitive that a timestamp without timezone is stored in the data dictionary with the OS timezone instead of DB timezone. – Raihan Jun 24 '20 at 16:54
  • As written by Roberto `DBTIMEZONE` is **only** used to normalize `TIMESTAMP WITH LOCAL TIME ZONE` data, thus I am not surprised. Data type `TIMESTAMP WITH LOCAL TIME ZONE` for any system data would be quite bad, because it would block modification of `DBTIMEZONE`. – Wernfried Domscheit Jun 24 '20 at 17:04
1

When a SELECT statement is executed against the V$LOGMNR_CONTENTS view, the archive redo log files are read sequentially. These archive redo log files are the ones present into the archive log destination. Translated records from the redo log files are returned as rows in this view. This continues until either the filter criteria specified at startup (EndTime or endScn) are met or the end of the archive log file is reached.

The field TIMESTAMP is the Timestamp when the database change was made. This timestamp corresponds to the SCN transformation SCN_TO_TIMESTAMP, so that for a given SCN you have a correspondent timestamp.

The field COMMIT_TIMESTAMP is the timestamp when the transaction was committed; only meaningful if the COMMITTED_DATA_ONLY option was chosen in a DBMS_LOGMNR.START_LOGMNR() invocation. As you know, querying the redo logs and archive logs require that you invoke this package in a log miner session.

Actually, Oracle uses sometimes DATE data types when it probably should use TIMESTAMP in a lot of different dictionary fields. Why ? I honestly don't know, it is the same when they use for some dictionary views owner, for others table_owner and for others owner_name.

The DBTIMEZONE is specified in the CREATE DATABASE statement, so in the moment you create the database. you can change the DBTIMEZONE by using ALTER DATABASE

alter database set time_zone = 'EST';

Keep in mind that altering the database time zone will only take effect after shutdown/startup, and it is not recommendable.

TIMESTAMP WITH TIME ZONE is a variant of TIMESTAMP that includes a time zone region name or time zone offset in its value. The time zone offset is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time, formerly Greenwich Mean Time).

Oracle Database normalizes all new TIMESTAMP WITH LOCAL TIME ZONE data to the time zone of the database when the data is stored on disk.Oracle Database does not automatically update existing data in the database to the new time zone. Therefore, you cannot reset the database time zone if there is any TIMESTAMP WITH LOCAL TIME ZONE data in the database. You must first delete or export the TIMESTAMP WITH LOCAL TIME ZONE data and then reset the database time zone. For this reason, Oracle does not encourage you to change the time zone of a database that contains data.

An example of my case: I have an Oracle Database in Azure ( where all the servers are using UTC ) In my case I chose to use UTC instead of using a different DBTIMEZONE. Then I created a function to transform any timestamp stored in any table to my time zone.

I wonder why you need to read the redo/archive logs, do you have to recover some lost transactions ? I hope the explanation is satisfactory, please don't hesitate to comment or ask whatever other doubts you may have.

Roberto Hernandez
  • 8,231
  • 3
  • 14
  • 43
  • 1
    Hi Roberto, thank you for the elaborate description. But it does not answer my question. The DATE data that we can read from the COMMIT_TIMESTAMP column of V$LOGMNR_CONTENTS view, what timezone is it in? If anyone wants to do something with this timestamp, they have to know the timezone of it. Did you mean it's in the timezone of the DBTIMEZONE of the database? Well, it's not. I have performed DML operations in the database and read their redo logs. I have done it while changing the DBTIMEZONE a few times. The results don't change and none of them are right. – Raihan Jun 23 '20 at 00:19
  • Hi @Raihan The COMMIT_TIMESTAMP is in the timezone where the database was originally at the moment that transaction was commited. If the filed is TIMESTAMP, you don't actually need to know which timezone is, unless the fileld was configured as TIMIESTAMP WITH LOCAL TIME ZONE. Can you elaborate a bit which DMLs did you run ? thank you – Roberto Hernandez Jun 23 '20 at 06:08
  • Hello @Roberto ... As I mentioned before the name of the columns is COMMIT_TIMESTAMP but the type of the column is `DATE`. DATE has no timezone information, so I definitely need it. What DMLs I ran do not matter. Because I am reading the data dictionary view V$LOGMNR_CONTENTS, which has the COMMIT_TIMESTAMP column. Can you explain what it means by "in the timezone where the database was originally at the moment"? How do you find it from the database? Is it in an initialization parameter, a dictionary view, an env variable, or somewhere else? – Raihan Jun 23 '20 at 19:14
  • Hello @Raihan. I did not explain myself before in the right way. Sorry for that. Let me try to clarify a bit the topic. You won't be able to get the TIMEZONE from the V$LOGMNR_CONTENTS, as you well said. What I meant before is that the DATE stored in that field COMMIT_TIMESTAMP represents a TIMESTAMP in the format of a date field, but with no TIME ZONE associated. If you did not purge your alert log, any change of DBTIMEZONE is stored there. I did not find any dictionary view containing timezone changes at database level. – Roberto Hernandez Jun 24 '20 at 05:47
  • Continuation .... However, if you had already a field with TIMESTAMP WITH LOCAL TIME ZONE in a table created before the DATE stored in V$LOGMNR_CONTENTS, you couldn't have changed the DBTIMEZONE, because Oracle prevents changes on this parameter when a field is stored with this data type. – Roberto Hernandez Jun 24 '20 at 05:55
  • @Raihan, as I explained also in my original answer, Oracle stores some fields with names containing the word TIMESTAMP when the data type is actually a DATE. Why they do that ? I honestly don't know, but it is a good point from your side. The only way to get the TIMEZONE in your case is by indirect means, as far as I know – Roberto Hernandez Jun 24 '20 at 05:57