Perform the following tasks after you complete the offline database cleanup process.
Reclaiming tablespaces
During the offline database cleanup process, temporary swap tables are created. The tablespace still remains occupied and must be reclaimed. The task of reclaiming the tablespace is performed by the database administrator and differs according to the type of database — whether Oracle or SQL Server.
For instructions on reclaiming the free space in tablespaces, to be performed by the database administrator, consult Oracle documentation. The following query can be used to see the actual required size of datafiles to resize:
SELECT file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) reqdsize,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
FROM dba_data_files a,
( SELECT file_id, MAX(block_id+blocks-1) hwm
FROM dba_extents
GROUP BY file_id ) b
WHERE a.file_id = b.file_id(+) ORDER BY savings DESC
For instructions on reclaiming the free space in tablespaces, to be performed by the database administrator, consult SQL Server documentation. The following query can be used to see the actual required size of datafiles to resize:
SELECT name AS [File Name] , physical_name AS [Physical Name], size/128.0 AS [Total Size in MB],
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In MB]
FROM sys.database_files OPTION (RECOMPILE);
Gathering and comparing metrics
After running the cleanup process, it is worthwhile to capture the current size of the database tables so that these sizes can be compared to the pre-cleanup sizes. To capture database table sizes, run one of the provided scripts, depending on your database type — either Oracle or SqlServer.
Compare the resulting CSV file to the CSV file that was generated before you ran the cleanup process.
For a list of database tables that are impacted by the cleanup process and their correlation with the Offline Cleanup modules, expand the following table.
Module name | Table name |
|---|
Job Run Event | JOB_RUN_EVENT |
Job Run Event | DEPLOY_JOB_RUN_EVENT |
Snapshot | SNAPSHOT_AUTH_DEPLOY_JOB_RUNS |
Snapshot | SNAPSHOT_COMPONENT_STATS |
Snapshot | SNAPSHOT_PART |
Snapshot | SNAPSHOT_GENERIC_ASSET |
Snapshot | SNAPSHOT_SYSTEM_INFO_OBJECT |
Snapshot | SNAPSHOT_EVENT_LOG |
Snapshot | SNAPSHOT_DOT_NET_ASSEMBLY |
Snapshot | SNAPSHOT_COM_OBJECT_CNT |
Snapshot | SNAPSHOT_METABASE_OBJECT_CNT |
Snapshot | SNAPSHOT_BLFILE_CNT |
Snapshot | SNAPSHOT_GPO_SETTING |
Snapshot | SNAPSHOT_VMWARE_OBJ_CNT |
Snapshot | SNAPSHOT_CFG_FILE_OBJECT_CNT |
Snapshot | SNAPSHOT_COM_OBJECT |
Snapshot | SNAPSHOT_GPO_SETTING_CNT |
Snapshot | SNAPSHOT_METABASE_OBJECT |
Snapshot | SNAPSHOT_BLFILE |
Snapshot | SNAPSHOT_OS_USER |
Snapshot | SNAPSHOT_OS_GROUP |
Snapshot | SNAPSHOT_CONFIG_FILE_OBJECT |
Snapshot | SNAPSHOT_SOFTWARE |
Snapshot | SNAPSHOT_SERVICE |
Snapshot | SNAPSHOT_SYS_INFO_OBJECT_CNT |
Snapshot | SNAPSHOT_REGISTRY_OBJECT |
Snapshot | SNAPSHOT_REGISTRY_OBJECT_CNT |
Snapshot | SNAPSHOT_VMWARE_OBJECT |
Compliance | COMPLIANCE_REMEDIATION_RUN |
Compliance | CR_GROUP_RESULT |
Compliance | JOB_RESULT_CR_GROUP_RESULT |
Compliance | COMP_RULE_GROUP_RESULT_XREF |
Compliance | COMPLIANCE_RULE_RESULT |
Compliance | COMP_RULE_GROUP_RULE_RESULT |
Compliance | CONDITION_RESULT |
Compliance | CR_RULE_RESULT_SELECTOR |
Compliance | COMP_ENTRY_RESULT |
Compliance | COMP_ENTRY_RESULT_DETAIL |
Compliance | COND_RESULT_SUB_COND_RESULT |
Compliance | PROPERTY_CONDITION_RESULT |
Compliance | ASSET_ATTRIBUTE_COND_RESULT |
Compliance | ASSET_SELECTOR_RESULT |
Compliance | SELECTOR_CONDITION_RESULT |
Compliance | SELECTOR_RESULT_PATH |
Compliance | HRB_COMP_RULE_GROUP_RESULT |
Compliance | HRB_CONDITION_RESULT |
Compliance | SCAP_RESULT_TARGET_DATA |
Compliance | SCAP_RESULT_NETWORK_DATA |
Compliance | SCAP_RESULT_RULE_DATA |
Deploy | DEPLOY_JOB_RUN |
Deploy | DEPLOY_JOB_RUN_CLASS_REF |
Deploy | DEPLOY_JOB_RUN_ATTRIBUTE_REF |
Deploy | JOB_RESULT_GEN_DEPOT_OBJECT |
Deploy | DEPLOY_JOB_DEVICE_RUN |
Patching | PATCHING_JOB_RESULTS_ASSOC |
Patching | PATCHING_JOB_RUN_PART |
Patching | PATCH_RMD_DNL_JOB_RUN_ASSOC |
Patching | PATCH_CUJ_RESULT |
Patching | BL_PATCH_JOB_RESULT_COUNT |
Patching | BL_PATCH_JOB_RES_PATCH_COUNT |
Patching | PATCH_DEPENDENCY_MAP |
Patching | BL_PATCH_ANALYSIS_RES_DATA |
Patching | BL_PATCH_RES_DEVICE_CNT |
Patching | BL_PATCH_DOWNLOAD_RES_DATA |
Patching | PATCH_DOWNLOAD_RESULT |
Patching | PATCH_RMD_RESULT |
Patching | PAJOB_RUN_CATALOG_ASSOC |
Patching | PATCH_RMD_JOB_ASSOC |
Audit | AUDIT_OBJECT |
Audit | AUDIT_RESULT_PART_RULE_SET |
Audit | AUDIT_OBJECT_PART |
Audit | AUDIT_GPO_SETTING_CNT |
Audit | AUDIT_REGISTRY_OBJECT_CNT |
Audit | AUDIT_COM_OBJECT_CNT |
Audit | AUDIT_OS_USER |
Audit | AUDIT_CONFIG_FILE_OBJECT |
Audit | AUDIT_DOT_NET_ASSEMBLY |
Audit | AUDIT_TARGET_PART_STATUS |
Audit | AUDIT_COM_OBJECT |
Audit | AUDIT_METABASE_OBJECT_CNT |
Audit | AUDIT_GPO_SETTING |
Audit | AUDIT_EVENT_LOG |
Audit | AUDIT_CFG_FILE_OBJECT_CNT |
Audit | AUDIT_VMWARE_OBJECT |
Audit | AUDIT_OBJECT_PART_COUNT |
Audit | AUDIT_BLFILE |
Audit | AUDIT_SOFTWARE |
Audit | AUDIT_BLFILE_CNT |
Audit | AUDIT_VMWARE_OBJ_CNT |
Audit | AUDIT_SYS_INFO_OBJECT_CNT |
Audit | AUDIT_REGISTRY_OBJECT |
Audit | AUDIT_SYSTEM_INFO_OBJECT |
Audit | AUDIT_SERVICE |
Audit | AUDIT_METABASE_OBJECT |
Audit | AUDIT_OS_GROUP |
Audit | AUDIT_GENERIC_ASSET |
Audit Trail | AUDIT_TRAIL |
Audit Trail | APP_SERVER_AUDIT_TRAIL |
Audit Trail | AUTO_PRINCIPAL_AUDIT_TRAIL |
Audit Trail | BL_ACL_POLICY_AUDIT_TRAIL |
Audit Trail | BL_ASSET_CLASS_AUDIT_TRAIL |
Audit Trail | BLGROUP_AUDIT_TRAIL |
Audit Trail | BLROLE_AUDIT_TRAIL |
Audit Trail | BLUSER_AUDIT_TRAIL |
Audit Trail | BL_ACL_TEMPLATE_AUDIT_TRAIL |
Audit Trail | BL_AUTH_PROFILE_AUDIT_TRAIL |
Audit Trail | COMPONENT_AUDIT_TRAIL |
Audit Trail | CUSTOM_COMMAND_AUDIT_TRAIL |
Audit Trail | DEPOT_OBJECT_AUDIT_TRAIL |
Audit Trail | DEVICE_AUDIT_TRAIL |
Audit Trail | EXECUTION_TASK_AUDIT_TRAIL |
Audit Trail | EXTENDED_OBJECT_AUDIT_TRAIL |
Audit Trail | JOB_AUDIT_TRAIL |
Audit Trail | LDAP_CONNECTION_AUDIT_TRAIL |
Audit Trail | LDAP_QUERY_AUDIT_TRAIL |
Audit Trail | PM_DEVICE_AUDIT_TRAIL |
Audit Trail | PROP_SET_CLASS_AUDIT_TRAIL |
Audit Trail | PROP_SET_INSTANCE_AUDIT_TRAIL |
Audit Trail | ROUTING_POLICY_AUDIT_TRAIL |
Audit Trail | SERVER_CONFIG_DEF_AUDIT_TRAIL |
Audit Trail | SYSTEM_PACKAGE_AUDIT_TRAIL |
Audit Trail | TEMPLATE_AUDIT_TRAIL |
Audit Trail | TOKEN_RULE_SET_AUDIT_TRAIL |
Shared Data Objects | CONFIG_FILE_OBJECT |
Shared Data Objects | CONFIG_FILE_OBJECT_VALUE |
Shared Data Objects | OBJECT_OVERFLOW |
Shared Data Objects | OBJECT_OVERFLOW_DETAIL |
Job Run Framework | JOB_RUN |
Job Run Framework | JOB_RUN_OVERRIDDEN_PSI |
Job Run Framework | JOB_RUN_ETL_CDC |
Job Run Framework | NIMBUS_TEMPLATE_INSTANCE |
Job Run Framework | JOB_RESULT |
Job Run Framework | JOB_RUN_SCHEDULE |
Job Run Framework | JOB_RESULT_VSMDISCOVERY |
Job Run Framework | JOB_RESULT_DEPOT_OBJECT |
Job Run Framework | JOB_RESULT_TEMPLATE |
Job Run Framework | JOB_RESULT_DEVICE |
Job Run Framework | JOB_RESULT_ATRIUM_TO_BL_SYNC |
Job Run Framework | JOB_RESULT_BLGROUP |
Job Run Framework | JOB_RESULT_COMPONENT |
Job Run Framework | NTISAN_NIC |
Job Run Framework | NTILAN_NIC |
Job Run Framework | NTIBOOT_ORDER |
Job Run Framework | NTIPOLICY |
Job Run Framework | NTIVMNIC_CONFIG |
Job Run Framework | NTIAUDIT_TRAIL |
Job Run Framework | BATCH_JOB_RUN |
Job Run Framework | EXECUTION_TASK_JOB_RUN |
