Why OceanBase DDL Expansion Can Crash Your Service and How to Fix It

Fault Background

A customer migrated a production environment from Oracle to OceanBase (OB). The DBA altered a column from varchar2(20) to varchar2(100), which caused a service outage.

Business Impact

Application reported "Connection is closed" errors.

INSERT response time increased significantly.

Read/write operations on the affected table were blocked.

Emergency Handling

Kill the DDL session.

Restart the application services.

Issue Reproduction

Create Table and Insert Data

-- 01: create table (adopted from Oracle schema)
CREATE TABLE TEST_ONLINE_DDL (
  ID INT,
  COL_01 VARCHAR2(20),               -- normal column
  COL_02 VARCHAR2(20) NOT NULL ENABLE VALIDATE,
  COL_03 VARCHAR2(20) NOT NULL ENABLE NOVALIDATE
);

-- 02: insert test data
INSERT INTO TEST_ONLINE_DDL VALUES
  (1,'NOVAL_01','VALIDATE_01','NORMAL_北京'),
  (2,'NOVAL_02','VALIDATE_02',''),
  (3,'NOVAL_03','VALIDATE_03','NORMAL_上海浦东');
COMMIT;

-- 03: repeat n times
INSERT INTO TEST_ONLINE_DDL SELECT * FROM TEST_ONLINE_DDL;
COMMIT;

Data Migration

The table is migrated from Oracle to OB using OMS (details omitted).

Table Structure Comparison

After migration, the OB DDL differs in the handling of COL_03:

Oracle: "COL_03" VARCHAR2(20) NOT NULL ENABLE NOVALIDATE OceanBase:

"COL_03" VARCHAR2(20), CHECK (("COL_03" is not null)) ENABLE NOVALIDATE

Oracle’s NOT NULL ENABLE NOVALIDATE is converted to a CHECK constraint in OB.

DDL Comparison

ALTER TABLE TEST_ONLINE_DDL MODIFY COL_01 VARCHAR2(100);
ALTER TABLE TEST_ONLINE_DDL MODIFY COL_02 VARCHAR2(100);
ALTER TABLE TEST_ONLINE_DDL MODIFY COL_03 VARCHAR2(100);

Performance impact:

COL_01 – seconds, table_id unchanged, no DML block.

COL_02 – seconds, table_id unchanged, no DML block.

COL_03 – minutes, table_id changed, DML blocked.

Root Cause Analysis

The core issue is the incompatibility between the OMS conversion logic and OceanBase’s DDL change rules.

OMS Conversion Rule

During Oracle‑to‑OB migration, OMS automatically converts NOT NULL ENABLE NOVALIDATE to a NOT NULL CHECK constraint with a generated name like %_OBCHECK_%. Although functionally equivalent, this triggers OceanBase’s offline DDL downgrade.

Note: Fixed in OMS 4.3.2.

OceanBase DDL Change Rule

When a CHECK constraint depends on a column that is being lengthened, OceanBase degrades the operation from online DDL to offline DDL, rebuilding the table and holding an MDL lock, which blocks DML.

Special note: Similar downgrade occurs for primary keys, partition keys, or any CHECK constraint that becomes invalid after a column change. The online DDL fix is pending in future releases.

Solution

The remediation consists of two steps: restore the original NOT NULL attribute and drop the redundant NOT NULL CHECK constraint.

Generate ALTER statements to restore plain NOT NULL

SELECT 'ALTER TABLE '||C.OWNER||'.'||C.TABLE_NAME||' MODIFY ('||COL.COLUMN_NAME||' NOT NULL ENABLE NOVALIDATE);'
FROM dba_constraints c
JOIN dba_cons_columns col ON c.constraint_name=col.constraint_name
WHERE c.constraint_type='C'
  AND c.owner='WMS'
  AND c.status='ENABLED'
  AND c.VALIDATED='NOT VALIDATED'
  AND c.constraint_name LIKE '%_OBCHECK_%'
  AND c.search_condition LIKE '%is not null%'
ORDER BY c.owner,c.table_name,col.column_name;

Drop the redundant NOT NULL CHECK constraints

SELECT 'ALTER TABLE '||C.OWNER||'.'||C.TABLE_NAME||' DROP CONSTRAINT '||C.CONSTRAINT_NAME||' ;'
FROM dba_constraints c
WHERE c.constraint_type='C'
  AND c.owner='WMS'
  AND c.status='ENABLED'
  AND c.VALIDATED='NOT VALIDATED'
  AND c.constraint_name LIKE '%_OBCHECK_%'
  AND c.search_condition LIKE '%is not null%'
ORDER BY c.owner,c.table_name;

Operation Time Window

Reverse link exists – after forward switch, before reverse incremental start: no reverse DDL sync, compresses cut‑over window.

Reverse link exists – after reverse link start: does not affect cut‑over window, requires setting source.ignoreDdl=TRUE in OMS component.

No reverse link – business low‑peak period: low business impact.

How to Determine Whether a DDL Is Online

DBA should verify if the DDL operation changes the table’s TABLE_ID. If the ID stays the same, the DDL is executed online; if it changes, the operation was downgraded to offline DDL.

Query TABLE_ID Before DDL

SELECT DISTINCT(TABLE_ID)
FROM DBA_OB_TABLE_LOCATIONS
WHERE TABLE_NAME='target_table_name';

Execute DDL (e.g., modify column length)

ALTER TABLE target_table_name MODIFY column_name VARCHAR2(new_length);

Query TABLE_ID After DDL and Compare

If TABLE_ID remains unchanged : the DDL was online, no table rebuild, DML continues normally.

If TABLE_ID changes : the DDL was downgraded to offline, the table was rebuilt and a global lock was held, potentially blocking DML.

This method does not apply to partition‑key or primary‑key operations.

References

Column type change rules: https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000002016923

How to judge Online DDL: https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000003980674