OceanBase数据库查看表历史DDL操作记录

对于3.1.5-OceanBase CE版本

1.查询table_id

SELECT 
    gmt_create,
    operation_type,
    ddl_stmt_str,
    table_id
FROM oceanbase.__all_ddl_operation
WHERE ddl_stmt_str like '%your_table_name%'
ORDER BY gmt_create DESC
limit 100;

其中your_table_name替换为实际的表名，或者已知的执行的过的DDL字符串，比如我是这样的：

SELECT 
    gmt_create,
    operation_type,
    ddl_stmt_str,
    table_id
FROM oceanbase.__all_ddl_operation
WHERE ddl_stmt_str like '%ALTER TABLE llm_rule ADD COLUMN version%'
ORDER BY gmt_create DESC
limit 100;

因为同一个数据库可能多次删除和创建了同名的表，所以要仔细 辨别当前表的table_id到底是哪一个。

2.查询操作记录

SELECT 
    gmt_create AS 操作时间,
    operation_type AS 操作类型,
    ddl_stmt_str
FROM oceanbase.__all_ddl_operation
WHERE table_id = your_table_id and ddl_stmt_str !=''
ORDER BY gmt_create DESC;

其中your_table_id替换为上一步查到的实际table_id

对于5.7.25-OceanBase_CE-v4.3.5.5版本

1.根据表名查询table_id

SELECT 
    t.table_id,
    t.table_name
FROM oceanbase.__all_table t
WHERE t.table_name = 'your_table_name';

2.查询操作记录

SELECT 
    gmt_create AS 操作时间,
    operation_type AS 操作类型,
    ddl_stmt_str
FROM oceanbase.__all_ddl_operation
WHERE table_id = your_table_id and ddl_stmt_str !=''
ORDER BY gmt_create DESC;