1.使用回收站
删除表，不加purge，默认放入回收站
SQL> drop table t;
查看回收站
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T BIN$NBwN6ppqdoLgUKjAgPgUEg==$0 TABLE 2016-05-31:11:34:45
访问T表，无法访问，代表T表逻辑上被删除了
SQL> select * from t;
select * from t
*
ERROR at line 1:
ORA-00942: table or view does not exist
访问回收站里的对象，成功，代表T表，物理上没有被删除
SQL> select * from "BIN$NBwN6ppqdoLgUKjAgPgUEg==$0";
还原T表
SQL> flashback table t to before drop;
有两张T先后被删除，都进入了回收站
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T BIN$NBwN6ppsdoLgUKjAgPgUEg==$0 TABLE 2016-05-31:11:36:32
T BIN$NBwN6pprdoLgUKjAgPgUEg==$0 TABLE 2016-05-31:11:36:10
后进先出进行还原
SQL> flashback table t to before drop;
还是两张T先后被删除，想两张都还原
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T BIN$NBwN6pptdoLgUKjAgPgUEg==$0 TABLE 2016-05-31:11:37:58
T BIN$NBwN6pprdoLgUKjAgPgUEg==$0 TABLE 2016-05-31:11:36:10
先还原最晚进入回收站的T表
SQL> flashback table t to before drop;
再次还原时，报错，因为该用户下已经存在一个表叫T了，不允许重名
SQL> flashback table t to before drop;
flashback table t to before drop
*
ERROR at line 1:
ORA-38312: original name is used by an existing object
通过重命名还原方式进行还原
SQL> flashback table t to before drop rename to t_dept;
依然是两张T先后进入回收站
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T BIN$NBwN6ppvdoLgUKjAgPgUEg==$0 TABLE 2016-05-31:11:39:30
T BIN$NBwN6ppudoLgUKjAgPgUEg==$0 TABLE 2016-05-31:11:39:11
手动指定还原哪一个
SQL> flashback table "BIN$NBwN6ppudoLgUKjAgPgUEg==$0" to before drop;
2.空间回收实验
回收站的空间回收
创建一个足够小的表空间
SQL> create tablespace test datafile '/u01/app/oracle/oradata/orcl/test.dbf' size 1m;
创建一个使用该表空间的用户
SQL> create user test identified by test default tablespace test;
SQL> grant resource,connect to test;
SQL> grant select on scott.emp to test;
空间回收实验
SQL> conn test/test
Connected.
SQL> create table t as select * from scott.emp;
Table created.
SQL> drop table t;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T BIN$NBzFZF0clSLgUKjAgPgYYg==$0 TABLE 2016-05-31:13:37:38
SQL> create table t2 as select * from scott.emp;
Table created.
SQL> insert into t2 select * from t2;
14 rows created.
SQL> /
28 rows created.
SQL> /
56 rows created.
SQL> /
112 rows created.
SQL> /
224 rows created.
SQL> /
448 rows created.
SQL> /
896 rows created.
SQL> /
1792 rows created.
SQL> /
3584 rows created.
SQL> /
7168 rows created.
SQL> /
insert into t2 select * from t2
*
ERROR at line 1:
ORA-01653: unable to extend table TEST.T2 by 8 in tablespace TEST
表空间满，无法完成后续的插入
检查回收站，发现回收站已被清空
SQL> show recyclebin
使用sys打开test表空间的自动扩展
继续进行实验，观察回收站空间的管理
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/test.dbf' autoextend on;
SQL> select * from t2;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> drop table t2;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T2 BIN$NBzFZF0dlSLgUKjAgPgYYg==$0 TABLE 2016-05-31:13:41:56
SQL> create table t as select * from scott.emp;
Table created.
SQL> insert into t select * from t;
14 rows created.
SQL> /
28 rows created.
SQL> /
56 rows created.
SQL> /
112 rows created.
SQL> /
224 rows created.
SQL> /
448 rows created.
SQL> /
896 rows created.
SQL> /
1792 rows created.
SQL> /
3584 rows created.
SQL> /
7168 rows created.
SQL> /
14336 rows created.
完成7168行插入以后，后续的插入没有报表空间满，说明，此时表空间的数据文件自动扩展了
但是，在自动扩展之前，也是优先清空回收站
SQL> show recyclebin
通过数据字典，观察表删除时，数据库对回收站里的对象如何去看待
使用test用户创建实验表
SQL> create table test as select * from scott.emp;
Table created.
使用sys用户查看数据字典
SQL> col segment_name for a20
SQL> col file_id for 999999
SQL> col extent_id for 999999
SQL> col block_id for 999999
TEST用户下，有一张TEST，通过dba_extents查看该表里空间的使用情况
可以看到该表，使用一个extent
SQL> select SEGMENT_NAME,FILE_ID,EXTENT_ID,BLOCK_ID from dba_extents
2 where owner='TEST' and SEGMENT_NAME='TEST';
SEGMENT_NAME FILE_ID EXTENT_ID BLOCK_ID
-------------------- ------- --------- --------
TEST 8 0 8
此时，查询TEST表空间，空闲空间的情况，可以发现，有一个空闲的extent
SQL> select file_id,block_id,blocks from dba_free_space where tablespace_name='TEST';
FILE_ID BLOCK_ID BLOCKS
------- -------- ----------
8 16 376
删除test表，使之进入回收站
SQL> drop table test;
Table dropped.
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST BIN$NBzFZF0elSLgUKjAgPgYYg==$0 TABLE 2016-05-31:13:48:42
此时，再去查询该表空间的空闲空间，发现多了一个extent
多出的这个extent，它的BLOCK_ID正是之前TEST表所使用的BLOCK，ID是一样的，都是8
说明，进入回收站的对象，数据库已然把它们当成了空闲的空间
SQL> select file_id,block_id,blocks from dba_free_space where tablespace_name='TEST';
FILE_ID BLOCK_ID BLOCKS
------- -------- ----------
8 16 376
8 8 8
3.关于索引和约束
创建表，及表上的索引和约束
SQL> create table t1 as select * from scott.emp;
SQL> create index idx_t1 on t1(empno);
SQL> alter table t1 modify(empno not null);
查看索引及约束的信息
SQL> select INDEX_NAME,TABLE_NAME from user_indexes;
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
IDX_T1 T1
SQL> select constraint_name from user_constraints;
CONSTRAINT_NAME
------------------------------
SYS_C0011075
删除T1表，放入回收站
SQL> drop table t1;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T1 BIN$NBzFZF0hlSLgUKjAgPgYYg==$0 TABLE 2016-05-31:14:04:05
发现索引也同步被删除了
SQL> select INDEX_NAME,TABLE_NAME from user_indexes;
发现约束还保留，但被改了名字，改成了和回收站里对象一样的名字
当表空间，空间不足时
数据库自动清空回收站里的数据时
该表被物理上清空的时候，这个约束也同步会被删除
SQL> select constraint_name from user_constraints;
CONSTRAINT_NAME
------------------------------
BIN$NBzFZF0flSLgUKjAgPgYYg==$0
通过回收站数据字典可以查看到索引也在回收站里面
SQL> select object_name,original_name from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME
------------------------------ --------------------------------
BIN$NBzFZF0glSLgUKjAgPgYYg==$0 IDX_T1
BIN$NBzFZF0hlSLgUKjAgPgYYg==$0 T1
还原T1表
SQL> flashback table t1 to before drop;
Flashback complete.
回收站空了
SQL> select object_name,original_name from user_recyclebin;
no rows selected
发现索引也同步被还原了，但是索引和约束的名称还保留着回收站里的对象名
SQL> select INDEX_NAME,TABLE_NAME from user_indexes;
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
BIN$NBzFZF0glSLgUKjAgPgYYg==$0 T1
SQL> select constraint_name from user_constraints;
CONSTRAINT_NAME
------------------------------
BIN$NBzFZF0flSLgUKjAgPgYYg==$0
可以选择手动维护索引及约束的名称，改成没被删除之前的样子
SQL> alter index "BIN$NBzFZF0glSLgUKjAgPgYYg==$0" rename to idx_t1;
Index altered.
SQL> alter table t1 rename constraint "BIN$NBzFZF0flSLgUKjAgPgYYg==$0" to SYS_C0011075;
Table altered.
SQL> select INDEX_NAME,TABLE_NAME from user_indexes;
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
IDX_T1 T1
SQL> select constraint_name from user_constraints;
CONSTRAINT_NAME
------------------------------
SYS_C0011075
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
