ORACLE Database / WAS (iAS) / SQL 자료실입니다.

ORACLE Database / WAS (iAS) / SQL 자료실 - 포기하지 않으면 실패하지 않는다!

Author : 조성환 [ ladmin ]

Vote: 1695, Hit: 6401, Lines: 181, Category: Etc.

Oracle 10g 에서 제공하는 recyclebin 에 대한 기능 설명

출처 : http://blog.empas.com/dbintegrity/24520976

Oracle 10g database 에서 제공하는 recyclebin 에 대한 기능을 소개합니다.

참고로 FLASHBACK DROP 기능을 위해 모든 oracle user 에게 recyclebin 이 제공 됩니다.

Flashback 테이블

실수로 삭제한 테이블을 손쉽게 다시 유효화할 수 있는 Oracle Database 10g의 Flashback 테이블 기능

매우 중요한 테이블을 실수로 삭제하여 즉시 복구해야 하는 상황은 생각보다 자주 일어나는 시나리오입니다. (때로는 이처럼 불운한 사용자가 DBA일 수도 있습니다!)

Oracle9i Database에는 Flashback 질의 옵션 개념이 도입되어 데이타를 과거의 시점에서부터 검색하지만, 테이블 삭제 같은 DDL 작업을 순간적으로 되돌릴 수는 없습니다. 이 경우 유일한 수단은 다른 데이타베이스에서 테이블스페이스 적시 복구를 사용한 다음, 엑스포트/임포트 또는 기타 메서드를 사용해 현재 데이타베이스에 테이블을 다시 생성하는 것입니다. 이 프로시저를 수행하려면 복제를 위해 다른 데이타베이스를 사용하는 것은 물론, DBA의 많은 노력과 귀중한 시간이 요구됩니다.

하지만 Oracle Database 10g의 Flashback 테이블 기능으로 들어가면 몇 개의 문만 실행하여 삭제된 테이블을 간단히 검색할 수 있습니다. 그럼, 지금부터 이 기능의 작동 원리에 대해 알아보도록 하겠습니다.

자유로운 테이블 삭제
먼저, 현재 스키마의 테이블을 확인해 봅시다.
SQL> select * from tab;

TNAME                    TABTYPE CLUSTERID
------------------------ ------- ----------
RECYCLETEST              TABLE

그런 다음, 아래와 같이 고의로 테이블을 삭제합니다.
SQL> drop table recycletest;

Table dropped.

이제 테이블의 상태를 확인합니다.
SQL> select * from tab;

TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE

RECYCLETEST 테이블이 사라졌지만 새 테이블인 BIN$04LhcpndanfgMAAAAAANPw==$0이 있다는 점에 주목합니다. 좀 더 자세히 설명하면 삭제된 테이블 RECYCLETEST가 완전히 사라지는 대신 시스템 정의 이름으로 이름이 변경된 것입니다. 이 테이블은 여전히 동일한 테이블스페이스에 있으며 원래 테이블과 구조도 동일합니다. 테이블에 인덱스 또는 트리거가 정의되어 있는 경우, 마찬가지로 테이블과 동일한 명명 규칙을 사용하여 이름이 변경됩니다. 프로시저 같은 종속적인 소스는 무효화되지만, 대신 원래 테이블의 트리거 및 인덱스가 이름이 변경된 테이블인 BIN$04LhcpndanfgMAAAAAANPw==$0에 들어가 삭제된 테이블의 완전한 객체 구조를 보존합니다.

테이블 및 연관된 객체는 PC에 있는 것과 유사한 “휴지통(RecycleBin)”이라고 하는 논리적 컨테이너에 들어갑니다. 하지만 이들 객체가 이전에 있던 테이블스페이스에서 옮겨지는 것은 아니며 계속 해당 테이블스페이스에서 공간을 차지하고 있습니다. 휴지통은 단순히 삭제된 객체의 목록을 만드는 논리적 구조입니다. 휴지통의 컨텐트를 확인하려면 SQL*Plus 프롬프트에서 다음 명령을 사용합니다(SQL*Plus 10.1이 있어야 함).
SQL> show recyclebin

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ ------------------
RECYCLETEST      BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE        2004-02-16:21:13:31

이렇게 하면 테이블의 원래 이름인 RECYCLETEST는 물론, 삭제된 후 생성된 새 테이블 이름과 동일한 휴지통에서의 새 이름이 표시됩니다. (참고: 정확한 이름은 플랫폼별로 다를 수 있습니다.) 테이블을 다시 유효화하기 위해서는 FLASHBACK TABLE 명령만 사용하면 됩니다.
SQL> FLASHBACK TABLE RECYCLETEST TO BEFORE DROP;

FLASHBACK COMPLETE.

SQL> SELECT * FROM TAB;

TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ----------
RECYCLETEST                    TABLE

자, 테이블이 정말 간단히 유효화되지 않습니까? 지금 휴지통을 확인하면 비어 있습니다.
여기서 유의할 점은 테이블을 휴지통에 넣는다고 해도 원래 테이블스페이스의 공간이 제거되는 것은 아니라는 것입니다. 공간을 제거하려면 다음을 사용해 휴지통을 지워야 합니다.
PURGE RECYCLEBIN;

하지만 Flashback 기능을 사용하지 않고 테이블을 완전히 삭제하려면 어떻게 해야 할까요? 이 경우 다음을 사용하면 테이블을 영구적으로 삭제할 수 있습니다.
DROP TABLE RECYCLETEST PURGE;

이 명령을 실행하면 테이블 이름이 휴지통 이름으로 변경되는 것이 아니라, 10g 이전 버전에서처럼 영구적으로 삭제됩니다.

휴지통 관리

이 프로세스에서 테이블을 완전히 삭제하지 않아 테이블스페이스를 해제하지 않은 상태에서 삭제된 객체가 테이블스페이스의 모든 공간을 차지하면 어떤 일이 발생할까요?

답은 간단합니다. 그 같은 상황은 결코 발생하지 않습니다. 데이타 파일에 데이타를 추가할 공간을 확보해야 할 정도로 휴지통 데이타가 테이블스페이스로 꽉 차는 상황이 발생하면 테이블스페이스는 이른바 “공간 압축” 상태에 들어갑니다. 위의 시나리오에서 객체는 선입선출 방식으로 휴지통에서 자동으로 지워지며, 종속된 객체(예: 인덱스)는 테이블보다 먼저 제거됩니다.

마찬가지로 특정 테이블스페이스에 정의된 사용자 할당량에도 공간 압축이 발생할 수 있습니다. 테이블에는 사용 가능한 공간이 충분하지만 사용자는 할당된 공간이 부족할 수 있습니다. 이러한 상황에서 Oracle은 해당 테이블스페이스의 사용자에 속한 객체를 자동으로 지웁니다.

이 외에도 여러 가지 방법으로 휴지통을 수동으로 제어할 수 있습니다. 삭제한 후 휴지통에서 TEST라고 명명된 특정 테이블을 삭제하려면 다음을 실행하거나,
PURGE TABLE TEST;

아래와 같이 해당 휴지통 이름을 사용합니다.
PURGE TABLE "BIN$04LhcpndanfgMAAAAAANPw==$0";

이 명령을 실행하면 휴지통에서 TEST 테이블과 인덱스, 제약 조건 등과 같은 모든 종속 객체가 삭제되어 일정 공간을 확보하게 됩니다. 하지만 휴지통에서 인덱스를 영구적으로 삭제하려면 다음을 사용합니다.
purge index in_test1_01;

이렇게 하면 인덱스만 제거되며 테이블의 복사본은 휴지통에 남아 있습니다.
때로는 상위 레벨에서 지우는 것이 유용할 수도 있습니다. 예를 들어, 테이블스페이스 USERS의 휴지통에 있는 모든 객체를 지워야 한다면 다음을 실행합니다.
PURGE TABLESPACE USERS;

휴지통에서 해당 테이블스페이스의 특정 사용자만 지워야 하는 경우도 있습니다. 이 접근방법은 사용자가 많은 수의 과도 상태 테이블을 생성 및 삭제하는 데이타 웨어하우스 유형의 환경에 유용합니다. 다음과 같이 위의 명령을 수정해 지우기 작업을 특정 사용자만으로 제한할 수 있습니다.
PURGE TABLESPACE USERS USER SCOTT;

사용자 SCOTT는 다음 명령으로 휴지통을 지웁니다.
PURGE RECYCLEBIN;

DBA는 다음을 사용해 테이블스페이스의 모든 객체를 지울 수 있습니다.
PURGE DBA_RECYCLEBIN;

위에서 살펴본 것처럼 휴지통은 사용자의 특정한 요구에 맞는 다양한 방식으로 관리할 수 있습니다.

테이블 버전 및 Flashback

다음과 같이 동일한 테이블을 여러 번 생성 및 삭제해야 하는 경우도 흔히 발생합니다.
CREATE TABLE TEST (COL1 NUMBER);
INSERT INTO TEST VALUES (1);
COMMIT;
DROP TABLE TEST;
CREATE TABLE TEST (COL1 NUMBER);
INSERT INTO TEST VALUES (2);
COMMIT;
DROP TABLE TEST;
CREATE TABLE TEST (COL1 NUMBER);
INSERT INTO TEST VALUES (3);
COMMIT;
DROP TABLE TEST;

여기서 TEST 테이블을 순간적으로 되돌린다면 COL1 열의 값은 어떻게 될까요? 기존의 개념에서 보면 휴지통에서 테이블의 첫 번째 버전이 검색되고 COL1 열의 값은 1이 될 것입니다. 하지만 실제로는 첫 번째가 아닌 테이블의 세 번째 버전이 검색되므로 COL1 열의 값은 1이 아닌 3이 됩니다.

이 때 삭제된 테이블의 다른 버전을 검색할 수도 있습니다. 하지만 TEST 테이블이 존재하는 이러한 작업이 불가능한데, 이 경우 다음 두 가지를 선택할 수 있습니다.
다음과 같이 이름 바꾸기 옵션을 사용합니다.
FLASHBACK TABLE TEST TO BEFORE DROP RENAME TO TEST2;
FLASHBACK TABLE TEST TO BEFORE DROP RENAME TO TEST1;

이렇게 하면 테이블의 첫 번째 버전은 TEST1으로, 두 번째 버전은 TEST2로 다시 유효화됩니다. 또한 TEST1 및 TEST2에서 COL1의 값은 각각 1과 2가 됩니다. 또는
복원할 테이블의 특정 휴지통 이름을 사용합니다. 이를 위해 먼저 테이블의 휴지통 이름을 식별한 후 다음을 실행합니다.
FLASHBACK TABLE "BIN$04LhcpnoanfgMAAAAAANPw==$0" TO BEFORE DROP RENAME TO TEST2;
FLASHBACK TABLE "BIN$04LhcpnqanfgMAAAAAANPw==$0" TO BEFORE DROP RENAME TO TEST1;

이렇게 하면 삭제된 테이블의 두 가지 버전이 복원됩니다.

주의 사항

삭제 취소 기능을 사용하면 테이블의 이름이 원래대로 돌아가지만 인덱스 및 트리거 같은 연관된 객체는 그렇지 않으며 계속 휴지통 이름으로 남아 있습니다. 또한 뷰 및 프로시저 같이 테이블에 정의된 소스는 재컴파일되지 않으며 무효화된 상태로 남게 됩니다. 이러한 이전 이름들은 수동으로 검색한 다음 순간적으로 되돌린 테이블에 적용해야 합니다.

이 정보는 USER_RECYCLEBIN으로 명명된 뷰에서 관리됩니다. 테이블을 순간적으로 되돌리기 전에 다음 질의를 사용해 이전 이름을 검색합니다.
SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE
FROM USER_RECYCLEBIN
WHERE BASE_OBJECT = (SELECT BASE_OBJECT FROM USER_RECYCLEBIN
WHERE ORIGINAL_NAME = 'RECYCLETEST')
AND ORIGINAL_NAME != 'RECYCLETEST';

OBJECT_NAME                    ORIGINAL_N TYPE
------------------------------ ---------- --------
BIN$04LhcpnianfgMAAAAAANPw==$0 IN_RT_01   INDEX
BIN$04LhcpnganfgMAAAAAANPw==$0 TR_RT      TRIGGER

테이블을 순간적으로 되돌리면 RECYCLETEST 테이블의 인덱스 및 트리거에는 OBJECT_NAME 열에 나타난 이름이 지정됩니다. 위의 질의에서는 원래 이름을 사용해 객체의 이름을 다음과 같이 변경할 수 있습니다.
ALTER INDEX "BIN$04LhcpnianfgMAAAAAANPw==$0" RENAME TO IN_RT_01;
ALTER TRIGGER "BIN$04LhcpnganfgMAAAAAANPw==$0" RENAME TO TR_RT;

한가지 유의해야 할 예외는 비트맵 인덱스입니다. 비트맵 인덱스를 삭제하면 휴지통에 들어가지 않으므로 검색할 수 없습니다. 또한 뷰에서 제약 조건 이름을 검색할 수 없습니다. 따라서 이 인덱스의 이름은 다른 소스에서 변경해야 합니다.

Flashback 테이블의 다른 용도

Flashback Drop Table에는 테이블 삭제 작업을 되돌리는 것 외에도 다른 기능이 있습니다. Flashback 질의와 마찬가지로 이를 사용해 테이블을 다른 시점으로 다시 유효화하여 전체 테이블을 “이전” 버전으로 바꿀 수 있습니다. 예를 들어, 다음 문을 사용하면 테이블을 시스템 변경 번호(SCN) 2202666520으로 다시 유효화합니다.
FLASHBACK TABLE RECYCLETEST TO SCN 2202666520;

이 기능은 Oracle Data Pump 기술로 다른 테이블을 생성하고 Flashback으로 테이블을 해당 SCN의 데이타 버전으로 채운 다음, 원래의 테이블을 새 테이블로 바꿉니다. 테이블을 어느 정도까지 순간적으로 되돌릴 수 있는지 확인하려면 Oracle Database 10g의 버전 관리 기능을 사용합니다. (자세한 내용은 이 시리즈의 1주 부분을 참조하십시오.) 또한 Flashback 절에 SCN 대신 타임 스탬프를 지정할 수도 있습니다.

Flashback 테이블 가능에 대한 자세한 내용은 Oracle Database Administrator's Guide 10g Release 1 (10.1)을 참조하십시오.

Prev: 오라클에서 테이블 삭제후 쓰레기 (recyclebin) 값 처리 방법
Next: DATE 형식으로 들어간 필드 날짜 수정하기 (초강추)

2007/11/19(15:31) from 203.234.120.78
CrazyWWWBoard 2000