다른 명령
테이블 스페이스 (TABLESPACE)
TABLESPACE 목록 조회
SELECT A.TABLESPACE_NAME , FILE_NAME , BYTES / 1024 / 1024 , MAXBYTES / 1024 / 1024 , AUTOEXTENSIBLE , B.BLOCK_SIZE , INCREMENT_BY * B.BLOCK_SIZE / 1024 / 1024 FROM DBA_DATA_FILES A , DBA_TABLESPACES B WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME ORDER BY B.BLOCK_SIZE, A.TABLESPACE_NAME, A.FILE_ID;
TABLESPACE 종류
현재 유저의 DEFAULT TABLESPACE 확인
SELECT * FROM USER_USERS ;-- DEFAUT TABLESPACE로 설정된 부분을 확인
유저의 DEFAULT TABLESPACE 변경
ALTER USER [유저명] DEFAULT TABLESPACE [테이블 스페이스명]
TABLESPACE 생성 구문
CREATE [BIGFILE/SMALLFILE] TABLESPACE 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%) : )
BIGFILE 테이블스페이스
- 8K 블록 - bigfile 테이블 스페이스에는 최대 32TB
- 32K 블록 - bigfile 파일 테이블 스페이스에는 최대 128TB
BIGFILE 테이블스페이스 장점
자동 수행 뷰 목록
BIGFILE 테이블스페이스 단점
UNDO TABLESPACE 생성
CREATE UNDO TABLESPACE [테이블 스페이스명] ==> 이부분만 다름. DATAFILE '\경로\TEST_UNDO.DBF' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 100M -- UNDO_MANAGEMENT와 UNDO_TABLESPACE, UNDO_RETENTION PARAMETER를 제공 -- 지역적으로 관리되는 익스텐트만 사용가능 -- 시스템에 의해 관리되는 익스텐트 할당만 사용가능하다. -- (UNIFORMSIZE를 할당 할 수 없고 AUTOALLOCATE만 가능)
TABLESPACE 변경
TABLESPACE 읽기/쓰기 모드 변경
- 읽기 전용 모드
alter tablespace <TABLESPACE명> read only;
- 읽기 쓰기 모드
alter tablespace <TABLESPACE명> read ,write;
OPEN 상태에서 DATAFILE 이동
alter tablespace TABLESPACE명 offline;
offline 된 T/S에 대해 복사/이동
cp /data1/xxx.dbf /data2/xxx.dbf or mv /data1/xxx.dbf /data2/xxx.dbf
OFFLINE 상태에서 DATAFILE 이동
alter tablespace TABLESPACE명 rename datafile '파일경로1/xxx.dbf' to '파일경로2/xxx.dbf'; alter tablespace TABLESPACE명 online;
MOUNT 상태에서 DATAFILE 이동
startup mount;
해당 T/S에 대해 복사/이동 후
alter database rename file '파일경로' to '파일경로'; alter database open;
- 모든 데이타 파일은 mount상태에서 복사/이동 가능
- system 파일은 mount상태에서만 복사/이동 가능
테이블의 TABLESPACE MOVE
ALTER TABLE [테이블명] MOVE TABLESPACE [테이블 스페이스명]
- -테이블스페이스 이동시 인덱스 리빌드 필요
ALTER INDEX [인덱스명] REBUILD ;
- 인덱스 리빌드 동시 수행 시
ALTER TABLE [테이블명] MOVE TABLESPACE [테이블 스페이스명] UPDATE INDEXES;
파티션닝 테이블 TABLESPACE MOVE
ALTER TABLE [테이블명] MOVE PARTITION [파티션명] TABLESPACE [테이블 스페이스명]
- 파티셔닝 테이블 인덱스 REBUILD
ALTER INDEX [인덱스명] REBUILD PARTITION [파티션명]
운영중인 테이블 TABLESPACE ONLINE MOVE
ALTER TABLE [테이블명] MOVE TABLESPACE [테이블 스페이스명] ONLINE -- online 옵션 ;
- 인덱스 리빌드 동시 수행 시
ALTER TABLE [테이블명] MOVE TABLESPACE [테이블 스페이스명] UPDATE INDEXES ONLINE -- online 옵션 ;
운영중인 파티션닝 테이블 TABLESPACE ONLINE MOVE
ALTER TABLE [테이블명] MOVE PARTITION [파티션명] TABLESPACE [테이블 스페이스명] ONLINE
TABLESPACE 사이즈 변경(RESIZE)
ALTER TABLESPACE ts_txxxx RESIZE 500G;
TABLESPACE 이름 변경
ALTER TABLESPACE [asis-TABLE] RENAME TO [tobe-TABLE]
인덱스의 TABLESPACE 변경
ALTER INDEX 인덱스명 REBUILD TABLESPACE [테이블 스페이스명] [ PARALLEL parallel_num ] [ LOGGING or NOLOGGING ] ;
인덱스의 TABLESPACE 변경 스크립트
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);
인덱스/테이블 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;
TABLESPACE 자동증가/최대 사이즈 변경
ALTER TABLESPACE ts_txxxx AUTOEXTEND ON NEXT 100M MAXSIZE 500G;-- 최대사이즈 , UNLIMITED => 무제한 증가
- 최대 TABLESPACE 사이즈 변경
ALTER TABLESPACE [테이블 스페이스명] ADD DATAFILE 'C:\경로\TEST2.DBF' SIZE 10M AUTOEXTEND ON NEXT 100M MAXSIZE 100G; ==> 10M씩 자동증가
데이터파일 사이즈 변경
ALTER DATABASE DATAFILE 'C:\경로\TEST1.DBF' RESIZE 10M;
데이터파일 추가
ALTER TABLESPACE [테이블 스페이스명] ADD DATAFILE 'C:\경로\TEST2.DBF' SIZE 10M;
데이터/템프파일 삭제
ALTER TABLESPACE [테이블 스페이스명] DROP DATAFILE 'data파일'; -- 템프TS DROP TEMPFILE 'data파일';
데이터 파일 삭제 후 OS 디스크 사이즈가 줄지 않을경우
attach_file 1) 테이블스페이스 사이즈를 줄인 (shrink) 후 데이터파일 삭제
2) DB 리부팅 하면 공간 확보 됨(DBWR 이 데이터파일을 사용중이기 때문에 공간이 줄지 않음)
TABLESPACE 삭제
TABLESPACE에 포함된 모든 세그먼트 삭제
DROP TABLESPACE [테이블 스페이스명] INCLUDING CONTENTS;-- TABLESPACE의 모든 세그먼트를 삭제한다. -- 단, 데이타가 있는 TABLESPACE는 삭제할수 없다.
TABLESPACE에 포함된 테이블의 참조/제약 조건 삭제
DROP TABLESPACE [테이블 스페이스명] CASCADE CONSTRAINTS; -- 삭제된 TABLESPACE 내의 테이블의 기본키와 유일키를 참조하는 -- 다른 TABLESPACE의 테이블로부터 참조무결성 제약 조건을 삭제한다.
TABLESPACE의 데이터파일 삭제
DROP TABLESPACE [테이블 스페이스명] INCLUDING CONTENTS AND DATAFILES; -- 물리적파일까지 삭제한다.
TABLESPACE 수정이나 삭제시 ONLINE/OFFLINE 설정
ALTER TABLESPACE [테이블 스페이스명] ONLINE ALTER TABLESPACE [테이블 스페이스명] OFFLINE
템프 TABLESPACE
TEMPORARY TABLESPACE 정보
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
템프테이블 사이즈 조정
ALTER DATABASE TEMPFILE 'D:\APP\ORADATA\ORCL\TEMP01.DBF' RESIZE 304M;
TEMPORARY TABLESPACE 생성
- . 신규생성만 가능.
: 사용중인 TEMP TABLESPACE 는 삭제가 안되므로 신규로 템프 TABLESPACE를 생성
CREATE TEMPORARY TABLESPACE [테이블 스페이스명] TEMPFILE '\경로\TEST_TEMP.DBF' SIZE 10M AUTOEXTEND ON NEXT 100M MAXSIZE 300G EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K -- LOCALY MANAGED TABLESPACE UNIFORM SIZE만 생성가능하다. -- (주의)AUTOALLOCATE, EXTENT MANAGEMENT DICIONARY OPTION을 사용하면 ORA-25319 ERROR 발생한다. -- RENAME 이 불가능하다.
TEMPORARY TABLESPACE를 DEFAULT TABLESPACE로 변경
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE [테이블 스페이스명];
TEMPORARY TABLESPACE 사이즈 증가
ALTER TABLESPACE TEMP AUTOEXTEND ON NEXT 100M MAXSIZE 500G;
TEMPORARY TABLESPACE 사이즈 추가
ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA' SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE 32767M;
TEMPORARY TABLESPACE 삭제
DROP TABLESPACE TEMP2;
TEMPORARY TABLESPACE DATA FILE 삭제
ALTER TABLESPACE TEMP DROP TEMPFILE '+DATA/temp.368.1013282149';
- +DATA/temp.368.1013282149 은 ASM 사용시
TEMPORARY TABLESPACE 사용율 조회 쿼리
/* 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
템프테이블 TABLESPACE 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
TABLESPACE 오브젝트별 정보
TABLESPACE/파일 확인(딕셔너리)
SELECT * FROM DBA_DATA_FILES ; SELECT * FROM DBA_TABLESPACES ; SELECT * FROM DBA_SEGMENTS;
TABLESPACE별 정보
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;
TABLESPACE별 파일 목록
SELECT SUBSTRB(TABLESPACE_NAME, 1, 10) AS "TABLESPACE" , 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;
TABLESPACE 사이즈 정보
TABLESPACE별 전체 오브젝트(테이블/인덱스/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 ;
TABLESPACE별 사이즈
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_name
테이블 TABLESPACE 사이즈
-- 테이블 사이즈
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
인덱스 TABLESPACE 사이즈
-- 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 TABLESPACE 사이즈
-- 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;
데이터파일 별 테이블스페이스 사이즈 조회
SELECT A.TABLESPACE_NAME "테이블스페이스명", A.FILE_NAME "파일경로", (A.BYTES - B.FREE) "사용공간", B.FREE "여유 공간", A.BYTES "총크기", TO_CHAR( (B.FREE / A.BYTES * 100) , '999.99')||'%' "여유공간" FROM ( SELECT FILE_ID, TABLESPACE_NAME, FILE_NAME, SUBSTR(FILE_NAME,1,200) FILE_NM, SUM(BYTES) BYTES FROM DBA_DATA_FILES GROUP BY FILE_ID,TABLESPACE_NAME,FILE_NAME,SUBSTR(FILE_NAME,1,200) ) A, ( SELECT TABLESPACE_NAME, FILE_ID, SUM(NVL(BYTES,0)) FREE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME,FILE_ID ) B WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.FILE_ID = B.FILE_ID;
TABLESPACE별 사용하는 파일의 크기 합
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;
TABLESPACE별 디스크 사용량
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;
공간의 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;
TABLESPACE에 포함된 테이블 명 보기
SELECT TABLESPACE_NAME , TABLE_NAME FROM DBA_TABLES WHERE TABLESPACE_NAME = UPPER('&TABLESPACE명') ORDER BY TABLESPACE_NAME , TABLE_NAME;
오브젝트별 TABLESPACE 및 데이터파일
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');
TABLESPACE별 Table, Index 개수
SELECT OWNER , TABLESPACE_NAME , SUM(DECODE(SEGMENT_TYPE, 'TABLE', 1, 0)) TAB , SUM(DECODE(SEGMENT_TYPE, 'INDEX', 1, 0)) IDX , SUM(DECODE(SEGMENT_TYPE, 'LOBINDEX', 1, 0)) LOB_IDX , SUM(DECODE(SEGMENT_TYPE, 'LOBSEGMENT', 1, 0)) LOB_SEG FROM DBA_SEGMENTS WHERE SEGMENT_TYPE IN ('TABLE', 'INDEX', 'LOBINDEX','LOBSEGMENT') GROUP BY OWNER, TABLESPACE_NAME;
파일위치별 TABLESPACE 아는 방법
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('&TABLESPACE명') ORDER BY A.FILE_NAME;
현재 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;
테이블의 익스텐트 정보 조회
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);
UNDO TABLESPACE
- UNDO Segment를 저장하고 있는 TABLESPACE , 관리자가 생성/관리 가능
- Instance당 여러 개가 동시에 존재할 수 있지만 사용은 한번에 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
UNDO 테이블 스페이스 삭제
DROP TABLESPACE UNDO_T1 INCLUDING CONTENTS AND DATAFILES;
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;
TABLESPACE 장애 처리
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
LOB포함된 TABLESPACE 용량 축소/REORG
해당 테이블스페이스를 사용중인 테이블/인덱스/LOB 조회
SELECT * FROM DBA_SEGMENTS WHERE TABLESPACE_NAME ='TS_TEST_D01';
테이블에서 사용중인 테이블스페이스 신규테이블스페이스로 이동
SELECT OWNER , TABLE_NAME , TABLESPACE_NAME , 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' MOVE TABLESPACE TS_TEST_D11;' -- 임시로 생성된 T/S로 이동 , 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' MOVE TABLESPACE '||TABLESPACE_NAME||';' MV_TS -- 다시 원래 테이블스페이스로 이동 FROM DBA_TABLES WHERE TABLESPACE_NAME ='TS_TEST_D01'; -- TS_TEST_D01을 사용하는 테이블
인덱스 T/S 이동(INDEX REBUILD)
SELECT OWNER , TABLE_NAME , INDEX_NAME , TABLESPACE_NAME , 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD TABLESPACE TS_TEST_I11;' RBD_TS FROM DBA_INDEXES WHERE TABLESPACE_NAME ='TS_TEST_I01'; -- TS_TEST_I01을 사용하는 인덱스
LOB T/S 이동
-- LOB 테이블 이동하기
select owner,table_name , 'alter table '||owner||'.'||table_name||' move lob('||col||') store as (tablespace TS_TEST_D01);' mv_lob_ts from ( select a.owner,a.table_name , listagg(a.column_name,',') within group (order by 1) col from dba_lobs a where table_name in ( SELECT DISTINCT TABLE_NAME FROM DBA_TAB_COLUMNS WHERE OWNER = 'TEST' AND DATA_TYPE LIKE '%LOB' ) group by a.owner,a.table_name ) --order by 1,2 ;
원래 T/S명으로 변경
ALTER TABLESPACE [asis-TABLE] RENAME TO [tobe-TABLE]