ORACLE
[ORACLE] Recyclebin
from-sh
2022. 1. 14. 15:57
728x90
-- 사전 설정
|
SQL> show parameter recyclebin;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
SQL> alter session set recyclebin=on;
|
-- recyclebin 조회
SQL> drop table oggs.tcustmer;
SQL> drop table oggs.tcustord;
* 일반 유저에서 조회됨 / sys, system 계정에서 조회 안됨
SQL> set line 3000
SQL> col OBJECT_NAME for a40
SQL> col ORIGINAL_NAME for a10
SQL> col PARTITION_NAME for a20
SQL> select * from recyclebin;
SQL> drop table oggs.tcustmer;
SQL> drop table oggs.tcustord;
* 일반 유저에서 조회됨 / sys, system 계정에서 조회 안됨
SQL> set line 3000
SQL> col OBJECT_NAME for a40
SQL> col ORIGINAL_NAME for a10
SQL> col PARTITION_NAME for a20
SQL> select * from recyclebin;
|
OBJECT_NAME ORIGINAL_N OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN CAN RELATED BASE_OBJECT PURGE_OBJECT SPACE
---------------------------------------- ---------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------- --- --- ---------- ----------- ------------ ----------
BIN$1TRHJo4GnongU9MyEKxDHw==$0 SYS_C00621 DROP INDEX OGG 2022-01-07:03:06:00 2022-01-10:00:04:57 2330551 NO YES 66008 66008 66009 8
3
BIN$1TRHJo4HnongU9MyEKxDHw==$0 TCUSTMER DROP TABLE OGG 2022-01-07:03:06:00 2022-01-10:00:04:57 2330555 YES YES 66008 66008 66008 8
BIN$1TRHJo4JnongU9MyEKxDHw==$0 SYS_C00621 DROP INDEX OGG 2022-01-07:03:06:00 2022-01-10:00:06:39 2330826 NO YES 66010 66010 66011 8
4
BIN$1TRHJo4KnongU9MyEKxDHw==$0 TCUSTORD DROP TABLE OGG 2022-01-07:03:06:00 2022-01-10:00:06:39 2330831 YES YES 66010 66010 66010 8
|
SQL> select * from "BIN$1TRHJo4HnongU9MyEKxDHw==$0";
CUST NAME CITY ST
---- ------------------------------ -------------------- --
WILL BG SOFTWARE CO. SEATTLE WA
JANE ROCKY FLYER INC. DENVER CO
-- recyclebin 복구
SQL> flashback table tcustmer to before drop;
SQL> flashback table tcustmer to before drop rename to t1;
Flashback complete.
SQL> select * from tcustmer;
CUST NAME CITY ST
---- ------------------------------ -------------------- --
WILL BG SOFTWARE CO. SEATTLE WA
JANE ROCKY FLYER INC. DENVER CO
-- recyclebin 삭제
SQL> purge table "BIN$1TRHJo4KnongU9MyEKxDHw==$0";
Table purged.
SQL> select * from recyclebin;
no rows selected
728x90