ch7. tablespace와 data file 관리하기. ★★★

[녹음9]

ch7. tablespace와 data file 관리하기.★★★

 

 

1. 개요

 

 

 

* oracle : 대용량의 데이터를 저장하고 관리할 때 사용하는 프로그램.

* 사용자가 select하면 server process가 tablespace에서 바로 찾아 조회를 하는데, 만약 tablespace에 없다면 데이터가 저장되어 있는 data file에 가서 해당 데이터가 들어있는 블록을 찾아서 tablespace로 가져온 후 사용자가 원하는 데이터를 꺼내서 전달해준다.

→ 즉, 사용자가 찾는 데이터는 반드시 메모리에 있는 tablespace에 존재해야 한다. (단, Direct Path와 같은 예외도 있다.)

* 하나의 Tablespace는 여러개의 DataFile을 가질 수 있다. (하나의 매장이 여러개의 창고를 가질 수 있는 것 처럼)

 

 

2. Tablespace의 종류 및 특징

 

(1) SYSTEM tablespace : 데이터 딕셔너리들이 저장되어 있다. (운영/튜닝 정보들) → system tablespace가 손상될 경우 oracle 서버가 시작되지 않으므로 손상되지 않도록 아주 조심해야 한다.

 * 데이터 딕셔너리 : 오라클 서버의 모든 정보를 저장하고 있는 아주 중요한 테이블이나 뷰. (oracle에서 사용하는 거의 모든 메타 정보나 운영 관련된 중요한 정보들) → sys계정의 소유이지만, sys조차도 이 테이블들의 내용을 바꾸지 못하고 조회만 할 수 있다.

데이터 딕셔너리
Base Table	데이터베이스를 생성할 때(dbca, create database 등) 생성된다.  → 원본데이터가 들어있기 때문에 사람은(DBA도) 접근할 수 없다.  → 그래서 내용을 조회할 수 있도록 추가적으로 Data dictionary view를 제공해준다.  → 여기부터
Data Dictionary view	dbca로 데이터베이스를 생성할 때 자동 생성되지만, create database 명령어로 수동 생성할 경우에는 자동으로 생성되지 않으므로, create database를 수행한 후 catalog.sql스크립트를 추가로 수행해 줘야 한다.  - 내용을 조회할 수 있다.
	* Static Dictionary : 딕셔너리에 저장된 정보들이 자동으로 변경되지 않는다.   → instance가 open일 때만 조회된다.   → 딕셔너리 종류(4개 카테고리 분류)  * USER_XXX : 해당 사용자가 생성한 내용만 볼 수 있는 딕셔너리.  * ALL_XXX : 해당 사용자가 생성한 것과 + 생성하지 않았더라도 접근 가능한 내용을 볼 수 있는 딕셔너리.  * DBA_XXX : 데이터베이스에 생성되어 있는 모든 내용을 다 볼 수 있는 딕셔너리.     → 수동으로 업데이트 하기위해 analyze table 테이블명 compute statistics; 명령어를 사용한다.(절대쓰지마)
	* Dynamic Dictionary (Dynamic Performance view) : 딕셔너리에 저장된 정보들이 자동으로 변경된다.   → Nomount상태부터 조회된다.(모든 상태 가능)   → v$xxx   → 조회하는 시점의 데이터를 실시간으로 알 수 있으며,       사용자가 조회하면 그 시점에 control file이나 메모리에 가서 데이터를를 꺼내와 보여준다.

 

딕셔너리에 저장되어 있는 주요 내용 정보

1. 데이터베이스의 논리적인 구조와 물리적인 구조 정보들 2. 객체의 정의와 공간 사용 정보들 3. 제약조건에 관련된 정보들 4. 사용자에 관련된 정보들 5. Role, privilege등에 관련된 정보들 6. 감사 및 보안 등에 관련된 정보들

 

[ static dictionary 실습 ] : 딕셔너리에 저장된 정보들이 업데이트를 해주지 않으면 자동으로 변경되지 않는다.  1. table을 생성하면 static dictionary와 dynamic dictionary 둘다에 table을 생성했다는 정보가 입력이 된다.  -> pl/sql을 이용해 100번 loop돌려서 데이터를 입력한다.  -> 100건의 데이터가 입력됐는지 select count로 table을 조회해본다.  -> user_tables 딕셔너리(static dictionary) 내용을 조회해보면 num_rows(stest테이블의 데이터 건수)에 한 건도 없는 것으로 조회된다.    2. analyze 명렁어를 이용해 딕셔너리 내용을 수동으로 업데이트 해준다.   -> 그러나 anlyze 명령어나 DBMS_STATS패키지는 해당 테이블을 전체 스캔하는 명령어이므로      절대로 함부로 사용해서는 안되는 명령어이다!!!    3. 다시 user_tables딕셔너리의 내용을 조회해보면 100건 입력된 결과가 보인다.  **이때 주의할 점이 DB에 사용자명, 테이블명, 인덱스명과 같은 것을 입력하면 DB에서는 전부 대문자로 바꿔서 저장을 해놓기 때문에, where절과 같이 대소문자를 구분하는 문장에서는 테이블명을 대문자로 검색해야 한다. **

 

 

(2) SYSAUX tablespace : oracle서버의 성능 튜닝을 위한 데이터들이 저장되어 있음.(10g부터)

 - 9i까지는 튜닝관련 딕셔너리들이 모두 system tablespace에 다 들어 있었지만,

   10g 부터는 튜닝관련 딕셔너리들이 SYSAUX tablespace에 별도로 저장이 되어있다.

 

(3) 일반 Tablespace : 관리자가 필요에 의해 만드는 tablespace. 얼마든지 만들 수 있고, 삭제할 수 있다.

 

[실습1] 일반 Tablespace 생성 및 조회하기.

  1. 일반 tablespace 생성

[문법]     create tablespace 테이블명      datafile ' ' size 1M;

 

 

  2. tablespace 조회하기.  

 

 

[실습2] 각 Data file의 실제 사용량 확인하는 방법 : 어차피 다 못외우니까 의미만 이해할 것. select오래걸림!!!!

 

 

 

[실습3] Tablespace 용량 관리하기.

Tablespace 용량이 가득 찼을 때 해결법 (용량 부족)

1. Data file을 하나 더 추가.  2. Data file을 크게 늘려주기 - 자동증가 / 수동증가

Tablespace에 table 추가하기.

create table scott.테이블명 (칼럼명 데이터타입) tablespace 테이블스페이스명

 

  (준비) : haksa tablespace에 iphak table을 만들고 일부러 가득 차게 해서 장애를 만든다.

 

  (조치방법1) 수동으로 tablespace에 data file을 하나 더 추가.

[문법]   alter tablespace 테이블명   add datafile '/app/oracle/oradata/testdb/haksa02.dbf' size 20M;

 

 

 

 

  (조치방법2) Data file 크기 수동으로 증가시키기.

[문법]   alter database datafile '/app/oracle/oradata/testdb/haksa01.dbf' resize 20M;

 

 

 

  (조치방법3) Data file 크기 자동으로 증가시키기.

[문법]   alter database datafile '/app/oracle/oradata/testdb/haksa01.dbf' autoextend on;

 1. haksa01.dbf 파일의 크기가 다 찼을 때 자동으로 증가시키도록 autoextend on 해준다. -> autoextensible이 yes로 바뀐것 확인.

 

2. 작업을 편하게 하기위해, tablespace의 자동증가가능성을 볼 수 있는 쿼리르 tsselect.sql로 저장.

 

 3. 100만번 loop돌리는 pl/sql쿼리를 이용해서 여러번 수행 후에 확인해보면 크기가 자동으로 증가된 것이 확인된다.

 

[ tip ]  ★자주쓰는 쿼리문을 저장해서 간단히 쓰고 싶을 때★  1. 원하는 쿼리문을 먼저 실행시킨 후  2. save 원하는쿼리파일명  을 적으면 .sql로 파일이 생성된다.  3. 이후에 @파일명  으로 실행시킨다.  4. 다음에 제거하고 싶을 때는 rm -fr 파일명.sql 로 제거한다.   또는, vi 원하는쿼리파일명.sql 로 만들어 vi창에 쿼리문을 입력한 후 저장해서 사용할 수 도 있다.

 

 

[실습4] Tablespace Offline

 

* tablespace offline : 사용자가 더이상 특정 tablespace에 접근 못하게 함. (select조차 안되도록)

  - 특정 tablespace의 데이터파일의 위치를 이동한다던지 혹은 특정 tablespace가 장애가 나서 복구해야할 때 유용하다.

  → tablespace를 offline한 후에 online 하게 되면 반드시 수동으로 checkpoint를 수행해서 SCN번호를 동기화 시켜야 한다.★

Tablespace를 Offline으로 하는 방법
Normal mode	**tablespace에 아무런 문제가 없을 때 정상적으로 수행  >alter tablespace haksa offline;
Temporary Mode	**offline시키고자 하는 tablespace의 data file에 하나라도 이상 생기는 경우  >alter tablespace haksa offline temporary;
Immediate Mode	**반드시 archive log mode일 경우에만 사용해야 한다.  >alter database datafile '/app/oracle/oradata/testdb/haksa01.dbf' offline;   **만약 no archive log mode일 경우  >alter database datafile '/app/oracle/oradata/testdb/haksa01.dbf' offline drop;    → 단 노 아카이브 모드에서 offline drop한 후 online을 하면 recovery하라는 메시지 나옴.

 

datafile이 offline인지 online인지 확인하는 방법

>select file# , name, status from v$datafile;   --v$datafile을 조회

 

archive 모드인지 no archive 모드인지 확인하는 방법

> archive log list;

 

 

1. archive모드인지 확인해보니 no archive log mode로 나온다.

 

 

2. no archive log mode일 경우에는 offline drop 시켜야 datafile을 offline할 수 있다.

-> offline drop 하면 recovery 메시지가 뜬다.

-> example tablespace는 normal mode로 offline한다.

 

3. tablespace명과 데이터파일의 상태, checkpoint 번호 등을 확인하는 쿼리를 tsoff.sql로 저장. 

 

4. offline된 tablespace는 checkpoint 번호가 다른것 과 다르게 나온다.

-> online 시키면 recovery를 제외한 offline상태는 online으로 바뀌지만, checkpoint는 여전히 다르다. 

 

5. checkpoint를 일으켜 동기화 시켜준다.

 

6. 동기화 후에 offline상태인 example tablespace의 checkpoint가 다른것들과 같아졌지만, recovery는 checkpoint상태가 바뀌지 않았다.

 

7. haksa tablespace의 다른 데이터파일도 offline하려고 하자, 이미 data file중 1개가 offline상태이므로 offline normal이 안된다.

-> 이럴 때는 offline temporary를 이용해 offline시켜준다.

 

8. haksa tablespace를 recovery복구해준후, haksa tablespace를 online시키면, online으로 복구 완료 되었지만, SCN이 여전히 다르다.

 

9. 다시한번 checkpoint를 일으켜 동기화시켜주면 haksa tablespace의 SCN이 다른 tablespace와 같아졌다.

 

[실습5] Data file 이동시키는 작업(순서가 중요!!!)

 

- 특정 디스크에 있는 data file들의 용량이 점점 증가하여 다른 더 큰 용량의 디스크를 설치 한 후 몇 개의 Data file을 신규 디스크로 이동시키는 작업을 할 때나, 복구할 때 사용된다.

- offline : 특정 tablespace만 shutdown했다는 의미와 같다

 

Offline 되는 tablespace의 data file 이동하기 순서

① 해당 tablespace offline ★  > alter tablespace haksa offline;

② data file을 대상 위치로 복사

③ 컨트롤 파일내의 해당 data file 위치 변경  > alter tablespace haksa rename     datafile '원래있던경로와파일명'     to '새로이사간곳의경로와파일명';

④ 해당 tablespace online.  > alter tablespace haksa online;

 

1. 특정 tablespace를 offline 한다. _ datafile들의 위치를 확인한다.

 

2. haksa tablespace의 상태가 offline된 것 확인.

 

3. data file을 대상 위치로 복사한다. -> 컨트롤 파일 내의 해당 data file의 위치를 변경한다.

 

4. 해당 tablespace를 online 한다.

 

 

* Offline 안되는 tablespace의 data file 이동하기 순서     - offline 되지 않는 tablespace : system tablespace/ 사용중인 undo tablespace/ default temporary tablespace.   * Redo log file 이동하기 순서(redo log file은 offline이 안되므로 반드시 database를 mount상태로 두고 작업해야 한다.)

① DB 종료

② 마운트 상태로 시작

③ Data file을 복사.

④ 컨트롤 파일내의 해당 data file 위치 변경  > alter tablespace haksa rename     datafile '원래있던경로와파일명'     to '새로이사간곳의경로와파일명';

⑤ DB open.

  (아래 tablespace 실습문제로 확인할 것.)

[실습6] Tablespace 삭제하기.

tablespace가 비어있는 상태일때.

drop tablespace haksa

tablespace가 비어있는 상태가 아닐 때.(값이 들어간 table이 하나라도 있으면 drop tablespace 로 바로 지워지지 않는다.)   ** 만약에 tablespace에 값이 없는 빈 table만 있을 때에는 drop tablespace로 바로 삭제된다.**

drop tablespace haksa including contents and datafiles;

 

1. 실습하기위해 tablespace haksa를 생성하고,그안에 iphak테이블을 생성한다.

2. iphak테이블에 값을 넣어주려는데, iphak테이블 생성할 때 scott계정에 생성했으므로, 값을 넣어줄 때도 scott계정에서 입력해야 한다.

3. tablespace를 drop해보니, 빈상태가 아니므로 error가 난다.(tablespace를 drop할 때는 sys계정으로 로그인해서 drop해야 한다.)

4. tablespace가 비어있는 상태가 아닐때에는 drop tablespace haksa including contents and datafiles; 옵션을 추가해서 삭제한다.

* 만약에 tablespace가 비어있다면 drop tablespace로 바로 삭제된다.

---

 

[tablespace 문제] 15분안에 풀어야 함 ★

 

Q.spfile을 사용.  /home/oracle/disk1/system01.dbf , sysaux01.dbf                     /disk2/undotbs01.dbf, users01.dbf, example01.dbf                     /disk3/ctrl01.ctl, redo01_a.rdo, redo02_a.rdo, redo03_a.rdo                     /disk4/ctrl02.ctl, redo01_b.rdo, redo02_b.rdo, redo03_b.rdo                     /disk5/ctrl03.ctl, redo01_c.rdo, redo02_c.rdo, redo03_c.rdo

 

[ Solution]

1.pfile인지 spfile인지 확인한다. value값에 값이 있으면 spfile이다.

 -> 현재 controlfile 경로를 확인한 후, spfile의 내용을 변경해준다.

 -> Instance 강제 종료.

 

 

2. 대상 디렉토리 생성 후 파일을 복사한다.

 ->정상적으로 startup시킨 후, 현재 control file을 조회한다.

 

 

3. 그룹 1,2,3을 삭제하기위해 그룹 4,5를 생성한다. (그룹은 최소 2개 이상이어야 하므로, 그냥 바로 1,2,3을 삭제할 수는 없다.)

 

4. 삭제하고자 하는 그룹의 상태를 Inactive상태로 만들어 주기 위해, db를 open해서, switchlog를 일으킨 후 checkpoint를 일으켜 동기화 시켜준다.

 

5. group 1,2,3을 삭제 한다.

 

6. 생성하고자 했던 logfile 그룹 1,2,3을 생성한다.

 

7. 해당그룹에 member를 추가해준다.

 

8. switch log와 checkpoint를 일으켜 삭제할 그룹 4,5를 inactive상태로 만들어 준다.

 

9. group4를 먼저 drop시켰다.

 

10. group5도 제거하기 위해 switchlog를일으키고, checkpoint도 일으켜 inactive상태로 만들었다.

 

11. 그룹5를 삭제한 후 ,확인.

 

12. 이제 datafile 을 이동시키기 위해 db를 종료한 후, mount상태로 켜준다.

 -> datafile들의 경로를 확인한 후, 카피해준다.

 

13. 컨트롤파일의 내용을 변경해준 후, DB를 open한다.

 

14. v$controlfile, v$datafile, v$logfile를 조회해서 확인. (끝)

 

Q. pfile을 사용하여 아래와 같이 구성하세요.  /data/disk1/control01.ctl, redo01_a.log, redo02_a.log, redo03_a.log          /disk2/control02.ctl, redo02_a.log, redo02_b.log, redo03_b.log          /disk3/control03.ctl, system01.dbf , sysaux01.dbf          /disk4/undotbs01.dbf, example01.dbf          /disk5/users01.dbf

 

-해보기-

 

 

 

---

 

 

[녹음10]

(4)Undo Tablespace

 * Undo Tablespace란 ? : undo segment(undo data만 저장하는 segment)를 저장하고 있는 특별한 tablespace

  - 관리자가 생성할 수 있고 관리할 수 있지만, undo data 외의 다른 데이터는 저장될 수 없다.

  - undo data라 하더라도 사용자가 관여할 수 없고 oracle server process가 직접 관리한다.

 

 (잠깐!)

segment란? 물건을 담는 병이라고 생각하면 쉽다.  data가 들어가 있으면 data segment  temp가 들어가 있으면 temp segment

 

 

 1) undo tablespace의 특징.

① oracle server process만 undo tablespace에 undo segment를 생성하고, 기본적으로 각 사용자별로 undo segment를 할당해서 undo data를 관리하며 사용자는 관여할 수 없다.   ② undo tablespace는 Instance당 여러 개가 동시에 존재할 수 있지만 사용되는 것은 한번에 1개 뿐이다.(특별한 경우 동시에 여러개 사용되기도 함.)  ③ undo tablespace 관리방법   - ★★AUM(automatic undo management)       : undo관리를 오라클이 자동으로 하는 것.(인스턴스당 충분한 크기 갖도록 할당.autoextend on)         -> tablespace를 생성만 하면 undo segment는 오라클이 다 자동으로 생성시킨다.          - 단점 : 특정 기간이 지나면 지워야 하는데 지울 수가 없다. undo를 새로만들어 설정 바꿔주고 지운다.          ->초기화 파라미터 파일(pfile,spfile)에 undo_management=auto로 설정(9i이후부터기본)                                                               undo_tablespace-undotbs (undo tablespace는 최소1개이상)     <-> MUM (manual undo management) : undo관리를 수동으로 하는 것.                                                               (주로 undo 관련 장애가 발생했을 때 복구 용도로 많이 사용됨.)                                                               ->초기화 파라미터 파일(pfile,spfile)에 undo_management=manual로 설정

 

 2) Undo Tablespace의 사용목적★★★

① Transaction Rollback : 사용자가 rollback 수행할 경우 undo tablespace에 저장된 undo data를 사용해서 rollback한다.   ② Read consistency (읽기 일관성) ★★★ :  DML이 진행중인 데이터(트랜잭션이 끝나지 않은 데이터)의 변경전 값을 다른 사용자가 볼 수 있게 되는 원리이다.  (예를들어서 사용자A가 data를 일지매 -> 홍길동으로 바꾸고 있을 때, 다른 사용자B가 select 하면 B사용자의 server process는 undo에 있는 데이터(변경전 데이터)를 DB buffer cache로 복사해와서 다른 사용자들에게 제공한다.)  ③ Transaction Recovery (Instance Recovery) : 운영중이던 DB서버가 비정상적으로 종료되었을 때 Roll Forward와 Roll Backward 작업을 수행해서 Dirty database를 clean Database로 만드는 과정에서 undo data가 사용된다.

 

 

*참고* data가 업데이트 되는 순서.

 

1) 데이터를 DB buffer cache로 복사.

2) Redo log buffer에 변경내용 기록.

3) Undo segment에 변경전 데이터 기록.

4) DB buffer cache의 원본 변경.

 

 

 3) Undo segment 할당되는 원리

** Undo segment의 종류   1. non-system undo : automode와 manual mode.   2. deferred undo.

 

※ undo data file의 크기가 증가만 되고, 다시 줄어들지는 않는다.   왜냐하면, commit을 수행해도 해당 undo segment안에 undo data는 지워지지 않고 남아 있기 때문이다.  commit을 수행하면 다른 server process가 덮어 쓸 수 있게 해줄 뿐, undo segment안의 자료를 delete 하는 것은 아니다.★★  [commit하면데이터몇초만에지워지냐 라고 애매하게물어봄 (면접 ★) : commit을해도 데이터는 지워지지않고, 덮어쓸 수 있도록 바뀌는것.]

1. undo tablespace안에 undo segment가 4개 할당되어 각각 A,B,C,D 사용자에 의해 사용되어 지고 있다.       2. 이 때 만약 E사용자가 새로 접속해서 DML을 수행하면,    3. E사용자의 server process는 가장먼저 undo segment를 확보하는데,       이때 신규를 할당받기 전에 기존에 만들어져 있던 segment중에서 트랜잭션이 완료된 것이 있는지 먼저 확인 한다.    4. 만약 A사용자가 그 시점에 commit을 수행하여 트랜잭션이 끝났다면 A가 쓰고있던 undo segment를 덮어 쓰게 된다.      5. 이 시점에 새로운 F사용자가 접속해서 DMl을 수행하려 할 때, 모든 사용자가 트랜잭션을 완료하지 않았다면, 새로운 undo segment를 하나 더 생성해서 기록한다.      6. datafile의 저장공간이 허용되는 범위까지 undo segment가 늘어나다가, 만약 data file에 더 공간이 없게 되면, 하나의 segment에 2개 세션 이상의 undo data를 함께 기록한다.    7. 그조차도 공간이 없으면 해당 트랜잭션은 에러가 난다.

 

 

현재 undo segment 사용내역 조회

SYS>select a.sid, a.serial#, a.username, b.used_urec, b.used_ublk     2  from v$session a, v$transaction b   3  where a.saddr=b.ses_addr;  --used_urec : undo사용 record수 / used_ublk : 사용중인 undo block개수

[터미널1창] : scott사용자로 emp테이블 업데이트.   [터미널2창] : 업데이트 전과 후. undo segment 사용내역 조회.

 

 4) undo tablespace 관리 실습

[실습1]현재 undo tablespace 상태 파악  :  show parameter undo;

 

[실습2] 신규 undo tablespace 생성

create undo tablespace 이름    datafile ' 경로 ' size 1M    autoextend on;  --크기자동증가옵션

 

 

[실습3] Undo tablespace 변경하기 : alter system set undo_tablespace= 변경할undotablespace ;

 

 5) 각 세션 별로 사용중인 undo segment 확인하기.

 (다른 터미널 창에서 DML을 수행하고 commit하지말고 조회해볼것.)

 

  - 세션 별로 하나의 undo segment를 할당한다.

 

 6) undo 관련 주요 parameter ★★★

UNDO_RETENTION (단위는 '초'이다.)	commit 수행 후에도 해당 undo segment 내의 데이터를 다른 서버 프로세스가 덮어 쓰지 못하도록 막아주는 시간. → 주로 CR작업으로 undo segment에 있는 데이터를 조회해야 하는 경우에 많이 발생한다. → undo segment 양이 부족하면 설정된 값이 무시되고 undo segment가 재사용 돼 버린다.    (문제점) : undo segment 여분이 있을 경우에만 적용되며, 만약 모든 undo segment가 사용중일 경우에는 적용되지 않게되어, undo segment가 재사용 되버린다.
UNDO_RETENTION_GUARANTEE	undo retention 파라미터로 설정된 시간 동안 재활용을 하지 않고 데이터를 지켜준다. (여분이 없어도)  (문제점) : commit을 수행해도 undo segment를 특정 시간동안 재활용 하지 않기 때문에 undo tablespace의 사용량이 증가될 수도 있다. -> 기본값은 NOGUARANTEE(사용안함)  ( undo retention guarantee로 설정 > alter tablespace undo테이블명 retention guarantee; )  ( undo retention noguarantee로 설정 > alter tablespace undo테이블명 retention noguarantee; )

 

(퀴즈Q) 다음 결과는 ? 10:00    A    select sum(sal) from emp;   --1시간소요된다. 10:10    B    update emp set sal=3000   --원래 2000이었다. where empno=7900; 10:15    B    commit; 11:00    A    결과는?   (보기) 1:2000 2:2500 3:3000 4:에러     (답) : 1번 ,4번 (설명) : 오라클에서 RC(읽기 일관성)은 매우 중요하다. 데이터가 첫번째 블락에 있을때는 2000, 마지막 블락에 있을때 만약 3000으로 된다면 일관성이 없는 것이다. commit을 날리게 되면 다른 사용자가 undo segment를 덮어쓸 수 있게 되는데, B가 10시 15분에 commit을 날리면, 10시 20분쯤 또다른 사용자인 C가 data를 덮어 써버려서 데이터가 없어지게 될 수도 있다. 그러면 A가 원본데이터를 찾으러 undo segment에 갔는데 없게 되므로, 그럼 ora-01555 snapshot too old라는 오류가 뜬다.   snapshot too old 오류란? : commit을 쳐서 undo data를 덮어 썼기 때문에 생기는 오류. 근데 문제는 commit을 안치면 CR작업이 많이 일어나게되고, commit 많이 치면 서버의 부하가 일어난다. 그래서 만든 것이 undo_retention으로, commit을 한 데이터를 (commit안한 데이터가 아니라) commit후에 undo에 덮어쓰지 않고 기다리는 시간이다.   그러므로 위 퀴즈에서는 그대로 undo segment에 있는 원래 값인 2000이 나오거나, 아예 snapshot too old 에러가 뜬다.

 

 7) undo tablespace 삭제하기 : drop tablespace undotbs1; 

  - 사용중인 undo segment를 포함하고 있는 undo tablespace는 삭제할 수 없다.

  - 가끔 사용하지 않는데 사용중이라면서 삭제가 되지 않는 경우

    : 해당 undo segment를 _offline_rollback_segmnets 라는 hidden parameter를 사용하여 강제로 offline시킨 후 삭제한다.

 

 

(5) temporary tablespace★

 : 임시자료를 저장하는 tablespace로, DB가 재시작되면 내용은 모두 사라진다.

 - 일반적으로 정렬작업 등 수행할경우 PGA공간이 부족하거나, export, import등 작업을 할 경우에도 사용된다.

 - 하나의 Instance에 여러개 만들 수 있으므로 각 사용자 별로 하나씩 각각 할당해 주는 것이 좋다.

 - 정렬작업이 크게 일어나는 작업전에 temporary tablespace를 크게 만들어주고 작업하는 것이 좋다.

 - one pass sort 란 : DB cache에서 정렬하기 위해 PGA로 옮길 때 원하는 건수를 한방에 보내 정렬하는 것이다.

   sort run 이란 : DB cache에서 정렬하기 위해 PGA로 옮길 때 PGA의 공간이 부족하면, 한번에 정렬할 수 있는 단위를 sort run이라고 부른다.

 - 만약에 데이터는 많은데 PGA용량이 적을 때,DB cache에서 sort run 단위에 맞춰 데이터를 10건씩 PGA에게 보내면 PGA가 정렬하여 temporary tablespace에 임시로 저장해둔다. temporary tablespace에 100건이 모두 차게 되면, Merge(정렬)작업 후에 사용자에게 Direct로 전달한다. ★★★(정렬어디서하는지)

 

 

 1) temporary tablespace 관리실습

 ① 기존 temporary tablespace 조회 : select tablespace_name , file_name  from dba_temp_files;

 

 ② 신규 temporary tablespace 생성

 : create temporary tablespace 테이블명

   tempfile '경로' size 1M

   autoextend on;

 

 ③ Default Temporary tablespace 설정.

  : 독자적인 temporar tablespace를 할당받지 못한 사용자나 system tablespace가 사용하는 temporary tablespace.

  - temporary tablespace를 여러 개 생성할 때, 반드시 1개를 default temporary tablespace로 지정해야 한다.

                      database_properties에서 '%'를 통해 DEFAULT_TEMP가 들어간 모든것을 조회해본다.

 

                _default temporary tablesapce를 temp2로 변경 : alter databse default temporary tablespace temp2;

 ④ temporary tablespace 크기 변경 : alter database tempfile '경로' reszie 100M;

 - temporary tablespace의 용량이 부족할 경우 sort나 exp/imp등 작업수행중에 에러가 발생하면, temporary tablespace의 크기를 증가시킨다. 

⑤ temporary tablespace 삭제하기 : drop tablespace temp2;

  -default temporary tablespace는 삭제되지 않는다.   

  -이럴 경우, 다른temporary tablespace를 defualt로 변경해준 뒤 삭제하면 된다. 

 

 

 

  2) temporary tablespace group (11g New Feature)

 

 : 여러 사용자가 동시에 하나의 temporary tablespace를 쓸 수 없으므로, 사용자별로 temporary tablespace를 각각 할당해 주는 것이 좋다. 이 때, default temporary tablespace는 무조건 하나 생성해줘야 한다.(독자적인 temporary tablespace를 할당받지 못한 사용자나, system tablespace가 사용하는 공간이다.)

 그런데 만약에 여러 사용자가 전부 하나의 스키마로 동시에 접속하는 경우 작업이 늦어지게 되므로, temporary tablesapce를 여러 개 만든 후 그룹으로 묶어주고 특정 schema에게 temporary tablespace group을 지정해 주면, 여러명의 사용자가 temporary tablespace를 각각 별도로 사용하므로 성능 향상이 이뤄진다.

 ① temporary tablespace group 생성

   : create temporary tablespace

tempfile '경로' size 1M

tablespace group 그룹명;

 ② 이미 만들어져 있는 temporary tablespace를 그룹에 추가 : alter tablespace 이름 tablespace group 그룹명;

 ③ temporary tablespace group 조회 : select * from 그룹명;

 ④ 만들어진 그룹을 사용자에게 할당 : alter user 계정 temporary tablespace 그룹명;

 ⑤ 시스템의 default temporary tablespace로 지정 : alter database default temporary tablespace 그룹명;

 ⑥ 그룹에서 탈퇴 : alter tablespace 이름 tablespace group '';

 

 

※ tablespace 전체가 아니라, datafile 하나만 지우고 싶을 때

: alter tablespace users drop datafile '경로';