ORACLE 테이블스페이스
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
- 1 테이블 스페이스
- 1.1 개념 잡기
- 1.2 현재 UNDO 상태 확인
- 1.3 테이블스페이스별 전체 오브젝트(테이블/인덱스/LOB) 사이즈
- 1.4 테이블스페이스별 사이즈
- 1.5 테이블/인덱스/LOB 오브젝트별 테이블스페이스 사이즈
- 1.6 테이블스페이스 확인
- 1.7 현재 유저의 DEFAULT TABLESPACE 확인
- 1.8 테이블스페이스 변경
- 1.9 TABLESPACE 삭제
- 1.10 TABLESPACE 생성
- 1.11 테이블스페이스 정보 확인
- 1.11.1 테이블스페이스별 파일 목록을 보기
- 1.11.2 테이블스페이스별 정보 보기
- 1.11.3 테이블스페이스별 사용하는 파일의 크기 합 보기
- 1.11.4 테이블스페이스별 디스크 사용량 보기
- 1.11.5 테이블스페이스의 테이블 명 보기
- 1.11.6 공간의 90% 이상을 사용하고 있는 Tablespace
- 1.11.7 Object별 테이블스페이스 및 데이터파일
- 1.11.8 Tablespace별 Table, Index 개수
- 1.11.9 파일위치별 테이블스페이스 아는 방법
- 1.11.10 현재 Extension 횟수가 MaxExtents의 80% 이상인 경우
- 1.11.11 테이블의 익스텐트 정보 조회
- 1.11.12 ROLLBACK SEGMENT의 사용상황 보기
- 2 템프테이블 스페이스
1 테이블 스페이스[편집]
1.1 개념 잡기[편집]
테이블스페이스란?
- 논리적인 데이터 저장구조(=DB Cache Buffer 내에서 데이터를 작업하는 공간)
(오라클은 데이터를 저장하고 작업할 때
* 메모리에 논리적으로는 Tablespace 공간을 만들어서 작업, 실제 작업이 일어나는 공간.(작업속도가 빠름)
* 물리적으로는 디스크에 Data File을 만들어서 저장(속도 느림)
- Database Buffer Cache에 Tablespace를 생성함(=SQL을 수행하면 해당 데이터는 반드시 Tablespace에 존재해야 함)
- Tablespace가 사용하는 메모리 공간이 클수록 일반적으로 수행 속도가 빨라짐
- dba_data_files 조회
1.1.1 Tablespace 의 종류 및 특징[편집]
1.1.1.1 SYSTEM Tablespace[편집]
- 데이터 딕셔너리 정보들이 저장되어 있음, 이 Tablespace 가 손상될 경우 Oracle 서버가 시작이 안됨
- SYS계정 소유이지만 조회만 가능!
- 데이터 딕셔너리 : 오라클 서버의 모든 정보를 저장하고 있는 아주 중요한 테이블이나 뷰들
┌ Base Table : 데이터베이스 생성시(dbca, create database등) 생성됨, 사람 접근 불가(DBA 조차)
└ Data Dictionary View : Base Table을 조회할 수 있도록 하는 뷰
┌ Static Dictionary : 내용이 실시간으로 변경 안됨
│ (USER_XXX, ALL_XXX, DBA_XXX) = Instance가 Open일 경우에만 조회 가능
└ Dynamic Performance View : 실시간으로 변경되는 내용을 볼 수 있음
조회 시점에 Control File/메모리로 가서 정보를 가져옴
Instance가 Nomount 상태부터 조회 가능
- 데이터 딕셔너리에 들어있는 주요 정보
- 데이터베이스의 논리적인 구조와 물리적인 구조 정보들
- 객체의 정의와 공간 사용 정보들
- 제약조건에 관련된 정보들
- 사용자에 관련된 정보들
- Role, Privilege 등에 관련된 정보들
- 감사 및 보안등에 관련된 정보들
1.1.1.3 데이터 Tablespace[편집]
: 가장 일반적으로 많이 사용되는 Tablespace로 관리자가 필요에 의해 만드는 Tablespace.
DBA 에 의해 얼마든지 생성하고 삭제할 수 있음
문제) Tablespace 용량 부족으로 에러가 발생되면?
해결방법 1) Data file을 크게 늘려줌(자동 증가/수동증가)
해결방법 2) Data file을 하나 더 추가
Tablespace Offline
- 사용자가 더 이상 해당 Tablespace에 접근하지 못한다는 의미
- 데이터파일의 위치를 이동하거나 특정 Tablespace가 장애가 나서 복구해야 할 때 사용
- Tablespace를 Offline하는 방법 3가지
1) Normal Mode
SQL> alter tablespace haksa offline ;
2) Temporary Mode
Normal이 수행되지 못할 때(Tablespace의 Data file 이상) 사용하는 방법
3) Immediate Mode
- 반드시 Archive Log Mode일 경우에만 사용해야 한다.
- Data file에 장애가 나서 데이터를 내려쓰지 못하는 상황에서 Tablespace를 offline해야 할 경우 사용
- Tablespace 이동하기
1) Offline되는 Tablespace의 Data file 이동하기
1. 해당 Tablespace Offline 하기
2. Data file을 대상 위치로 복사
3. Control file 내의 해당 Data file 위치 변경
4. 해당 Tablespace Online
2) Offline 안 되는 Tablespace의 Data file 이동하기
1. DB 종료
2. Mount 상태로 시작
3. Data file 복사
4. Control file의 내용 변경
5. DB Open
1.1.1.4 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
1.2 현재 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,21.3 테이블스페이스별 전체 오브젝트(테이블/인덱스/LOB) 사이즈[편집]
-- OBJECT별 테이블 사이즈 (UNDO,TEMP T/S 제외)
-- SELECT TABLESPACE_NAME
-- , SUM(SIZE_MB)
-- FROM (
SELECT A.TABLESPACE_NAME
, A.SEGMENT_NAME
, A.SEGMENT_TYPE
, ROUND(SUM(A.BYTES)/1024/1024) "SIZE_MB"
FROM DBA_SEGMENTS A
, DBA_TABLES B
WHERE A.SEGMENT_NAME = B.TABLE_NAME
AND A.SEGMENT_TYPE IN ('TABLE','TABLE PARTITION')
-- AND A.OWNER = '유저아이디'
GROUP BY A.TABLESPACE_NAME
, A.SEGMENT_NAME
, A.SEGMENT_TYPE
UNION ALL
-- INDEX SIZE
SELECT A.TABLESPACE_NAME
, A.SEGMENT_NAME
, A.SEGMENT_TYPE
, ROUND(SUM(A.BYTES)/1024/1024) "SIZE_MB"
FROM DBA_SEGMENTS A
, DBA_INDEXES B
WHERE A.SEGMENT_NAME = B.INDEX_NAME
AND A.SEGMENT_TYPE IN ('INDEX','INDEX PARTITION')
-- AND A.OWNER = '유저아이디'
GROUP BY A.TABLESPACE_NAME
, A.SEGMENT_NAME
, A.SEGMENT_TYPE
-- ORDER BY 2 DESC;
UNION ALL
-- LOB
SELECT A.TABLESPACE_NAME
, A.SEGMENT_NAME
, A.SEGMENT_TYPE
, ROUND(SUM(A.BYTES)/1024/1024) "SIZE_MB"
FROM DBA_SEGMENTS A
, DBA_LOBS B
WHERE A.segment_name = B.segment_name
AND A.SEGMENT_TYPE LIKE 'LOB%'
GROUP BY A.TABLESPACE_NAME
, A.SEGMENT_NAME
, A.SEGMENT_TYPE
ORDER BY SEGMENT_TYPE,TABLESPACE_NAME DESC
-- )
-- GROUP BY TABLESPACE_NAME
;1.4 테이블스페이스별 사이즈[편집]
SELECT Substr(df.tablespace_name,1,20) "Tablespace Name",
Substr(df.file_name,1,80) "File Name",
Round(df.bytes/1024/1024,0) "Size (M)",
decode(e.used_bytes,NULL,0,Round(e.used_bytes/1024/1024,0)) "Used (M)",
decode(f.free_bytes,NULL,0,Round(f.free_bytes/1024/1024,0)) "Free (M)",
decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes)*100,0)) "% Used"
FROM DBA_DATA_FILES DF,
(SELECT file_id,
sum(bytes) used_bytes
FROM dba_extents
GROUP by file_id) E,
(SELECT sum(bytes) free_bytes,
file_id
FROM dba_free_space
GROUP BY file_id) f
WHERE e.file_id (+) = df.file_id
AND df.file_id = f.file_id (+)
ORDER BY df.tablespace_name,
df.file_name1.5 테이블/인덱스/LOB 오브젝트별 테이블스페이스 사이즈[편집]
-- 테이블 사이즈
SELECT A.SEGMENT_NAME
, A.SEGMENT_TYPE
, ROUND(SUM(A.BYTES)/1024/1024/1024) "SIZE_GB"
FROM DBA_SEGMENTS A
, DBA_TABLES B
WHERE A.SEGMENT_NAME = B.TABLE_NAME
AND A.SEGMENT_TYPE IN ('TABLE','TABLE PARTITION')
-- AND A.OWNER = '유저아이디'
GROUP BY A.SEGMENT_NAME
, A.SEGMENT_TYPE-- 인덱스 사이즈
-- INDEX SIZE
SELECT A.SEGMENT_NAME
, A.SEGMENT_TYPE
, ROUND(SUM(A.BYTES)/1024/1024/1024) "SIZE_GB"
FROM DBA_SEGMENTS A
, DBA_INDEXES B
WHERE A.SEGMENT_NAME = B.INDEX_NAME
AND A.SEGMENT_TYPE IN ('INDEX','INDEX PARTITION')
-- AND A.OWNER = '유저아이디'
GROUP BY A.SEGMENT_NAME
, A.SEGMENT_TYPE
ORDER BY 2 DESC;-- LOB 사이즈
SELECT TABLE_NAME
, sum(bytes)
FROM (SELECT B.table_name AS table_name
, A.bytes
FROM DBA_SEGMENTS A
, DBA_LOBS B
WHERE A.segment_name = B.segment_name
)
group by table_name;
1.6 테이블스페이스 확인[편집]
SELECT * FROM DBA_DATA_FILES ;
SELECT * FROM DBA_TABLESPACES ;1.7 현재 유저의 DEFAULT TABLESPACE 확인[편집]
SELECT * FROM USER_USERS ;
==> DEFAUT TABLESPACE로 설정된 부분을 확인1.8 테이블스페이스 변경[편집]
1.8.1 TABLESPACE 사이즈 변경[편집]
-- 최대 사용 사이즈
ALTER TABLESPACE ts_txxxx
AUTOEXTEND ON
NEXT 100M
MAXSIZE 500G;
-- 리사이즈
ALTER TABLESPACE ts_txxxx RESIZE 500G;
1.8.2 유저의 DEFAULT TABLESPACE 변경[편집]
ALTER USER [유저명] DEFAULT TABLESPACE [테이블 스페이스명]1.8.3 테이블의 TABLESPACE 변경[편집]
ALTER TABLE [테이블명] MOVE TABLESPACE [테이블 스페이스명]SELECT 'ALTER TABLE '||a.table_name||' MOVE TABLESPACE TS_OO_'||substr(a.table_name,4,2)||'_D;'
FROM user_tables a
WHERE a.table_name NOT IN (SELECT table_name FROM user_part_tables)
AND substr(a.table_name,4,2) IN (SELECT SUBJECT_CD FROM TB_DBA_SUBJECT_CD);1.8.4 인덱스의 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.8.5 인덱스/테이블 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) ||
' HONEY_DATASPACE ' || 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=> 변경결과 => 테이블
alter TABLE TB_ASIS_TABLES move
HONEY_DATASPACE
storage ( initial 65536 next 1048576
minextents 1 maxextents 2147483645
pctincrease freelists );=> 인덱스
alter INDEX PK_ASIS_TABLES rebuild
HONEY_DATASPACE
storage ( initial 65536 next 1048576
minextents 1 maxextents 2147483645
pctincrease freelists );1.8.6 TABLESPACE 수정이나 삭제시 ONLINE/OFFLINE 설정[편집]
ALTER TABLESPACE [테이블 스페이스명] ONLINE
ALTER TABLESPACE [테이블 스페이스명] OFFLINE1.8.7 TABLESPACE의 물리적인 파일의 이름 또는 위치변경[편집]
ALTER TABLESPACE [asis-TABLE] RENAME TO [tobe-TABLE]1.8.8 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.9 TABLESPACE 삭제[편집]
DROP TABLESPACE [테이블 스페이스명] INCLUDE CONTENTS;
==> 테이블스페이스 내의 객체(테이블,인덱스등)를 다 지운다.DROP TABLESPACE [테이블 스페이스명] INCLUDING CONTENTS;
==> 테이블스페이스의 모든 세그먼트를 삭제한다.
==> 단, 데이타가 있는 테이블스페이스는 삭제할수 없다.DROP TABLESPACE [테이블 스페이스명] CASCADE CONSTRAINTS;
==> 삭제된 테이블스페이스 내의 테이블의 기본키와 유일키를 참조하는
다른 테이블스페이스의 테이블로부터 참조무결성 제약 조건을 삭제한다.DROP TABLESPACE [테이블 스페이스명] INCLUDING CONTENTS AND DATAFILES;
==> 물리적파일까지 삭제한다.1.10 TABLESPACE 생성[편집]
CREATE TABLESPACE [테이블 스페이스명]
DATAFILE 'C:\경로\TEST.DBF'SIZE 10M
==> 여기까지 작성하면 기본적인 사항에 대해서만 생성된다.
AUTOEXTEND ON NEXT 10M
==> (옵션)데이타 파일 용량초과시 자동증가설정
MAXSIZE 100M
==> (옵션)데이타파일 최대크기지정
EXTENT MANAGEMENT LOCAL
==> (옵션)
UNIFORM SIZE 1M
==> (옵션)1.10.1 TABLESPACE 생성2[편집]
CREATE TABLESPACE [테이블 스페이스명]
DATAFILE 'C:\경로\TEST.DBF'
SIZE 10M
DEFAULT STORAGE(
INITIAL 80K ==> 테이블 스페이스의 맨 첫번째 EXTENTS의 크기
NEXT 80K ==> 다음 EXTENTS의 크기
MINNEXTENTS 1 ==> 생성할 EXTENTS의 최소값
MAXNEXTENTS 121 ==> 생성할 EXTENTS의 최대값
PCTINCREASE 80 ==> EXTENTS의 증가율,(DEFAULT값은 50%)
) ONLINE ;
1.11 테이블스페이스 정보 확인[편집]
1.11.1 테이블스페이스별 파일 목록을 보기[편집]
SELECT SUBSTRB(TABLESPACE_NAME, 1, 10) AS "테이블스페이스"
, SUBSTRB(FILE_NAME, 1, 50) AS "파일명"
, TO_CHAR(BLOCKS, '999,999,990') AS "블럭수"
, TO_CHAR(BYTES, '99,999,999') AS "크기"
FROM DBA_DATA_FILES ORDER BY TABLESPACE_NAME, FILE_NAME;1.11.2 테이블스페이스별 정보 보기[편집]
SELECT A.TABLESPACE_NAME AS "TABLESPACE"
, A.INITIAL_EXTENT / 1024 AS "INIT(K)"
, A.NEXT_EXTENT / 1024 AS "NEXT(K)"
, A.MIN_EXTENTS AS "MIN"
, A.MAX_EXTENTS AS "MAX"
, A.PCT_INCREASE AS "PCT_INC(%)"
, B.FILE_NAME AS "FILE_NAME"
, B.BLOCKS * C.VALUE / 1024 / 1024 AS "SIZE(M)"
, B.STATUS AS "STATUS"
FROM DBA_TABLESPACES A
, DBA_DATA_FILES B
, V$PARAMETER C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME AND C.NAME = 'db_block_size' ORDER BY 1, 2;
1.11.3 테이블스페이스별 사용하는 파일의 크기 합 보기[편집]
SELECT SUBSTRB(TABLESPACE_NAME, 1, 10) AS TABLESPACE
, TO_CHAR(SUM(BYTES), '9,999,999,999,990') AS BYTES
, TO_CHAR(SUM(BLOCKS), '9,999,999,990') AS BLOCKS
FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME
UNION ALL
SELECT '총계', TO_CHAR(SUM(BYTES), '9,999,999,999,990') AS BYTES, TO_CHAR(SUM(BLOCKS), '9,999,999,990') AS BLOCKS
FROM DBA_DATA_FILES;1.11.4 테이블스페이스별 디스크 사용량 보기[편집]
SELECT A.TABLESPACE_NAME AS "TABLESPACE"
, A.INIT AS "INIT(K)"
, A.NEXT AS "NEXT(K)"
, A.MIN AS "MIN"
, A.MAX AS "MAX"
, A.PCT_INC AS "PCT_INC(%)"
, TO_CHAR(B.TOTAL, '999,999,999,990') AS "총량(바이트)"
, TO_CHAR(C.FREE, '999,999,999,990') AS "남은량(바이트)"
, TO_CHAR(B.BLOCKS, '9,999,990') AS "총블럭"
, TO_CHAR(D.BLOCKS, '9,999,990') AS "사용블럭"
, TO_CHAR(100 * NVL(D.BLOCKS, 0) / B.BLOCKS, '999.99') AS "사용율%"
FROM (SELECT TABLESPACE_NAME
, INITIAL_EXTENT / 1024 AS INIT
, NEXT_EXTENT / 1024 AS NEXT
, MIN_EXTENTS AS MIN
, MAX_EXTENTS AS MAX
, PCT_INCREASE AS PCT_INC
FROM DBA_TABLESPACES) A
, (SELECT TABLESPACE_NAME, SUM(BYTES) AS TOTAL, SUM(BLOCKS) AS BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) B
, (SELECT TABLESPACE_NAME, SUM(BYTES) AS FREE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) C
, (SELECT TABLESPACE_NAME, SUM(BLOCKS) AS BLOCKS
FROM DBA_EXTENTS
GROUP BY TABLESPACE_NAME) D
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+) AND A.TABLESPACE_NAME = C.TABLESPACE_NAME(+) AND A.TABLESPACE_NAME = D.TABLESPACE_NAME(+)
ORDER BY A.TABLESPACE_NAME;1.11.5 테이블스페이스의 테이블 명 보기[편집]
SELECT TABLESPACE_NAME, TABLE_NAME
FROM USER_TABLES
WHERE TABLESPACE_NAME = UPPER('&테이블스페이스명')
ORDER BY TABLESPACE_NAME, TABLE_NAME;1.11.6 공간의 90% 이상을 사용하고 있는 Tablespace[편집]
SELECT X.TABLESPACE_NAME
, TOTAL_SIZE / 1024 / 1024 TOTAL_SIZE
, USED_SIZE / 1024 / 1024 USED_SIZE
, (ROUND(USED_SIZE / TOTAL_SIZE, 2)) * 100 USED_RATIO
FROM (SELECT TABLESPACE_NAME, SUM(BYTES) TOTAL_SIZE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) X
, (SELECT TABLESPACE_NAME, SUM(BYTES) USED_SIZE
FROM DBA_EXTENTS
GROUP BY TABLESPACE_NAME) Y
WHERE X.TABLESPACE_NAME = Y.TABLESPACE_NAME(+) AND Y.USED_SIZE > .9 * X.TOTAL_SIZE;1.11.7 Object별 테이블스페이스 및 데이터파일[편집]
SELECT DISTINCT E.SEGMENT_NAME, E.TABLESPACE_NAME, F.FILE_NAME
FROM DBA_EXTENTS E
, DBA_DATA_FILES F
WHERE E.FILE_ID = F.FILE_ID
AND E.SEGMENT_TYPE = 'TABLE'
AND E.TABLESPACE_NAME NOT IN ('SYSTEM', 'TOOLS');1.11.8 Tablespace별 Table, Index 개수[편집]
SELECT OWNER
, TABLESPACE_NAME
, SUM(DECODE(SEGMENT_TYPE, 'TABLE', 1, 0))
, SUM(DECODE(SEGMENT_TYPE, 'INDEX', 1, 0))
FROM DBA_SEGMENTS WHERE SEGMENT_TYPE IN ('TABLE', 'INDEX') GROUP BY OWNER, TABLESPACE_NAME;1.11.9 파일위치별 테이블스페이스 아는 방법[편집]
SELECT SUBSTRB(A.FILE_NAME, 1, 40) AS FILE_NAME
, A.FILE_ID
, B.FREE_BYTES / 1024 AS FREE_BYTES
, B.MAX_BYTES / 1024 AS MAX_BYTES
FROM DBA_DATA_FILES A
, (SELECT FILE_ID, SUM(BYTES) AS FREE_BYTES, MAX(BYTES) AS MAX_BYTES
FROM DBA_FREE_SPACE
GROUP BY FILE_ID) B
WHERE A.FILE_ID = B.FILE_ID
AND A.TABLESPACE_NAME = UPPER('&테이블스페이스명')
ORDER BY A.FILE_NAME;1.11.10 현재 Extension 횟수가 MaxExtents의 80% 이상인 경우[편집]
SELECT TABLESPACE_NAME
, OWNER
, SEGMENT_NAME
, SEGMENT_TYPE
, EXTENTS
, MAX_EXTENTS
FROM SYS.DBA_SEGMENTS S
WHERE EXTENTS / MAX_EXTENTS > .8 AND MAX_EXTENTS > 0
ORDER BY TABLESPACE_NAME, OWNER, SEGMENT_NAME;1.11.11 테이블의 익스텐트 정보 조회[편집]
/*
오라클에서 스토리지 구조는 아래와 같다.
테이블 스페이스 -> 세그먼트 -> 익스텐트 -> 블록 -> OS 범위 -> 데이터 파일 -> 운영체제 블록
세그먼트의 이름,
해당 세그먼트의 최대 익스텐트 개수,
익스텐트 아이디
해당 세그먼트의 최대 익스텐트 개수 - 최대 익스텐트 아이디
딕셔너리 관리 테이블스페이스로 생성한 것으로 조회를 한다.
*/
SELECT B.SEGMENT_NAME
, B.MAX_EXTENTS
, MAX(C.EXTENT_ID) AS EXTENT_ID
, B.MAX_EXTENTS - MAX(C.EXTENT_ID) AS DIFF
FROM USER_TABLESPACES A
, USER_SEGMENTS B
, USER_EXTENTS C
WHERE A.EXTENT_MANAGEMENT = 'DICTIONARY'
AND B.TABLESPACE_NAME = A.TABLESPACE_NAME
AND C.SEGMENT_NAME = B.SEGMENT_NAME
GROUP BY B.SEGMENT_NAME, B.MAX_EXTENTS
HAVING B.MAX_EXTENTS - MAX(C.EXTENT_ID) <= 50
ORDER BY B.MAX_EXTENTS - MAX(C.EXTENT_ID);1.11.12 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_NAME2.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.6.2 TEMP TABLESPACE 삭제[편집]
DROP TABLESPACE TEMP2;2.7 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만 가능)2.8 템프테이블 사용율 조회 쿼리[편집]
/* 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