"ORACLE 테이블스페이스"의 두 판 사이의 차이
DB CAFE
(→템프테이블 스페이스) |
(→TEMPORARY TABLESPACE 생성) |
||
(사용자 2명의 중간 판 153개는 보이지 않습니다) | |||
1번째 줄: | 1번째 줄: | ||
− | =테이블 스페이스= | + | == 테이블 스페이스 (TABLESPACE) == |
− | + | === TABLESPACE 목록 조회 === | |
− | == | + | <source lang=sql> |
− | + | 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; | |
− | + | </source> | |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
+ | === TABLESPACE 종류 === | ||
+ | {{틀:고지상자 | ||
+ | |제목 = 테이블스페이스 사이즈 축소 작업 순서 | ||
+ | |내용 =1) SYSTEM TABLESPACE | ||
+ | :- 데이터 딕셔너리 정보들이 저장되어 있음, 이 TABLESPACE 가 손상될 경우 Oracle 서버가 시작이 안됨 | ||
+ | :- SYS계정 소유이지만 조회만 가능 | ||
+ | :- 딕셔너리 주요 정보 | ||
* 데이터베이스의 논리적인 구조와 물리적인 구조 정보들 | * 데이터베이스의 논리적인 구조와 물리적인 구조 정보들 | ||
− | |||
* 객체의 정의와 공간 사용 정보들 | * 객체의 정의와 공간 사용 정보들 | ||
− | |||
* 제약조건에 관련된 정보들 | * 제약조건에 관련된 정보들 | ||
− | |||
* 사용자에 관련된 정보들 | * 사용자에 관련된 정보들 | ||
− | |||
* Role, Privilege 등에 관련된 정보들 | * Role, Privilege 등에 관련된 정보들 | ||
− | |||
* 감사 및 보안등에 관련된 정보들 | * 감사 및 보안등에 관련된 정보들 | ||
+ | 2) SYSAUX TABLESPACE | ||
+ | : 10g ~ , Oracle 서버의 성능 튜닝을 위한 데이터 저장 | ||
+ | 3) 데이터 TABLESPACE | ||
+ | : 가장 일반적으로 많이 사용되는 TABLESPACE 로 DBA가 필요에 의해 만드는 TABLESPACE . | ||
+ | DBA에 의해 생성,삭제. | ||
+ | }} | ||
+ | ---- | ||
+ | === 현재 유저의 DEFAULT TABLESPACE 확인 === | ||
+ | <source lang="sql"> | ||
+ | SELECT * FROM USER_USERS ;-- DEFAUT TABLESPACE로 설정된 부분을 확인 | ||
+ | </source> | ||
+ | ==== 유저의 DEFAULT TABLESPACE 변경 ==== | ||
+ | <source lang="sql"> | ||
+ | ALTER USER [유저명] DEFAULT TABLESPACE [테이블 스페이스명] | ||
+ | </source> | ||
− | + | === TABLESPACE 생성 구문 === | |
+ | https://docs.oracle.com/database/121/SQLRF/img/create_tablespace.gif | ||
+ | https://docs.oracle.com/database/121/SQLRF/img/permanent_tablespace_clause.gif | ||
− | + | <source lang="sql"> | |
+ | 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%) | ||
+ | : ) | ||
+ | </source> | ||
+ | ==== BIGFILE 테이블스페이스 ==== | ||
+ | # 8K 블록 - bigfile 테이블 스페이스에는 최대 32TB | ||
+ | # 32K 블록 - bigfile 파일 테이블 스페이스에는 최대 128TB | ||
+ | ===== BIGFILE 테이블스페이스 장점 ===== | ||
+ | ===== 자동 수행 뷰 목록 ===== | ||
+ | {{틀:고지 상자 | ||
+ | |내용=장점 | ||
+ | # CREATE DATABASE 및 CREATE CONTROLFILE 문의 DB_FILES 초기화 매개 변수와 MAXDATAFILES 매개 변수를 조정하여 데이터 파일 정보에 필요한 SGA 공간의 양과 제어 파일의 크기를 줄일 수 있다. | ||
+ | # Bigfile 테이블 스페이스는 데이터 파일 투명성을 제공하여 데이터베이스 관리를 단순화합니다. | ||
+ | # ALTER TABLESPACE 문의 SQL 구문을 사용하면 기본 개별 데이터 파일이 아닌 테이블 스페이스에서 작업을 수행 할 수 있다. | ||
+ | # 빅파일 테이블 스페이스는 자동 세그먼트 공간 관리가있는 로컬 관리 테이블 스페이스에 대해서만 가능함. (언두,템프,시스템 테이블스페이스는 지원 안함) | ||
+ | }} | ||
+ | ===== BIGFILE 테이블스페이스 단점 ===== | ||
+ | {{틀:고지 상자 | ||
+ | |내용=BIGFILE 테이블스페이스 단점 | ||
+ | # 병렬 쿼리 실행 및 RMAN 백업 병렬화에 부정적인 영향을 미치므로 스트라이핑을 지원하지 않는 시스템에서 빅 파일 테이블 스페이스를 생성하지 마십시오. | ||
+ | # 큰 파일 크기를 지원하지 않는 OS 플랫폼에서 bigfile 테이블 스페이스를 사용하는 것은 권장되지 않으며 테이블 스페이스 용량을 제한 할 수 있습니다. | ||
+ | # Bigfile 테이블 스페이스는 ASM (Automatic Storage Management) 또는 스트라이핑 또는 RAID를 지원하는 기타 논리 볼륨 관리자 및 동적으로 확장 가능한 논리 볼륨과 함께 사용하기위한 것입니다. | ||
+ | }} | ||
− | ( | + | === UNDO TABLESPACE 생성 === |
+ | <source lang="sql"> | ||
+ | CREATE UNDO TABLESPACE [테이블 스페이스명] ==> 이부분만 다름. | ||
+ | DATAFILE '\경로\TEST_UNDO.DBF' | ||
+ | SIZE 10M | ||
+ | AUTOEXTEND ON NEXT 10M | ||
+ | MAXSIZE 100M | ||
+ | -- UNDO_MANAGEMENT와 UNDO_TABLESPACE, UNDO_RETENTION PARAMETER를 제공 | ||
+ | -- 지역적으로 관리되는 익스텐트만 사용가능 | ||
+ | -- 시스템에 의해 관리되는 익스텐트 할당만 사용가능하다. | ||
+ | -- (UNIFORMSIZE를 할당 할 수 없고 AUTOALLOCATE만 가능) | ||
+ | </source> | ||
+ | |||
+ | ---- | ||
+ | |||
+ | === TABLESPACE 변경 === | ||
+ | ==== TABLESPACE 읽기/쓰기 모드 변경 ==== | ||
+ | * 읽기 전용 모드 | ||
+ | <source lang="sql"> | ||
+ | alter tablespace <TABLESPACE명> read only; | ||
+ | </source> | ||
+ | * 읽기 쓰기 모드 | ||
+ | <source lang="sql"> | ||
+ | alter tablespace <TABLESPACE명> read ,write; | ||
+ | </source> | ||
− | + | ==== OPEN 상태에서 DATAFILE 이동 ==== | |
+ | <source lang="sql"> | ||
+ | alter tablespace TABLESPACE명 offline; | ||
+ | </source> | ||
+ | ===== offline 된 T/S에 대해 복사/이동 ===== | ||
+ | <source lang=sh> | ||
+ | cp /data1/xxx.dbf /data2/xxx.dbf | ||
+ | or | ||
+ | mv /data1/xxx.dbf /data2/xxx.dbf | ||
+ | </source> | ||
− | + | ===== OFFLINE 상태에서 DATAFILE 이동 ===== | |
+ | <source lang="sql"> | ||
+ | alter tablespace TABLESPACE명 rename datafile '파일경로1/xxx.dbf' to '파일경로2/xxx.dbf'; | ||
+ | alter tablespace TABLESPACE명 online; | ||
+ | </source> | ||
− | + | ==== MOUNT 상태에서 DATAFILE 이동 ==== | |
+ | <source lang="sql"> | ||
+ | startup mount; | ||
+ | </source> | ||
− | + | ===== 해당 T/S에 대해 복사/이동 후 ===== | |
+ | <source lang="sql"> | ||
+ | alter database rename file '파일경로' to '파일경로'; | ||
+ | alter database open; | ||
+ | </source> | ||
− | + | # 모든 데이타 파일은 mount상태에서 복사/이동 가능 | |
+ | # system 파일은 mount상태에서만 복사/이동 가능 | ||
− | + | === 테이블의 TABLESPACE MOVE === | |
+ | <source lang="sql"> | ||
+ | ALTER TABLE [테이블명] | ||
+ | MOVE TABLESPACE [테이블 스페이스명] | ||
+ | </source> | ||
+ | ;-테이블스페이스 이동시 인덱스 리빌드 필요 | ||
+ | <source lang="sql"> | ||
+ | ALTER INDEX [인덱스명] REBUILD ; | ||
+ | </source> | ||
+ | |||
+ | * 인덱스 리빌드 동시 수행 시 | ||
+ | <source lang="sql"> | ||
+ | ALTER TABLE [테이블명] | ||
+ | MOVE TABLESPACE [테이블 스페이스명] | ||
+ | UPDATE INDEXES; | ||
+ | </source> | ||
+ | ==== 파티션닝 테이블 TABLESPACE MOVE ==== | ||
+ | <source lang="sql"> | ||
+ | ALTER TABLE [테이블명] | ||
+ | MOVE PARTITION [파티션명] TABLESPACE [테이블 스페이스명] | ||
+ | </source> | ||
+ | * 파티셔닝 테이블 인덱스 REBUILD | ||
+ | <source lang="sql"> | ||
+ | ALTER INDEX [인덱스명] | ||
+ | REBUILD PARTITION [파티션명] | ||
+ | </source> | ||
− | + | === 운영중인 테이블 TABLESPACE ONLINE MOVE === | |
+ | <source lang="sql"> | ||
+ | ALTER TABLE [테이블명] | ||
+ | MOVE TABLESPACE [테이블 스페이스명] | ||
+ | ONLINE -- online 옵션 | ||
+ | ; | ||
+ | </source> | ||
− | |||
− | - | + | * 인덱스 리빌드 동시 수행 시 |
+ | <source lang="sql"> | ||
+ | ALTER TABLE [테이블명] | ||
+ | MOVE TABLESPACE [테이블 스페이스명] | ||
+ | UPDATE INDEXES | ||
+ | ONLINE -- online 옵션 | ||
+ | ; | ||
+ | </source> | ||
+ | ==== 운영중인 파티션닝 테이블 TABLESPACE ONLINE MOVE ==== | ||
+ | <source lang="sql"> | ||
+ | ALTER TABLE [테이블명] | ||
+ | MOVE PARTITION [파티션명] TABLESPACE [테이블 스페이스명] | ||
+ | ONLINE | ||
+ | </source> | ||
− | + | {{틀:알림 | |
+ | |내용 = 온라인 Move 작업시 장단점 | ||
+ | # Move 작업 중 다른 세션의 DML 작업이 가능함 | ||
+ | # 파티션의 로컬인덱스는 USABLE 상태로 바로 사용가능함(unusable 되지 않음) | ||
+ | # 도메인 인덱스가 포함된 테이블은 사용불가 | ||
+ | # 온라인 Move 작업시 parallel DML , Direct Path Insert 가 안됨(즉,성능은 좋지않다) | ||
+ | }} | ||
− | + | === TABLESPACE 사이즈 변경(RESIZE) === | |
+ | <source lang="sql"> | ||
+ | ALTER TABLESPACE ts_txxxx | ||
+ | RESIZE 500G; | ||
+ | </source> | ||
+ | === TABLESPACE 이름 변경 === | ||
+ | <source lang="sql"> | ||
+ | ALTER TABLESPACE [asis-TABLE] RENAME TO [tobe-TABLE] | ||
+ | </source> | ||
+ | ---- | ||
+ | === 인덱스의 TABLESPACE 변경 === | ||
+ | <source lang="sql"> | ||
+ | ALTER INDEX 인덱스명 REBUILD TABLESPACE [테이블 스페이스명] | ||
+ | [ PARALLEL parallel_num ] | ||
+ | [ LOGGING or NOLOGGING ] | ||
+ | ; | ||
+ | </source> | ||
+ | === 인덱스의 TABLESPACE 변경 스크립트 === | ||
+ | <source lang="sql"> | ||
+ | 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); | ||
+ | </source> | ||
+ | |||
+ | === 인덱스/테이블 TABLESPACE 변경 스크립트 === | ||
+ | <source lang="sql"> | ||
+ | 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; | ||
+ | </source> | ||
− | |||
− | |||
− | + | === TABLESPACE 자동증가/최대 사이즈 변경 === | |
+ | <source lang="sql"> | ||
+ | ALTER TABLESPACE ts_txxxx | ||
+ | AUTOEXTEND ON | ||
+ | NEXT 100M | ||
+ | MAXSIZE 500G;-- 최대사이즈 , UNLIMITED => 무제한 증가 | ||
+ | </source> | ||
− | |||
− | + | * 최대 TABLESPACE 사이즈 변경 | |
+ | <source lang="sql"> | ||
+ | ALTER TABLESPACE [테이블 스페이스명] | ||
+ | ADD DATAFILE 'C:\경로\TEST2.DBF' SIZE 10M | ||
+ | AUTOEXTEND ON NEXT 100M | ||
+ | MAXSIZE 100G; | ||
− | + | ==> 10M씩 자동증가 | |
+ | </source> | ||
− | + | === 데이터파일 사이즈 변경 === | |
+ | <source lang="sql"> | ||
+ | ALTER DATABASE DATAFILE 'C:\경로\TEST1.DBF' | ||
+ | RESIZE 10M; | ||
+ | </source> | ||
− | + | === 데이터파일 추가 === | |
+ | <source lang="sql"> | ||
+ | ALTER TABLESPACE [테이블 스페이스명] | ||
+ | ADD DATAFILE 'C:\경로\TEST2.DBF' SIZE 10M; | ||
+ | </source> | ||
− | + | === 데이터/템프파일 삭제 === | |
+ | <source lang="sql"> | ||
+ | ALTER TABLESPACE [테이블 스페이스명] | ||
+ | DROP DATAFILE 'data파일'; | ||
+ | -- 템프TS DROP TEMPFILE 'data파일'; | ||
+ | </source> | ||
− | + | === 데이터 파일 삭제 후 OS 디스크 사이즈가 줄지 않을경우 === | |
+ | {{틀:타이틀 투명 | ||
+ | |보더색=#696969 | ||
+ | |배경색=#483d8b | ||
+ | |제목= 1) 테이블스페이스 사이즈를 줄인 (shrink) 후 데이터파일 삭제 | ||
+ | 2) DB 리부팅 하면 공간 확보 됨(DBWR 이 데이터파일을 사용중이기 때문에 공간이 줄지 않음) | ||
+ | }} | ||
− | - | + | ---- |
− | + | === TABLESPACE 삭제 === | |
+ | ==== TABLESPACE에 포함된 모든 세그먼트 삭제 ==== | ||
+ | <source lang="sql"> | ||
+ | DROP TABLESPACE [테이블 스페이스명] | ||
+ | INCLUDING CONTENTS;-- TABLESPACE의 모든 세그먼트를 삭제한다. | ||
+ | -- 단, 데이타가 있는 TABLESPACE는 삭제할수 없다. | ||
+ | </source> | ||
+ | ==== TABLESPACE에 포함된 테이블의 참조/제약 조건 삭제 ==== | ||
+ | <source lang="sql"> | ||
+ | DROP TABLESPACE [테이블 스페이스명] | ||
+ | CASCADE CONSTRAINTS; -- 삭제된 TABLESPACE 내의 테이블의 기본키와 유일키를 참조하는 | ||
+ | -- 다른 TABLESPACE의 테이블로부터 참조무결성 제약 조건을 삭제한다. | ||
+ | </source> | ||
− | + | ==== TABLESPACE의 데이터파일 삭제 ==== | |
+ | <source lang="sql"> | ||
+ | DROP TABLESPACE [테이블 스페이스명] | ||
+ | INCLUDING CONTENTS AND DATAFILES; -- 물리적파일까지 삭제한다. | ||
+ | </source> | ||
− | |||
− | + | === TABLESPACE 수정이나 삭제시 ONLINE/OFFLINE 설정 === | |
+ | <source lang="sql"> | ||
+ | ALTER TABLESPACE [테이블 스페이스명] ONLINE | ||
+ | ALTER TABLESPACE [테이블 스페이스명] OFFLINE | ||
+ | </source> | ||
− | + | ---- | |
− | + | === 템프 TABLESPACE === | |
− | + | ==== TEMPORARY TABLESPACE 정보 ==== | |
+ | <source lang="sql"> | ||
+ | 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 | ||
+ | </source> | ||
− | + | ===== 템프테이블 사이즈 조정 ===== | |
+ | <source lang="sql"> | ||
+ | ALTER DATABASE TEMPFILE 'D:\APP\ORADATA\ORCL\TEMP01.DBF' | ||
+ | RESIZE 304M; | ||
+ | </source> | ||
− | + | ==== TEMPORARY TABLESPACE 생성 ==== | |
− | + | *. '''신규생성만 가능'''. | |
+ | : 사용중인 TEMP TABLESPACE 는 삭제가 안되므로 신규로 템프 TABLESPACE를 생성 | ||
+ | <source lang=sql> | ||
+ | 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 이 불가능하다. | ||
+ | </source> | ||
− | + | ==== TEMPORARY TABLESPACE를 DEFAULT TABLESPACE로 변경 ==== | |
+ | <source lang="sql"> | ||
+ | ALTER DATABASE DEFAULT TEMPORARY TABLESPACE [테이블 스페이스명]; | ||
+ | </source> | ||
− | + | ==== TEMPORARY TABLESPACE 사이즈 증가 ==== | |
+ | <source lang="sql"> | ||
+ | ALTER TABLESPACE TEMP | ||
+ | AUTOEXTEND ON | ||
+ | NEXT 100M | ||
+ | MAXSIZE 500G; | ||
+ | </source> | ||
+ | ---- | ||
− | + | ==== TEMPORARY TABLESPACE 사이즈 추가 ==== | |
+ | <source lang="sql"> | ||
+ | ALTER TABLESPACE TEMP | ||
+ | ADD TEMPFILE '+DATA' SIZE 10G | ||
+ | AUTOEXTEND ON | ||
+ | NEXT 100M | ||
+ | MAXSIZE 32767M; | ||
+ | </source> | ||
+ | ---- | ||
− | + | ==== TEMPORARY TABLESPACE 삭제 ==== | |
+ | <source lang="sql"> | ||
+ | DROP TABLESPACE TEMP2; | ||
+ | </source> | ||
− | + | ==== TEMPORARY TABLESPACE DATA FILE 삭제 ==== | |
+ | <source lang="sql"> | ||
+ | ALTER TABLESPACE TEMP DROP TEMPFILE '+DATA/temp.368.1013282149'; | ||
+ | </source> | ||
+ | - +DATA/temp.368.1013282149 은 ASM 사용시 | ||
− | + | ==== TEMPORARY TABLESPACE 사용율 조회 쿼리 ==== | |
− | + | <source lang="sql"> | |
+ | /* 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 | ||
+ | </source> | ||
− | + | ==== 템프테이블 TABLESPACE sort 사용 현황 ==== | |
+ | <source lang="sql"> | ||
+ | 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 | ||
+ | </source> | ||
+ | ---- | ||
− | + | == TABLESPACE 오브젝트별 정보 == | |
− | + | === TABLESPACE/파일 확인(딕셔너리) === | |
+ | <source lang="sql"> | ||
+ | SELECT * FROM DBA_DATA_FILES ; | ||
+ | SELECT * FROM DBA_TABLESPACES ; | ||
+ | SELECT * FROM DBA_SEGMENTS; | ||
+ | </source> | ||
− | + | === TABLESPACE별 정보 === | |
− | == | + | <source lang="sql"> |
− | <source lang=sql> | + | SELECT A.TABLESPACE_NAME AS "TABLESPACE" |
− | SELECT A.TABLESPACE_NAME,A. | + | , 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 | |
− | WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME | + | AND C.NAME = 'db_block_size' |
− | ORDER BY 1,2 | + | ORDER BY 1, 2; |
+ | </source> | ||
+ | ---- | ||
+ | === TABLESPACE별 파일 목록 === | ||
+ | <source lang="sql"> | ||
+ | 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; | ||
</source> | </source> | ||
− | == | + | ---- |
+ | |||
+ | === TABLESPACE 사이즈 정보 === | ||
+ | |||
+ | ==== TABLESPACE별 전체 오브젝트(테이블/인덱스/LOB) 사이즈 ==== | ||
-- OBJECT별 테이블 사이즈 (UNDO,TEMP T/S 제외) | -- OBJECT별 테이블 사이즈 (UNDO,TEMP T/S 제외) | ||
<source lang=sql> | <source lang=sql> | ||
208번째 줄: | 539번째 줄: | ||
, A.SEGMENT_TYPE | , A.SEGMENT_TYPE | ||
UNION ALL | UNION ALL | ||
+ | |||
-- INDEX SIZE | -- INDEX SIZE | ||
− | |||
SELECT A.TABLESPACE_NAME | SELECT A.TABLESPACE_NAME | ||
, A.SEGMENT_NAME | , A.SEGMENT_NAME | ||
242번째 줄: | 573번째 줄: | ||
; | ; | ||
</source> | </source> | ||
− | + | ---- | |
− | == | + | ==== TABLESPACE별 사이즈 ==== |
<source lang=sql> | <source lang=sql> | ||
− | SELECT | + | 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 | ||
</source> | </source> | ||
− | == 테이블 | + | ==== 테이블 TABLESPACE 사이즈 ==== |
-- 테이블 사이즈 | -- 테이블 사이즈 | ||
<source lang=sql> | <source lang=sql> | ||
277번째 줄: | 612번째 줄: | ||
</source> | </source> | ||
− | + | ==== 인덱스 TABLESPACE 사이즈 ==== | |
-- INDEX SIZE | -- INDEX SIZE | ||
<source lang=sql> | <source lang=sql> | ||
293번째 줄: | 628번째 줄: | ||
</source> | </source> | ||
− | + | ==== LOB TABLESPACE 사이즈 ==== | |
+ | -- LOB 사이즈 | ||
<source lang=sql> | <source lang=sql> | ||
SELECT TABLE_NAME | SELECT TABLE_NAME | ||
305번째 줄: | 641번째 줄: | ||
group by table_name; | group by table_name; | ||
</source> | </source> | ||
− | + | ==== 데이터파일 별 테이블스페이스 사이즈 조회 ==== | |
− | + | <source lang="sql"> | |
− | + | 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, |
− | <source lang="sql"> | + | TABLESPACE_NAME, |
− | SELECT | + | 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; | |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
</source> | </source> | ||
− | |||
− | |||
− | |||
− | |||
− | + | ==== TABLESPACE별 사용하는 파일의 크기 합 ==== | |
− | == | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
<source lang="sql"> | <source lang="sql"> | ||
− | + | SELECT SUBSTRB(TABLESPACE_NAME, 1, 10) AS TABLESPACE | |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
, TO_CHAR(SUM(BYTES), '9,999,999,999,990') AS BYTES | , TO_CHAR(SUM(BYTES), '9,999,999,999,990') AS BYTES | ||
, TO_CHAR(SUM(BLOCKS), '9,999,999,990') AS BLOCKS | , TO_CHAR(SUM(BLOCKS), '9,999,999,990') AS BLOCKS | ||
513번째 줄: | 681번째 줄: | ||
FROM DBA_DATA_FILES; | FROM DBA_DATA_FILES; | ||
</source> | </source> | ||
+ | ---- | ||
− | === | + | ==== TABLESPACE별 디스크 사용량 ==== |
− | |||
<source lang="sql"> | <source lang="sql"> | ||
SELECT A.TABLESPACE_NAME AS "TABLESPACE" | SELECT A.TABLESPACE_NAME AS "TABLESPACE" | ||
544번째 줄: | 712번째 줄: | ||
FROM DBA_EXTENTS | FROM DBA_EXTENTS | ||
GROUP BY TABLESPACE_NAME) D | GROUP BY TABLESPACE_NAME) D | ||
− | + | WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+) | |
− | WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+) AND A.TABLESPACE_NAME = C.TABLESPACE_NAME(+) AND A.TABLESPACE_NAME = D.TABLESPACE_NAME(+) | + | AND A.TABLESPACE_NAME = C.TABLESPACE_NAME(+) |
− | ORDER BY A.TABLESPACE_NAME; | + | AND A.TABLESPACE_NAME = D.TABLESPACE_NAME(+) |
+ | ORDER BY A.TABLESPACE_NAME; | ||
</source> | </source> | ||
− | + | ---- | |
− | = | + | ====공간의 90% 이상을 사용하고 있는 TABLESPACE ==== |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | ===공간의 90% 이상을 사용하고 있는 | ||
<source lang="sql"> SELECT X.TABLESPACE_NAME | <source lang="sql"> SELECT X.TABLESPACE_NAME | ||
, TOTAL_SIZE / 1024 / 1024 TOTAL_SIZE | , TOTAL_SIZE / 1024 / 1024 TOTAL_SIZE | ||
570번째 줄: | 731번째 줄: | ||
WHERE X.TABLESPACE_NAME = Y.TABLESPACE_NAME(+) AND Y.USED_SIZE > .9 * X.TOTAL_SIZE; | WHERE X.TABLESPACE_NAME = Y.TABLESPACE_NAME(+) AND Y.USED_SIZE > .9 * X.TOTAL_SIZE; | ||
</source> | </source> | ||
+ | ---- | ||
− | === | + | === TABLESPACE에 포함된 테이블 명 보기=== |
− | + | <source lang="sql"> | |
+ | SELECT TABLESPACE_NAME | ||
+ | , TABLE_NAME | ||
+ | FROM DBA_TABLES | ||
+ | WHERE TABLESPACE_NAME = UPPER('&TABLESPACE명') | ||
+ | ORDER BY TABLESPACE_NAME | ||
+ | , TABLE_NAME; | ||
+ | </source> | ||
+ | ---- | ||
+ | === 오브젝트별 TABLESPACE 및 데이터파일 === | ||
<source lang="sql"> | <source lang="sql"> | ||
SELECT DISTINCT E.SEGMENT_NAME, E.TABLESPACE_NAME, F.FILE_NAME | SELECT DISTINCT E.SEGMENT_NAME, E.TABLESPACE_NAME, F.FILE_NAME | ||
581번째 줄: | 752번째 줄: | ||
AND E.TABLESPACE_NAME NOT IN ('SYSTEM', 'TOOLS'); </source> | AND E.TABLESPACE_NAME NOT IN ('SYSTEM', 'TOOLS'); </source> | ||
− | === | + | === TABLESPACE별 Table, Index 개수=== |
<source lang="sql"> | <source lang="sql"> | ||
SELECT OWNER | SELECT OWNER | ||
, TABLESPACE_NAME | , TABLESPACE_NAME | ||
− | , SUM(DECODE(SEGMENT_TYPE, 'TABLE', 1, 0)) | + | , SUM(DECODE(SEGMENT_TYPE, 'TABLE', 1, 0)) TAB |
− | , SUM(DECODE(SEGMENT_TYPE, 'INDEX', 1, 0)) | + | , SUM(DECODE(SEGMENT_TYPE, 'INDEX', 1, 0)) IDX |
− | FROM DBA_SEGMENTS WHERE SEGMENT_TYPE IN ('TABLE', 'INDEX') GROUP BY OWNER, TABLESPACE_NAME; | + | , 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; | ||
</source> | </source> | ||
− | ===파일위치별 | + | === 파일위치별 TABLESPACE 아는 방법 === |
<source lang="sql"> | <source lang="sql"> | ||
603번째 줄: | 778번째 줄: | ||
GROUP BY FILE_ID) B | GROUP BY FILE_ID) B | ||
WHERE A.FILE_ID = B.FILE_ID | WHERE A.FILE_ID = B.FILE_ID | ||
− | AND A.TABLESPACE_NAME = UPPER('& | + | AND A.TABLESPACE_NAME = UPPER('&TABLESPACE명') |
ORDER BY A.FILE_NAME; </source> | ORDER BY A.FILE_NAME; </source> | ||
619번째 줄: | 794번째 줄: | ||
===테이블의 익스텐트 정보 조회=== | ===테이블의 익스텐트 정보 조회=== | ||
− | + | {{틀:고지상자 | |
− | + | |제목 = 오라클에서 스토리지 구조는 아래와 같다. | |
− | + | |내용 = 테이블 스페이스 -> 세그먼트 -> 익스텐트 -> 블록 -> OS 범위 -> 데이터 파일 -> 운영체제 블록 | |
− | |||
− | |||
세그먼트의 이름, | 세그먼트의 이름, | ||
해당 세그먼트의 최대 익스텐트 개수, | 해당 세그먼트의 최대 익스텐트 개수, | ||
익스텐트 아이디 | 익스텐트 아이디 | ||
해당 세그먼트의 최대 익스텐트 개수 - 최대 익스텐트 아이디 | 해당 세그먼트의 최대 익스텐트 개수 - 최대 익스텐트 아이디 | ||
− | 딕셔너리 관리 | + | 딕셔너리 관리 TABLESPACE로 생성한 것으로 조회를 한다. |
− | + | }} | |
− | + | <source lang="sql"> | |
SELECT B.SEGMENT_NAME | SELECT B.SEGMENT_NAME | ||
, B.MAX_EXTENTS | , B.MAX_EXTENTS | ||
645번째 줄: | 818번째 줄: | ||
ORDER BY B.MAX_EXTENTS - MAX(C.EXTENT_ID); | ORDER BY B.MAX_EXTENTS - MAX(C.EXTENT_ID); | ||
</source> | </source> | ||
+ | ---- | ||
+ | |||
+ | == 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 | ||
+ | }} | ||
+ | ---- | ||
− | ===ROLLBACK SEGMENT의 사용상황 보기=== | + | === 현재 UNDO 상태 확인 === |
+ | <source lang=sql> | ||
+ | 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 | ||
+ | </source> | ||
+ | === UNDO 테이블 스페이스 삭제 === | ||
+ | <source lang=sql> | ||
+ | DROP TABLESPACE UNDO_T1 INCLUDING CONTENTS AND DATAFILES; | ||
+ | </source> | ||
+ | |||
+ | |||
+ | ---- | ||
+ | ===ROLLBACK SEGMENT의 사용상황 보기=== | ||
<source lang="sql"> | <source lang="sql"> | ||
--: EXTENTS = 현재 할당된 EXTENT의 수 --: EXTENDS = 마지막 트랜잭션에 의해 할당된 EXTENT의 수 | --: EXTENTS = 현재 할당된 EXTENT의 수 --: EXTENDS = 마지막 트랜잭션에 의해 할당된 EXTENT의 수 | ||
662번째 줄: | 873번째 줄: | ||
WHERE A.SEGMENT_ID = B.USN(+) ORDER BY 1; </source> | WHERE A.SEGMENT_ID = B.USN(+) ORDER BY 1; </source> | ||
− | = | + | == TABLESPACE 장애 처리 == |
− | == | + | |
− | <source lang= | + | === TABLESPACE 용량 부족으로 에러 발생시 === |
− | + | * 방법 1) Data file을 크게 늘려줌(자동 증가/수동증가) | |
+ | * 방법 2) Data file을 하나 더 추가 | ||
+ | |||
+ | ==== Tablespace Offline ==== | ||
+ | |||
+ | * 사용자가 더 이상 해당 Tablespace에 접근하지 못한다는 의미 | ||
+ | * 데이터파일의 위치를 이동하거나 특정 Tablespace가 장애가 나서 복구해야 할 때 사용 | ||
+ | |||
+ | |||
+ | ==== TABLESPACE 를 Offline하는 방법 3가지==== | ||
+ | 1) Normal Mode | ||
+ | <source lang=sql> | ||
+ | SQL> alter TABLESPACE TS_TEST offline ; | ||
</source> | </source> | ||
− | + | 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 하기 | ||
+ | #* <SOURCE LANG=SQL>ALTER TABLESPACE TS_TEST OFFLINE;</SOURCE> | ||
+ | # Data file을 대상 위치로 복사 | ||
+ | # Control file 내의 해당 Data file 위치 변경 | ||
+ | # 해당 Tablespace Online | ||
+ | #* <SOURCE LANG=SQL>ALTER TABLESPACE TS_TEST ONLINE;</SOURCE> | ||
+ | 2) Offline 안 되는 TABLESPACE 의 Data file 이동하기 | ||
+ | # DB 종료 | ||
+ | # Mount 상태로 시작 | ||
+ | # Data file 복사 | ||
+ | # Control file의 내용 변경 | ||
+ | # DB Open | ||
− | == | + | ---- |
+ | |||
+ | == LOB포함된 TABLESPACE 용량 축소/REORG == | ||
+ | {{틀:고지상자 | ||
+ | |제목 = 테이블스페이스 사이즈 축소 작업 순서 | ||
+ | |내용 =# T/S 사용중인 테이블/인덱스/LOB 조회 | ||
+ | # 테이블 T/S 이동 | ||
+ | # 인덱스 T/S 이동(INDEX REBUILD) | ||
+ | # LOB T/S 이동 | ||
+ | }} | ||
+ | === 해당 테이블스페이스를 사용중인 테이블/인덱스/LOB 조회 === | ||
<source lang=sql> | <source lang=sql> | ||
− | + | SELECT * | |
− | + | FROM DBA_SEGMENTS | |
− | + | WHERE TABLESPACE_NAME ='TS_TEST_D01'; | |
− | |||
− | |||
− | |||
− | |||
− | |||
</source> | </source> | ||
− | === | + | |
− | <source lang= | + | === 테이블에서 사용중인 테이블스페이스 신규테이블스페이스로 이동 === |
− | ALTER | + | <source lang=sql> |
+ | 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을 사용하는 테이블 | ||
</source> | </source> | ||
− | == | + | === 인덱스 T/S 이동(INDEX REBUILD) === |
− | <source lang= | + | <source lang=sql> |
− | + | 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을 사용하는 인덱스 |
− | + | </source> | |
− | + | === LOB T/S 이동 === | |
+ | -- LOB 테이블 이동하기 | ||
+ | <source lang=sql> | ||
+ | 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 | ||
+ | ; | ||
</source> | </source> | ||
− | == | + | === 원래 T/S명으로 변경 === |
− | + | <source lang=sql> | |
− | <source lang= | + | ALTER TABLESPACE [asis-TABLE] RENAME TO [tobe-TABLE] |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | -- | ||
− | |||
</source> | </source> | ||
+ | [[Category:oracle]] |
2024년 6월 10일 (월) 11:22 기준 최신판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
- 1 테이블 스페이스 (TABLESPACE)
- 1.1 TABLESPACE 목록 조회
- 1.2 TABLESPACE 종류
- 1.3 현재 유저의 DEFAULT TABLESPACE 확인
- 1.4 TABLESPACE 생성 구문
- 1.5 UNDO TABLESPACE 생성
- 1.6 TABLESPACE 변경
- 1.7 테이블의 TABLESPACE MOVE
- 1.8 운영중인 테이블 TABLESPACE ONLINE MOVE
- 1.9 TABLESPACE 사이즈 변경(RESIZE)
- 1.10 TABLESPACE 이름 변경
- 1.11 인덱스의 TABLESPACE 변경
- 1.12 인덱스의 TABLESPACE 변경 스크립트
- 1.13 인덱스/테이블 TABLESPACE 변경 스크립트
- 1.14 TABLESPACE 자동증가/최대 사이즈 변경
- 1.15 데이터파일 사이즈 변경
- 1.16 데이터파일 추가
- 1.17 데이터/템프파일 삭제
- 1.18 데이터 파일 삭제 후 OS 디스크 사이즈가 줄지 않을경우
- 1.19 TABLESPACE 삭제
- 1.20 TABLESPACE 수정이나 삭제시 ONLINE/OFFLINE 설정
- 1.21 템프 TABLESPACE
- 1.21.1 TEMPORARY TABLESPACE 정보
- 1.21.2 TEMPORARY TABLESPACE 생성
- 1.21.3 TEMPORARY TABLESPACE를 DEFAULT TABLESPACE로 변경
- 1.21.4 TEMPORARY TABLESPACE 사이즈 증가
- 1.21.5 TEMPORARY TABLESPACE 사이즈 추가
- 1.21.6 TEMPORARY TABLESPACE 삭제
- 1.21.7 TEMPORARY TABLESPACE DATA FILE 삭제
- 1.21.8 TEMPORARY TABLESPACE 사용율 조회 쿼리
- 1.21.9 템프테이블 TABLESPACE sort 사용 현황
- 2 TABLESPACE 오브젝트별 정보
- 3 UNDO TABLESPACE
- 4 TABLESPACE 장애 처리
- 5 LOB포함된 TABLESPACE 용량 축소/REORG
1 테이블 스페이스 (TABLESPACE)[편집]
1.1 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;
1.2 TABLESPACE 종류[편집]
1.3 현재 유저의 DEFAULT TABLESPACE 확인[편집]
SELECT * FROM USER_USERS ;-- DEFAUT TABLESPACE로 설정된 부분을 확인
1.3.1 유저의 DEFAULT TABLESPACE 변경[편집]
ALTER USER [유저명] DEFAULT TABLESPACE [테이블 스페이스명]
1.4 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%)
: )
1.4.1 BIGFILE 테이블스페이스[편집]
- 8K 블록 - bigfile 테이블 스페이스에는 최대 32TB
- 32K 블록 - bigfile 파일 테이블 스페이스에는 최대 128TB
1.4.1.1 BIGFILE 테이블스페이스 장점[편집]
1.4.1.2 자동 수행 뷰 목록[편집]
android 장점
- CREATE DATABASE 및 CREATE CONTROLFILE 문의 DB_FILES 초기화 매개 변수와 MAXDATAFILES 매개 변수를 조정하여 데이터 파일 정보에 필요한 SGA 공간의 양과 제어 파일의 크기를 줄일 수 있다.
- Bigfile 테이블 스페이스는 데이터 파일 투명성을 제공하여 데이터베이스 관리를 단순화합니다.
- ALTER TABLESPACE 문의 SQL 구문을 사용하면 기본 개별 데이터 파일이 아닌 테이블 스페이스에서 작업을 수행 할 수 있다.
- 빅파일 테이블 스페이스는 자동 세그먼트 공간 관리가있는 로컬 관리 테이블 스페이스에 대해서만 가능함. (언두,템프,시스템 테이블스페이스는 지원 안함)
1.4.1.3 BIGFILE 테이블스페이스 단점[편집]
android BIGFILE 테이블스페이스 단점
- 병렬 쿼리 실행 및 RMAN 백업 병렬화에 부정적인 영향을 미치므로 스트라이핑을 지원하지 않는 시스템에서 빅 파일 테이블 스페이스를 생성하지 마십시오.
- 큰 파일 크기를 지원하지 않는 OS 플랫폼에서 bigfile 테이블 스페이스를 사용하는 것은 권장되지 않으며 테이블 스페이스 용량을 제한 할 수 있습니다.
- Bigfile 테이블 스페이스는 ASM (Automatic Storage Management) 또는 스트라이핑 또는 RAID를 지원하는 기타 논리 볼륨 관리자 및 동적으로 확장 가능한 논리 볼륨과 함께 사용하기위한 것입니다.
1.5 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만 가능)
1.6 TABLESPACE 변경[편집]
1.6.1 TABLESPACE 읽기/쓰기 모드 변경[편집]
- 읽기 전용 모드
alter tablespace <TABLESPACE명> read only;
- 읽기 쓰기 모드
alter tablespace <TABLESPACE명> read ,write;
1.6.2 OPEN 상태에서 DATAFILE 이동[편집]
alter tablespace TABLESPACE명 offline;
1.6.2.1 offline 된 T/S에 대해 복사/이동[편집]
cp /data1/xxx.dbf /data2/xxx.dbf
or
mv /data1/xxx.dbf /data2/xxx.dbf
1.6.2.2 OFFLINE 상태에서 DATAFILE 이동[편집]
alter tablespace TABLESPACE명 rename datafile '파일경로1/xxx.dbf' to '파일경로2/xxx.dbf';
alter tablespace TABLESPACE명 online;
1.7 테이블의 TABLESPACE MOVE[편집]
ALTER TABLE [테이블명]
MOVE TABLESPACE [테이블 스페이스명]
- -테이블스페이스 이동시 인덱스 리빌드 필요
ALTER INDEX [인덱스명] REBUILD ;
- 인덱스 리빌드 동시 수행 시
ALTER TABLE [테이블명]
MOVE TABLESPACE [테이블 스페이스명]
UPDATE INDEXES;
1.7.1 파티션닝 테이블 TABLESPACE MOVE[편집]
ALTER TABLE [테이블명]
MOVE PARTITION [파티션명] TABLESPACE [테이블 스페이스명]
- 파티셔닝 테이블 인덱스 REBUILD
ALTER INDEX [인덱스명]
REBUILD PARTITION [파티션명]
1.8 운영중인 테이블 TABLESPACE ONLINE MOVE[편집]
ALTER TABLE [테이블명]
MOVE TABLESPACE [테이블 스페이스명]
ONLINE -- online 옵션
;
- 인덱스 리빌드 동시 수행 시
ALTER TABLE [테이블명]
MOVE TABLESPACE [테이블 스페이스명]
UPDATE INDEXES
ONLINE -- online 옵션
;
1.8.1 운영중인 파티션닝 테이블 TABLESPACE ONLINE MOVE[편집]
ALTER TABLE [테이블명]
MOVE PARTITION [파티션명] TABLESPACE [테이블 스페이스명]
ONLINE
assignment 온라인 Move 작업시 장단점
- Move 작업 중 다른 세션의 DML 작업이 가능함
- 파티션의 로컬인덱스는 USABLE 상태로 바로 사용가능함(unusable 되지 않음)
- 도메인 인덱스가 포함된 테이블은 사용불가
- 온라인 Move 작업시 parallel DML , Direct Path Insert 가 안됨(즉,성능은 좋지않다)
1.9 TABLESPACE 사이즈 변경(RESIZE)[편집]
ALTER TABLESPACE ts_txxxx
RESIZE 500G;
1.10 TABLESPACE 이름 변경[편집]
ALTER TABLESPACE [asis-TABLE] RENAME TO [tobe-TABLE]
1.11 인덱스의 TABLESPACE 변경[편집]
ALTER INDEX 인덱스명 REBUILD TABLESPACE [테이블 스페이스명]
[ PARALLEL parallel_num ]
[ LOGGING or NOLOGGING ]
;
1.12 인덱스의 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);
1.13 인덱스/테이블 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.14 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씩 자동증가
1.15 데이터파일 사이즈 변경[편집]
ALTER DATABASE DATAFILE 'C:\경로\TEST1.DBF'
RESIZE 10M;
1.16 데이터파일 추가[편집]
ALTER TABLESPACE [테이블 스페이스명]
ADD DATAFILE 'C:\경로\TEST2.DBF' SIZE 10M;
1.17 데이터/템프파일 삭제[편집]
ALTER TABLESPACE [테이블 스페이스명]
DROP DATAFILE 'data파일';
-- 템프TS DROP TEMPFILE 'data파일';
1.18 데이터 파일 삭제 후 OS 디스크 사이즈가 줄지 않을경우[편집]
attach_file 1) 테이블스페이스 사이즈를 줄인 (shrink) 후 데이터파일 삭제
2) DB 리부팅 하면 공간 확보 됨(DBWR 이 데이터파일을 사용중이기 때문에 공간이 줄지 않음)
1.19 TABLESPACE 삭제[편집]
1.19.1 TABLESPACE에 포함된 모든 세그먼트 삭제[편집]
DROP TABLESPACE [테이블 스페이스명]
INCLUDING CONTENTS;-- TABLESPACE의 모든 세그먼트를 삭제한다.
-- 단, 데이타가 있는 TABLESPACE는 삭제할수 없다.
1.19.2 TABLESPACE에 포함된 테이블의 참조/제약 조건 삭제[편집]
DROP TABLESPACE [테이블 스페이스명]
CASCADE CONSTRAINTS; -- 삭제된 TABLESPACE 내의 테이블의 기본키와 유일키를 참조하는
-- 다른 TABLESPACE의 테이블로부터 참조무결성 제약 조건을 삭제한다.
1.19.3 TABLESPACE의 데이터파일 삭제[편집]
DROP TABLESPACE [테이블 스페이스명]
INCLUDING CONTENTS AND DATAFILES; -- 물리적파일까지 삭제한다.
1.20 TABLESPACE 수정이나 삭제시 ONLINE/OFFLINE 설정[편집]
ALTER TABLESPACE [테이블 스페이스명] ONLINE
ALTER TABLESPACE [테이블 스페이스명] OFFLINE
1.21 템프 TABLESPACE[편집]
1.21.1 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
1.21.1.1 템프테이블 사이즈 조정[편집]
ALTER DATABASE TEMPFILE 'D:\APP\ORADATA\ORCL\TEMP01.DBF'
RESIZE 304M;
1.21.2 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 이 불가능하다.
1.21.3 TEMPORARY TABLESPACE를 DEFAULT TABLESPACE로 변경[편집]
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE [테이블 스페이스명];
1.21.4 TEMPORARY TABLESPACE 사이즈 증가[편집]
ALTER TABLESPACE TEMP
AUTOEXTEND ON
NEXT 100M
MAXSIZE 500G;
1.21.5 TEMPORARY TABLESPACE 사이즈 추가[편집]
ALTER TABLESPACE TEMP
ADD TEMPFILE '+DATA' SIZE 10G
AUTOEXTEND ON
NEXT 100M
MAXSIZE 32767M;
1.21.6 TEMPORARY TABLESPACE 삭제[편집]
DROP TABLESPACE TEMP2;
1.21.7 TEMPORARY TABLESPACE DATA FILE 삭제[편집]
ALTER TABLESPACE TEMP DROP TEMPFILE '+DATA/temp.368.1013282149';
- +DATA/temp.368.1013282149 은 ASM 사용시
1.21.8 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
1.21.9 템프테이블 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
2 TABLESPACE 오브젝트별 정보[편집]
2.1 TABLESPACE/파일 확인(딕셔너리)[편집]
SELECT * FROM DBA_DATA_FILES ;
SELECT * FROM DBA_TABLESPACES ;
SELECT * FROM DBA_SEGMENTS;
2.2 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;
2.3 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;
2.4 TABLESPACE 사이즈 정보[편집]
2.4.1 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
;
2.4.2 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
2.4.3 테이블 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
2.4.4 인덱스 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;
2.4.5 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;
2.4.6 데이터파일 별 테이블스페이스 사이즈 조회[편집]
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;
2.4.7 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;
2.4.8 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;
2.4.9 공간의 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;
2.5 TABLESPACE에 포함된 테이블 명 보기[편집]
SELECT TABLESPACE_NAME
, TABLE_NAME
FROM DBA_TABLES
WHERE TABLESPACE_NAME = UPPER('&TABLESPACE명')
ORDER BY TABLESPACE_NAME
, TABLE_NAME;
2.6 오브젝트별 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');
2.7 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;
2.8 파일위치별 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;
2.9 현재 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;
2.10 테이블의 익스텐트 정보 조회[편집]
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);
3 UNDO TABLESPACE[편집]
- UNDO Segment를 저장하고 있는 TABLESPACE , 관리자가 생성/관리 가능
- Instance당 여러 개가 동시에 존재할 수 있지만 사용은 한번에 1개만
3.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
3.2 UNDO 테이블 스페이스 삭제[편집]
DROP TABLESPACE UNDO_T1 INCLUDING CONTENTS AND DATAFILES;
3.3 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;
4 TABLESPACE 장애 처리[편집]
4.1 TABLESPACE 용량 부족으로 에러 발생시[편집]
- 방법 1) Data file을 크게 늘려줌(자동 증가/수동증가)
- 방법 2) Data file을 하나 더 추가
4.1.1 Tablespace Offline[편집]
- 사용자가 더 이상 해당 Tablespace에 접근하지 못한다는 의미
- 데이터파일의 위치를 이동하거나 특정 Tablespace가 장애가 나서 복구해야 할 때 사용
4.1.2 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해야 할 경우 사용
4.1.3 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
5 LOB포함된 TABLESPACE 용량 축소/REORG[편집]
5.1 해당 테이블스페이스를 사용중인 테이블/인덱스/LOB 조회[편집]
SELECT *
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME ='TS_TEST_D01';
5.2 테이블에서 사용중인 테이블스페이스 신규테이블스페이스로 이동[편집]
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을 사용하는 테이블
5.3 인덱스 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을 사용하는 인덱스
5.4 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
;
5.5 원래 T/S명으로 변경[편집]
ALTER TABLESPACE [asis-TABLE] RENAME TO [tobe-TABLE]