【有问必答】如何查询表结构变更记录（DDL 执行记录）？

官方精选 有问必答
#1

OCEANBASE怎么查询表结构变更记录啊

#3

业务租户：

select o.tenant_id, o.gmt_create,  o.database_id, d.database_name, o.table_id, t.table_name, o.operation_type, o.ddl_stmt_str 
from oceanbase.__all_ddl_operation o left join oceanbase.`__all_database` d on (o.database_id=d.database_id)
left join oceanbase.__all_table t on (o.table_id=t.table_id)
where o.gmt_create >='2024-10-09 14:30:17'
order by o.gmt_create ;
4 个赞
#5

在 obpilot 大神的基础上加了一个 o.ddl_stmt_str != ‘’ and t.table_name = ‘xxx’ 的过滤条件~

select o.tenant_id, o.gmt_create,  o.database_id, d.database_name, o.table_id, t.table_name, o.operation_type, o.ddl_stmt_str 
from oceanbase.__all_ddl_operation o left join oceanbase.`__all_database` d on (o.database_id=d.database_id)
left join oceanbase.__all_table t on (o.table_id=t.table_id)
where o.ddl_stmt_str != '' and t.table_name = 't1'
order by o.gmt_create ;


obclient [test]> create table t1(c1 int);
Query OK, 0 rows affected (0.125 sec)

obclient [test]> alter table t1 add column c2 int;
Query OK, 0 rows affected (0.120 sec)

obclient [test]> select o.tenant_id, o.gmt_create,  o.database_id, d.database_name, o.table_id, t.table_name, o.operation_type, o.ddl_stmt_str
    -> from oceanbase.__all_ddl_operation o left join oceanbase.`__all_database` d on (o.database_id=d.database_id)
    -> left join oceanbase.__all_table t on (o.table_id=t.table_id)
    -> where o.ddl_stmt_str != '' and t.table_name = 't1'
    -> order by o.gmt_create ;
+-----------+----------------------------+-------------+---------------+----------+------------+----------------+----------------------------------+
| tenant_id | gmt_create                 | database_id | database_name | table_id | table_name | operation_type | ddl_stmt_str                     |
+-----------+----------------------------+-------------+---------------+----------+------------+----------------+----------------------------------+
|         0 | 2024-10-11 10:09:16.898112 |      500001 | test          |   500008 | t1         |              4 | create table t1(c1 int)          |
|         0 | 2024-10-11 10:09:28.312967 |      500001 | test          |   500008 | t1         |              3 | alter table t1 add column c2 int |
+-----------+----------------------------+-------------+---------------+----------+------------+----------------+----------------------------------+
2 rows in set (0.003 sec)
4 个赞
#6

oceanbase.__all_ddl_operation 这张内部表里记录了所有 DDL 的执行记录，大家可以把它用起来。

把这个问题放到 “有问必答” 板块，是因为无意中在网上看到了一篇博客 “【OceanBase小知识】—— 谁动了我的表结构”，觉得好像确实值得拿出来和大家分享下~

最后再顺手推荐一下 @靖顺 的博客 —— 数据库练兵场

1 个赞