Oracle Structure 셋째날(Undo Table)

/* AUM(AUTOMATIC UNDO MANAGEMENT) TABLESPACE */

 

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

 

UNDO TABLE:

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

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

 

SELECT *

FROM DBA_TABLESPACES;

 

 

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');

 

 

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

--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

 

 

 

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

select *

FROM V$PARAMETER

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

 

 

--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'

);

 

 

 

--기존에 생성하여 사용중인 다른 테이블 스페이스 제거

DROP TABLESPACE PROJECTTBS;

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

 

--안에 들어 있는 CONTENTS 확인하기

 

SELECT *

FROM DBA_SEGMENTS

WHERE TABLESPACE_NAME='PROJECTTBS';

 

--일반 옵션으론 삭제 되지 않는다. 삭제를 하려면

--( 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'

 

;

 

--새롭게 생성한 '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';

 

 

중요한UNDO TABLESPACE 옵션 값 살펴보기

AUTOEXTEND OPTION

--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;