【OceanBase 小知识】—— 表结构变更记录查询

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 = 'xxxx'
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-31 10:19:16.975028 |      500001 | test          |   500008 | t1         |              4 | create table t1(c1 int)          |
|         0 | 2024-10-31 10:19:28.739826 |      500001 | test          |   500008 | t1         |              3 | alter table t1 add column c2 int |
+-----------+----------------------------+-------------+---------------+----------+------------+----------------+----------------------------------+
2 rows in set (0.004 sec)