OCP PL/SQL

Transcription

1 5 장 managing Database Storage Structures

2 오라클스토리지구조 오라클 데이터베이스 테이블스페이스 세그먼트 익스텐트 테이블스페이스 세그먼트 익스텐트 세그먼트 익스텐트 테이블스페이스 익스텐트 세그먼트 익스텐트 오브젝트 세그먼트테이블인덱스파티션파티션인덱스 뷰시퀀스동의어 시스템 데이터블록 운영체제블록 디스크 데이터파일 SQL> SELECT FILE_NAME, BYTES/1024/1024 MB FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'USERS'; FILE_NAME MB /data1/users_01.dbf 10 /data2/users_02.dbf 20

3 데이터블록의개요 SGA 공유풀 데이터버퍼캐쉬리두로그버퍼 SELECT * FROM 사원 WHERE 사원 ID= 이창구 112 최수련 113 장우형 오라클의 I/O 단위 DB_BLOCK_SIZE 파라메터로크기설정 하나이상의운영체제블록으로구성

4 데이터블록의구조 데이터블록헤더 데이터헤더 캐시계층트랙젝션계층테이블딕렉토리행디렉토리 사용가능한공간 데이터계층 데이터

5 데이터블록크기에따른장 / 단점 9. 오라클스토리지 데이터블록크기를크게설정 데이터블록크기를작게설정 장점 단점 블록사용율이높음 블록사용율이낮음 한번의 I/O 로많은데이터추출 한번의 I/O 로적은데이터추출 단점 블록에대한경합가능성높음 장점 블록에대한경합가능성낮음

6 테이블스페이스와데이터파일 데이터베이스, 테이블스페이스, 데이터파일은서로연관되어있지만중요한차이점이있다. 오라클데이터베이스는테이블스페이스라고부르는한개이상의논리적저장단위로구성되어있으며, 테이블스페이스에는모든데이터베이스의데이터가저장된다. 오라클데이터베이스의각테이블스페이스는데이터파일이라고부르는한개이상의파일들로구성되며, 오라클이설치된운영체제에위치한물리적저장구조이다. 데이터베이스의데이터는데이터파일에저장되며, 데이터파일은각테이블스페이스에포함된다. 예를들어, 가장간단한오라클데이터베이스는하나의테이블스페이스와하나의데이터파일로구성된다. 또한, 각각두개의데이터파일이포함된세개의테이블스페이스의경우, 데이터베이스는전체 6 개의데이터파일을포함하게된다. 참고로, 하나의데이터베이스는최대 65,535 개의데이터파일을포함할수있다. 1-6

7 1-7 테이블스페이스의저장공간관리 테이블스페이스는익스텐트단위로저장공간을할당 지역관리테이블스페이스 : 익스텐트가해당테이블스페이스의비트맵으로관리되어지며, 비트맵의각비트는단일블록또는블록그룹의상태를표시하게된다. 익스텐트가할당되거나재활용을위해해제되면, 오라클서버는비트맵을변경하여블록의상태를변경한다. 딕셔너리관리테이블스페이스 : 익스텐트가데이터딕셔너리에서관리되어지며, 익스텐트가할당되거나해제되면데이터딕셔너리의적당한테이블을변경한다. 이방식은이전버전의오라클과의역호환성을위해지원되며, 가능하면모든테이블스페이스를지역관리테이블스페이스로사용하는편이유리한다. 사용된저장공간과비어있는저장공간을추적하는방식차이

8 1-8 Tablespace 생성 1. 데이터베이스홈페이지의 [ 관리 ] 탭에서 [ 테이블스페이스 ] 링크를클릭한다.

9 1-9 Tablespace 생성 2. [ 테이블스페이스 ] 페이지에서 [ 생성 ] 버튼을클릭한다. 참고로, 기존테이블스페이스와유사한테이블스페이스를생성하고자하는경우, 기존테이블스페이스를선택하고,[ 작업 ] 에서 [ 다음과같이생성 ] 을선택한다음 [ 실행 ] 버튼을클릭한다.

10 1-10 Tablespace 생성 3. 테이블스페이스의이름을입력한다. 4. 페이지하단의 [ 확장영역관리 ] 영역에서 [ 로컬에서관리됨 ] 을선택한다. 지역관리테이블스페이스의익스텐트는오라클데이터베이스서버에의해해당테이블스페이스내에서효율적으로관리된다. 반면, 딕셔너리관리테이블스페이스의경우, 익스텐트를할당및해제하면해당정보를추적하기위해데이터딕셔너리의정보가검색된다. 딕셔너리관리테이블스페이스는앞으로사용되지않을예정이며오라클에서도딕셔너리, 관리테이블스페이스의사용을권장하지않는다. 5. [ 유형 ] 영역에서 [ 영구 ] 를선택한다. 영구테이블스페이스는시스템또는사용자에의해생성되는영구적인데이터베이스객체를저장한다.

11 1-11 Tablespace 생성 6. [ 상태 ] 영역에서 [ 읽기 / 쓰기 ] 를선택한다. 읽기 / 쓰기상태는사용자가해당테이블스페이스에읽기및쓰기작업을수행할수있다는것을의미한다.

12 1-12 Tablespace 생성 7. 페이지하단의 [ 데이터파일 ] 영역에서 [ 추가 ] 버튼을클릭하여테이블스페이스에데이터파일을추가한다. 테이블스페이스는최소한한개이상의데이터파일을포함해야한다. 스트라이핑또는 RAID 를지원하는자동저장영역관리 (ASM) 또는논리적볼륨관리자와동적으로확장가능한볼륨을사용하는대용량데이터베이스의경우, bigfile 테이블스페이스를사용할수있다.

13 1-13 Tablespace 생성 8. [ 데이터파일추가 ] 페이지에서파일디렉터리와파일크기는디폴트값을사용한다. 9. [ 저장영역 ] 영역에서 [ 데이터파일이가득차면자동으로확장됨 ( 자동확장 )] 을선택하고, [ 증분 ] 에는데이터파일이가득찼을때, 확장될크기를지정한다. [ 최대파일크기 ] 는 [ 무제한 ] 으로둔상태에서 [ 계속 ] 버튼을클릭한다.

14 1-14 Tablespace 생성 10. [ 저장영역 ] 탭을클릭하면 [ 저장영역 ] 페이지가표시된다. 11. [ 저장영역 ] 페이지에서디폴트값을사용하고, [ 확인 ] 버튼을클릭한다.

15 1-15 Tablespace 생성 12. [ 테이블스페이스 ] 페이지에서생성된테이블스페이스를선택하고, [ 편집 ] 을클릭한다.

16 Tablespace 생성 13. [ 임계값탭 ] 을클릭한다테이블스페이스의공간사용량이임계값에도달했을때, 적절한조치작업과권고사항을관리자에게전달하게된다. 14. 임계값을변경하였으면 [ 적용 ] 버튼을클릭한다. 1-16

17 지역관리테이블스페이스의저장영역속성 지역관리테이블스페이스내의익스텐트는할당방법 자동 : 자동할당이라고부르며테이블스페이스내의익스텐트크기를시스템이직접관리한다. 익스텐트의크기를지정할수없으며, 임시테이블스페이스의경우, 자동할당을설정할수는없다. 균일 : 테이블스페이스의모든익스텐트가지정된크기로일정하게할당된다. 디폴트 1M 이며, 임시테이블스페이스의모든익스텐트크기는동일하다. 언두테이블스페이스의경우, 익스텐트를균일하게설정할수는없다. 1-17

18 지역관리테이블스페이스의저장영역속성 지역관리테이블스페이스내의세그먼트공간관리지정값 자동 : 오라클은비트맵을이용하여세그먼트내의비어있는공간을관리한다. 비트맵은세그먼트내에행이입력될수있는블록을비어있는공간의양에따라각데이터블록의상태를표시한다. 데이터블록에비어있는공간이발생하면, 해당블록의새로운상태가비트맵에변경된다. 비트맵을사용하면오라클이비어있는공간을자동으로관리할수있기때문에이러한형식의공간관리방식을자동세그먼트공간관리라고부른다. 수동 : 세그먼트내의비어있는공간을관리하기위해프리리스트를사용하며프리리스트는행이입력될수있는공간을가지고있는데이터블록의리스트를의미한다. 이러한형식의공간관리방식을수동세그먼트공간관리라고부르며, 테이블스페이스내의스키마객체가생성될때, PCTUSED, FREELISTS, FREELISTS GROUPS 파라메터를설정해주어야한다. 이방식은과거버전과의역호환성을위해지원된다. 1-18

19 지역관리테이블스페이스의장점 1. 익스텐트의지역관리는재귀적공간관리작업을발생시키지않는다. 딕셔너리관리테이블스페이스의경우, 익스텐트의할당및해제작업이발생하면언두세그먼트또는데이터딕셔너리테이블에도익스텐트의할당및해제작업이발생하기때문이다. 2. 지역관리테이블스페이스는데이터딕셔너리테이블에비어있는공간에대한정보를기록하지않기때문에데이터딕셔너리테이블과경합이발생하지않는다. 3. 지역관리테이블스페이스는인접한빈공간을자동으로추적하기때문에비어있는익스텐트를하나로모아줄필요가없다. 4. 지역관리테이블스페이스에의해서관리되는익스텐트의크기는시스템에의해자동으로결정된다. 5. 지역관리테이블스페이스에서익스텐트의할당및해제는데이터딕셔너리의테이블을업데이트하지않으므로, 언두정보를발생시키지않는다. 단, 테이블스페이스할당량정보는특수한경우로서데이터딕셔너리정보를업데이트한다. 사실 9i 부터모든테이블스페이스는 local managed 가맞다. 1-19

20 지역관리테이블스페이스의저장영역속성 로깅 테이블스페이스내의객체에변경이발생하면, 변경사항은리두스트림이라고부르는곳에기록된다. 리두스트림은메모리에서기동되며, 온라인리두로그파일에기록되고아카이브로그파일에기록될수도있다. 이프로세스를종료시킬수도있으며, 로깅속성을해제하면장애가발생했을때, 해당테이블스페이스내의객체들을복구할수없게된다. 임계값 테이블스페이스 [ 임계값 ] 탭을클릭하면현재테이블스페이스에대한임계값을변경할수있다. 데이터베이스의디폴트임계값을사용하거나테이블스페이스마다경고임계값과한계임계값을각각설정할수도있다. 또한, 임계값을비활성화할수도있다. 1-20

21 1-21 내장 (Built-in) 테이블스페이스 SYSTEM : SYSTEM 테이블스페이스는데이터베이스관리를위해오라클데이터베이스서버에의해서사용된다. 여기에는데이터딕셔너리와데이터베이스에대한관리정보가포함되어있는테이블이저장되어있다. 데이터딕셔너리와관리테이블은 SYS 스키마에저장되며 SYS 사용자와적절한권한을가진관리자만접근할수있다. SYSAUX : SYSTEM 테이블스페이스의보조테이블스페이스이며, 과거오라클버전의특정컴포넌트와제품은자신의고유데이터를저장하기위해 SYSTEM 테이블스페이스를사용하거나별도의테이블스페이스를생성하여사용했지만, 이제는 SYSAUX 테이블스페이스를공통으로사용한다. Oracle Database 10g 이상은반드시 SYSAUX 테이블스페이스를사용해야한다.

22 1-22 내장 (Built-in) 테이블스페이스 TEMP : (order by, group by, rollup, cube ) SQL 문장을처리할때, 발생하는임시테이블과인덱스를저장하기위해사용하는테이블스페이스이며, 대표적으로정렬작업이있다. 모든데이터베이스는임시테이블스페이스를포함하고있으며, 각사용자에게사용할임시테이블스페이스를할당하게된다. 미리구성된데이터베이스의경우, 디폴트임시테이블스페이스로 TEMP 테이블스페이스가지정되어있으며오라클은임시테이블스페이스가할당되지않은, 사용자들에게디폴트임시테이블스페이스를지정한다. UNDOTBS1 : ( rollback 관련 ) 데이터베이스서버가언두정보를저장하기위해사용하는언두테이블스페이스이다. 모든데이터베이스는언두테이블스페이스를반드시생성해야한다.

23 1-23 내장 (Built-in) 테이블스페이스 USERS : 영구사용자객체와데이터를저장하기위해사용하는테이블스페이스로서,USERS 테이블스페이스는일반사용자에의해생성되는객체들의디폴트테이블스페이스이다. SYS 와 SYSTEM 사용자의디폴트영구테이블스페이스는 SYSTEM 테이블스페이스이다. EXAMPLE : (scott. Hr ) 데이터베이스생성시, 샘플스키마가저장되는테이블스페이스로서, 샘플스키마는여러운영체제에서공통적으로사용할수있는예제를포함하고있다. 또한, 샘플스키마는오라클문서와교육과정에포함된모든예제의기반이된다.

24 이름 테이블스페이스의변경 테이블스페이스에새로운이름을입력하고 [ 적용 ] 버튼을클릭한다. 상태 테이블스페이스는다음의 3 가지종류중하나의상태를갖는다. 테이블스페이스의종류에따라지정할수없는상태도있다. 1. 읽기 / 쓰기 2. 읽기전용 3. 오프라인 1-24

25 테이블스페이스의변경 읽기 / 쓰기 : 테이블스페이스가온라인상태이고읽기및쓰기가가능하다. 읽기전용 : 테이블스페이스를읽기전용상태로설정하면과도 (transition) 읽기모드상태로설정된다. 이상태에서기존트랜잭션을커밋하거나롤백할수있지만, 더이상의 DML 작업은허용되지않는다. 테이블스페이스가온라인상태이어야만읽기전용상태로설정할수있으며, SYSTEM 또는 SYSAUX 테이블스페이스는읽기전용상태로설정할수없다. 오프라인 : 온라인테이블스페이스를오프라인으로설정하면, 임시적으로사용할수없는상태가된다. 단, 데이터베이스의나머지테이블스페이스는오픈되어있으므로데이터의접근이가능하다. 4 가지옵션으로변경이가능하다. 1-25

26 Tablespce off line 변경유형 - 일반 (Normal) : 테이블스페이스내의어떤데이터파일에도오류가존재하지않으면해당테이블스페이스를 OFFLINE NORMAL 상태로변경가능하다. 오라클은테이블스페이스내의모든데이터파일에대하여체크포인트를발생시킨다. 임시 (Temporary) 테이블스페이스내의데이터파일에오류가있더라도해당테이블스페이스를 OFFLINE TEMPORARY 상태로변경할수있다. 데이터파일을오프라인상태로변경하여, 해당파일들에체크포인트를발생시킨다. 만약, 어떤데이터파일도오프라인되어있지않은상태에서 TEMPORARY 구문을사용하게되면, 다음번에테이블스페이스를온라인상태로변경했을때, 미디어복구는불필요하게된다. 그러나, 테이블스페이스내의한개이상의데이터파일이쓰기오류로인하여오프라인상태로되어있고해당테이블스페이스를 OFFLINETEMPORARY 상태로변경한경우, 테이블스페이스를다시온라인상태로변경했을때, 미디어복구가필요하게된다. 즉시 (Immediate) : 테이블스페이스를 OFFLINE IMMEDIATE 상태로변경한경우, 오라클은어떠한데이터파일에도체크포인트를발생시키지않는다. 테이블스페이스를 OFFLINE IMMEDIATE 상태로변경하면해당테이블스페이스를다시온라인상태로변경한후에미디어복구가필요하게된다. 만약, 데이터베이스가 NOARCHIVELOG 모드로설정되어있다면테이블스페이스를 OFFLINEIMMEDIATE 상태로변경하는것은불가능하다. - 복구용 (For recovery) : FOR RECOVER 설정은사용되지않을예정이며, 과거버전과의호환성을위해아직남아있다. 1-26

27 1-27 테이블스페이스상태변경

28 크기변경 테이블스페이스상태변경 테이블스페이스에데이터파일을추가하거나, 기존데이터파일의크기를변경하여테이블스페이스의용량을증가시킬수있다. 테이블스페이스에데이터파일을추가하려면 [ 데이터파일 ] 영역에서 [ 추가 ] 버튼을클릭하고데이터파일추가페이지에서데이터파일에대한정보를입력하면된다. 테이블스페이스의이름을변경불가 1-28

29 1-29 테이블스페이스상태변경 기존데이터파일의크기를변경하려면 [ 데이터파일 ] 영역에서확장하고자하는데이터파일을선택한다음, [ 편집 ] 버튼을클릭한다. [ 테이블스페이스편집 ] 페이지에서 [ 파일크기 ] 에변경될파일의크기를입력한다. 테이블스페이스의크기는확장되거나축소될수있지만기존에사용하던공간이하로축소시키는경우에는 ORA 에러가발생하게된다.

30 저장영역변경 테이블스페이스상태변경 [ 저장영역 ] 탭을클릭하여 [ 테이블스페이스편집 ] 페이지를오픈하면, [ 로깅활성화 ] 영역에서해당테이블스페이스의로깅여부를변경할수있다. 1-30

31 임계값 테이블스페이스상태변경 1. 데이터베이스기본임계값사용 : 사전에지정된디폴트값을사용하지만, 디폴트값을변경할수도있다. 2. 임계값지정 : 해당테이블스페이스의임계값을지정한다. 3. 임계값비활성화 : 해당테이블스페이스의사용량경보를비활성화한다. 1-31

32 테이블스페이스관련작업 1. 데이터파일추가 : 테이블스페이스에데이터파일을추가한다. 2. 다음과같이생성 : 해당테이블스페이스를템플리트로이용하여테이블스페이스를생성 3. DDL 생성 : 테이블스페이스를생성하는 DDL 문장을생성한다. 생성된문장을복사해서텍스트파일이붙여넣으면스크립트파일이나문서를작성할수있다. 4. 읽기전용으로설정 : 테이블스페이스의모든쓰기작업을중단시킨다. 단, 현재진행중인트랜잭션은완료시킬수있지만, 새로운 DML 작업은진행할수없다. 5. 쓰기가능으로설정 : DML 작업또는테이블스페이스에객체를생성할수있다. 6. 온라인으로설정 : 테이블스페이스가오프라인상태이면온라인상태로변경한다. 7. 재구성 : 재구성마법사를시작하여, 테이블스페이스내의객체를아직사용되지않은공 8. 간에다시생성한다. 9. 세그먼트권고자실행 : 세그먼트권고자는객체의단편화정도에따라재구성이필요한객체를결정하는데도움을준다. 10. 종속성표시 : 해당테이블스페이스에연관된모든객체를표시한다. 11. 테이블스페이스내용표시 : 해당테이블스페이스내의모든세그먼트를표시한다. 12. 오프라인으로설정 : 테이블스페이스가현재온라인상태이면오프라인상태로변경하며, 삭제는불가능하다. 1-32

33 1-33 테이블스페이스관련작업

34 1-34 테이블스페이스삭제 테이블스페이스가더이상필요하지않은경우에는해당테이블스페이스를삭제할수있으며, 해당테이블스페이스내에포함된모든세그먼트도같이삭제된다. 테이블스페이스를삭제하려면 DROP TABLESPACE 시스템권한을부여받아야한다. 테이블스페이스를삭제하면관련데이터베이스의컨트롤파일내의파일포인터도제거되며, 삭제된테이블스페이스의운영체제상의파일도같이삭제되도록지정할수있다. 만약, 테이블스페이스를삭제할때, 해당운영체제상의파일이같이삭제되지않도록설정할수도있으며, 이런경우에는나중에별도의운영체제명령으로데이터파일을삭제해야한다. 테이블스페이스의테이블이현재사용중이거나트랜잭션을롤백하는데필요한언두데이터가저장되어있는테이블스페이스는삭제가불가능하다. 테이블스페이스는온라인또는오프라인상태로설정이가능하므로삭제하기전에먼저해당테이블스페이스를오프라인상태로변경하여야함.

35 1-35 테이블스페이스삭제 1. 테이블스페이스페이지를열고삭제하고자할테이블스페이스를선택한다. 2. [ 예 ] 를클릭한다.

36 1-36 테이블스페이스정보확인

37 테이블스페이스정보확인 테이블스페이스정보 DBA_TABLESPACES V$TABLESPACE 데이터파일정보 DBA_DATA_FILES V$DATAFILE 임시파일정보 DBA_TEMP_FILES V$TEMPFILE 1-37

38 연습문제 다음과같은정보를기반으로새로운테이블스페이스를생성한다. 테이블스페이스이름 : jino 확장영역관리 : 로컬에서관리됨 유형 : 영구 상태 : 읽기 / 쓰기 파일이름 : inventory01. 율 파일디렉터리 : 디폴트 파일크기 : 50MB 자동확장 세그먼트공간관리 : 자동 로깅활성화 디폴트임계값설정 1-38