ORACLE 테이블스페이스
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
- 1 테이블 스페이스
- 1.1 테이블스페이스란?
- 1.2 TABLESPACE 의 종류 및 특징
- 1.3 현재 UNDO 상태 확인
- 1.4 테이블스페이스별 전체 오브젝트(테이블/인덱스/LOB) 사이즈
- 1.5 테이블스페이스별 사이즈
- 1.6 테이블/인덱스/LOB 오브젝트별 테이블스페이스 사이즈
- 1.7 테이블스페이스 확인
- 1.8 현재 유저의 DEFAULT TABLESPACE 확인
- 1.9 테이블스페이스 변경
- 1.10 TABLESPACE 삭제
- 1.11 TABLESPACE 생성
- 1.12 테이블스페이스 정보 확인
- 1.12.1 테이블스페이스별 파일 목록을 보기
- 1.12.2 테이블스페이스별 정보 보기
- 1.12.3 테이블스페이스별 사용하는 파일의 크기 합 보기
- 1.12.4 테이블스페이스별 디스크 사용량 보기
- 1.12.5 테이블스페이스의 테이블 명 보기
- 1.12.6 공간의 90% 이상을 사용하고 있는 Tablespace
- 1.12.7 Object별 테이블스페이스 및 데이터파일
- 1.12.8 Tablespace별 Table, Index 개수
- 1.12.9 파일위치별 테이블스페이스 아는 방법
- 1.12.10 현재 Extension 횟수가 MaxExtents의 80% 이상인 경우
- 1.12.11 테이블의 익스텐트 정보 조회
- 1.12.12 ROLLBACK SEGMENT의 사용상황 보기
- 2 템프테이블 스페이스
1 테이블 스페이스[편집]
1.1 테이블스페이스란?[편집]
- 논리적인 데이터 저장구조(=DB Cache Buffer 내에서 데이터를 작업하는 공간) - DBA_DATA_FILES 뷰 조회
1.2 TABLESPACE 의 종류 및 특징[편집]
1.2.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.2.2 SYSAUX TABLESPACE[편집]
: 10g 버전부터 등장, Oracle 서버의 성능 튜닝을 위한 데이터들이 저장되어 있음
1.2.3 데이터 TABLESPACE[편집]
: 가장 일반적으로 많이 사용되는 TABLESPACE 로 DBA 필요에 의해 만드는 TABLESPACE . DBA에 의해 생성,삭제.
- 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
1.2.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.3 현재 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.4 테이블스페이스별 전체 오브젝트(테이블/인덱스/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.5 테이블스페이스별 사이즈[편집]
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.6 테이블/인덱스/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.7 테이블스페이스 확인[편집]
SELECT * FROM DBA_DATA_FILES ;
SELECT * FROM DBA_TABLESPACES ;1.8 현재 유저의 DEFAULT TABLESPACE 확인[편집]
SELECT * FROM USER_USERS ;
==> DEFAUT TABLESPACE로 설정된 부분을 확인1.9 테이블스페이스 변경[편집]
1.9.1 TABLESPACE 사이즈 변경[편집]
-- 최대 사용 사이즈
ALTER TABLESPACE ts_txxxx
AUTOEXTEND ON
NEXT 100M
MAXSIZE 500G;
-- 리사이즈
ALTER TABLESPACE ts_txxxx RESIZE 500G;
1.9.2 유저의 DEFAULT TABLESPACE 변경[편집]
ALTER USER [유저명] DEFAULT TABLESPACE [테이블 스페이스명]1.9.3 테이블의 TABLESPACE 변경[편집]
- Tablespace 변경
ALTER TABLE [테이블명] MOVE TABLESPACE [테이블 스페이스명]- 무제한(Unlimited) Tablespace 변경
ALTER DATABASE DATAFILE '데이터파일경로' AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED; -- 자동증가 100M 씩 ,무제한 증가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.9.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.9.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.9.6 TABLESPACE 수정이나 삭제시 ONLINE/OFFLINE 설정[편집]
ALTER TABLESPACE [테이블 스페이스명] ONLINE
ALTER TABLESPACE [테이블 스페이스명] OFFLINE1.9.7 TABLESPACE의 물리적인 파일의 이름 또는 위치변경[편집]
ALTER TABLESPACE [asis-TABLE] RENAME TO [tobe-TABLE]1.9.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.10 TABLESPACE 삭제[편집]
DROP TABLESPACE [테이블 스페이스명] INCLUDE CONTENTS;
==> 테이블스페이스 내의 객체(테이블,인덱스등)를 다 지운다.DROP TABLESPACE [테이블 스페이스명] INCLUDING CONTENTS;
==> 테이블스페이스의 모든 세그먼트를 삭제한다.
==> 단, 데이타가 있는 테이블스페이스는 삭제할수 없다.DROP TABLESPACE [테이블 스페이스명] CASCADE CONSTRAINTS;
==> 삭제된 테이블스페이스 내의 테이블의 기본키와 유일키를 참조하는
다른 테이블스페이스의 테이블로부터 참조무결성 제약 조건을 삭제한다.DROP TABLESPACE [테이블 스페이스명] INCLUDING CONTENTS AND DATAFILES;
==> 물리적파일까지 삭제한다.1.11 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 ==> (옵션)1.11.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.12 테이블스페이스 정보 확인[편집]
1.12.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.12.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.12.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.12.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.12.5 테이블스페이스의 테이블 명 보기[편집]
SELECT TABLESPACE_NAME, TABLE_NAME
FROM USER_TABLES
WHERE TABLESPACE_NAME = UPPER('&테이블스페이스명')
ORDER BY TABLESPACE_NAME, TABLE_NAME;1.12.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.12.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.12.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.12.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.12.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.12.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.12.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