Overview of Maintaining the Stage Tables

When you load large volumes of data into Oracle HCM Cloud, the stage tables can grow rapidly.

You're recommended to create a schedule to delete processed data sets that you no longer need in the stage tables. How often you delete processed data sets from the stage tables depends on the volume and frequency of your data loads. During data migration, consider deleting every large data set as its processing completes. This topic describes the role of stage tables in data loading and explains how the deletion process maintains the stage tables automatically.

How Data Is Loaded to Oracle HCM Cloud

This figure shows how data is loaded to the application tables.

Data that you load using either HCM Data Loader or HCM Spreadsheet Data Loader is imported first to stage tables, where some validation occurs. HCM Spreadsheet Data Loader has its own stage tables, from where valid data is loaded to the HCM Data Loader stage tables. Valid data is loaded from the HCM Data Loader stage tables to the application tables. When a data set contains data for an object that supports roll back, such as element entry, the data for the entire data set can be moved to secondary stage tables, ensuring it's not purged prematurely and doesn't negatively impact the bulk load processing which occurs from the main stage tables

Note: In the Import and Load Data page, data sets that are retained for an extended period in the secondary stage table can be identified by the green tick that are retained for an extended period in the secondary stage table can be identified by the green tick in the Extended Retention column.

How to Extend the Period of Retention for a Data Set

The Enable Automatic Extended Data Set Retention configuration parameter determines if data sets containing data that can be validated or rolled back are automatically moved to the secondary staging tables. The extended retention period is defined by the Days to Preserve Extended Retention Data Sets configuration parameter. You can override the default behavior at the data set level by using the EXTEND_DATA_SET_RETENTION SET instruction. If you have loaded a data set and it is held for the standard retention period within the primary staging tables, you can extend the data set retention by moving it to the secondary staging tables from the Import and Load Data page.

How Processed Data Sets Are Deleted from Stage Tables

You can delete processed data sets from three sets of stage tables.

• To delete HCM Spreadsheet Data Loader data sets from all stage tables, use the Delete Spreadsheet Stage Table Data task. It runs the Delete HCM Spreadsheet Data Loader Stage Table Data process.

• To delete data sets from the HCM Data Loader extended retention stage tables, use the Delete Stage Table Data task. It runs the Delete HCM Data Loader Stage Table Data process.

  Alternatively, you can delete individual data sets from these stage table using the Delete button on the Data Sets table within the Import and Load Data interface.

Perform all tasks in the Data Exchange work area.

You can delete:

• Individual data sets

• All data sets that currently match specified criteria

  Tip: You can schedule a recurring deletion request. For example, you can schedule weekly deletion of all data sets that haven't been updated in the last 7 days.

When you run the Import and Load Data process, it determines whether a schedule exists for the Delete HCM Data Loader Stage Table Data process. If no schedule exists, then the process creates one to run Delete HCM Data Loader Stage Table Data nightly.

Note: If the Import and Load Data process identifies that an existing schedule is failing because the initiating user is no longer having the permissions to run the process, the schedule will be automatically deleted with a warning.

When you delete extended retention data sets from the HCM Spreadsheet Data Loader stage tables, they're deleted automatically from the HCM Data Loader stage tables. You can also delete spreadsheet data sets explicitly from the HCM Data Loader stage tables. In this case, they remain available in the HCM Spreadsheet Data Loader stage tables. You can't recover deleted data sets. Don't delete data sets that you want to report on or that you want to extract error information for.

How Delete HCM Data Loader Stage Table Data Works

The Delete HCM Data Loader Stage Table Data process deletes data sets from the HCM Data Loader and extended retention stage tables. You can delete from one set of stage tables at a time and this is determined by the data set type. Whenever the process runs for standard data sets, the process:

1. Purges automatically from the stage tables any data set that hasn't been updated in the last 30 days. You can specify a different number of days by setting the Days to Preserve Standard Data Sets Before Deleting configuration parameter.

2. Reviews the number of data lines still in the stage tables. If the number exceeds a specified maximum value, then data sets may be archived automatically. You specify:

   • The maximum number of data lines on the Maximum Data Lines Before Archiving configuration parameter.

   • The number of days since a data set was last updated before it can be backed up. Specify this value on the Days to Preserve Standard Data Sets Before Archiving configuration parameter.

Therefore, a data set is a candidate for automatic archiving only if:

• The number of data lines in the stage tables exceeds the value specified on the Maximum Data Lines Before Archiving parameter.

• The data set hasn't been updated in the number of days specified on the Days to Preserve Standard Data Sets Before Archiving parameter.

The archive starts with the oldest data set and continues until the number of data lines in the stage tables drops below the specified maximum.

The same process is used to delete extended retention data sets. The retention period of extended retention data sets is controlled by the Days to Preserve Extended Retention Data Sets.

Archived Data Sets

These values remain in the stage tables when a data set is archived:

• The statuses of the data set and business objects

• The record count

• Any error messages

All other data from the data set is deleted. After a data set is archived

• You can't open error management pages for that data set.

• It doesn't appear in search results when you search for data sets to purge.

• You can access the data using a custom query only.

You can archive data sets manually by running the Delete HCM Data Loader Stage Table Data process with the Action parameter set to Archive. You can't create a schedule when the action is Archive.