You use purge scripts to remove large volumes of obsolete data from the NI database, without using the NI application.

A date-based condition can be used for filtering the OCIM table data. This results in the required data that needs to be cleared and you can delete this data using the purge scripts.

The purge scripts:

• Provide parallel execution of OCIM and NI tables.
• Use tables to capture the following information:
  • Individual purge records on both OCIM and NI tables and scan levels.
  • Scan IDs on which the purge scripts are run.
  • Errors captured during the purge run.
• Delete the tables with comparatively high volumes of data.
• Delete the Bash shell scripts to run the SQLs with the corresponding DB connection details.

Purge tables capture the details related to every purge script. The tables that capture these details are:

• PURGED_RECORD_DATA: This table captures the details of every purge activity such as time taken, number of records deleted across scan IDs, and number of records deleted at the table level. The table contains the following columns:
  • PURGED_RECORD_DATA.PURGE_ID: A unique ID for a purge activity. This column cannot be NULL.
  • PURGED_RECORD_DATA .CREATE_DATE: The starting time of a purge activity.
  • PURGED_RECORD_DATA .SCAN_ID: The scan entity ID that is gathered from the DISSCANRUN table based on the filter criteria.

    Note:

    One purge activity can have multiple scan IDs.
  • PURGED_RECORD_DATA.PURGE_DURATION: The time taken for purging records from a scan ID in a specific table. This value is calculated in milliseconds.
  • PURGED_RECORD_DATA .TABLE_NAME: The table name from which the data is purged.
  • PURGED_RECORD_DATA .RECORDS_DELETED: The number of records deleted in a scan ID from a specific table.

  Note:

  • If TABLE_NAME and SCAN_ID are both null in the same row, then the specific row provides the total purge duration and total records deleted across all tables and scan IDs in that purge activity.

  • The purge script creates the PURGE_SEQ sequence that is assigned to the PURGED_RECORD_DATA.PURGE_ID column.

• PURGE_SCANS: This table temporarily captures the scan IDs used by a purge activity. This table is used to avoid using any surplus IDs that are created during or after an initial purge script. The table contains PURGE_SCANS.ENTITYID that provides the scan entity IDs gathered from the DISSCANRUN table based on the required filter criteria. These IDs are populated by running the REFRESH_SCANS script.
• PURGE_ERROR: This table captures the details of any errors that occur during the purge activities. The table contains the following columns:
  • PURGE_ERROR.PURGE_ID: The purge ID for which error is observed.
  • PURGE_ERROR.SCAN_ID: The scan entity ID for which error is observed.
  • PURGE_ERROR.TABLE_NAME: The table name for which error is observed.
  • PURGE_ERROR.ERROR: The description of the error observed.

To run the purge scripts, you require the following permissions granted to the schema user:

• GRANT SCHEDULER_ADMIN TO <MDS Schema User>
• GRANT EXECUTE ON DBMS_LOCK TO <MDS Schema User>
• GRANT CREATE ANY VIEW TO <MDS Schema User>
• GRANT CREATE PROCEDURE TO <MDS Schema User>

To run the purge scripts:

1. Run stored procedures and functions in the following order, if they are not present:
   • WAIT_AND_GET_LEFOUT_JOBS
   • TIMESTAMP_DIFF_IN_MILLISECONDS
   • PURGE_DI_BATCH
   • PURGE_DI_CHAR_BATCH
   • PURGE_DICONFIGITEM_BATCH
   • PURGE_DICONFIGITEM_CHAR_BATCH
   • PURGE_EQUIP_BATCH
   • PURGE_EQ_CHAR_BATCH
   • PURGE_PHYSICALPORT_BATCH
   • PURGE_PHYSICALPORT_CHAR_BATCH
   • PURGE_SERVICECONFIGITEM_CHAR_BATCH
   • PURGE_SERVICECONFIGITEM_BATCH
   • OCIM_PURGE
   • NI_PURGE_BATCH

   Note:

   Verify if the above stored procedures and functions are added during NI installation. If not, you must add them manually.

2. Run the REFRESH_SCANS script while setting TIMELIMIT with the date you plan to run the purge script.

   This script clears the existing entries and adds new scan IDs as per the selection criteria.

   Note:

   By default, this value is set to 30 which indicates that the script selects the data related to scans created 30 days or older to the purge activity.
3. Run the OCIM_MAIN script as follows:
   1. Run with multiple batches of fewer rows initially.
   2. (Optional) Customize the number of parallel jobs using the parallel_jobs variable, based on the CPU availability.
4. Run the NI_MAIN script.

   Note:

   You can customize the number of parallel jobs using the parallel_jobs variable. If required, you can tune the parallel job wait time by changing the lines 33 and 45 within the NI_MAIN script.
5. (Optional) To run the OCIM_MAIN and NI_MAIN scripts from a remote machine, use the bash shell scripts startOCIMPurge and startNIPurge in a local Linux environment. These scripts require the DB connection details or you can edit these scripts to set the connection details and then run the corresponding SQL script. The nohup.out file captures the log that is generated by running the script.

   Note:

   You require SQL*plus installed on the remote machine to run the startOCIMPurge and startNIPurge scripts.
6. Verify and resolve any errors occurred after running the OCIM_MAIN and NI_MAIN scripts.

After completing the purging activities, refresh the memory space so that you can use it for performing other tasks.

To do so:

1. Move the tables to refresh the space as follows:

   Alter table <tablename> move;

2. Rebuild the indexes.