TABLE_NAME TABLE_TYPE
------------------------------ -----------
BONUS TABLE
DEPT TABLE
EMP1 TABLE
EMP1_LOG TABLE
ERRMSG TABLE
SALGRADE TABLE
STUDENT1 TABLE
STUDENT2 TABLE
SYS_TEMP_FBT TABLE
TAB_NEWTS TABLE
VIEW_STUDENT VIEW
11 rows selected.
--删除表
scott@TESTDB11>drop table student1;
Table dropped.
scott@TESTDB11>drop table student2;
Table dropped.
--出现在回收站中
scott@TESTDB11>show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
STUDENT1 BIN$49Z5BR9gDJDgRAgAJzxnug==$0 TABLE 2013-08-13:09:58:24
STUDENT2 BIN$49Z5BR9hDJDgRAgAJzxnug==$0 TABLE 2013-08-13:09:58:29
--清空回收站
scott@TESTDB11>purge recyclebin;
Recyclebin purged.
scott@TESTDB11>show recyclebin;
--
scott@TESTDB11>drop table emp1;
Table dropped.
scott@TESTDB11>show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP1 BIN$49Z5BR9jDJDgRAgAJzxnug==$0 TABLE 2013-08-13:10:01:38
scott@TESTDB11>
scott@TESTDB11>select * from "BIN$49Z5BR9jDJDgRAgAJzxnug==$0";
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 3400 20
7499 ALLEN SALESMAN 7698 20-FEB-81 2400 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1875 500 30
7566 JONES MANAGER 7839 02-APR-81 4462.5 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1875 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 4275 30
7788 SCOTT ANALYST 7566 19-APR-87 4500 20
7844 TURNER SALESMAN 7698 08-SEP-81 2250 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1650 20
7900 JAMES CLERK 7698 03-DEC-81 1425 30
7902 FORD ANALYST 7566 03-DEC-81 4500 20
11 rows selected.
scott@TESTDB11>flashback table emp1 to before drop;
Flashback complete.
--回收站中已经没有了
scott@TESTDB11>show recyclebin;
scott@TESTDB11>select * from emp1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 3400 20
7499 ALLEN SALESMAN 7698 20-FEB-81 2400 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1875 500 30
7566 JONES MANAGER 7839 02-APR-81 4462.5 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1875 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 4275 30
7788 SCOTT ANALYST 7566 19-APR-87 4500 20
7844 TURNER SALESMAN 7698 08-SEP-81 2250 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1650 20
7900 JAMES CLERK 7698 03-DEC-81 1425 30
7902 FORD ANALYST 7566 03-DEC-81 4500 20
11 rows selected.
--对于系统表空间不会有回收站
--存在于回收站中的内容会因为空间的紧张而被删除
--回收站中有多个同名的被删除
scott@TESTDB11>select * from emp1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 3400 20
7499 ALLEN SALESMAN 7698 20-FEB-81 2400 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1875 500 30
7566 JONES MANAGER 7839 02-APR-81 4462.5 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1875 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 4275 30
7788 SCOTT ANALYST 7566 19-APR-87 4500 20
7844 TURNER SALESMAN 7698 08-SEP-81 2250 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1650 20
7900 JAMES CLERK 7698 03-DEC-81 1425 30
7902 FORD ANALYST 7566 03-DEC-81 4500 20
11 rows selected.
scott@TESTDB11>drop table emp1;
Table dropped.
--出现在回收站中
scott@TESTDB11>show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP1 BIN$49nL2H4iEtngRAgAJzxnug==$0 TABLE 2013-08-13:13:56:19
--再创建一个emp1表存储不同的数据
scott@TESTDB11>create table emp1 as select * from dept;
Table created.
scott@TESTDB11>drop table emp1;
Table dropped.
scott@TESTDB11>show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP1 BIN$49nL2H4jEtngRAgAJzxnug==$0 TABLE 2013-08-13:13:56:45
EMP1 BIN$49nL2H4iEtngRAgAJzxnug==$0 TABLE 2013-08-13:13:56:19
--可以在闪回之前，查询回收站中表的具体内容
scott@TESTDB11>select * from "BIN$49nL2H4jEtngRAgAJzxnug==$0";
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
--按回收站的名称来闪回表，避免出现歧义
scott@TESTDB11>flashback table "BIN$49nL2H4jEtngRAgAJzxnug==$0" to before drop;
Flashback complete.
scott@TESTDB11>select * from emp1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
--只剩下一个了
scott@TESTDB11>show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP1 BIN$49nL2H4iEtngRAgAJzxnug==$0 TABLE 2013-08-13:13:56:19
--把剩下的也闪回,名称出现冲突，已经已经有对象叫EMP1了
scott@TESTDB11>flashback table "BIN$49nL2H4iEtngRAgAJzxnug==$0" to before drop;
flashback table "BIN$49nL2H4iEtngRAgAJzxnug==$0" to before drop
*
ERROR at line 1:
ORA-38312: original name is used by an existing object
--可以在闪回的过程中改名
scott@TESTDB11>flashback table "BIN$49nL2H4iEtngRAgAJzxnug==$0" to before drop rename to emp2;
Flashback complete.
--删除时不进入回收站
scott@TESTDB11>drop table emp2 purge;
Table dropped.
scott@TESTDB11>show recyclebin;
--清除回收站中的指定内容
scott@TESTDB11>drop table emp1
2 ;
Table dropped.
scott@TESTDB11>show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP1 BIN$49nL2H4kEtngRAgAJzxnug==$0 TABLE 2013-08-13:14:27:49
scott@TESTDB11>purge table "BIN$49nL2H4kEtngRAgAJzxnug==$0";
Table purged.
发表于 2020-6-30 14:46:02
QQ好友和群
QQ空间
腾讯微博
腾讯朋友
收藏
楼主
