In Autonomous, DBA, OCI, Oracle database on October 13, 2022 at 08:36

In a blog post in 2020, entitled SYSDATE and Time Zones in the Autonomous Database, I covered the sysdate/systimestamp issue in ADB-S. Basically, you are allowed to change the database and session timezones in ADB, but this doesn’t change the SYSDATE and SYSTIMESTAMP in the timezones. So, the PL/SQL packages, procedure and functions and in particular all SQL using SYSDATE and SYSTIMESTAMP might not return what you expect.

But now, there is a parameter called SYSDATE_AT_DBTIMEZONE available now on system level. Depending on the value of SYSDATE_AT_DBTIMEZONE, you see either the date and time based on the default Autonomous Database time zone, Coordinated Universal Time ‎(UTC)‎, or based on the time zone that you set in your database.

Here is how it works. Let us first check the database timezone:

The value of SYSDATE_AT_DBTIMEZONE is the default, FALSE:

With the default value of FALSE, I see GMT time:

If I change from FALSE to TRUE, then I see database TZ time:

If you decide to change the TZ, then you must restart the Autonomous Database instance for the change to take effect.

So, when SYSDATE_AT_DBTIMEZONE is FALSE in a session, calls to SYSDATE and SYSTIMESTAMP return values based on the default Autonomous Database time zone, Coordinated Universal Time ‎(UTC)‎. When SYSDATE_AT_DBTIMEZONE is TRUE in a session, calls to SYSDATE or SYSTIMESTAMP return the date and time based on the database time zone.

In case you need your application to show the database timezone (or a certain TZ) when calling SYSDATE or SYSTIMESTAMP, then change this new parameter to TRUE, set the correct TZ, if needed, and restart!

There is also a new view in ADB-S called DBA_OPERATOR_ACCESS. This view stores information on the actions that OCI cloud operations performs on your Autonomous Database. This view will not show results if Oracle Cloud Infrastructure cloud operations hasn’t performed any actions or run any statements in your Autonomous Database instance.

The DBA_OPERATOR_ACCESS view provides information starting on October 4, 2022, the date this feature was introduced. You cannot see anything done before October 4, 2022.

The view is based on the PDB_SYNC$ table:

The view contains the following 4 columns:

1. SQL_TEXT: SQL text of the statement executed by the operator

2. EVENT_TIMESTAMP: Timestamp of the operator action in UTC

3. REQUEST_ID: Request number related to the reason behind the operator action. This could be a bug number, an SR number, or a change ticket request number that provides information on the reason for the action

4. REASON: Reason for the operator action. This provides context for the reason behind the action and may have a value such as: MITIGATION, DIAGNOSTIC COLLECTION, or CUSTOMER REQUEST

So, the DBA_OPERATOR_ACCESS view provides good and useful information on the top level SQL statements that OCI cloud operations performs.

  1. SYSDATE_AT_DBTIMEZONE on session level is with ATP since September 2021. This week’s news is SYSDATE_AT_DBTIMEZONE on system level!
    alter system set SYSDATE_AT_DBTIMEZONE=true; and be happier. 🙂

  2. As for the databases I can recommend tools to connect db, for example oracle odbc driver, bigcommerce odbc driver, SQL connector etc. It depends on databases.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: