oracle 闪回表定义,Oracle闪回表基本操作

1.查看oracle回收站并且做闪回操作

SQL>select * from dba_recyclebin

SQL>flashback table emp01 to before drop;

Flashback complete.

2.做闪回操作并且对表重新命名

SQL>flashback table “BIN$JLbmbfKW6YPgUKjAtQYUKQ==$0" to before drop rename to emp02;

Flashback complete.

3.闪回表，并查看表上某列的索引是否闪回

SQL> create index ind_empno on emp02(empno);

Index created.

SQL> select index_name from user_indexes;

INDEX_NAME

------------------------------

IND_EMPNO

PK_DEPT

SQL> drop table emp02;

Table dropped.

SQL> flashback table emp02 to before drop;

Flashback complete.

SQL> select index_name from user_indexes;

INDEX_NAME

------------------------------

BIN$JLbmbfKX6YPgUKjAtQYUKQ==$0        //索引闪回了，但是名字是回收站的名字

PK_DEPT

SQL> drop index "BIN$JLbmbfKX6YPgUKjAtQYUKQ==$0";      //删除索引，记得加引号

Index dropped.

SQL> create index ind_empno on emp02(empno);            //重建索引

Index created.

SQL> select index_name from user_indexes;

INDEX_NAME

------------------------------

IND_EMPNO

PK_DEPT

4.恢复多个同名的表

SQL> drop table emp02;

Table dropped.

SQL> create table emp02 as select * from emp;

Table created.

SQL> drop table emp02;

Table dropped.

SQL> flashback table emp02 to before drop;

Flashback complete.

SQL> flashback table emp02 to before drop;

flashback table emp02 to before drop

*

ERROR at line 1:

ORA-38312: original name is used by an existing object

SQL> flashback table "BIN$JLbmbfKa6YPgUKjAtQYUKQ==$0" to before drop rename to emp03;

Flashback complete.

5.purge的基本应用

SQL> drop table emp04 purge;

Table dropped.

SQL> drop table emp03;

Table dropped.

SQL> drop table emp02;

Table dropped.

SQL> purge table emp02;

Table purged.

SQL> purge table "BIN$JLbmbfKd6YPgUKjAtQYUKQ==$0";

Table purged.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30135314/viewspace-1839883/，如需转载，请注明出处，否则将追究法律责任。