Oracle RECYCLEBIN & PURGE

# What is recycle bin in oracle

• The Recycle Bin is a virtual container where all dropped objects reside.
  （回收站是一个虚拟容器，所有删除的对象都存放在其中)
• The dropped objects occupy the same space as when they were created.
  （被删除的对象占用的空间与创建时的空间相同。）
• Once the tables are dropped any associated objects such as indexes, constraints, nested tables, and other dependent objects are prefixed with BIN$, and they are not moved. （删除表后，任何关联的对象（如索引、约束、嵌套表和其他依赖对象）都以 BIN$ 为前缀，并且不会移动它们。）
• You can continue to access the data in a dropped table or even use Flashback Query against it. Each user has the same rights and privileges on Recycle Bin objects before they were dropped. You can view your dropped tables by querying the new RECYCLEBIN view.
  （您可以继续访问已删除表中的数据，甚至可以对其使用闪回查询。每个用户在删除回收站对象之前对回收站对象具有相同的权限和特权。可以通过查询新的回收站视图来查看已删除的表。）

# RECYCLEBIN

# For a user

• 都是可查看回收站中当前用户的对象

SELECT * FROM RECYCLEBIN;

SELECT Object_Name, Original_Name, Type FROM User_Recyclebin;

# For system-wide

• 可查看所有回收站的对象，但是需要 DBA 角色或相应权限才能访问该视图。

SELECT Object_Name, Original_Name, Type FROM dba_Recyclebin;

# DBA_SYNONYMS

• RECYCLEBIN 与 User_Recyclebin 相同，他们都是同一个视图创建出来的，只不过同义词名称不同。

select * from dba_synonyms where table_name like '%'||upper('recyclebin')||'%';

# Recycle Bin Status

• 通过初始化参数检查 oracle 中是否启用了回收站

show parameter recycle_bin;

• Open RecycleBin

alter system set recyclebin=on deferred;

• Close RecycleBin

alter system set recyclebin=off deferred;

• 注意如果不加 deferred 关键字，会发生错误： ORA-02096。同时该参数对当前会话是不会发生作用，需要开启新的会话。

# Clear Recycle Bin

• 对象被删除时，系统会对回收站对象命名，格式：BIN$unique_id$version

# PURGE TABLE

PURGE TABLE test.temp;

# PURGE RECYCLEBIN

PURGE RECYCLEBIN;

# PURGE INDEX

PURGE INDEX <INDEX Name>

# PURGE TABLESPACE

PURGE TABLESPACE <Tablespace name>;

PURGE TABLESPACE <Tablespace name>  <User name>;

# FLASHBACK

FLASHBACK TABLE TEMP TO BEFORE DROP;

# RECYCLEBIN OBJECT_NAME

create table temp as select * from <OBJECT_NAME>

• 参考文档：
  • https://techgoeasy.com/recycle-bin-in-oracle
  • https://blog.csdn.net/qq_36743482/article/details/86741365