[오라클]저장구조, tablespace, chaining, migration

[STORAGE RELATIONSHIP STRUCTURE]

-DATABASE, TABLESPACES, DATAFILES : 사람 눈으로 보이는 영역

-RBS : ROLLBACK SEGMENT 의 약자. 현재 UNDO로 더 많이 씀

DATABASE : 테이블스페이스가 저장되어 공유되는 논리적 집합체

TABLESPACES :  오라클에서 물리적으로 존재하지 않는 논리적인 공간. SYSTEM, USERS, RBS, TEMP

DATAFILES : DISK에 저장되는 물리적 DBF 파일 (SYSTEM01.DBF, USERS.DBF, TEMP.DBF)

SEGMENTS : 오라클에서 물리적으로 존재하지 않는 논리적인 공간. EXTENT가 모여서 SEGMENT를 구성

EXTENTS : 블록이 모여서 EXTENT 를 구성

BLOCKS : 가장 기본적인 저장소는 BLOCK

 

-오라클DEFAULT > TABLESPACE > SEGMENT > EXTENT > BLOCK

-DBA는 블록단위를 계산해서 데이터가 얼마나 증가할지 계산 가능

-DB_BLOCK_SIZE : 입출력하는 최소한의 단위. 데이터베이스 블록의 크기. 파라미터 파일에서 설정가능

-Multiple Block Size Support : 블록 사이즈는 2kb ~ 32kb 사이를 지원

-Standard Block Size : SYSTEM, TEMPORARY 테이블스페이스에서 사용. DB_CACHE_SIZE는 버퍼 캐시의 표준 블록사이즈를 설정.

 

-Block Space Usage

pctfree 는 수정할 경우 늘어나는 데이터를 수용하기 위한 빈공간이다.

(하나의 블록에 pctfree = 20으로 설정해둔다면 해당 블록은 메모리 80% 까지만 저장한다 )

pctused는 재사용되기 위해 필요한 블럭의 사용량을 설정한다.

(pctused . = 40 : 사용영역이 40%보다 작아야 행을 삽입할 수 있다.)

 

 

---

 

[블록 사이즈 확인 (8KB인 걸 볼 수 있음)]

- CMD 이용

 

- 파라미터파일 조회

 

[SGA 메모리 조회]

 

[테이블스페이스 생성시 블록단위를 8KB(DEFAULT)가 아닌 다른 크기로 설정]

① 캐시 사이즈 변경

 

② 테이블스페이스 생성 시 블록단위를 4KB로 설정(원하는 단위로 바꿀 수 있음)

 

③ 테이블스페이스의 블록 사이즈 조회시 설정된 것을 볼 수 있다.

 

 

---

 

[TEMPORARY TABLESPACE DEFAULT 조회]

COL DESCRIPTION FORMAT A36           --컬럼길이제한

COL PROPERTY_NAME FORMAT A25     --컬럼길이제한

COL PROPERTY_VALUE FORMAT A15     --컬럼길이제한

SELECT * FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE '%TEMP%';

 

[TEMPORARY TABLESPACE 생성]

[TEMPORARY TABLESPACE DEFAULT 변경]

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TMP;

 

 

 

---

 

[UNDO TABLESPACE]

데이터버퍼캐시	로그버퍼캐시	UNDO TABLESPACE
DBWR	LGWR
DBF파일	LOG파일

[UNDO 설정 조회]

SHOW PARAMETER UNDO;

 

관리는 자동. UNDO TBS에 남아있는 값을 900초마다 지우겠다. (DB에서 15분은 굉장히 긴 시간. 로그파일은 3초마다 작성되던 것 기억)

 

[UNDO TABLESPACE 생성]

CREATE UNDO TABLESPACE UNDO

DATAFILE 'C:\APP\ITWILL\ORADATA\TESTDB\UNDO01.DBF' SIZE 10M;

 

 

[UNDO TABLESPACE 조회]

SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,STATUS

FROM DBA_ROLLBACK_SEGS;

 

[UNDO TABLESPACE 삭제]

① 파라미터 파일 수정

 

② UNDO는 DB가 OPEN되어 있으면 삭제 불가능

SHUTDOWN IMMEDIATE

 

③ 삭제 후 재시작

STARTUP

 

④ UNDO 로 DEFAULT가 설정되었으므로 삭제 불가능

DROP TABLESPACE UNDO;

 

⑤ UNDOTBS1 삭제 진행

DROP TABLESPACE UNDOTBS1;

 

⑥ UNDO 테이블스페이스 상태 조회

SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,STATUS  FROM DBA_ROLLBACK_SEGS;

 

- CMD 화면

 

 

---

 

[MIGRATION] 

1. ROWID FORMAT : 데이터가 저장되어있는 위치정보

- DATA OBJECT NUMBER(6) - RELATIVE FILE NUMBER(3)- BLOCK NUMBER(6) - ROW NUMBER(3)

- RELATIVE FILE NUMBER(3) : 동일할 경우 같은 데이터 파일에 저장이 되어있다

- BLOCK NUMBER(6) :  몇번블록을 사용하는지

- ROW NUMBER(3) : 몇번째 행의 번호

1-1. ROW의 구조(데이터베이스 블록안에 존재)

 

1-2. 컬럼을 지울 때, (컬럼명 뿐만 아니라 컬럼에 있는 데이터도 같이 지워야 함)

ALTER TABLE  HR.EMPLOYEES

DROP COLUMN COMMENTS

CASCADE CONSTRAINTS CHECKPOINT 1000;

2. ROW CHAINING & ROW MIGRATION

ROW가 UPDATE 될 경우,  비효율을 막기 위해서 포인터를 저장. 포인터에 있는 주소를 읽어 다른 BLOCK으로 옮겨가지 않고 해당 BLOCK에 저장될 수 있도록 지원

 

- CHAINING : VARCHAR로 데이터를 저장할 경우 발생. 관리자의 재정비 작업 필요.

ROW에 정상적으로 데이터가 입력된 후, 삭제 or 변경작업 ▶ 해당 블록에는 삭제된 행크기 만큼의 빈공간 발생.새로운 데이터가 입력되면 빈 공간이 존재하는 블록에 데이터가 입력되고, 공간 부족시 나머지 데이터가 또 다른 새로운 블록에 입력된다.

 

- MIGRATION

ROW에 사용자가 입력한 데이터들이 존재하여 저장 공간이 없을 때, 기존 데이터의 변경작업이 일어나면 변경에 의해 저장할 수 있는 공간이 없게 된다.

이 때, 서버에서는 변경할 수 없었던 행들을 모아 새로운 블록으로 이동시켜 변경작업을 수행한다.

(기존블록에는 나머지 데이터가 담긴 블록의 주소를 포인터로 기억시킴)

 

- CHAINING 현상 확인하고 MIGRATION 진행

① 테이블 생성시 원하는 테이블스페이스로 지정이 가능

CREATE TABLE CHAIN_TEST

(NAME VARCHAR2(100))

TABLESPACE USERS;

 

 

② 테이블에 데이터 삽입 (1024개)

 

③ CHAIN_TEST 테이블을 ANALYZE해서 CHAINED_ROWS 테이블로 작성

해당 경로에 있는 SQL문 활용

C:\app\itwill\product\11.2.0\dbhome_1\RDBMS\ADMIN\UTLCHAIN.SQL

 

CHAINED_ROWS 테이블 생성

create table CHAINED_ROWS (

  owner_name         varchar2(30),

  table_name         varchar2(30),

  cluster_name       varchar2(30),

  partition_name     varchar2(30),

  subpartition_name  varchar2(30),

  head_rowid         rowid,

  analyze_timestamp  date

);

 

 

-데이터 업데이트 진행 (이때 CHAINING현상 발생)

UPDATE CHAIN_TEST SET NAME='BBBBBBBBBBBBBBBBBBBBBBBBBBBBBB';

 

-데이터 분석

ANALYZE TABLE CHAIN_TEST LIST CHAINED ROWS;

 

-CHAINING된 데이터 조회

SELECT * FROM CHAINED_ROWS;

 

 

④ CHAIN_TEST 테이블, CHAINED_ROW 테이블의 ROWID가 동일한 테이터를 별도 테이블에 저장 (백업 개념)

-백업할 테이블 생성

CREATE TABLE CHAIN_TEMP

AS

SELECT * FROM CHAIN_TEST

WHERE ROWID IN

(SELECT HEAD_ROWID FROM CHAINED_ROWS WHERE TABLE_NAME ='CHAIN_TEST');

 

- 체이닝된 데이터 백업되었는지 확인

SELECT COUNT(*) FROM CHAIN_TEMP;

 

 

⑤ CHAIN_TEST 테이블에 ROWID 가 같은 데이터를 삭제

-체인된 데이터 삭제

DELETE CHAIN_TEST

WHERE ROWID IN

(SELECT HEAD_ROWID FROM CHAINED_ROWS WHERE TABLE_NAME ='CHAIN_TEST');

 

-체인되지 않은 데이터만 존재

SELECT COUNT(*) FROM CHAIN_TEST;

 

-백업해둔 데이터 삽입

INSERT INTO CHAIN_TEST (SELECT * FROM CHAIN_TEMP);

 

-기존 ANALYZE 분석내용 삭제(DELETE와 같음.TRUNCATE 커밋안해도됨)

TRUNCATE TABLE CHAINED_ROWS;

 

 

⑥ CHAIN_TEST 테이블을 ANALYZE해서 CHAINED_ROW 테이블로 작성 (다시 진행) - 0개 조회.

-분석진행

ANALYZE TABLE CHAIN_TEST LIST CHAINED ROWS;

 

-CHAINED ROW 조회

SELECT COUNT(*) FROM CHAINED_ROWS;

 

 

---

 

[분산형 데이터베이스]

-PARTITION TABLE (3개 존재) : 범위 분할, 해쉬 분할, 리스트 분할

1. 범위(RANGE PARTITON) 분할

① 테이블 스페이스 생성 (H1999, H2000, H2001)

CREATE TABLESPACE H1999

DATAFILE 'C:\ADISK\DISK1\H1999.DBF' SIZE 1M

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO

 

CREATE TABLESPACE H2000

DATAFILE 'C:\ADISK\DISK1\H2000.DBF' SIZE 1M

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO

 

CREATE TABLESPACE H2001

DATAFILE 'C:\ADISK\DISK1\H2001.DBF' SIZE 1M

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO

 

② 테이블 생성 (T_RANGE)

CREATE TABLE T_RANGE

(IDATE DATE,

NO VARCHAR2(2),

QRY NUMBER)

PARTITION BY RANGE(IDATE)

(PARTITION T1 VALUES LESS THAN (TO_DATE('2000-01-01','YYYY-MM-DD'))

TABLESPACE H1999,

PARTITION T2 VALUES LESS THAN (TO_DATE('2001-01-01','YYYY-MM-DD'))

TABLESPACE H2000,

PARTITION T3 VALUES LESS THAN (MAXVALUE)

TABLESPACE H2001);

 

③ 인덱스에 따라서 파티션을 나눠주는 것이므로 인덱스를 생성해줘야 한다.

 

- 날짜 형식 변경 : ALTER session SET NLS_DATE_FORMAT='YYYY-MM-DD’ ;

 

변경 전

 

변경 후

 

④ 파티션 조회

SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE, TABLESPACE_NAME

FROM DBA_TAB_PARTITIONS

WHERE TABLE_OWNER='SYS' AND TABLE_NAME='T_RANGE';

 

 

⑤ 파티션별 데이터 조회

 

⑥ 업데이트 진행시 변경된 내용으로 소속 파티션도 변경됨

 

2. 해쉬(HASH PARTITON) 분할

① 테이블 스페이스 생성 (T_HASH1, T_HASH2, T_HASH3)

CREATE TABLESPACE T_HASH1

DATAFILE 'C:\ADISK\DISK2\HASH1.DBF' SIZE 1M

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO

 

CREATE TABLESPACE T_HASH2

DATAFILE 'C:\ADISK\DISK2\HASH2.DBF' SIZE 1M

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO

 

CREATE TABLESPACE T_HASH3

DATAFILE 'C:\ADISK\DISK2\HASH3.DBF' SIZE 1M

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO

 

② 테이블 생성 (PANMAE)

CREATE TABLE PANMAE

(PANDAY DATE,

JEPUMID VARCHAR2(10),

SU NUMBER)

PARTITION BY HASH(PANDAY) PARTITIONS 3

STORE IN (T_HASH1, T_HASH2, T_HASH3);

 

③ 파티션 조회

SELECT TABLE_NAME, PARTITION_NAME, TABLESPACE_NAME

FROM DBA_TAB_PARTITIONS

WHERE TABLE_OWNER='SYS' AND TABLE_NAME=’PANMAE’;

 

 

④ SYS에서 붙여준 파티션이름으로 조회. (오라클에서 가장 최적의 형태로 분할시킨 것으로 많이 사용한다.)

 

3. 리스트(LIST PARTITON) 분할

- 리스트를 구분하여 파티션을 생성한다

JJ,KN,KB,JN,JB  -> REGION_SOUTH

CN,CB,KK,SO,KW ->  REGION_MIDDLE

HH, PA, HK -> REGION_NORTH

 

① 테이블 스페이스 생성 (T_LIST1, T_LIST2, T_LIST3)

CREATE TABLESPACE T_LIST1

DATAFILE 'C:\ADISK\DISK3\LIST1.DBF' SIZE 1M

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO

 

CREATE TABLESPACE T_LIST2

DATAFILE 'C:\ADISK\DISK3\LIST2.DBF' SIZE 1M

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO

 

CREATE TABLESPACE T_LIST3

DATAFILE 'C:\ADISK\DISK3\LIST3.DBF' SIZE 1M

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO

 

② 테이블 생성 (T_LOCAL)

CREATE TABLE T_LOCAL

(LOC CHAR(2),

SIDO VARCHAR2(10),

SI VARCHAR2(10))

PARTITION BY LIST(LOC)

(PARTITION REGION_SOUTH VALUES ('JJ','KN','KB','JN','JB')

TABLESPACE T_LIST1,

PARTITION REGION_MIDDLE VALUES ('CN','CB','KK','SO','KW')

TABLESPACE T_LIST2,

PARTITION REGION_NORTH VALUES ('HH','PA','HK')

TABLESPACE T_LIST3)

 

③ 데이터 입력

INSERT INTO T_LOCAL VALUES('JJ','제주','서귀포시');
INSERT INTO T_LOCAL VALUES('KN','경남','마산시');
INSERT INTO T_LOCAL VALUES('KB','경북','포항시');
INSERT INTO T_LOCAL VALUES('JJ','전남','광양시');
INSERT INTO T_LOCAL VALUES('JB','전북','정읍시');
INSERT INTO T_LOCAL VALUES('CN','충남','아산시');
INSERT INTO T_LOCAL VALUES('CB','충북','청주시');
INSERT INTO T_LOCAL VALUES('KK','경기','수원시');
INSERT INTO T_LOCAL VALUES('SO','서울','송파구');
INSERT INTO T_LOCAL VALUES('KW','강원','춘천시');
INSERT INTO T_LOCAL VALUES('HH','황해','해주시');
INSERT INTO T_LOCAL VALUES('PA','평안','평양시');
INSERT INTO T_LOCAL VALUES('HK','함경','함흥시');
INSERT INTO T_LOCAL VALUES('IC','인천','부평구');

 

④ 파티션 조회.

SELECT * FROM T_LOCAL PARTITION;

SELECT * FROM T_LOCAL PARTITION(REGION_SOUTH);

SELECT * FROM T_LOCAL PARTITION(REGION_MIDDLE);

SELECT * FROM T_LOCAL PARTITION(REGION_NORTH);

 

[삭제]

-삭제는 테이블을 먼저 지우고 테이블스페이스를 지워야 한다