LOB types

The LOB data type is used to store data of types such as TEXT, BLOB, JSON, and Geometry. The storage methods for LOB data are categorized into two types: in-row storage and out-of-row storage.

Inline storage

Inline storage stores LOB data together with the row data of the primary table, requiring only one storage access operation when reading LOB data.

External storage

Outbound storage stores LOB data in a LOB auxiliary table. When reading LOB data, you must first read the primary table row to obtain the locator of the external LOB, and then use this locator to read the actual LOB data from the LOB auxiliary table. This process involves two storage access operations.

LOB types

In Oracle-compatible mode, common LOB types include:

• BLOB (Binary Large Object): A data type used to store large files such as images, documents, and audio.
• CLOB (Character Large Object): A data type used to store single-byte and multibyte character data.
• JSON: Stores data in JSON format, commonly used for processing structured data.
• SDO_GEOMETRY: A data type used to store and manipulate geometric data. It is a composite data type used to represent two-dimensional or three-dimensional geometric shapes.
• XMLType: Stores data in XML format, facilitating the processing and querying of XML documents.

LOB Consistency Verification

When OUTROW storage is used, the primary table and the LOB auxiliary table are associated through LOB IDs: the set of LOB IDs in the locator of the primary table must match the set of LOB IDs present in the LOB auxiliary table. Inconsistencies, such as "IDs present in the primary table but absent in the auxiliary table" or "IDs present in the auxiliary table but absent in the primary table," can lead to read anomalies or space that cannot be reclaimed.

This feature uses a LOB verification task (LOB Task) to scan the primary table and collect the LOB ID set, then compares it with the LOB ID set obtained from scanning the LOB auxiliary table. It outputs whether the sets are consistent and provides discrepancy information, facilitating routine inspections during off-peak hours or troubleshooting to confirm whether data relationships are normal.

Usage instructions in conjunction with resource groups and I/O benchmarks

LOB consistency verification scans the primary table and the LOB auxiliary table, resulting in significant I/O and CPU overhead. When setting limits such as I/O ceilings for verification tasks, these depend on the disk I/O benchmark of the tenant (generated by I/O calibration). The following recommendations apply to different deployment scenarios:

• Environments where I/O calibration has been completed by default, such as public clouds: Typically, you can directly configure a resource management plan and a resource group within the tenant.
• Private clouds or environments where I/O calibration has not been performed: You must first complete disk I/O calibration in the sys tenant, then plan the MAX_IOPS/MIN_IOPS for units in the business tenant based on the benchmark, and align it with the I/O limits in the resource group. For calibration commands, progress viewing, and the relationship with resource isolation for background tasks, see Resource isolation for background tasks; disk performance calibration steps are described separately in Disk performance calibration.

Complete example

When using the consistency verification feature, you must first perform I/O calibration, then create a dedicated resource plan for LOB verification, and configure the resource management plan and resource group.

Step 1: I/O calibration (Optional)

If I/O calibration has not been performed, you need to execute I/O calibration in the sys tenant first. Calibration may take 1-2 minutes from completion to taking effect.

ALTER SYSTEM RUN JOB 'io_calibration';

After calibration is complete, you can view the status of the calibration task through GV$OB_IO_CALIBRATION_STATUS and view the benchmark IOPS for each node through GV$OB_IO_BENCHMARK. When configuring unit IOPS for a tenant, it is recommended to leave approximately 10% headroom relative to the benchmark (for example, the IOPS corresponding to a 16 KB read) to prevent disk I/O saturation from increasing latency for online workloads.

SELECT * FROM GV$OB_IO_CALIBRATION_STATUS;
SELECT * FROM GV$OB_IO_BENCHMARK;

Step 2: Create a dedicated resource plan for LOB verification

Within the business tenant where verification needs to be run, you can create a separate resource plan to map LOB verification-related background tasks to a dedicated resource group and limit CPU and IOPS. In the following example, the plan name is LOB_CHECK, the resource group name is LOB_CHECK, and the FUNCTION mapping assigns background tasks with the value LOB_CHECK to this group. Adjust parameters such as MGMT_P1, UTILIZATION_LIMIT, MAX_IOPS, MIN_IOPS, and WEIGHT_IOPS according to your actual specifications.

BEGIN DBMS_RESOURCE_MANAGER.CREATE_PLAN('LOB_CHECK', 'plan for lob_check');
END;
/
BEGIN DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP('LOB_CHECK', 'LOB_CHECK');
END;
/
BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    PLAN => 'LOB_CHECK',
    GROUP_OR_SUBPLAN => 'LOB_CHECK',
    COMMENT => 'LOB_CHECK_GROUP',
    MGMT_P1 => 30,
    UTILIZATION_LIMIT => 30,
    MAX_IOPS => 20,
    MIN_IOPS => 0,
    WEIGHT_IOPS => 20);
END;
/
BEGIN DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING('FUNCTION', 'LOB_CHECK', 'LOB_CHECK');
END;
/

After activating the resource management plan, the above mappings and limits will take effect for sessions and background tasks:

SET GLOBAL resource_manager_plan = 'LOB_CHECK';

For the meaning and considerations of resource_manager_plan, see resource_manager_plan.

For descriptions of the DBMS_RESOURCE_MANAGER procedures, parameters, and privileges, see Overview of DBMS_RESOURCE_MANAGER and Configure resource isolation within a tenant (Oracle-compatible mode).

Step 3: Configure a scheduled task (Optional)

If you need to perform LOB consistency verification periodically, you can manually enable a scheduled task. By default, it triggers at 4:00 AM every Sunday.

-- Start Task
CALL DBMS_SCHEDULER.ENABLE('lob_check_job');
-- Close Task
CALL DBMS_SCHEDULER.DISABLE('lob_check_job');
-- Modify the scheduled task's trigger cycle and start time
CALL DBMS_LOB_MANAGER.RESCHEDULE_JOB('2025-12-01 17:48:00', 'FREQ=DAILY; INTERVAL=1');

View the scheduling task information:

SELECT * FROM DBA_SCHEDULER_JOBS WHERE job_name='lob_check_job';

Step 4: Execute LOB consistency verification

-- Trigger a consistency check on all data tables containing LOB auxiliary tables for the current tenant.
CALL DBMS_LOB_MANAGER.CHECK_LOB();

-- Trigger consistency verification only for the table with the specified table_id.
CALL DBMS_LOB_MANAGER.CHECK_LOB('[500001, 500003]');

-- Task control (only for verification tasks)
CALL DBMS_LOB_MANAGER.CANCEL_JOB('check_lob');
CALL DBMS_LOB_MANAGER.SUSPEND_JOB('check_lob');
CALL DBMS_LOB_MANAGER.RESUME_JOB('check_lob');

Step 5: View the LOB consistency verification results

View the current LOB task progress through DBA_OB_LOB_CHECK_TASKS and view exception results through DBA_OB_LOB_CHECK_EXCEPTION_RESULT.

SELECT * FROM DBA_OB_LOB_CHECK_TASKS;
SELECT * FROM DBA_OB_LOB_CHECK_EXCEPTION_RESULT;

References

• BLOB data type
• CLOB data type
• JSON data type
• SDO_GEOMETRY
• XMLType