ORACLE 테이블스페이스
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 테이블 스페이스[편집]
1.1 TABLESPACE 종류[편집]
- SYSTEM TABLESPACE
- 데이터 딕셔너리 정보들이 저장되어 있음, 이 TABLESPACE 가 손상될 경우 Oracle 서버가 시작이 안됨 - SYS계정 소유이지만 조회만 가능
- 딕셔너리 주요 정보
- 데이터베이스의 논리적인 구조와 물리적인 구조 정보들
- 객체의 정의와 공간 사용 정보들
- 제약조건에 관련된 정보들
- 사용자에 관련된 정보들
- Role, Privilege 등에 관련된 정보들
- 감사 및 보안등에 관련된 정보들
- SYSAUX TABLESPACE
: 10g 버전부터 등장, Oracle 서버의 성능 튜닝을 위한 데이터 저장
- 데이터 TABLESPACE
: 가장 일반적으로 많이 사용되는 TABLESPACE 로 DBA가 필요에 의해 만드는 TABLESPACE . DBA에 의해 생성,삭제.
1.2 테이블스페이스 생성/변경/중지/삭제[편집]
1.2.1 TABLESPACE 생성[편집]
CREATE TABLESPACE 테이블스페이스명
DATAFILE '파일경로1' SIZE integer [M/K], '파일경로2' SIZE integer [M/K]
[ MINIMUM EXTENT integer [M/K]]
[ BLOCKSIZE integer [K]]
[ DEFAULT STORAGE (
INITIAL integer [M/K]
NEXT integer [M/K]
MAXEXTENTS integer
MINEXTENTS integer
PCTINCREASE integer)]
[ ONLINE | OFFLINE ]
[ PERMANENT | TEMPORARY ]
[ EXTENT MANAGEMENT [ DICTIONARY | LOCAL
[ AUTOALLOCATE | UNIFORM [ SIZE integer [M/K]]]]]
[ SEGMENT SPACE MANAGEMENT [ MANUAL | AUTO]]
;
-- AUTOEXTEND ON NEXT 10M ==> (옵션)데이타 파일 용량초과시 자동증가설정
-- MAXSIZE 100M ==> (옵션)데이타파일 최대크기지정
-- EXTENT MANAGEMENT LOCAL ==> (옵션)
-- UNIFORM SIZE 1M ==> (옵션)
-- DEFAULT STORAGE(
: INITIAL 80K ==> 테이블 스페이스의 맨 첫번째 EXTENTS의 크기
: NEXT 80K ==> 다음 EXTENTS의 크기
: MINNEXTENTS 1 ==> 생성할 EXTENTS의 최소값
: MAXNEXTENTS 121 ==> 생성할 EXTENTS의 최대값
: PCTINCREASE 80 ==> EXTENTS의 증가율,(DEFAULT값은 50%)
: )
1.2.1.1 UNDO TABLESPACE 생성[편집]
CREATE UNDO TABLESPACE [테이블 스페이스명] ==> 이부분만 다름.
DATAFILE 'C:\경로\TEST_UNDO.DBF'
SIZE 10M
AUTOEXTEND ON NEXT 10M
MAXSIZE 100M
==> UNDO_MANAGEMENT와 UNDO_TABLESPACE, UNDO_RETENTION PARAMETER를 제공
==> 지역적으로 관리되는 익스텐트만 사용가능
==> 시스템에 의해 관리되는 익스텐트 할당만 사용가능하다.
: (UNIFORMSIZE를 할당 할 수 없고 AUTOALLOCATE만 가능)
1.2.2 테이블스페이스 변경[편집]
- OPEN 상태에서 DATAFILE 이동
alter tablespace 테이블스페이스명 offline;
- OFFLINE 상태에서 DATAFILE 이동
offline 된 T/S에 대해 복사/이동 후
alter tablespace 테이블스페이스명 rename datafile '파일경로' to '파일경로';
alter tablespace 테이블스페이스명 online;
- MOUNT 상태에서 DATAFILE 이동
startup mount;
- 해당 T/S에 대해 복사/이동 후
alter database rename file '파일경로' to '파일경로';
alter database open;
- 모든 데이타 파일은 mount상태에서 복사/이동 가능
- system 파일은 mount상태에서만 복사/이동 가능
1.2.2.1 테이블의 테이블스페이스 MOVE[편집]
ALTER TABLE [테이블명]
MOVE TABLESPACE [테이블 스페이스명]
1.2.2.2 TABLESPACE 사이즈 변경(RESIZE)[편집]
ALTER TABLESPACE ts_txxxx
RESIZE 500G;
1.2.2.3 TABLESPACE 자동증가/최대 사이즈 변경[편집]
ALTER TABLESPACE ts_txxxx
AUTOEXTEND ON
NEXT 100M
MAXSIZE 500G;-- 최대사이즈 , UNLIMITED => 무제한 증가
1.3 TABLESPACE 사이즈 관리[편집]
- 테이블스페이스 사이즈 변경
ALTER TABLESPACE TS_D01 RESIZE 100M;
- 데이터파일 사이즈 변경
ALTER DATABASE DATAFILE 'C:\경로\TEST1.DBF' RESIZE 10M;
- 데이터파일 추가
ALTER TABLESPACE [테이블 스페이스명] ADD DATAFILE 'C:\경로\TEST2.DBF' SIZE 10M;
- 최대 테이블스페이스 사이즈 변경
ALTER TABLESPACE [테이블 스페이스명]
ADD DATAFILE 'C:\경로\TEST2.DBF' SIZE 10M
AUTOEXTEND ON NEXT 100M
MAXSIZE 100G;
==> 10M씩 자동증가
1.4 TABLESPACE 삭제[편집]
DROP TABLESPACE [테이블 스페이스명] INCLUDE CONTENTS;
==> 테이블스페이스 내의 객체(테이블,인덱스등)를 다 지운다.
DROP TABLESPACE [테이블 스페이스명] INCLUDING CONTENTS;
==> 테이블스페이스의 모든 세그먼트를 삭제한다.
==> 단, 데이타가 있는 테이블스페이스는 삭제할수 없다.
DROP TABLESPACE [테이블 스페이스명] CASCADE CONSTRAINTS;
==> 삭제된 테이블스페이스 내의 테이블의 기본키와 유일키를 참조하는
다른 테이블스페이스의 테이블로부터 참조무결성 제약 조건을 삭제한다.
DROP TABLESPACE [테이블 스페이스명] INCLUDING CONTENTS AND DATAFILES;
==> 물리적파일까지 삭제한다.
1.4.1 인덱스의 TABLESPACE 변경[편집]
ALTER INDEX 인덱스명 REBUILD TABLESPACE [테이블 스페이스명]
[ PARALLEL parallel_num ]
[ LOGGING or NOLOGGING ]
;
SELECT 'ALTER INDEX '||index_name||' REBUILD TABLESPACE TS_OO_'||substr(a.index_name,4,2)||'_I;'
FROM user_indexes a
WHERE a.index_name NOT IN (SELECT index_name from user_part_indexes)
AND substr(a.index_name,4,2) IN (SELECT SUBJECT_CD FROM TB_DBA_SUBJECT_CD);
1.4.2 인덱스/테이블 TABLESPACE 변경 스크립트[편집]
SELECT DECODE (segment_type, 'TABLE', segment_name, table_name) order_col1
, DECODE (segment_type, 'TABLE', 1, 2) order_col2
, 'alter '
|| segment_type
|| ' '
|| segment_name
|| DECODE (segment_type, 'TABLE', ' MOVE ', ' REBUILD ')
|| CHR (10)
|| ' TS_오너_01 '
|| CHR (10)
|| ' storage ( initial '
|| initial_extent
|| ' next '
|| next_extent
|| CHR (10)
|| ' minextents '
|| min_extents
|| ' maxextents '
|| max_extents
|| CHR (10)
|| ' pctincrease '
|| pct_increase
|| ' freelists '
|| freelists
|| ');'
FROM user_segments, (SELECT table_name, index_name FROM user_indexes)
WHERE segment_type IN ('TABLE', 'INDEX') AND segment_name = index_name(+)
ORDER BY 1, 2;
1.4.3 TABLESPACE 수정이나 삭제시 ONLINE/OFFLINE 설정[편집]
ALTER TABLESPACE [테이블 스페이스명] ONLINE
ALTER TABLESPACE [테이블 스페이스명] OFFLINE
1.4.4 TABLESPACE 이름 변경[편집]
ALTER TABLESPACE [asis-TABLE] RENAME TO [tobe-TABLE]
1.4.5 ROLLBACK SEGMENT의 사용상황 보기[편집]
--: EXTENTS = 현재 할당된 EXTENT의 수 --: EXTENDS = 마지막 트랜잭션에 의해 할당된 EXTENT의 수
SELECT SUBSTRB(A.SEGMENT_NAME, 1, 10) AS SEGMENT_NAME
, SUBSTRB(A.TABLESPACE_NAME, 1, 10) AS TABLESPACE_NAME
, TO_CHAR(A.SEGMENT_ID, '99,999') AS SEG_ID
, TO_CHAR(A.MAX_EXTENTS, '999,999') AS MAX_EXT
, TO_CHAR(B.EXTENTS, '999,999') AS EXTENTS
, TO_CHAR(B.EXTENDS, '999,999') AS EXTENDS
, TO_CHAR((A.INITIAL_EXTENT + (B.EXTENTS - 1) * A.NEXT_EXTENT) / 1000000, '9,999.999') AS "ALLOC(MB)"
, TO_CHAR(XACTS, '9,999') AS XACTS
FROM DBA_ROLLBACK_SEGS A
, V$ROLLSTAT B
WHERE A.SEGMENT_ID = B.USN(+) ORDER BY 1;
2 템프테이블 스페이스[편집]
2.1 TEMP TABLESPACE 확인[편집]
2.1.1 TEMP TABLESPACE 확인[편집]
SELECT * FROM DBA_TEMP_FILES ;
- 사용중인 TEMP TABLESPACE 는 삭제가 안되므로 신규로 템프 테이블스페이스를 생성
CREATE BIGFILE TEMPORARY TABLESPACE TEMP2 TEMPFILE
SIZE 100G AUTOEXTEND ON NEXT 100M MAXSIZE 300G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
2.2 TEMP TABLESPACE 변경[편집]
ALTER TABLESPACE TEMP
AUTOEXTEND ON
NEXT 100M
MAXSIZE 500G;
2.3 템프 테이블스페이스 조회[편집]
SELECT *
FROM dba_temp_free_space;
2.4 템프 테이블 스페이스 정보[편집]
SELECT d.TABLESPACE_NAME
, d.FILE_NAME
, d.BYTES / 1024 / 1024 SIZE_MB
, d.AUTOEXTENSIBLE
, d.MAXBYTES / 1024 / 1024 MAXSIZE_MB
, d.INCREMENT_BY * (v.BLOCK_SIZE / 1024) / 1024 INCREMENT_BY_MB
FROM dba_temp_files d, v$tempfile v
WHERE d.FILE_ID = v.FILE#
ORDER BY d.TABLESPACE_NAME, d.FILE_NAME
2.5 템프테이블 스페이스 사이즈 추가[편집]
ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA' SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE 32767M;
2.6 TEMP TABLESPACE 생성[편집]
- . RENAME 이 불가능하다. 신규생성만 가능함.
CREATE TEMPORARY TABLESPACE [테이블 스페이스명]
TEMPFILE 'C:\경로\TEST_TEMP.DBF'
SIZE 10M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 512K
==> LOCALY MANAGED TABLESPACE NUIFORM SIZE만 생성가능하다.
(주의)AUTOALLOCATE, EXTENT MANAGEMENT DICIONARY OPTION을 사용하면 ORA-25319 ERROR 발생한다.
==> RENAME 이 불가능하다.
2.6.1 TEMP TABLESPACE를 DEFAULT TABLESPACE로 변경[편집]
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE [테이블 스페이스명]
2.7 템프테이블 사용율 조회 쿼리[편집]
/* TEMP 사용 쿼리 확인 */
select a.username, a.sid, a.serial#, b.blocks, a.program, a.module, a.action, a.machine, a.status, a.event, d.sql_Text ,
b.blocks*8192/1024/1024 mb
from v$session a,
v$sort_usage b,
v$process c,
v$sqlarea d
where a.saddr = b.session_addr
and a.paddr = c.addr
and a.sql_hash_value= d.hash_value
and b.tablespace like 'TEMP%'
--and a.username ='MIG_ADM'
ORDER BY A.MACHINE, SQL_TEXT
== 템프러리 테이블스페이스 sort 사용 현황
SELECT
A.tablespace_name tablespace,
D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM
v$sort_segment A,
(
SELECT
B.name,
C.block_size,
SUM (C.bytes) / 1024 / 1024 mb_total
FROM
v$tablespace B,
v$tempfile C
WHERE
B.ts#= C.ts#
GROUP BY
B.name,
C.block_size
) D
WHERE
A.tablespace_name = D.name
GROUP by
A.tablespace_name,
D.mb_total
2.7.1 Undo TABLESPACE[편집]
: Undo Segment를 저장하고 있는 TABLESPACE , 관리자가 생성/관리 가능 Instance당 여러 개가 동시에 존재할 수 있지만 사용은 한번에 1개만 자동 관리 기법(AUM, Automatic Undo Management)
- Undo Data : 사용자가 DML을 수행할 경우 발생하는 원본데이터, Oracle Server Process가 직접 관리
ex) 홍길동→일지매로 업데이트 시 홍길동이 Undo Data
- Undo Segment : Undo Data만을 저장하는 Segment
2.7.1.1 현재 UNDO 상태 확인[편집]
SELECT A.TABLESPACE_NAME,A.STATUS, B.TOTAL_MB, A.USE_MB,
ROUND(RATIO_TO_REPORT(A.USE_MB) OVER(PARTITION BY A.TABLESPACE_NAME) * 100)||'%' AS PCT
FROM (SELECT TABLESPACE_NAME, STATUS,
ROUND(SUM(BYTES/1024/1024)) USE_MB
FROM DBA_UNDO_EXTENTS
GROUP BY TABLESPACE_NAME,STATUS
)A,
(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024) TOTAL_MB
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME LIKE 'UNDO%'
GROUP BY TABLESPACE_NAME
)B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
ORDER BY 1,2
2.7.2 현재 유저의 DEFAULT TABLESPACE 확인[편집]
SELECT * FROM USER_USERS ;
==> DEFAUT TABLESPACE로 설정된 부분을 확인
2.7.4 테이블스페이스 장애 처리[편집]
2.7.4.1 TABLESPACE 용량 부족으로 에러 발생시[편집]
- 방법 1) Data file을 크게 늘려줌(자동 증가/수동증가)
- 방법 2) Data file을 하나 더 추가
- Tablespace Offline
- 사용자가 더 이상 해당 Tablespace에 접근하지 못한다는 의미 - 데이터파일의 위치를 이동하거나 특정 Tablespace가 장애가 나서 복구해야 할 때 사용
- TABLESPACE 를 Offline하는 방법 3가지
1) Normal Mode
SQL> alter TABLESPACE TS_TEST offline ;
2) Temporary Mode
Normal이 수행되지 못할 때(Tablespace의 Data file 이상) 사용하는 방법
3) Immediate Mode
- 반드시 Archive Log Mode일 경우에만 사용. - Data file에 장애가 나서 데이터를 내려쓰지 못하는 상황에서 TABLESPACE 를 offline해야 할 경우 사용
- TABLESPACE 이동하기
1) Offline되는 Tablespace의 Data file 이동하기
- 해당 Tablespace Offline 하기
- ALTER TABLESPACE TS_TEST OFFLINE;
- Data file을 대상 위치로 복사
- Control file 내의 해당 Data file 위치 변경
- 해당 Tablespace Online
- ALTER TABLESPACE TS_TEST ONLINE;
2) Offline 안 되는 TABLESPACE 의 Data file 이동하기
- DB 종료
- Mount 상태로 시작
- Data file 복사
- Control file의 내용 변경
- DB Open