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

During external storage linkage, the primary table and the LOB auxiliary table are associated through LOB IDs: the set of LOB IDs in the primary table locator must match the set of LOB IDs present in the LOB auxiliary table. Inconsistencies such as "LOB IDs present in the primary table but absent in the auxiliary table" or "LOB IDs present in the auxiliary table but absent in the primary table" can lead to read errors or prevent space from being reclaimed.

This feature uses a LOB task to scan the primary table and collect a set of LOB IDs. It then compares this set with the LOB IDs obtained from scanning the LOB auxiliary table, and outputs whether they are consistent and any discrepancy information. This facilitates 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 tables, which incurs significant I/O and CPU overhead. When imposing limits such as an I/O cap on a verification task, it relies on the disk I/O baseline of the dependent tenant (generated by I/O calibration). The following recommendations are provided for different deployment scenarios:

• Environments where I/O calibration has been completed by default, such as public clouds: In these cases, you can typically configure resource management plans and resource groups directly within the tenant.
• Private cloud or environments without I/O calibration: First, perform disk I/O calibration in the sys tenant. Then, plan the MAX_IOPS/MIN_IOPS for business tenants based on benchmarks, ensuring they align with the I/O quotas of the resource groups. For calibration commands, progress viewing, and resource isolation from background tasks, see Resource isolation for background tasks. For disk performance calibration steps, see 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 must first execute it in the sys tenant. The calibration may take 1 to 2 minutes to complete and take effect.

ALTER SYSTEM RUN JOB 'io_calibration';

After calibration, you can view the calibration task status through GV$OB_IO_CALIBRATION_STATUS and view the benchmark IOPS of each node through GV$OB_IO_BENCHMARK. When configuring the unit IOPS for a tenant, it is recommended to reserve about 10% margin based on the benchmark (for example, the IOPS corresponding to reading 16 KB). This helps prevent disk fullness from affecting online business latency.

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

Step 2: Create a dedicated resource plan for LOB verification

You can create a separate resource plan within the business tenant that requires verification, 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 and the resource group name is also LOB_CHECK. 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; /

The above mappings and quotas take effect for sessions and background tasks only after you activate the resource management plan.

SET GLOBAL resource_manager_plan = 'LOB_CHECK';

For the meaning and considerations of resource_manager_plan, see resource_manager_plan.

For more information about 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)

To periodically perform LOB consistency verification, you can manually enable a scheduled task. By default, the task is triggered every Sunday at 04:00.

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

View scheduling task information:

SELECT * FROM DBA_SCHEDULER_JOBS WHERE job_name='lob_check_job';

Step 4: Perform 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 (Verification Tasks Only)
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

You can query the DBA_OB_LOB_CHECK_TASKS view for the progress of current LOB tasks and query the DBA_OB_LOB_CHECK_EXCEPTION_RESULT view for exception results.

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