Configuration for Oracle connection

To configure freshness rule for Oracle, it is required to enable the Flashback feature in the database

Permissions required to configure Freshness for Oracle connection.

Steps to configure Flashback feature

1. Verify if Flashback is enabled

   Query to be executed:

   SELECT flashback_on FROM v$database;

   Output YES - Flashback is enabled NO- Flashback is not enabled

   To check Log Mode

   Query to be executed:

   SELECT LOG_MODE FROM V$DATABASE;

   Output NOARCHIVELOG - Flashback is not enabled. ARCHIVELOG - Enabled

2. If the Flashback feature is not enabled, then you can enable it by following the steps below.

   1. Create a Flashback Data Archive

      Query to be executed:

      create flashback archive fda1 tablespace users retention 1 month;

      Tip: The retention period can be modified depending on the business requirement.

      Output Flashback archive created

   2. Enable Flashback Data Archive on Tables

      Query to be executed:

      Enable Flashback for table ALTER TABLE <tableName> FLASHBACK ARCHIVE <flashback_Archive_Name>; alter table ATM.TEST flashback archive fda1;

      Output Flashback archive altered

   3. Verify if flashback is enabled

      Query to be executed:

      SELECT * FROM dba_flashback_archive_tables WHERE table_name = 'TEST_MOD';

      Output returned if flashback is enabled TABLE_NAME|OWNER_NAME|FLASHBACK_ARCHIVE_NAME|ARCHIVE_TABLE_NAME |STATUS | ----------+----------+----------------------+-------------------+-------+ TEST_MOD |ATM |FLASHBACKDATAARCHIVE |SYS_FBA_HIST_136146|ENABLED|

   If no result is returned, it indicates that flashback is not enabled.

3. Fetch historical data

   If the flashback is enabled, you can execute the following query to capture the freshness metric.

   Query to be executed:

   SELECT CASE WHEN NOT EXISTS (SELECT 1 FROM dba_flashback_archive_tables WHERE table_name = '<table_name>') THEN NULL WHEN (SELECT MAX(versions_startscn) FROM "<schema_name>"."<table_name>" VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE) IS NULL THEN NULL ELSE TO_CHAR(FROM_TZ(CAST(SCN_TO_TIMESTAMP((SELECT MAX(versions_startscn) FROM "<schema_name>"."<table_name>" VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE)) AS TIMESTAMP), SESSIONTIMEZONE) AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.FF3TZH:TZM') END AS PRECISELY_VALUE, CASE WHEN NOT EXISTS (SELECT 1 FROM all_tables WHERE table_name = '<table_name>' AND owner = '<schema_name>') THEN 'TABLE NOT FOUND' WHEN (SELECT COUNT(*) FROM dba_flashback_archive_tables WHERE table_name = <table_name>) = 0 THEN 'FLASHBACK FEATURE NOT ENABLED' WHEN (SELECT MAX(versions_startscn) FROM '<schema_name>'.'<table_name>' VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE) IS NULL THEN 'NO VERSIONED SCN DATA FOUND' ELSE NULL END AS PRECISELY_COMMENT FROM dual

   Output PRECISELY_VALUE |PRECISELY_COMMENT| -------------------+-----------------+ 2025-06-16 23:06:00| |