首页 » OceanBase » OceanBase中支持闪回Truncate表吗？（v3 VS v4）

OceanBase中支持闪回Truncate表吗？（v3 VS v4）

Oracle DBA都知道Oracle的回收站功能可以做drop table后的flashback操作，之前在网上我也做过这方面的分享<Know more about ORACLE’S RECYCLEBIN>，目前部分国产数据库在回收站功能上做了flashback truncate table的支持，像GaussDB (ustore), GoldenDB, Oceanbase同样也支持回收站的功能，今天看到一篇文档， 是有人问Oceanbase支持truncate吗？利用deepseek回答的，答案也对，也不对，只是不够严谨，这可能也是目前国产数据库在快速迭代期的现状，相对oracle，在国产库上大部分DBA实战经验不足，或因资源没有环境，导致多是文档类专家，这也就和deepseek类大模型一样，很容易产生错误的后果。

这是我在GaussDB的测试 体验一下GaussDB集中式(本地部署)的Flashback/TIMECAPSULE 闪回功能

先看AI回答
Deepseek公网

Deepseek带深度思考

某私域数据库AI

下面我在Oceanbase V4.2 动手测试一下
1，测试drop table
2，测试truncate table

[root@anbob.com ~]# obclient -h192.168.56.100 -P2883 -usys@ob_ora
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 11
Server version: OceanBase 4.2.1.2 (r102010012023120119-130bf91ba413a00bb696fe8853906fde1f29f83d) (Built Dec  1 2023 20:00:25)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


obclient [SYS]> select sysdate from dual;
+-----------+
| SYSDATE   |
+-----------+
| 07-APR-25 |
+-----------+
1 row in set (0.049 sec)


obclient [SYS]>  SHOW VARIABLES LIKE 'recyclebin';
+---------------+-------+
| VARIABLE_NAME | VALUE |
+---------------+-------+
| recyclebin    | OFF   |
+---------------+-------+
1 row in set (0.193 sec)

obclient [SYS]> SET recyclebin = on;     --- set [Global]  recyclebin = xx 回收站开关
Query OK, 0 rows affected (0.188 sec)

obclient [SYS]> SHOW VARIABLES LIKE 'recyclebin';
+---------------+-------+
| VARIABLE_NAME | VALUE |
+---------------+-------+
| recyclebin    | ON    |
+---------------+-------+
1 row in set (0.034 sec)


obclient [SYS]> create table test_drop (id int);
Query OK, 0 rows affected (12.081 sec)

obclient [SYS]>  SHOW RECYCLEBIN;
Empty set (0.051 sec)

obclient [SYS]> create index idx_test_drop_id on test_drop(id);
Query OK, 0 rows affected (37.705 sec)

obclient [SYS]> insert into test_drop values(1);
Query OK, 1 row affected (1.285 sec)

obclient [SYS]> commit;
Query OK, 0 rows affected (0.921 sec)

obclient [SYS]> SHOW VARIABLES LIKE 'recyclebin';
+---------------+-------+
| VARIABLE_NAME | VALUE |
+---------------+-------+
| recyclebin    | ON    |
+---------------+-------+
1 row in set (0.066 sec)

obclient [SYS]> show recyclebin;
Empty set (0.178 sec)

## 测试drop

obclient [SYS]> drop table test_drop;
Query OK, 0 rows affected (4.124 sec)

obclient [SYS]> show recyclebin;
+------------------------------+-------------------------------+-------+------------------------------+
| OBJECT_NAME                  | ORIGINAL_NAME                 | TYPE  | CREATETIME                   |
+------------------------------+-------------------------------+-------+------------------------------+
| RECYCLE_$_1_1744018087127440 | __idx_500014_IDX_TEST_DROP_ID | INDEX | 07-APR-25 05.28.07.531431 PM |
| RECYCLE_$_1_1744018088617104 | TEST_DROP                     | TABLE | 07-APR-25 05.28.08.582432 PM |
+------------------------------+-------------------------------+-------+------------------------------+
2 rows in set (0.062 sec)

obclient [SYS]> select * from TEST_DROP;
ORA-00942: table or view 'SYS.TEST_DROP' does not exist


obclient [SYS]> flashback table TEST_DROP to before drop;
Query OK, 0 rows affected (1.431 sec)

obclient [SYS]> select * from TEST_DROP;
+------+
| ID   |
+------+
|    1 |
+------+
1 row in set (0.156 sec)

obclient [SYS]> show recyclebin;
Empty set (0.221 sec)


obclient [SYS]> select INDEX_NAME from dba_indexes where TABLE_NAME='TEST_DROP';
+--------------------------------+
| INDEX_NAME                     |
+--------------------------------+
| RECYCLE_OBIDX_1744018222289605 |
+--------------------------------+
1 row in set (0.632 sec)

obclient [SYS]> SELECT i.index_name, i.index_type, i.uniqueness,
    ->        c.column_name, c.column_position
    -> FROM user_indexes i
    -> JOIN user_ind_columns c ON i.index_name = c.index_name
    -> WHERE i.table_name = 'TEST_DROP';
+--------------------------------+------------+------------+-------------+-----------------+
| INDEX_NAME                     | INDEX_TYPE | UNIQUENESS | COLUMN_NAME | COLUMN_POSITION |
+--------------------------------+------------+------------+-------------+-----------------+
| RECYCLE_OBIDX_1744018222289605 | NORMAL     | NONUNIQUE  | ID          |               1 |
+--------------------------------+------------+------------+-------------+-----------------+
1 row in set (4.406 sec)


## 测试truncate

obclient [SYS]> show variables like 'ob_enable_truncate_flashback';
+------------------------------+-------+
| VARIABLE_NAME                | VALUE |
+------------------------------+-------+
| ob_enable_truncate_flashback | OFF   |
+------------------------------+-------+
1 row in set (0.352 sec)

obclient [SYS]> set ob_enable_truncate_flashback = on;
Query OK, 0 rows affected (0.147 sec)

obclient [SYS]> show variables like 'ob_enable_truncate_flashback';
+------------------------------+-------+
| VARIABLE_NAME                | VALUE |
+------------------------------+-------+
| ob_enable_truncate_flashback | ON    |
+------------------------------+-------+
1 row in set (0.115 sec)


obclient [SYS]> truncate table TEST_DROP;
Query OK, 0 rows affected (4.676 sec)

obclient [SYS]> show recyclebin;
Empty set (0.121 sec)

obclient [SYS]> show variables like 'ob_enable_truncate_flashback';
+------------------------------+-------+
| VARIABLE_NAME                | VALUE |
+------------------------------+-------+
| ob_enable_truncate_flashback | ON    |
+------------------------------+-------+
1 row in set (0.149 sec)

obclient [SYS]> select * from v$version;
+---------------------------------------------------------------------------------------------------------------+--------+
| BANNER                                                                                                        | CON_ID |
+---------------------------------------------------------------------------------------------------------------+--------+
| OceanBase 4.2.1.2 (r102010012023120119-130bf91ba413a00bb696fe8853906fde1f29f83d) (Built Dec  1 2023 20:00:25) |      0 |
+---------------------------------------------------------------------------------------------------------------+--------+
1 row in set (0.261 sec)

可见DROP TABLE可以进回收站，但是truncate table没有， 去官方看一下参数的解释。

官方文档OB v4版本ob_enable_truncate_flashback参数章节

ob_enable_truncate_flashback 用于设置是否启用表截断的闪回。

“该变量已不再使用。在 V4.x 版本中，执行 TRUNCATE TABLE 操作不再支持进入回收站。尽管在 V4.x 版本中，该变量仍可查询和设置，但相关功能并不会生效。”

那直接说结论，truncate table 在回收站打开，并且ob_enable_truncate_flashback打开时，在v3版本是可以进回收站的，但是在V4 版本中不再支持。 哪为什么不支持了呢？因为机制发生了改变。

在官方博客上有一篇博主写的比较详细，建议阅读<浅析 OceanBase 4.x 版本的 truncate table 为什么不进回收站了？>，意思是在Oceanbase v4之前版本, truncate table 使用的是 drop table + create table,  而drop table是进__recyclebin的内部库改名， 所以之前truncate 复用了drop的功能，OceanBase 在 4.x 版本新增加了一个 tablet 的概念，如同oracle中的segment与tables的关系.  这一引入，解决的是当有大量业务对象truncate时，在表及所有附属对象元信息在truncate 的re-create时的开销.  直接换成了类似Oracle 的segment操作和修改data_object_id的内部操作.

 

— over —

 

打赏

,

目前这篇文章还没有评论(Rss)

我要评论

上一篇：

下一篇：