segment 확장에 대한 시뮬레이션
- 데이터파일 사이즈 1M, AUTOEXTEND = NO 인 테이블스페이스 생성
CREATE TABLESPACE insa_tbs
DATAFILE'/u01/app/oracle/oradata/ORA19C/insa_tbs.dbf' SIZE 1M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;- extent는 local로 관리하고 사이즈는 system이 자동으로 resize하게 autoallocate로 설정
-
autoextend 및 데이터파일 크기 확인
SELECT * FROM dba_data_files;
-
insa table(segment) 생성
CREATE TABLE hr.insa
TABLESPACE insa_tbs
AS
SELECT * FROM hr.employees WHERE 1 = 2;- where 1 = 2는 테이블 포맷만 가져옴
- segments, extents 확인
SELECT * FROM dba_segments WHERE OWNER='HR' AND segment_name='INSA';
SELECT * FROM dba_extents WHERE OWNER='HR' AND segment_name='INSA';- segment를 생성하고 바로 extent를 확인 해보면 extent가 할당되지 않은걸 확인할 수 있다.
- 해당 현상은 deferred_segment_creation 파라미터가 TRUE이기 때문에 발생
SHOW PARAMETER deferred_segment_creation;
- insa 테이블에 대량의 데이터 insert
INSERT INTO hr.insa
SELECT * FROM hr.employees;
INSERT INTO hr.insa
SELECT * FROM hr.insa; -- 배수로 insert- autoextend = no인 1M 데이터파일에 대량의 데이터를 insert하면 용량이 가득차 오류를 발생
- 테이블스페이스에 속한 데이터파일에 free 공간이 없어서 extent를 할당할 수 없어서 오류발생
- rollback을 하면 데이터는 rollback 되지만 할당된 extent는 해제되지 않고 그대로 할당되어 있다.
- 해제되지 않고 15개의 extent가 남아있는걸 볼수 있다.
- truncate를 통해 extent 해제
truncate table hr.insa;
SELECT * FROM dba_extents WHERE OWNER='HR' AND segment_name='INSA';- truncate는 undo 발생량이 작고 extent를 해제한다. delete는 undo 발생하고 extent를 해제하지 않는다.
AUTOEXTEND ON 설정 시뮬레이션
- autoextend on 설정 테이블스페이스 생성
CREATE TABLESPACE insa_tbs
DATAFILE'/u01/app/oracle/oradata/ORA19C/insa_tbs.dbf' SIZE 1M
AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;2.ins 테이블생성(segment)
CREATE TABLE hr.insa
TABLESPACE insa_tbs
AS
SELECT * FROM hr.employees WHERE 1 = 2;- 대량의 데이터 insert
INSERT INTO hr.insa
SELECT * FROM hr.employees;
INSERT INTO hr.insa
SELECT * FROM hr.insa; --배수의 데이터 insert- segments와 extents를 확인해보면 대량의 extents가 할당된걸 확인 할 수 있고, 데이터 파일의 사이즈도 1M를 넘어 자동으로 확장된걸 확인 할 수 있다.
SELECT * FROM dba_segments WHERE OWNER='HR' AND segment_name='INSA';
SELECT * FROM dba_extents WHERE OWNER='HR' AND segment_name='INSA';
- extent가 초기에는 64kbyte 할당되었다가 대량의 데이터가 들어오자 system이 자동으로 1mbyte로 변경하였다.
- rollback을 시킨후 107건의 데이터만 다시 insert한다.
rollback;
INSERT INTO hr.insa
SELECT * FROM hr.employees;
SELECT * FROM dba_extents WHERE OWNER='HR' AND segment_name='INSA';- extents를 확인해보면 rollback을 했음에도 대량의 extent가 할당되어있는걸 확인할 수 있다.
- 대량의 extent가 할당되어있는 상태에서 적은양의 데이터만 insert하고 해당 데이터를 full scan 하게 될 경우 전체 extent를 data buffer cache에 올리게 되어 성능상으로 매우 안좋아진다.
free space
- 데이터파일의 사용가능한 용량을 조회할 수 있다.
- free 용량이 0인 경우 조회되지 않는다.
SELECT tablespace_name, file_id, sum(bytes)/1024/1024 AS free_mbyte
FROM dba_free_space --free 공간이 아예 없으면 조회되지 않는다.
GROUP BY tablespace_name, file_id ; -- free 공간 확인 뷰
- 다른 딕셔너리와 join을 통해 직관적인 정보를 얻을 수 있다
SELECT a.tablespace_name,
b.file_name,
b.bytes/1024/1024 as "Total Size MB",
(b.bytes - c.free_byte)/1024/1024 as "Used Size MB",
c.free_byte/1024/1024 as "Free Size MB",
b.autoextensible
FROM dba_tablespaces a, dba_data_Files b,
(SELECT tablespace_name, file_id, sum(bytes) AS free_byte
FROM dba_free_space
GROUP BY tablespace_name, file_id) c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name
AND b.file_id = c.file_id;
테이블스페이스 크기 조정
테이블스페이스 생성 후에 AUTOEXTEND ON 을 지정하여 자동으로 확장기능 설정
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ORA19C/insa_tbs.dbf' AUTOEXTEND ON테이블스페이스 생성 후에 수동으로 데이터파일 RESIZE 조절
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ORA19C/insa_tbs.dbf' RESIZE 10M;테이블스페이스 데이터파일 추가
- 하나의 테이블스페이스 당 최대 포함할 수 있는 데이터파일의 수는 1022개
ALTER TABLESPACE insa_tbs ADD DATAFILE '/u01/app/oracle/oradata/ORA19C/insa_tbs02.dbf' SIZE 10M AUTOEXTEND ON;- free space를 통한 확인
테이블스페이스 삭제
DROP TABLESPACE insa_tbs INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
- CASCADE CONSTRAINTS : 서로다른 테이블스페이스안에 세그먼트들이 FOREING KEY 제약조건을 가지고 있을때 삭제가 가능하게 만드는 옵션
sqlplus 열폭 조절
테이블스페이스 모드 확인
select b.file_id,b.tablespace_name, b.file_name, a.checkpoint_change#, a.enabled
from v$datafile a , dba_data_files b
where a.file# = b.file_id;테이블스페이스 READ ONLY
- partial checkpoint 발생
- read only tablespace 안에 있는 테이블의 데이터를 읽을 수만 있다.(select 문 수행)
- DML 불허
- 객체 삭제 가능 (DROP 가능)
ALTER TABLESPACE insa_tbs READ ONLY;
-
insa_tbs 테이블스페이스에 table을 생성하려고 하면 오류가 발생한다.
-
다만, 기존에 있던 테이블에 대한 컬럼 추가,수정은 가능하다.
-
기존 테이블에 있던 컬럼 삭제는 불가능하다. 왜냐하면 extent에 있던 데이터를 삭제하는 작업을 수행하기 때문이다.
-
read only 모드에서도 not null 제약조건은 추가 할수 있고,
pk , unique 제약조건 같은 경우 유니크 인덱스를 해당 테이블스페이스에 만들게 되면 추가 할수가 없다. -
foreign key 제약조건은 참조되는 테이블스페이스가 read only 모드가 아닌경우 추가 가능하다.
READ WRITE 모드 변경
-
READ ONLY 모드에서 READ WRITE 모드로 변경 하는 경우에도 partial checkpoint가 발생한다.
-
READ ONLY 모드에서 DROP은 가능하기때문에 변경사항을 checkpoint 해야한다.
-
테이블스페이스가 READ ONLY 모드일 때 데이터 파일은 변경되지 않지만, 다른 테이블스페이스나 전체 데이터베이스의 변경 사항이 있을 수 있습니다. READ WRITE 모드로 전환하기 전에 모든 데이터 파일의 상태를 동기화하여 일관된 상태를 보장해야 합니다.
테이블스페이스 OFFLINE
- partial checkpoint 발생
- 테이블스페이스에 속한 객체들은 사용할 수 없다.
- OFFLINE으로 설정할 수 없는 테이블스페이스
- SYSTEM
- UNDO
- Temporary tablespace
- 데이터 이관작업시 사용
OFFLINE 옵션
-
NORMAL : 기본값, partial checkpoint 발생
-
장애발생 시 사용 옵션
- TEMPORARY : 가능한 데이터 파일에 속한 dirty buffer만 디스크로 쓰는 작업수행(부분체크포인트 발생)
- IMMEDIATE : 체크포인트 발생하지 않고 즉시 offline 수행한다. 즉 후에 복구작업을 수행해야 한다. archivelog mode 에서 수행하는 옵션
데이터파일 이관 작업
- 테이블스페이스를 OFFLINE NORMAL로 설정
ALTER TABLESPACE insa_tbs OFFLINE NORMAL;
- 물리적으로 데이터파일 이동
mv -v /u01/app/oracle/oradata/insa_tbs.dbf /u01/app/oracle/oradata/ORA19C/insa_tbs.dbf
- 기존 데이터파일을 새로운 데이터파일 위치로 수정
ALTER TABLESPACE insa_tbs RENAME DATAFILE
'/u01/app/oracle/oradata/insa_tbs.dbf' TO
'/u01/app/oracle/oradata/ORA19C/insa_tbs.dbf';- 테이블스페이스를 ONLINE
ALTER TABLESPACE insa_tbs ONLINE;
모든 데이터 파일 이관
SELECT name FROM v$datafile;
SELECT name FROM v$tempfile;
-
기존 위치에 있는 데이터 파일들을 새로운 위치로 데이터 파일 이관작업
-
새로운 위치 : /home/oracle/userdata
-
리터럴 문자로 수정작업하기
SELECT 'mv -v '||name || ' /home/oracle/userdata' FROM v$datafile;
SELECT 'mv -v '||name || ' /home/oracle/userdata' FROM v$tempfile;
-
오라클데이터베이스를 정상적인 종료
shutdown immediate
-
모든 데이터 파일을 새로운 디스크 위치로 이동
-
오라클 데이터베이스를 MOUNT 까지만 열기
STARTUP MOUNT -
기존 데이터 파일을 새로운 위치로 데이터파일 수정
ALTER DATABASE RENAME FILE '기존파일위치' TO '이관 파일위치';
-
datafile, tempfile 딕셔너리 뷰 확인
-
정상적으로 open
ALTER DATABSE OPEN; -
이관 파일위치 확인
SELECT * FROM dba_data_Files;
