휴지통(Recycle Bin)

[생짜초보]

 Recycle Bin이란?

다들 아시다시피 Oracle10g부터 database상의 "휴지통(Recycle Bin)"이 등장하게 되었습니다.

이 recycle bin은 Windows의 휴지통과 마찬가지로,

"drop table …"을 통해 제거한 table을 restore할 수 있도록 임시로 보관하는 장소입니다.

Oracle의 recycle bin에 있는 objects는 영구히 제거되기 전까지는

disk상에서 공간을 차지하기 때문에,

drop한 만큼 user에게 할당된 quota에 여유가 생기거나 하지 없습니다.

참고로, recycle bin은 schema 및 tablespace에 종속되어 있기 때문에,

어떤 schema가 drop될 때에, 그 schema의 recycle bin도 깨끗하게 clear됩니다.

그리고 어떤 tablespace가 drop되는 경우, 해당 tablespace상에서 create된 후 drop되어

recycle bin에 저장되어 있던 objects들도 깨끗하게 clear됩니다.

2. Flashback Drop기능을 enable/disable하기

이것은 "recyclebin"이라는 initialization parameter를 통해 설정할 수 있습니다.

session level에서 enable/disable하고자 한다면,

 alter session set recyclebin = on;

 alter session set recyclebin = off;

전체적으로 적용시키고자 한다면,

 alter system set recyclebin = on;

 alter system set recyclebin = off;

를 실행합니다.

Flashback Drop기능을 disable해도

이미 recycle bin에 저장되어 있는 objects는 제거되거나 하지 않습니다.

3. Recycle Bin에 있는 objects를 확인하기

recycle bin에 있는 objects를 확인하는 방법은 다음과 같습니다.

① select * from recyclebin;

② select * from user_recyclebin;

③ show recyclebin;

④ select * from dba_recyclebin;

①②③은 접속 중인 해당 schema에 종속된 recycle bin에 저장된 objects만 보여주며,

④은 database전체의 recycle bin에 저장된 objects를 보여줍니다.

(물론 ④을 실행하기 위해서는 system metadata에 접근할 수 있는 권한이 필요합니다)

recycle bin에 있는 objects는 recycle bin으로 옮겨지면서 붙여진

임시이름(recyclebin name)을 통해 다음과 같이 access할 수 있습니다.

 SQL> show recyclebin;

original name	recyclebin name	object type	drop time
1000	BIN$iPkaxpvboGjgQBwDKnsYSw==$0	table	2010/06/14:16:04:49

 /* recycle bin에 저장되는 임시이름에는 특수문자가 들어가기 때문에 

     반드시 double quotation으로 둘러싸야 합니다 */

 SQL> select * from BIN$iPkaxpvboGjgQBwDKnsYSw==$0;

 select * from BIN$iPkaxpvboGjgQBwDKnsYSw==$0

                                        *

 ERROR at line 1:

 ORA-00933: SQL command not properly ended

 SQL> select * from "BIN$iPkaxpvboGjgQBwDKnsYSw==$0";

location_id	street_address	postal_code	city
1000	1297 Via Cola di Rie	00989	Roma
1100	93091 Calle della Testa	10934	Venice
1200	2017 Shinjuku-ku	1689	Tokyo
...

4. Recycle Bin에 있는 objects를 제거(purge)하기 (=쓰레기통 비우기)

 SQL> connect hr/****

 /* 아래의 결과를 보면 2개의 tables는 "users" tablespace에,

     다른 2개의 tables는 "tools" tablespace에 존재하고 있다는 것을 알 수 있습니다 */

 SQL> select segment_name, tablespace_name from user_segments;

segment_name	segment_type	tablespace_name
departments	table	users
jobs	table	users
locations	table	tools
regions	table	tools

 SQL> drop table departments;

 Table dropped.

 SQL> drop table jobs;

 Table dropped.

 SQL> drop table locations;

 Table dropped.

 SQL> drop table regions;

 Table dropped.

 SQL> select object_name, original_name, ts_name from recyclebin;

object_name	original_name	ts_name
BIN$iPmnXi+WkFrgQBwDKnsZOg==$0	departments	users
BIN$iPmnXi+UkFrgQBwDKnsZOg==$0	jobs	users
BIN$iPmnXi+QkFrgQBwDKnsZOg==$0	locations	tools
BIN$iPmnXi+SkFrgQBwDKnsZOg==$0	regions	tools

 /* recycle bin에서 지정된 임시 이름(recyclebin name)을 이용해서

     영구적으로 제거할 수 있습니다 */

 SQL> purge table "BIN$iPmnXi+WkFrgQBwDKnsZOg==$0";

 Table purged.

 SQL> select object_name, original_name, ts_name from recyclebin;

object_name	original_name	ts_name
BIN$iPmnXi+UkFrgQBwDKnsZOg==$0	jobs	users
BIN$iPmnXi+QkFrgQBwDKnsZOg==$0	locations	tools
BIN$iPmnXi+SkFrgQBwDKnsZOg==$0	regions	tools

 /* table의 원래 이름(original name)을 이용해서 영구적으로 제거할 수도 있습니다 */

 SQL> purge table locations;

 Table purged.

SQL> select object_name, original_name, ts_name from recyclebin;

object_name	original_name	ts_name
BIN$iPmnXi+UkFrgQBwDKnsZOg==$0	jobs	users
BIN$iPmnXi+SkFrgQBwDKnsZOg==$0	regions	tools

/* "users" tablespace에 저장되어 있던 recycle bin의 objects를 영구적으로 제거합니다. */

/* 주의할 점이 있는데, 이 명령을 실행하는 schema에 따라서 결과가 다르다는 것입니다.

    일반 user로 이 command를 실행하면 해당 schema에 속한 recycle bin의 objects만이

    제거되지만, 만약 "sys" 혹은 "system" 같은 privileged user로 이 command를 실행하면,

    모든 schemas에 대해서 "users" tablespace에 저장되어 있던

    모든 recycle bin의 objects가 영구히 제거됩니다. */

 SQL> purge tablespace users;

 Tablespace purged.

 SQL> select object_name, original_name, ts_name from recyclebin;

object_name	original_name	ts_name
BIN$iPmnXi+SkFrgQBwDKnsZOg==$0	regions	tools

 SQL> connect / as sysdba

 Connected.

 SQL> select owner, object_name, original_name, ts_name from dba_recyclebin;

owner	object_name	original_name	ts_name
oe	BIN$iPicLG1qMnvgQBwDKnsXOg==$0	warehouses	tools
hr	BIN$iPlXomwrF73gQBwDKnsYuw==$0	regions	tools

 /* "hr" schema의 objects중에서 "tools"에 저장되어 있었던

     recycle bin의 objects만을 purge합니다 */

 SQL> purge tablespace tools user hr;

 Tablespace purged.

 SQL> select owner, object_name, original_name, ts_name from dba_recyclebin;

owner	object_name	original_name	ts_name
oe	BIN$iPicLG1qMnvgQBwDKnsXOg==$0	warehouses	tools

 /* schema에 관계없이 database전체에 대해서

     recycle bin에 있는 모든 objects를 purge합니다 */

 SQL> purge dba_recyclebin;

 DBA Recyclebin purged.

 SQL> select owner, object_name, original_name, ts_name from dba_recyclebin;

 no rows selected

 SQL> connect oe/****

 Connected.

 SQL> select segment_name, tablespace_name from user_segments;

segment_name	segment_type	tablespace_name
customers	table	users
inventories	table	tools

 SQL> drop table customers;

 Table dropped.

 SQL> drop table inventories;

 Table dropped.

 SQL> show recyclebin;

original name	recyclebin name	object type	drop time
customers	BIN$iPpZl264L9DgQBwDKnsaow==$0	table	2010/06/14 17:34:41
inventories	BIN$iPpZl261L9DgQBwDKnsaow==$0	table	2010/06/14 17:34:38

 /* current schema에 속한 모든 recycle bin의 objects를 purge합니다 */

 SQL> purge recyclebin;

 Recyclebin purged.

 /* recycle bin이 비었기 때문에 아무 결과도 return되지 않습니다 */

 SQL> show recyclebin;

 SQL>

※ 참고로, Flashback Drop기능이 enable된 상태라고 하더라도,

    "purge" option을 붙여서 drop하게 되면

    drop한 objects가 recycle bin에 저장되지 않고 영구히 제거됩니다.

⇒ drop table <table_name> purge;

5. drop된 objects를 restore하기

"flashback table"이라는 command를 사용해서 restore할 수 있는데, 특별한 권한은 필요없습니다.

특정 table을 drop할 수 있었다면, " flashback table " command를 통해 restore할 수 있다.

 SQL> show recyclebin;

original name	recyclebin name	object type	drop time
customers	BIN$iPpZl264L9DgQBwDKnsaow==$0	table	2010/06/14 17:34:41
customers	BIN$iPqfJRmrwF7gQBwDKnsbTA==$0	table	2010/06/14 17:55:03
customers	BIN$iPqfJRm7wG7gQBwDKnsbTA==$0	table	2010/06/14 18:03:27
inventories	BIN$iPpZl261L9DgQBwDKnsaow==$0	table	2010/06/14 17:34:38
promotions	BIN$iPqfJRmbwF7gQBwDKnsbTA==$0	table	2010/06/14 17:53:25

 /* original name을 이용해서 drop된 object를 restore합니다 */

 SQL> flashback table inventories to before drop;

 Flashback complete.

 SQL> show recyclebin;

original name	recyclebin name	object type	drop time
customers	BIN$iPpZl264L9DgQBwDKnsaow==$0	table	2010/06/14 17:34:41
customers	BIN$iPqfJRmrwF7gQBwDKnsbTA==$0	table	2010/06/14 17:55:03
customers	BIN$iPqfJRm7wG7gQBwDKnsbTA==$0	table	2010/06/14 18:03:27
promotions	BIN$iPqfJRmbwF7gQBwDKnsbTA==$0	table	2010/06/14 17:53:25

/* restore할 때에 table명을 rename할 수 있습니다 */

 SQL> flashback table promotion to before drop rename to proms;

 Flashback complete.

 SQL> show recyclebin;

original name	recyclebin name	object type	drop time
customers	BIN$iPpZl264L9DgQBwDKnsaow==$0	table	2010/06/14 17:34:41
customers	BIN$iPqfJRmrwF7gQBwDKnsbTA==$0	table	2010/06/14 17:55:03
customers	BIN$iPqfJRm7wG7gQBwDKnsbTA==$0	table	2010/06/14 18:03:27

 /* 동일한 table이 여러 번 drop된 경우에는, 위와 같이 복수의 versions가 저장되게 되는데,

     이 경우 "recyclebin name"을 참조하여 원하는 version을 restore할 수 있습니다 */

 SQL> flashback table "BIN$iPqfJRmrwF7gQBwDKnsbTA==$0" to before drop;

 Flashback complete.

 ※ 만약 위에서 "flashback table" 뒤에 original name을 사용하는 경우에는

     최신 version(최근에 drop된 image)이 restore됩니다

 /* restore된 2번째 version은 더 이상 recycle bin에 존재하지 않게 되었습니다 */

 SQL> show recyclebin;

original name	recyclebin name	object type	drop time
customers	BIN$iPpZl264L9DgQBwDKnsaow==$0	table	2010/06/14 17:34:41
customers	BIN$iPqfJRm7wG7gQBwDKnsbTA==$0	table	2010/06/14 18:03:27

 SQL> drop table departments;

 Table dropped.

 /* 앞에서도 간단히 언급했지만 table이 drop되어 recycle bin에 담기는 경우,

     그 table에 종속된 indexes도 함께 담깁니다 */

 SQL> select object_name, original_name, type from recyclebin;

object_name	original_name	type
BIN$iQhKuu4XSu7gQBwDKns4Ag==$0	dept_id_pk	index
BIN$iQhKuu4YSu7gQBwDKns4Ag==$0	dept_location_ix	index
BIN$iQhKuu4bSu7gQBwDKns4Ag==$0	departments	table

 SQL> flashback table departments to before drop;

 Flashback complete.

 /* 안타깝게도 index의 경우 restore될 때에 original name이 아닌 recyclebin name으로

     restore됩니다. 그 결과 다음과 같은 결과를 보이게 됩니다. */

 SQL> select index_name from user_tables where table_name = 'DEPARTMENTS';

index_name

BIN$iQhKuu4XSu7gQBwDKns4Ag==$0

BIN$iQhKuu4YSu7gQBwDKns4Ag==$0

 /* index name이 중요하지 않다면 이 상태로 놔두어도 되지만,

     원래 index name으로 복구하고 싶다면 다음과 같이 index name을 rename해줍니다. */

 SQL> alter index "BIN$iQhKuu4XSu7gQBwDKns4Ag==$0" rename to dept_id_pk;

 Index altered.

 SQL> alter index "BIN$iQhKuu4YSu7gQBwDKns4Ag==$0" rename to dept_location_ix;

 Index altered.

 /* index name이 성공적으로 rename되었다는 것을 알 수 있습니다. */

 SQL> select index_name from user_tables where table_name = 'DEPARTMENTS';

index_name

dept_id_pk

dept_location_ix

※ 위에서 보면, index의 경우에는 table과는 다르게 original name으로 restore되지 않아서

    rename을 해야 하는 등의 번거로운 점이 있습니다.

    이 이유에 대해서 곰곰히 생각해 본 결과, 그럴 만한 이유가 있다는 것을 알았습니다.

    예를 들어, index도 original name으로 restore된다고 가정하면,

    만약 동일한 이름의 index가 이미 존재했을 때에 이 restore는 실패하게 됩니다.

    즉, index라는 restore의 主대상이 아닌 부수적인 object 때문에

    主대상인 table까지도 restore되지 못하는 일이 발생하게 된다는 것입니다.

    이런 경우 원인을 바로 찾지 못한 채 시간을 소비할 수도 있습니다.

    이런 현상을 막기 위해 restore의 主대상인 table만 original name으로 restore되도록 하고,

    나머지의 부수적인 objects는 recyclebin name으로 restore되도록 설계되어 있는 것 같습니다.

6. summary

이것으로 Flashback Drop기능에 대해서 제가 아는 내용은 거의 정리된 것 같습니다.

위의 모든 내용은 Oracle11g의 standard documents를 참조한 후,

Oracle11g환경에서 검증한 내용을 바탕으로 해서 작성했기 때문에,

Oracle10g환경과는 다소 다른 결과가 나타날 수도 있습니다.

• #oracle
• #flashback
• #drop