flashback drop
|
|
|
|
|
1、每个用户自己建立的表空间上都有一个回收站 SQL> conn scott/tiger Connected. SQL> select * from tab; DEPT TABLE EMP TABLE BONUS TABLE SALGRADE TABLE BIN$tAcLIS4r9qbgQAB/AQA2mw==$0 TABLE LXTB1 TABLE BIN$tAcLIS4s9qbgQAB/AQA2mw==$0 TABLE
SQL> drop table lxtb1; SQL> show recyclebin; ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- EMP1 BIN$tAcLIS4s9qbgQAB/AQA2mw==$0 TABLE 2011-12-14:12:47:17 LXTB1 BIN$tBi/ze9iTkTgQAB/AQBpLA==$0 TABLE 2011-12-15:11:04:56 LXTB5 BIN$tAcLIS4r9qbgQAB/AQA2mw==$0 TABLE 2011-12-14:12:46:01 11:09:45 SQL> desc "BIN$tBi/ze9iTkTgQAB/AQBpLA==$0"; Name Null? Type ----------------------------------------------------------------- -------- -------------------------------------------- ID NUMBER(38) 2、闪回 11:11:07 SQL> flashback table lxtb1 to before drop;
Flashback complete.
11:11:19 SQL> show recyclebin; //查看回收站里面没有了 ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- EMP1 BIN$tAcLIS4s9qbgQAB/AQA2mw==$0 TABLE 2011-12-14:12:47:17 LXTB5 BIN$tAcLIS4r9qbgQAB/AQA2mw==$0 TABLE 2011-12-14:12:46:01 11:11:29 SQL> select * from scott.lxtb1; 100 101 102
3 rows selected.
|
|
purge |
1、不放回回收站，直接删除 11:14:25 SQL> drop table lxtb1 purge;
Table dropped. 11:15:34 SQL> show recyclebin; ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- EMP1 BIN$tAcLIS4s9qbgQAB/AQA2mw==$0 TABLE 2011-12-14:12:47:17 LXTB5 BIN$tAcLIS4r9qbgQAB/AQA2mw==$0 TABLE 2011-12-14:12:46:01
|
|
闪回的表和现在有表重名 |
11:18:41 SQL> select * from lxtb3; 1
1 row selected.
11:18:45 SQL> drop table lxtb3;
Table dropped.
11:22:29 SQL> show recyclebin; ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- EMP1 BIN$tAcLIS4s9qbgQAB/AQA2mw==$0 TABLE 2011-12-14:12:47:17 LXTB3 BIN$tBi/ze9jTkTgQAB/AQBpLA==$0 TABLE 2011-12-15:11:19:47 LXTB5 BIN$tAcLIS4r9qbgQAB/AQA2mw==$0 TABLE 2011-12-14:12:46:01
11:19:47 SQL> create table lxtb3(id int) tablespace test;
Table created. 11:20:29 SQL> select * from tab; DEPT TABLE EMP TABLE BONUS TABLE SALGRADE TABLE BIN$tAcLIS4r9qbgQAB/AQA2mw==$0 TABLE BIN$tAcLIS4s9qbgQAB/AQA2mw==$0 TABLE LXTB1 TABLE BIN$tBi/ze9jTkTgQAB/AQBpLA==$0 TABLE LXTB3 TABLE
11:20:05 SQL> flashback table lxtb3 to before drop; flashback table lxtb3 to before drop * ERROR at line 1: ORA-38312: original name is used by an existing object
恢复直接改名 11:22:34 SQL> flashback table lxtb3 to before drop rename to lxtb3bak;
Flashback complete. 11:24:54 SQL> select * from lxtb3bak; 1
1 row selected.
|
|
清除回收站 |
1:25:36 SQL> drop table lxtb3bak;
Table dropped.
11:27:01 SQL> show recyclebin; ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- EMP1 BIN$tAcLIS4s9qbgQAB/AQA2mw==$0 TABLE 2011-12-14:12:47:17 LXTB3BAK BIN$tBi/ze9kTkTgQAB/AQBpLA==$0 TABLE 2011-12-15:11:27:01 LXTB5 BIN$tAcLIS4r9qbgQAB/AQA2mw==$0 TABLE 2011-12-14:12:46:01 11:27:08 SQL> purge table lxtb3bak; //从回收站表把清除
Table purged.
11:29:04 SQL> select * from tab; DEPT TABLE EMP TABLE BONUS TABLE SALGRADE TABLE BIN$tAcLIS4r9qbgQAB/AQA2mw==$0 TABLE BIN$tAcLIS4s9qbgQAB/AQA2mw==$0 TABLE LXTB1 TABLE LXTB3 TABLE
8 rows selected.
11:29:10 SQL> show recyclebin; ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- EMP1 BIN$tAcLIS4s9qbgQAB/AQA2mw==$0 TABLE 2011-12-14:12:47:17 LXTB5 BIN$tAcLIS4r9qbgQAB/AQA2mw==$0 TABLE 2011-12-14:12:46:01 11:29:18 SQL> purge recyclebin;
Recyclebin purged.
11:29:32 SQL> select * from tab; DEPT TABLE EMP TABLE BONUS TABLE SALGRADE TABLE LXTB1 TABLE LXTB3 TABLE
6 rows selected.
11:29:40 SQL> show recyclebin;
注意：system表空间没有回收站，删除表后不能闪回 |
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30606702/viewspace-1980239/ ，如需转载，请注明出处，否则将追究法律责任。
