Oracle Database
Oracle FLASHBACK TABLE - sample
에너자이죠
2010. 3. 30. 13:48
널리 알려진 기능이지만 사용하는 방법 sample 입니다.
PC 내에 있는 scrpt 정리하다 삭제 전에 한번 올려 봅니다.
혹시 누군가에겐 필요할까 싶어서 ㅎㅎ
(절대 몇일 동안 oracle post가 없어 올리는 건 아닙니다. --;)
ora102@TEST4:/app/oracle/product/db/rdbms/log> sqlplus scott/tiger
SQL*Plus: Release 10.2.0.3.0 - Production on Sun Aug 3 14:57:17 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$U4gkooULB1PgQLMKTWwM7A==$0 TABLE
BIN$U4iK63kw1zTgQLMKTWx98g==$0 TABLE
EMP TABLE
AAA TABLE
SQL>
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP BIN$U4iK63kw1zTgQLMKTWx98g==$0 TABLE 2008-08-03:14:32:03
TEST BIN$U4gkooULB1PgQLMKTWwM7A==$0 TABLE 2008-08-03:14:03:27
SQL> FLASHBACK TABLE TEST TO BEFORE DROP;
Flashback complete.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$U4iK63kw1zTgQLMKTWx98g==$0 TABLE
TEST TABLE
EMP TABLE
AAA TABLE
SQL> purge recyclebin;
Recyclebin purged.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST TABLE
EMP TABLE
AAA TABLE
SQL> drop table test;
Table dropped.
SQL> select * from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME OPERATION
------------------------------ -------------------------------- ---------
TYPE TS_NAME CREATETIME
------------------------- ------------------------------ -------------------
DROPTIME DROPSCN PARTITION_NAME CAN CAN
------------------- ---------- -------------------------------- --- ---
RELATED BASE_OBJECT PURGE_OBJECT SPACE
---------- ----------- ------------ ----------
BIN$U4juzy+wgKngQLMKTWwgxQ==$0 TEST DROP
TABLE USERS 2008-08-03:13:57:59
2008-08-03:14:59:59 15636802 YES YES
56903 56903 56903
SQL*Plus: Release 10.2.0.3.0 - Production on Sun Aug 3 14:57:17 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$U4gkooULB1PgQLMKTWwM7A==$0 TABLE
BIN$U4iK63kw1zTgQLMKTWx98g==$0 TABLE
EMP TABLE
AAA TABLE
SQL>
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP BIN$U4iK63kw1zTgQLMKTWx98g==$0 TABLE 2008-08-03:14:32:03
TEST BIN$U4gkooULB1PgQLMKTWwM7A==$0 TABLE 2008-08-03:14:03:27
SQL> FLASHBACK TABLE TEST TO BEFORE DROP;
Flashback complete.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$U4iK63kw1zTgQLMKTWx98g==$0 TABLE
TEST TABLE
EMP TABLE
AAA TABLE
SQL> purge recyclebin;
Recyclebin purged.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST TABLE
EMP TABLE
AAA TABLE
SQL> drop table test;
Table dropped.
SQL> select * from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME OPERATION
------------------------------ -------------------------------- ---------
TYPE TS_NAME CREATETIME
------------------------- ------------------------------ -------------------
DROPTIME DROPSCN PARTITION_NAME CAN CAN
------------------- ---------- -------------------------------- --- ---
RELATED BASE_OBJECT PURGE_OBJECT SPACE
---------- ----------- ------------ ----------
BIN$U4juzy+wgKngQLMKTWwgxQ==$0 TEST DROP
TABLE USERS 2008-08-03:13:57:59
2008-08-03:14:59:59 15636802 YES YES
56903 56903 56903