View the Warehouse Refresh Statistics

Using an additional mechanism, you can view the details of data refresh in the warehouse and the tables that got changed.

The following three drill down tables contain information that you can use for data refresh details. To view these tables, you can use tools such as SQL Developer and connect to the Autonomous AI Lakehouse associated with your Fusion Data Intelligence instance as OAX_USER. These tables will be populated for your instance after the feature is available. The tables retain values for about 6 months and then archived.

This capability is available in a phased rollout.

Table	What does it contain?	When is it updated?
FDI_REFRESH_SUMMARY	Summary of each data refresh including scheduled time, completion time and published record count. One row for each refresh.	Updated when the refresh goes In-progress and when the refresh completes (includes post-processing).
FDI_REFRESH_STAGES	Contains completion time for refresh stages. One row for each stage.	Updated at completion of each stage for Priority, Primary, Secondary, as applicable.
FDI_REFRESH_FACTORS	Factors affecting each refresh. One row for each factor, as applicable.	Updated if there are any factors that affected the refresh. Supported factors are source is in maintenance and customer High service ADW sessions.

Table Schema Details

Following are the schema details of these tables:

FDI_REFRESH_SUMMARY

Column	Data Type	Values
REFRESH_ID	VARCHAR2	Unique ID to join with other tables
SOURCE_TYPE_CODE	VARCHAR2	All supported sources such as `FUSION`, `NETSUITE`, `BIP`, `EBS`, and `SALESFORCE`.
REFRESH_TYPE	VARCHAR2	ACTIVATION_SCHEDULED, see Activate a Data Pipeline for a Functional Area. ACTIVATION_RUN_NOW, see Activate a Data Pipeline for a Functional Area. ONDEMAND_REFRESH, see Refresh a Data Pipeline for a Functional Area. SCHEDULED_REFRESH, see Schedule Incremental Data Refreshes. APPLICATION_UPGRADE, see Manage Application Updates. RELOAD_SOURCE, see Reset and Reload the Data Source. DATA_MAINTENANCE, see Schedule Data Maintenance. SCHEDULED_REFRESH_PRIORITY, see Schedule Incremental Data Refreshes. FREQUENT_DATA_REFRESH_V1, see Schedule Frequent Refreshes of Data with Original Refresh Mechanism. DEPLOY_CUSTOM_DATA_CONFIG, data Refresh when custom data configuration is deployed. For example, when you activate the descriptive flex fields, see Activate the Descriptive Flex Fields.
NUM_STAGES	NUMBER	1 to 4 (Stages refer to the number of priority refresh requests and the post publish stage. For details of different stages, see Schedule Incremental Data Refreshes). The following examples show the value for NUM_STAGES and the names of those stages populated in the FDI_REFRESH_STAGES table: Job without priority: NUM_STAGES = 2 DATA_PUBLISHED POST_PROCESSING Incremental with priority: NUM_STAGES = 3 PRIO_DATA_PUB_ORD_0 DATA_PUBLISHED POST_PROCESSING Multistage incremental without selecting priority, secondary (index file has 1): NUM_STAGES = 2 DATA_PUBLISHED POST_PROCESSING Multistage incremental without selecting priority dataset (index file has 1,2): NUM_STAGES = 3 PRIO_DATA_PUB_ORD_1 DATA_PUBLISHED POST_PROCESSING Multistage incremental without primary (index file has 0,2): NUM_STAGES = 3 PRIO_DATA_PUB_ORD_0 DATA_PUBLISHED POST_PROCESSING Multistage incremental without primary (index file has 0,1): NUM_STAGES = 3 PRIO_DATA_PUB_ORD_0 DATA_PUBLISHED POST_PROCESSING Multistage incremental(index file has 0,1,2): NUM_STAGES = 4 PRIO_DATA_PUB_ORD_0 PRIO_DATA_PUB_ORD_1 DATA_PUBLISHED POST_PROCESSING
STATUS	VARCHAR2	IN PROGRESS, COMPLETED
SCHEDULED_TIME	TIMESTAMP	-
COMPLETION_TIME	TIMESTAMP	-
PUBLISHED_REC_COUNT	NUMBER	-

FDI_REFRESH_STAGES

Column	Data Type	Values
REFRESH_ID	VARCHAR2	Unique ID to join with other tables
STAGE_NAME	VARCHAR2	Names of stages in the scheduled incremental refresh. See Schedule Incremental Data Refreshes. The following examples show the values in NUM_STAGES column of the FDI_REFRESH_SUMMARY table and the corresponding values in this column: Job without priority: NUM_STAGES = 2 DATA_PUBLISHED POST_PROCESSING Incremental with priority: NUM_STAGES = 3 PRIO_DATA_PUB_ORD_0 DATA_PUBLISHED POST_PROCESSING Multistage incremental without selecting priority, secondary (index file has 1): NUM_STAGES = 2 DATA_PUBLISHED POST_PROCESSING Multistage incremental without selecting priority dataset (index file has 1,2): NUM_STAGES = 3 PRIO_DATA_PUB_ORD_1 DATA_PUBLISHED POST_PROCESSING Multistage incremental without primary (index file has 0,2): NUM_STAGES = 3 PRIO_DATA_PUB_ORD_0 DATA_PUBLISHED POST_PROCESSING Multistage incremental without primary (index file has 0,1): NUM_STAGES = 3 PRIO_DATA_PUB_ORD_0 DATA_PUBLISHED POST_PROCESSING Multistage incremental(index file has 0,1,2): NUM_STAGES = 4 PRIO_DATA_PUB_ORD_0 PRIO_DATA_PUB_ORD_1 DATA_PUBLISHED POST_PROCESSING
COMPLETION_TIME	TIMESTAMP	-

FDI_REFRESH_FACTORS

Column	Data Type	Values
REFRESH_ID	VARCHAR2	Unique ID to join with other tables
FACTOR_CODE	VARCHAR2	SOURCE_MAINTENANCE - The time the refresh process waited because the source system was under maintenance. CUSTOMER_HIGH_ADW_SERVICE - The number of concurrent Autonomous AI Lakehouse user sessions using HIGH service observed during data refresh process. See Usage Guidelines for Autonomous AI Lakehouse Associated with Oracle Fusion Data Intelligence.
FACTOR_DETAILS	VARCHAR2	Additional details for the factor such as minutes or number of sessions. For example, the number of minutes if FACTOR_CODE is SOURCE_MAINTENANCE and the number of sessions if FACTOR_CODE is CUSTOMER_HIGH_ADW_SERVICE.

Here are some sample queries to use these tables:

View Refresh Summary for the Last 1 Day using this query:

 SELECT * FROM FDI_REFRESH_SUMMARY WHERE COMPLETION_TIME > SYSDATE - 1;

View Stage Details for a Specific Refresh using this query:

SELECT * FROM FDI_REFRESH_STAGES WHERE REFRESH_ID = <REFRESH_ID>;

View Impacted Refresh Factors by Refresh ID using this query:

SELECT * FROM FDI_REFRESH_FACTORS WHERE REFRESH_ID = <REFRESH_ID>;

View Changed Tables

To view the tables that got changed, you can use the FDI_REFRESHED_DATASETS table. It shows the warehouse tables refreshed and the time-stamp at which they are refreshed.

Here are some sample queries:

View all the datasets that are refreshed using this query:

SELECT table_name, to_char(last_refresh_time,'DD-MON-YYY HH24:MI:SS') as last_refresh_datetime FROM fdi_refreshed_datasets;

View all the datasets refreshed during last 2 days using this query:

SELECT * FROM fdi_refreshed_datasets WHERE last_refresh_time > sysdate - 2;

View all the datasets refreshed during last 1 hour using this query:

SELECT * FROM fdi_refreshed_datasets where last_refresh_time > sysdate-INTERVAL '1' HOUR;