Know more about ORACLE’S RECYCLEBIN
Recyclebin回收站是oracle数据库对于drop table的一种回退机制，可以使用flashback drop特性闪回已drop还在recyclebin中的对象。纵观其他数据库像SQL Server也提供了类似功能， MySQL可以利用recycle_bin插件，但是作用在MySQL slave实例上，当在master实例上进行drop操作时，slave实例可以拦截drop操作，先进行数据备份再进行删除操作。 而在PostgreSQL中可以利用event trigger和pg_event_trigger_dropped_objects 来手动实现drop时 rename对象的方式变相实现recyclebin功能， 当然也可以使用一些插件如Pgtrashcan、postgresql-recycle-bin；在Opengauss中也增强了flashback的功能，但是需要注意更新方式是使用Ustore还是Astore, recyclebin不支持Ustore，只支持Astore,而且同样支持Truncate。
回收站为闪回操作提供了一层保障，10g引入后默认回收站功能是打开，可以在线实例级关闭，也可以SESSION级关闭， 或在drop是附加purge选项不进回收站， 进入回收站的对象占用原表空间相同的大小，删除的表和任何关联的对象（例如索引、约束、嵌套表和其他依赖对象）不会移动，它们只是使用前缀 BIN$$ 重命名,回收站不适用于SYS schema。回收站在某些情况下可能存在性能问题，最后会分享遇到过2个案例，记录一下1，library cache lock 2,增加额外逻辑读。
1，什么情况下会purge回收站
回收站中的对象将保留在数据库中，在空间充足的前提时，直到被删除对象的所有者决定使用新的 PURGE 命令永久删除它们，回收站对象计入用户配额。回收站中的对象在以下情况下也会automatically purged自动回收空间：
1， 用户创建新表或或数据增涨导致超出配额时；
2， 表空间空间紧张无法分配CREATE/INSERT空间请求时;
当然也存在几个已知BUG如 RECYCLEBIN does not automatically purge under space pressure
2， 回收站的对象purge顺序
2.1 如果数据文件autoextend on自动扩展数据文件时,同时回收站中也存在该表空间已经删除的对象时，当新的INSERT因表空间紧张，是扩展数据文件和purge 回收站中的对象哪个优先呢？
— env: oracle 19.3
SQL> create tablespace tbs_drop datafile '/u01/app/oracle/oradata/ANBOB19C/pdb1/tbs_drop01.dbf' size 4m autoextend on maxsize 200m;
Tablespace created.
SQL> create table anbob.obj1 tablespace tbs_drop as select * from dba_objects;
Table created.
SQL> @seg anbob.obj1;
SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
12 ANBOB OBJ1 TABLE TBS_DROP 1536 195 138
SQL> drop table anbob.obj1;
Table dropped.
SQL> select * from dba_recyclebin;
OWNER OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN CAN RELATED BASE_OBJECT PURGE_OBJECT SPACE
-------- ------------------------------ ---------------- --------- --------- ----------- ------------------- ------------------- ---------- ---------------- --- --- ---------- ----------- ------------ ----------
ANBOB BIN$383i8ep/Cy7gVTa3m8Jymg==$0 OBJ1 DROP TABLE TBS_DROP 2022-05-25:10:32:08 2022-05-25:10:32:37 35377478 YES YES 79500 79500 79500 1536
SQL> @seg anbob.BIN$383i8ep/Cy7gVTa3m8Jymg==$0
SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE
---------- -------------------- ------------------------------ ------------------------------ --------------------
SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
------------------------------ ---------- ---------- ----------
12 ANBOB BIN$383i8ep/Cy7gVTa3m8Jymg==$0 TABLE
TBS_DROP 1536 195 138
SQL> @df
Container Free Alloc
CON_ID Name TABLESPACE_NAME Num Files Space Meg. Space Meg. PCT
---------- --------------- ------------------------------ --------- ---------------- ---------------- ----------
3 PDB1 SYSAUX 1 24 440 .95
SYSTEM 1 237 710 .67
TBS_DROP 1 13 14 .08
TEMP 1 50
UNDOTBS1 1 209 1,955 .89
USERS 2 1,133 2,256 .5
SQL> @dfa
TABLESPACE_NAME TOTAL_SIZE FREE_SIZE RECYCLEBIN_SIZE PCT_USED PCT_FREE_IN_RECYCLEBIN
------------------------------ ---------- ---------- --------------- ---------- ----------------------
TBS_DROP 14680064 13565952 12582912 .08 .93
SQL> create table anbob.obj2 tablespace tbs_drop as select * from dba_objects;
Table created.
SQL> select * from dba_recyclebin;
no rows selected
SQL> @dfa
TABLESPACE_NAME TOTAL_SIZE FREE_SIZE RECYCLEBIN_SIZE PCT_USED PCT_FREE_IN_RECYCLEBIN
------------------------------ ---------- ---------- --------------- ---------- ----------------------
SYSTEM 744488960 248250368 .67
SYSAUX 461373440 25296896 .95
UNDOTBS1 2049966080 219283456 .89
TBS_DROP 14680064 983040 .93
USERS 2365849600 1188036608 .5
## db alert log
create tablespace tbs_drop datafile '/u01/app/oracle/oradata/ANBOB19C/pdb1/tbs_drop01.dbf' size 4m autoextend on maxsize 200m Completed: create tablespace tbs_drop datafile '/u01/app/oracle/oradata/ANBOB19C/pdb1/tbs_drop01.dbf' size 4m autoextend on maxsize 200m 2022-05-25 10:32:09.543000 +08:00 Resize operation completed for file# 195, old size 4096K, new size 5120K Resize operation completed for file# 195, old size 5120K, new size 6144K Resize operation completed for file# 195, old size 6144K, new size 7168K Resize operation completed for file# 195, old size 7168K, new size 8192K Resize operation completed for file# 195, old size 8192K, new size 9216K Resize operation completed for file# 195, old size 9216K, new size 10240K Resize operation completed for file# 195, old size 10240K, new size 11264K Resize operation completed for file# 195, old size 11264K, new size 12288K 2022-05-25 10:32:10.620000 +08:00 Resize operation completed for file# 195, old size 12288K, new size 13312K Resize operation completed for file# 195, old size 13312K, new size 14336K -- 每二次 创建12M表并未resize
结论：
当数据文件自动扩展打开和回收站中存在已删除对象，如果表空间申请新的空间不足时，purge回收站中此表空间已存在对象优先于resize 数据文件自动扩展，如果purge完回收站中的此表空间的对象，空间依旧不足才会数据文件自动扩展。
2.2， 如果回收站中有多个对象，是按drop的先后顺序FIFO 算法还是优先释放匹配大小的空间segment呢？
— env: Oracle 19.3
SQL> create table anbob.obj tablespace users as select * from dba_objects where rownum<=10; Table created. SQL> create index anbob.idx_obj on anbob.obj(object_id,object_name) tablespace tbs_drop; Index created. SQL> @seg anbob.obj SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK ---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- -- -------- ---------- 0 ANBOB OBJ TABLE USERS 8 12 27538 SQL> @seg anbob.idx_obj SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK ---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- -- -------- ---------- 0 ANBOB IDX_OBJ INDEX TBS_DROP 8 195 130 SQL> select bytes,BLOCKS from dba_segments where segment_name='OBJ' and owner='ANBOB'; BYTES BLOCKS ---------- ---------- 65536 8 create table anbob.obj10 tablespace tbs_drop as select * from anbob.obj; create table anbob.obj11 tablespace tbs_drop as select * from anbob.obj; create table anbob.obj12 tablespace tbs_drop as select * from anbob.obj; create table anbob.obj13 tablespace tbs_drop as select * from anbob.obj; create table anbob.obj14 tablespace tbs_drop as select * from anbob.obj; create table anbob.obj15 tablespace tbs_drop as select * from anbob.obj; create table anbob.obj16 tablespace tbs_drop as select * from anbob.obj; create table anbob.obj17 tablespace tbs_drop as select * from anbob.obj; create table anbob.obj18 tablespace tbs_drop as select * from anbob.obj; create table anbob.obj19 tablespace tbs_drop as select * from anbob.obj; begin for i in 10..19 loop begin dbms_output.put_line(i); dbms_lock.sleep(2); execute immediate 'drop table anbob.obj'||i; end; end loop; end; SQL> drop table anbob.obj; Table dropped. SQL> drop table anbob.obj2; Table dropped. SQL> SELECT S.TABLESPACE_NAME, r.owner,OBJECT_NAME,ORIGINAL_NAME,SPACE,TS_NAME,DROPTIME,DROPSCN,RELATED,BASE_OBJECT,PURGE_OBJECT FROM DBA_SEGMENTS S, DBA_RECYCLEBIN R WHERE S.OWNER = R.OWNER AND S.SEGMENT_NAME = R.OBJECT_NAME order by DROPSCN; TABLESPACE_NAME OWNER OBJECT_NAME ORIGINAL_N SPACE TS_NAME DROPTIME DROPSCN RELATED BASE_OBJECT PURGE_OBJECT ---------------- ---------- ------------------------------ ---------- ---------- --------------- ------------------- ---------- ---------- ----------- ------------ TBS_DROP ANBOB BIN$383i8eqACy7gVTa3m8Jymg==$0 OBJ10 8 TBS_DROP 2022-05-25:11:10:17 35380344 79502 79502 79502 ANBOB BIN$383i8eqBCy7gVTa3m8Jymg==$0 OBJ11 8 TBS_DROP 2022-05-25:11:10:19 35380358 79503 79503 79503 ANBOB BIN$383i8eqCCy7gVTa3m8Jymg==$0 OBJ12 8 TBS_DROP 2022-05-25:11:10:21 35380371 79504 79504 79504 ANBOB BIN$383i8eqDCy7gVTa3m8Jymg==$0 OBJ13 8 TBS_DROP 2022-05-25:11:10:23 35380383 79505 79505 79505 ANBOB BIN$383i8eqECy7gVTa3m8Jymg==$0 OBJ14 8 TBS_DROP 2022-05-25:11:10:25 35380395 79506 79506 79506 ANBOB BIN$383i8eqFCy7gVTa3m8Jymg==$0 OBJ15 8 TBS_DROP 2022-05-25:11:10:27 35380407 79507 79507 79507 ANBOB BIN$383i8eqGCy7gVTa3m8Jymg==$0 OBJ16 8 TBS_DROP 2022-05-25:11:10:30 35380419 79508 79508 79508 ANBOB BIN$383i8eqHCy7gVTa3m8Jymg==$0 OBJ17 8 TBS_DROP 2022-05-25:11:10:32 35380431 79509 79509 79509 ANBOB BIN$383i8eqICy7gVTa3m8Jymg==$0 OBJ18 8 TBS_DROP 2022-05-25:11:10:34 35380443 79510 79510 79510 ANBOB BIN$383i8eqJCy7gVTa3m8Jymg==$0 OBJ19 8 TBS_DROP 2022-05-25:11:10:36 35380455 79511 79511 79511 ANBOB BIN$383i8eqKCy7gVTa3m8Jymg==$0 IDX_OBJ 8 TBS_DROP 2022-05-25:11:13:10 35380542 79498 79498 79499 USERS ANBOB BIN$383i8eqLCy7gVTa3m8Jymg==$0 OBJ 8 USERS 2022-05-25:11:13:10 35380545 79498 79498 79498 TBS_DROP ANBOB BIN$383i8eqMCy7gVTa3m8Jymg==$0 OBJ2 1536 TBS_DROP 2022-05-25:11:13:37 35380567 79501 79501 79501 13 rows selected. SQL> create table anbob.obj3 tablespace tbs_drop as select * from dba_objects; Table created. SQL> SELECT S.TABLESPACE_NAME, r.owner,OBJECT_NAME,ORIGINAL_NAME,SPACE,TS_NAME,DROPTIME,DROPSCN,RELATED,BASE_OBJECT,PURGE_OBJECT FROM DBA_SEGMENTS S, DBA_RECYCLEBIN R WHERE S.OWNER = R.OWNER AND S.SEGMENT_NAME = R.OBJECT_NAME order by DROPSCN; TABLESPACE_NAME OWNER OBJECT_NAME ORIGINAL_N SPACE TS_NAME DROPTIME DROPSCN RELATED BASE_OBJECT PURGE_OBJECT ------------------------------ ---------- ------------------------------ ---------- ---------- --------------- ------------------- ---------- ---------- ----------- ------------ USERS ANBOB BIN$383i8eqLCy7gVTa3m8Jymg==$0 OBJ 8 USERS 2022-05-25:11:13:10 35380545 79498 79498 79498 SQL> @seg anbob.obj3 SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK ---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ---------- 12 ANBOB OBJ3 TABLE TBS_DROP 1536 195 226
这个测试
1, 创建了1个users表空间的表和它的1个索引在tbs_drop表空间，判断跨表空间的对象如何处理，大小64k
2, 同样又复制了10个64k的表，
3, 按顺序先删除10个小表obj10-19，再删跨表空间的表obj,再删除12M的obj2,
4, 再创建和obj2相同大小的OBJ3
结论：
如果是按大小顺序，那理论上是purge掉obj2 就可以放的下obj3.但结果并不是，而是按按删除顺序，直到最后一个purge obj2，释放出的空间才能放到的obj3, 确认并不是按空间大小优先级，而是按删除顺序， 这里USERS表空间的obj表上所创建的索引idx_obj（在tbs_drop表空间）也被自动清理，表obj因为在users表空间不受影响依旧在回收站中。
之前遇到的案例清理回收站的递归SQL一般是delete from RecycleBin$ where bo=:1， BO表示的基于表对象的OBJ ID, 如索引不可能独立存在，它是表的附属对象，所以索引的BO=TABLE的BO.上面的查询dba_recyclebin 记录可以关注列值。查看视图定义
SQL> @v dba_recyclebin
Show SQL text of views matching "%dba_recyclebin%"...
V_OWNER VIEW_NAME TEXT
------------------------- ------------------------------ ----------------------------------------------------------------------------------------------------
SYS DBA_RECYCLEBIN select u.name, o.name, r.original_name,
decode(r.operation, 0, 'DROP', 1, 'TRUNCATE', 'UNDEFINED'),
decode(r.type#, 1, 'TABLE', 2, 'INDEX', 3, 'INDEX',
4, 'NESTED TABLE', 5, 'LOB', 6, 'LOB INDEX',
7, 'DOMAIN INDEX', 8, 'IOT TOP INDEX',
9, 'IOT OVERFLOW SEGMENT', 10, 'IOT MAPPING TABLE',
11, 'TRIGGER', 12, 'CONSTRAINT', 13, 'Table Partition',
14, 'Table Composite Partition', 15, 'Index Partition',
16, 'Index Composite Partition', 17, 'LOB Partition',
18, 'LOB Composite Partition',
'UNDEFINED'),
t.name,
to_char(o.ctime, 'YYYY-MM-DD:HH24:MI:SS'),
to_char(r.droptime, 'YYYY-MM-DD:HH24:MI:SS'),
r.dropscn, r.partition_name,
decode(bitand(r.flags, 4), 0, 'NO', 4, 'YES', 'NO'),
decode(bitand(r.flags, 2), 0, 'NO', 2, 'YES', 'NO'),
r.related, r.bo, r.purgeobj, r.space
from sys."_CURRENT_EDITION_OBJ" o, sys.recyclebin$ r, sys.user$ u, sys.ts$ t
where o.obj# = r.obj#
and r.owner# = u.user#
and r.ts# = t.ts#(+)
no rows selected
SQL> @desc dba_recyclebin
Name Null? Type
------------------------------- -------- ----------------------------
1 OWNER NOT NULL VARCHAR2(128)
2 OBJECT_NAME NOT NULL VARCHAR2(128)
3 ORIGINAL_NAME VARCHAR2(128)
4 OPERATION VARCHAR2(9)
5 TYPE VARCHAR2(25)
6 TS_NAME VARCHAR2(30)
7 CREATETIME VARCHAR2(19)
8 DROPTIME VARCHAR2(19)
9 DROPSCN NUMBER
10 PARTITION_NAME VARCHAR2(128)
11 CAN_UNDROP VARCHAR2(3)
12 CAN_PURGE VARCHAR2(3)
13 RELATED NOT NULL NUMBER
14 BASE_OBJECT NOT NULL NUMBER
15 PURGE_OBJECT NOT NULL NUMBER
16 SPACE NUMBER
上面我们看到TBS_DROP表空间因空间不足auto purge掉了索引，并不是把BO#的全drop ，因为相同bo#的table在另一个表空间而未受影响。 那是否清理索引递归SQL不同？下面做个sql trace.
1, 创建个跨表空间的表和索引，索引在表空间tbs_drop
2, 创建表和索引都在表空间tbs_drop
3, 删除1# 2#创建的表，进入回收站
4，创建个大表填充tbs_drop 查看purge回收站的方法
SQL> flashback table anbob.obj to before drop;
Flashback complete.
SQL> create index anbob.idx_obj on anbob.obj(object_id,object_name) tablespace tbs_drop;
Index created.
SQL> create table anbob.obj19 tablespace tbs_drop as select * from anbob.obj;
Table created.
SQL> create index anbob.idx_19 on anbob.obj19(object_id) tablespace tbs_drop;
Index created.
SQL> drop table anbob.obj;
Table dropped.
SQL> drop table anbob.obj19;
Table dropped.
SQL> select obj#, type#, flags, related, bo, purgeobj, con# from RecycleBin$;
OBJ# TYPE# FLAGS RELATED BO PURGEOBJ CON#
---------- ---------- ---------- ---------- ---------- ---------- ----------
79540 1 30 79540 79540 79540 0
79538 2 18 79498 79498 79538 0
79498 1 30 79498 79498 79498 0 --users tablespace
79541 2 18 79540 79540 79541 0
SQL> @46on 12
Session altered.
SQL> create table anbob.obj6 tablespace tbs_drop as select * from dba_objects;
Table created.
SQL> @46off
Session altered.
SQL> @t
TRACEFILE
-----------------------------------
/u01/app/oracle/diag/rdbms/anbob19c/anbob19c/trace/anbob19c_ora_6437.trc
SQL> select obj#, type#, flags, related, bo, purgeobj, con# from RecycleBin$;
OBJ# TYPE# FLAGS RELATED BO PURGEOBJ CON#
---------- ---------- ---------- ---------- ---------- ---------- ----------
79498 1 30 79498 79498 79498 0
[oracle@oel7db1 ~]$ egrep -i recyclebin -n /u01/app/oracle/diag/rdbms/anbob19c/anbob19c/trace/anbob19c_ora_6437.trc|grep -v 'STAT'
45121:select obj#, type#, flags, related, bo, purgeobj, con# from RecycleBin$ where ts#=:1 and to_number(bitand(flags, 16)) = 16 order by dropscn
8267:select dropscn from recyclebin$ where obj#=:1
48555:delete from RecycleBin$ where purgeobj=:1
49807:delete from RecycleBin$ where purgeobj=:1
50935:select dropscn from recyclebin$ where obj#=:1
51963:delete from RecycleBin$ where bo=:1
结论：
多次尝试相同的现象，个数匹配，递归SQL显示先是根据dropscn排序查看该表空间中的对象，dropscn#为 redo change#, 表和所属索引并不相同，对于table 类型使用的是OB#，而对于索引对象使用的是purgeobj，BIND值同样可以确认对象，这样做想想也合理，先删除索引，使用purgeobj 如果空间满足要求，表可以不用清理，而删除表时使用bo,这样会级联删除索引(包括其它表空间的对象)， 因为索引不能脱离了表独立存在。
3， 如何监控回收站占比
with x as (select tablespace_name, SUM(bytes) sz from dba_data_files group by tablespace_name)
,y as (select tablespace_name, SUM(bytes) fsz from dba_free_space group by tablespace_name)
,z as (SELECT
S.TABLESPACE_NAME,
COUNT(*) NUM_SEGMENTS,
SUM(BYTES) bin_sz
FROM
DBA_SEGMENTS S,
DBA_RECYCLEBIN R
WHERE
S.OWNER = R.OWNER AND
S.SEGMENT_NAME = R.OBJECT_NAME
GROUP BY
S.TABLESPACE_NAME)
select x.tablespace_name,x.sz total_size,y.fsz free_size, z.bin_sz recyclebin_size,round(1-fsz/sz,2) pct_used,round(bin_sz/fsz,2) pct_free_in_recyclebin from x,y,z where x.tablespace_name = y.tablespace_name(+) and x.tablespace_name=z.tablespace_name(+)
TABLESPACE_NAME TOTAL_SIZE FREE_SIZE RECYCLEBIN_SIZE PCT_USED PCT_FREE_IN_RECYCLEBIN
------------------------------ ---------- ---------- --------------- ---------- ----------------------
TBS_DROP 14680064 13565952 12582912 .08 .93
4， 故障案例
a) 案例1在 oracle recyclebin过大导致的insert逻辑读暴增问题记录了该问题，insert values的操作产生了大量的逻辑读，如果可以复现， 启用SQL trace可以跟踪到对象或SQL 是在delete recyclebin$，在auto purge recycelbin reuse空间。 如果bo列不存在索引，所以FTS回收站对象大，导致回收站基表逻辑读增加。
临时解决方法是给recyclebin$ BO列创建索引.
b) 案例2在《Troubleshooting library cache lock when concurrent insert》记录， 持续了几十秒的library cache lock， 之前我分享过格式化P3值的方法，案例中是X mode的对象id, 在数据库中查询不存在， 也是简单的insert value, 如果分析SQL的history执行情况也有可能发现案例1的问题（逻辑读增加）， 但当时会话显示library cache lock, ASH视图可以作为参考 IN_PARSE， IN_SQL_EXECUTION，IS_SQLID_CURRENT，本案例是IN_SQL_EXECUTION：Y和IS_SQLID_CURRENT：N，应该是递归。 未存在trigger,audit等信息， 而且P3值指向所对象都不存在， 最终尝试logminer分析归档日志以对象号为关键字，查到同时间段确实是delete from “SYS”.”RECYCLEBIN$” where “OBJ#” = xxx的操作，也是回收站空间的reuse.
5， 回收站的清理策略
截止到当前的19c 在recyclebin$表都未预创建BO索引，可见不希望回收站存在过多的数据，除以上案例性能影响外，比如查询表空间使用率时也会因为回收站对象过多影增加查询时长，回收站目前没有自动的清理策略，可以自己编写job任务，检空回收站对象所占free space的比例，或根据回收站中的droptime日期定时清理。
— over —
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。