Undo TableSpace 관리(생성/제거)

UNDO TABLE:

ROLLBACK을 실행했을 때, SERVER PROCESS에게 UNDO SEGMENT를 주어 기존 단계로

돌아가기 위해 상태를 기억하고 있는 테이블.

 

1. UNDO TableSpace 조회

SELECT *

FROM DBA_TABLESPACES;

 

 

1. Undo table의 용량조회

    

SELECT A.BYTES "전체 BYTES", A.BLOCKS "전체 BLOCKS"

,B.BYTES"Free Btyes", B.BLOCKS"FREE BLOCKS"

,A.BYTES-B.BYTES"사용중인 BYTES"

,A.BLOCKS-B.BLOCKS"사용중인 BLOCKS"

FROM

(

SELECT BYTES,BLOCKS

FROM DBA_DATA_FILES

WHERE TABLESPACE_NAME='UNDOTBS1'

)A

CROSS JOIN

(

SELECT SUM(BYTES)"BYTES",SUM(BLOCKS)"BLOCKS"

FROM DBA_FREE_SPACE

WHERE TABLESPACE_NAME='UNDOTBS1'

)B;

 

--아래에서 40M로 사이즈를 늘려주었음 ( 기본값은 30M가 나올 수 있다.)

 

SELECT *

FROM V$SESSION

WHERE USERNAME IS NOT NULL AND

USERNAME NOT IN ('sysman','dbsnmp');

 

 

1. UNDO TABLE 전체 용량 늘리기

    

SELECT *

FROM DBA_DATA_FILES

WHERE TABLESPACE_NAME='UNDOTBS1';

 

 

ALTER DATABASE DATAFILE 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORACLE10\UNDOTBS01.DBF' RESIZE 40M; -- 41943040BYTES

 

1. Undo Table 관련 Parameter 살펴보기(1)

   Select *

FROM V$PARAMETER

WHERE NAME='undo_management'; --value : auto : undo의 관리는 오라클이 알아서 관리(=auto)

 

1. Undo TableSpace 생성

 

기본적으로 C:\oracle\product\10.1.0\oradata\oracle10\에 있는 UNDOTBS01.DBF 이 아닌 다른 DBF파일을 생성하여 사용해보자.

 

 

실습을 위해 D:\UNDOTBS 폴더 생성후,

 

CREATE UNDO TABLESPACE NEWUNDOTBS DATAFILE 'D:\UNDOTBS\NEWUNDOTBS01.DBF' SIZE 30M;

 

    이제 기존에 사용하던 Undo Tablespace와 위에서 방금 생성한 Undo Tablespace를 조회해보자.

 

SELECT *

FROM DBA_DATA_FILES

WHERE TABLESPACE_NAME IN

(

SELECT TABLESPACE_NAME

FROM DBA_TABLESPACES

WHERE CONTENTS='UNDO'

);

 

 

1. 기존에 사용중인 Undo TableSpace 제거

 

DROP TABLESPACE PROJECTTBS;

--ORA-01549: 테이블스페이스가 비어있지 않으므로 INCLUDING CONTENTS 옵션을 사용해 주십시오

메시지와 함께 삭제되지 않는 것을 확인할 수 있다. 이는 tablespace에 정보(contents)가 들어있기 때문이다.

 

이제 그 안에 들어 있는 CONTENTS를 확인해 보자.

 

SELECT *

FROM DBA_SEGMENTS

WHERE TABLESPACE_NAME='PROJECTTBS';

 

Contents가 들어있는 테이블을 강제로 제거하기 위해서는

    

    

--( DROP TABLESPACE PROJECTTBS INCLUDING CONTENTS; ) 명령을 사용하여 삭제해야 한다.(아래 내용에서 실습)

 

 

--실습을 위해 새로운 테이블스페이스 생성 CREATE TABLESPACE TESTTBS DATAFILE 'D:\PROJECTDATA\TESTTBS01.DBF' SIZE 2M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;     --테이블 스페이스 제거   DROP TABLESPACE TESTTBS; --옵션을 사용하지 않고도 잘 제거된다.       SELECT * FROM DBA_DATA_FILES 을 실행하여 삭제된 테이블 스페이스 잘 날라갔는지 확인한다.   잘 날라갔다….이제 HDD에 가서 확인을 해보면,     그림과 같이 여전히 남아 있는 것을 확인할 수 있다. (테이블스페이스에 데이터가 존재한다는 가정하에서) 이제 물리적인 하드 공간에서도 이 테이블스페이스를 제거하기 위해서는   DROP TABLESPACE TESTTBS INCLUDING CONTENTS AND DATAFILES;   를 사용하여 지워야 한다.

 

 

이와 같이 이제 기존 UNDO TABLESPACE를 제거하여보자.

 

DROP TABLESPACE UNDOTBS1

INCLUDING CONTENTS AND DATAFILES;

--ORA-30013: 실행 취소 테이블스페이스 'UNDOTBS1'은(는) 현재 사용 중임

 

즉, 현재 사용중이므로 새롭게 생성한 UNDO TABLESPACE를 활성화시키고, UNDOTBS1은 비활성화시켜야 한다.

 

 

 

--UNDO TABLESPACE 변경하기

 

--현재 사용중인 UNDO TABLESPACE를 모른다는 가정하에서 현 undo tablespace 조회

 

SELECT *

FROM V$PARAMETER

WHERE NAME='undo_tablespace'

 

 

1. --새롭게 생성한 'NEWUNDOTBS' UNDO TABLESPACE는 활성화시킨다.

 

ALTER SYSTEM SET UNDO_TABLESPACE ='NEWUNDOTBS'

SCOPE =BOTH;

 

--이제 다시 기존 UNDO_TABBLESPACE를 제거한다.

DROP TABLESPACE UNDOTBS1

INCLUDING CONTENTS AND DATAFILES;

 

 

    

 

    

--새롭게 지정된 UNDO TABLESPACE의 사용량 조회   SELECT A.BYTES "전체 BYTES", A.BLOCKS "전체 BLOCKS" ,B.BYTES"Free Btyes", B.BLOCKS"FREE BLOCKS" ,A.BYTES-B.BYTES"사용중인 BYTES" ,A.BLOCKS-B.BLOCKS"사용중인 BLOCKS" FROM ( SELECT BYTES,BLOCKS FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='NEWUNDOTBS' )A CROSS JOIN ( SELECT SUM(BYTES)"BYTES",SUM(BLOCKS)"BLOCKS" FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME='NEWUNDOTBS' )B;

 

 

(위에서 실습한) 기존 원 상태로 되돌리기

--실습을 위해서는 우선 Oracle을 재구동하여야 된다.   CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE 'C:\oracle\product\10.1.0\oradata\oracle10\UNDOTBS01.DBF' SIZE 30M;   ALTER SYSTEM SET UNDO_TABLESPACE ='UNDOTBS1' SCOPE =BOTH;   DROP TABLESPACE NEWUNDOTBS INCLUDING CONTENTS AND DATAFILES;     결과값 확인:   SELECT * FROM V$PARAMETER WHERE NAME='undo_tablespace';

 

1. Undo Table 관련 Parameter 살펴보기(2)

    

AUTOEXTEND 옵션 활성화

 

--UNDO TABLESPACE의 용량을 다 썼을 때, 문제가 발생하게 되는데, 관리자가 일일이 확인할 필요 없이 자동적으로 사이즈가 커지는 기능

 

SELECT *

FROM DBA_DATA_FILES

WHERE TABLESPACE_NAME IN

(

SELECT TABLESPACE_NAME

FROM DBA_TABLESPACES

WHERE CONTENTS='UNDO'

);

 

--AUTOEXTENSIBLE 값 (기본값 NO)

테이블 스페이스를 생성할 때, 해당 옵션을 함꼐 기술하여 생성하는 것이 좋다.

 

방법1

UNDO 테이블 스페이스 생성시

CREATE UNDO TABLESPACE UNDOTBS1

DATAFILE 'C:\oracle\product\10.1.0\oradata\oracle10\UNDOTBS01.DBF' SIZE 30M

AUTOEXTEND ON;

 

방법 2

ALTER DATABASE

DATAFILE 'C:\oracle\product\10.1.0\oradata\oracle10\UNDOTBS01.DBF'

AUTOEXTEND ON;