Oracle Admin1 -3

* Oracle managed file (OMF)

             oracle이 file 정의 자동 관리

1) alter system

    set db_create_file_dest='?/ORADATA/u05';

             :datafile의 위치

2) create tablespace insa;

             :datafile spec 생략

 

정의하지 않아도 자동 설정

 

 

 

dba_xxx : Data Dictionary (system tablespace)

v$xxx : controlfile

 

 

 

9-17.

 

create tablespace test_tbs

datafile size 5m

blocksize 32k         ← error

 

ERROR at line 1:

ORA-00382: 53248 not a valid block size, valid range [2048..32768]

 

32k 할당할 수 있는 메모리를 먼저 구성해야 함.

 

1) alter system

set db_32k_cache_size=4M;

           → 메모리 할당시 Error 발생시 다른 메모리 줄여 줌.

             (alter system set shared_pool_size=32M;)

 

 

2) create tablespace test_tbs

datafile size 5m

blocksize 32k;

             → 32k 가 있는 버퍼가 있어야 함. 

3) select * from dba_tablespaces;

 

 

default - 4k       set size - 32k

→ memory 충분하게 주면 초과하는 부분 링크(체인)

 

 

9-20.

 

* pctfree : update(성장) 을 위한 여분 공간

 

             예) pctfree 0

             ========== emp ===========

                                 →→→→→→→→

             _____________________________

                           |103  ↑ |107..    |      ↓

                           |102  ↑ |106..    |      ↓

row header->     |101null|105..     |  101 (이주)

chain정보           |100..    |104...   |  'abc....'

             -----------------------------

                           32          33          34

① insert : null

② update : null → 'abcde...xyz'

③ select : 101 → 32,34

 

             예) pctfree 20

             _____________________________ 

                           |'abc..'|             |

             -----------------------------    →  20% (pctfree)

                           |103  ↑ |107..    |

                           |102  ↑ |106..    |

                           |101null|105..     | 

                           |100..    |104...   | 

             -----------------------------

                           32          33          34

① insert : null

② update : null → 'abcde...xyz'

③ select : 101 →32

 

 

* pctused : block used 상태인 최소 percentage

                               ↑

                             free

 

freelist - segment 저장할 수 있는 리스트 목록 저장

 

 

 

 

9-21.

 

drop table hr.test1;

 

create table hr.test1

(no number(20),

 name varchar2(20))

tablespace userdata

storage (initial 100k next 100k)

initrans 5

maxtrans 50

pctfree 20

pctused 60;

 

select * from dba_tables

where tablespace_name = upper('userdata');

 

 

 

 

9-27

 

drop table hr.test1;

 

create table hr.test1

(no number(20),

 name varchar2(20))

tablespace users

storage (initial 100k next 100k)

initrans 5

maxtrans 50

pctfree 20

pctused 60;

 

select * from dba_tables

where tablespace_name = upper('users');

 

 

 

 

 

10-4.

 

update hr.employees

set salary = 1000

where employee_id = 100;

 

select * from v$session;

select * from v$rollname;

select * from v$transaction;

 

* 읽기 일관성 : select 시점의 data 보장

 

 

 

 

             hr                                                               hr

 

 1) 읽기의 일관성 요구

--set transaction read only;

 

2) select * from employees;

 

 

                                                     3)update employees

                                                     set salary = 1000

                                                     where employee_id = 100;

                                                     commit

 

4) select * from employee;

 

5) rollback; or commit; (일관성 해제)

 

 

 

 

 

 

10-30. 연습문제

 

 

             hr                                                  sys

                                                     1) show parameter undo

                                                     2) create undo tablespace undo2

                                                         datafile '?/ORADATA/u03/undo2.dbf' size 15M;

 

3) update employees 

    set salary = 1000

    where employee_id > 200

 

                                                     4) alter system set undo_tablespace = undo2;

                                                         select * from v$rollstat;

 

                                                     5) drop tablespace undotbs1 including contents

                                                         and datafiles; ←error

 

6) rollback; → logout                    6)pending offline → offline 변경후 삭제 시도.

                                                        → 시도후 offline 변경 되었는지 확인.

                                                        dba_rollback_segs

 

 

 

 

11-10.

 

select rowid, e.*

from hr.employees e;

                              → alias

 

 

 

 

* heap table : 일반 table / 무작위 저장.

 

 

drop table emp;

create table emp

(

 no number(10),

 name varchar2(20),

 deptno number(10)

) tablespace users

  partition by list (deptno)

             (

             partition p1 values (10,20),

             partition p2 values (30,40),

             partition p3 values (50,60),

             partition p4 values (70,80),

             partition p5 values (default)

             );

 

insert into emp

 

select employee_id,

             last_name,

             department_id

from hr.employees;

            

 

 

select * from emp partition(p2);

=========================

drop table emp;

create table emp

(

 no number(10),

 name varchar2(20),

 deptno number(10)

) tablespace users

  partition by list (deptno)

             (

             partition p1 values (10,20) tablespace users,

             partition p2 values (30,40) tablespace users,

             partition p3 values (50,60) tablespace users,

             partition p4 values (70,80) tablespace users,

             partition p5 values (default) tablespace users

             );

 

insert into emp

select employee_id,

             last_name,

             department_id

from hr.employees;

            

 

 

 

11-18. 임시 테이블 생성

-hr-

create global temporary table temp

(

no number(10),

name varchar2(20)

) on commit preserve rows;

 

 

 

 

 

on commit preserve rows

-hr-

             A                                                                              B

 

insert into temp                                                        select * from temp;

values (100, 'king');

commit;

 

select * from temp;

 

 

 

 

 

 

alter table employees move;

 

alter index emp_emp_id_pk rebuild;

 

select * from employees

where employee_id = 100;

           ----------->emp_emp_id_pk (index 정의 )