ILM Database Administrator’s Tasks
For a database administrator, there are two key phases involved with managing your data using ILM.
• Preparation Phase: Covers the database level configuration that needs to be done before the ILM solution runs in a production environment.
• Naming Convention: Covers the recommended naming convention to be used for partitions/subpartitions and tablespaces.
Preparation Phase
Note: To successfully implement ILM as described here, the following DB Version and Patch are pre-requisites: database version 19.3.0.0 or newer.
The steps needed to enable ILM functionality differ depending on whether ILM is enabled as part of the initial implementation of the product or enabled ILM on an existing implementation where data already exists in the respective tables.
• Transform NON-ILM implementation to ILM Enabled Implementation: The following steps provide a high level overview of steps that must be performed to implement ILM on enabled MOs for an existing implementation. Please refer to
Sample SQL For Enabling ILM in C2M for CCB (Existing Installation)Sample SQL For Enabling ILM for CCB (Existing Installation) for detailed information using To Do Entry as an example.
1. Rename the existing tables (Parent table followed by child table(s)), and primary key index associated with ILM enabled MOs by renaming the tables.
2. Save the DDLs for the secondary indexes as you will need to recreate them later.
3. Drop secondary indexes on the renamed tables.
4. Create Partitioned table with no secondary indexes for ILM enabled MOs using a CTAS operation (Create Table as Select), which will also load the data into the partitioned table structure.
Functional Note: ILM enabled MOs should have the ILM date (ILM_DT) populated when data is moved into the new partitioned table. Please refer to the
Module Specific ILM Implementation Details section below for initial load details on which date column to use as the basis for populating the ILM date. Often it is based on Create Date (CRE_DTTM). ILM_ARCH_SW should initially be set to ‘N’.
5. Enable logging option.
6. Create Primary Key index.
7. Create Primary Key Constraint of parent table.
8. Create secondary indexes for the newly-created partitioned tables. This includes creating an index used specifically to benefit the ILM Crawler batch. The recommendation for this index name is to prefix it with "ILM".
Note: This can be created specifying parallel index create; remember to turn off parallelism after the index is created.
9. Follow similar operation for all child tables for this MO, such as rename child table, and primary key index, generate DDL for secondary index, drop secondary index etc. Sample DDL for child tables their partitioning and indexes can be found in
Sample SQL For Enabling ILM in C2M for CCB (Existing Installation)Sample SQL For Enabling ILM for CCB (Existing Installation). Please note that child table should be partitioned using reference partitioning of the parent table’s partitioning key.
10. Drop the original, renamed tables after verifying the newly created partitioned tables.
On-going Maintenance Phase
The following steps provide a high level overview of what needs to be done for on-going maintenance for ILM on enabled maintenance objects.
Please refer to the
Sample SQL for Periodic Maintenance for CCB Data for detailed information using two maintenance objects as examples.
1. Add the partition:
a. Create Tablespace to be used for the new parent table partition.
b. Since, we define MAXVALUE Partition; new partition can only be created using “SPLIT” operation. Identify and use next HIGH_VALUE Partition for the split operation.
c. All the child table(s) partition(s)\LOB(s) must be altered to use the same tablespace as that of the parent table’s partition.
d. Enable advanced compression on all child table(s).
e. Copy partition level statistics from the previous partition
2. Archive the partition:
a. Make the tablespace that will be archived READ ONLY.
b. Check that no records have ILM_ARCH_SW = ‘N’.
• If record count is zero, proceed with further steps.
• If record count is not zero, then change the tablespace back to READ WRITE MODE as Archive is not Feasible at the time.
c. Create an archive tablespace for the partition that needs to be archived.
d. Create staging tables using the new archive tablespace. Load data for all child tables first.
e. Create staging table using the new archive tablespace and load data for the parent table.
f. Export tablespace using TRANSPORT_TABLESPACES method.
Make Sure Tablespace datafile required for further import is preserved.
g. Drop the partition, partition the tablespace and archive the tablespace (as it is already exported).
3. Restore the partition:
a. Create a new tablespace to restore the partition.
b. Add partition using split operation on next greater high value partition.
If the table contains LOBS, there will an additional statement in split partition DDL indicating tablespace where the LOBs will be stored.
c. Enable advanced compression on all child table(s).
d. Import Tablespace using TRANSPORT_TABLESPACES method.
e. Load data into the parent table first from the staging table
f. Load data into the child table from the staging table
g. Drop the archive tablespace after import and data loading is successful.
4. Move Data between different storage tiers:
The ILM facilities can be used within the database to implement storage savings, as follows:
• Use ILM Assistant to define the data groups to be used for the individual objects. Assign those data groups to partitions and storage devices to implement the storage savings.
• Use ILM assistant to generate the necessary commands to implement the data changes manually or use Automatic Storage Management (ASM) to automate the data storage policies.
• Optionally, use Automatic Data Optimization to provide further optimizations.
For more information about ILM refer to the following:
• Oracle Database VLDB and Partitioning Guide (19c) available at:
• Oracle Enterprise Manager 13.4 Lifecycle Management available at: