LOB types

The LOB type is used to store data types such as TEXT, BLOB, JSON, and Geometry. The LOB type can be stored in two ways: inline storage (INROW) and out-of-line storage (OUTROW).

Inline storage

Inline storage stores LOB data with the main table rows. When you read LOB data, only one storage access operation is required.

LOB storage

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

LOB types

In Oracle mode, common LOB types include:

• BLOB (Binary Large Object): A binary large object, typically used to store large files such as images, files, and music.

• CLOB (Character Large Object): A character large object, used to store single-byte and multibyte character data.
• JSON: A data type used to store JSON-formatted data, commonly used for processing structured data.
• SDO_GEOMETRY: A composite data type used to store and process geometric data, representing two-dimensional or three-dimensional geometric shapes.
• XMLType: A data type used to store XML-formatted data, facilitating the processing and querying of XML documents.

LOB consistency check

When an external storage is accessed, the main table and the LOB auxiliary table are associated through LOB IDs. The set of LOB IDs in the locator of the main table should be consistent with the set of LOB IDs in the LOB auxiliary table. If there are inconsistencies, such as "the main table has LOB IDs that the auxiliary table does not" or "the auxiliary table has LOB IDs that the main table does not," it may lead to issues such as read errors or inability to recycle space.

This feature uses a LOB check task (LOB Task) to scan the main table and collect the set of LOB IDs. It then compares this set with the set of LOB IDs obtained from the LOB auxiliary table scan, outputting whether they are consistent and the differences. This helps in routine inspections during off-peak hours or in troubleshooting to confirm the normality of data relationships.

Considerations when used with resource groups and I/O benchmarks

LOB consistency checks scan the main table and LOB auxiliary table, which have significant I/O and CPU overhead. When setting I/O limits for the check task, you need to rely on the tenant's disk I/O benchmark (generated by I/O calibration). The following recommendations are provided for different deployment scenarios:

• In environments like public clouds where I/O calibration is already completed by default: You can typically directly configure resource management plans and resource groups within the tenant.
• In private clouds or environments where I/O calibration has not been performed: First, complete the disk I/O calibration in the system tenant. Then, based on the benchmark, plan the MAX_IOPS and MIN_IOPS for business tenants, and combine this with the I/O limits in the resource groups. For details about the calibration commands, progress monitoring, and resource isolation for background tasks, see Resource isolation for background tasks. For the steps of disk performance calibration, see Disk performance calibration.

Full example

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

Step 1: I/O calibration (optional)

If you have not performed I/O calibration, you must perform I/O calibration in the sys tenant. It may take 1-2 minutes for the calibration to take effect.

ALTER SYSTEM RUN JOB 'io_calibration';

After the calibration is completed, you can view the calibration task status in GV$OB_IO_CALIBRATION_STATUS and the benchmark IOPS of each node in GV$OB_IO_BENCHMARK. When you configure the unit IOPS for a tenant, we recommend that you reserve about 10% of the benchmark IOPS (such as the IOPS corresponding to 16 KB reads) to avoid affecting the latency of online business due to disk full.

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

Step 2: Create a dedicated resource plan for LOB consistency check

In the business tenant where you want to run the LOB consistency check, you can create a dedicated resource plan. You can map the LOB consistency check-related background tasks to the dedicated resource group and limit the CPU and IOPS. The following example creates a resource plan named LOB_CHECK and a resource group named LOB_CHECK. It maps the background tasks whose value is LOB_CHECK to the resource group by using the FUNCTION mapping. You must adjust the MGMT_P1, UTILIZATION_LIMIT, MAX_IOPS, MIN_IOPS, and WEIGHT_IOPS parameters according to your 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 you activate the resource management plan, the mapping and limits take effect for the session and background tasks:

SET GLOBAL resource_manager_plan = 'LOB_CHECK';

For more information about the resource_manager_plan parameter and its considerations, see resource_manager_plan.

For more information about the DBMS_RESOURCE_MANAGER procedures, parameters, and privileges, see DBMS_RESOURCE_MANAGER overview and Configure resource isolation within a tenant (Oracle mode).

Step 3: Configure a scheduled task (optional)

If you want to periodically run the LOB consistency check, you can manually enable the scheduled task. By default, the task is triggered at 4:00 a.m. on Sunday.

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

View the scheduled task information:

SELECT * FROM DBA_SCHEDULER_JOBS WHERE job_name='lob_check_job';

Step 4: Run the LOB consistency check

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

-- Trigger a consistency check for a specific table.
CALL DBMS_LOB_MANAGER.CHECK_LOB('[500001, 500003]');

-- Control the check task.
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 check results

You can view the progress of the LOB task in DBA_OB_LOB_CHECK_TASKS and the exception results in 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