【Oracle】回收站
☆回收站概念
oracle从10g开始,引入回收站(Recycle Bin)概念。回收站的全称叫:Tablespace Recycle Bin。回收站是一个逻辑区域,oracle并没有为它分配物理空间。当表被Drop后,如果回收站中空间够用的话,表并没有被立即删除,oracle会给表加上一个被删除的标记。只有当回收站空间不足,或者手动清理回收站,这些被删除的表才会被彻底删除。所以当我们误删除以后,可以用闪回技术还原被删除的表。
☆开启、关闭回收站
可以使用命令查看数据库是否开启了回收站,当下列中的value值为on时表明开启,off表示关闭
SYS@LGR> show parameter recyclebin; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ recyclebin string on SYS@LGR> select name,value from v$parameter where name='recyclebin'; NAME VALUE -------------------- ---------- recyclebin on
☆可以通过设置初始化参数recyclebin启用或者禁用回收站功能。
##会话级别关闭回收站
SYS@LGR> ALTER SESSION SET RECYCLEBIN=OFF;
Session altered.
##系统级别关闭回收站
SQL> alter system set recyclebin = off scope = spfile;
System altered.
☆闪回删除
语法:
FLASHBACK TABLE [schema.] {BEFORE DROP [RENAME TO
table_new_name]}
回收站中表名的含义:(格式:BINglobalUIDversion )
- BIN:表示 RECYCLEBIN
- globalUID:是一个全局唯一的,24 个字节,该标识与原对象名没有 任何关系
- version:指数据库分配的版本号
1) 表准备环境(创建测试表 t ,索引 idx_t ,触发器 trg_t)
SYS@LGR> conn scott/tiger; Connected. SCOTT@LGR> create table t(x number(2),d date); Table created. SCOTT@LGR> create unique index idx_t on t(x); Index created. SCOTT@LGR> create or replace trigger trg_t 2 before insert on t 3 for each row 4 begin 5 if :new.d is null then 6 :new.d :=sysdate; 7 end if; 8 end; 9 / Trigger created
2)删除表t
SCOTT@LGR> drop table t; Table dropped. SCOTT@LGR> select tname,tabtype from tab; TNAME TABTYPE ------------------------------ ------- BIN$Q8N67S/xGAngUwEAAH9JoA==$0 TABLE BIN$QyLhuF3CKuHgUwEAAH/Q8Q==$0 TABLE BONUS TABLE DEPT TABLE EMP TABLE EMP1 TABLE EMP2 TABLE SALGRADE TABLE T1 TABLE TMPD_DEPT TABLE TMPP_DEPT TABLE 11 rows selected. SCOTT@LGR> show recyclebin; ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- DEPT1 BIN$QyLhuF3CKuHgUwEAAH/Q8Q==$0 TABLE 2016-12-08:17:01:23 T BIN$Q8N67S/xGAngUwEAAH9JoA==$0 TABLE 2016-12-16:16:37:28 SCOTT@LGR> SELECT original_name, object_name, type,droptime FROM 2 user_recyclebin; ORIGINAL_NAME OBJECT_NAME TYPE DROPTIME -------------- ------------------------------ -------- ------------------- IDX_T BIN$Q8N67S/vGAngUwEAAH9JoA==$0 INDEX 2016-12-16:16:37:28 DEPT1 BIN$QyLhuF3CKuHgUwEAAH/Q8Q==$0 TABLE 2016-12-08:17:01:23 TRG_T BIN$Q8N67S/wGAngUwEAAH9JoA==$0 TRIGGER 2016-12-16:16:37:28 T BIN$Q8N67S/xGAngUwEAAH9JoA==$0 TABLE 2016-12-16:16:37:28
3)闪回表
SCOTT@LGR> flashback table t to before drop; Flashback complete. SCOTT@LGR> SELECT original_name, object_name, type,droptime FROM user_recyclebin; ORIGINAL_NAME OBJECT_NAME TYPE DROPTIME -------------------------------- ------------------------------ ------------------------- ------------------- DEPT1 BIN$QyLhuF3CKuHgUwEAAH/Q8Q==$0 TABLE 2016-12-08:17:01:23
4)查看闪回的表相关对象的状态
通过以下查询,发现以下两个问题:
1 :触发器的状态为失效的
2:对象名称,除表名以外,其他的都已不是原来的名称
SCOTT@LGR> SELECT object_name,object_type,status FROM user_objects; OBJECT_NAME OBJECT_TYPE STATUS -------------------------------- ------------------- ------- DEPT TABLE VALID PK_DEPT INDEX VALID BONUS TABLE VALID SALGRADE TABLE VALID EMP1 TABLE VALID EMP2 TABLE VALID T1 TABLE VALID TMPD_DEPT TABLE VALID EMP TABLE VALID TMPP_DEPT TABLE VALID T TABLE VALID BIN$Q8N67S/wGAngUwEAAH9JoA==$0 TRIGGER INVALID BIN$Q8N67S/vGAngUwEAAH9JoA==$0 INDEX VALID
5) 重新编译触发器,并 重命名触发器
SCOTT@LGR> alter trigger"BIN$Q8N67S/wGAngUwEAAH9JoA==$0" compile; Trigger altered. SCOTT@LGR> alter trigger"BIN$Q8N67S/wGAngUwEAAH9JoA==$0" rename to trg_t; Trigger altered. SCOTT@LGR> SELECT object_name,object_type,status FROM user_objects; OBJECT_NAME OBJECT_TYPE STATUS ------------------------------ ------------------- ------- DEPT TABLE VALID PK_DEPT INDEX VALID BONUS TABLE VALID SALGRADE TABLE VALID EMP1 TABLE VALID EMP2 TABLE VALID T1 TABLE VALID TMPD_DEPT TABLE VALID EMP TABLE VALID TMPP_DEPT TABLE VALID T TABLE VALID BIN$Q8N67S/vGAngUwEAAH9JoA==$0 INDEX VALID TRG_T TRIGGER VALID