Admin 1. 05장 Managing Database Storage Structures

tablespace   datafile ...                                                                 ==> 물리적

segment  ...          extent ...              block ...                      ==> 논리적

논리적 저장단위         이름이 있는 최소 단위      읽고 쓰는 최소 단위

-이름이 있는           -object                                    

 

 

tablespace 만들기

create tablespace ts1 datafile '/home/oracle/ts1.dbf' size 10M;

  

 

유저입장 ex)scott

create table x(x varchar2(20))
tablespace ts1;

 

desc user_tables;

select TABLE_NAME, TABLESPACE_NAME from user_tables;

 

 

S SYS> desc dba_tablespaces;

S SYS> desc v$tablespace;

S SYS> desc dba_segments;

S SYS> desc dba_extents;

S SYS> desc v$datafile;

 

 

tablespace 공간 부족시 추가 하는 방법

1. 데이터 파일 크기 증가
2. 데이터 파일에 자동증가 옵션 줌
3. T/S 데이터 파일을 추가

 

3번 방법

alter tablespace ts1 add datafile '/home/oracle/ts1_2.dbf' size 20M;

alter tablespace ts1 add datafile size 20M

 

 extents

delete문 쓰면 공간이 줄어드나? NO

 select count(*), sum(bytes) from dba_extents
 where owner='SCOTT' and segment_name='X';
  

COUNT(*) SUM(BYTES)
---------- ----------
        24    9437184

delete table x;

COUNT(*) SUM(BYTES)
---------- ----------
        24    9437184

 

 

해결방법

truncate table x;

COUNT(*) SUM(BYTES)
---------- ----------
         1      65536

 

보는 방법

select blocks,bytes from dba_extents where owner='SCOTT' and segment_name='X'

 BLOCKS      BYTES
---------- ----------
         8      65536
         8      65536
         8      65536
         8      65536
         8      65536
         8      65536
         8      65536
         8      65536
         8      65536
         8      65536
         8      65536
         8      65536
         8      65536
         8      65536
         8      65536
         8      65536
       128    1048576

17 rows selected.

 

select count(*), sum(bytes) from dba_extents where owner='SCOTT' and segment_name='X'

  COUNT(*) SUM(BYTES)
---------- ----------
        17    2097152

 

 

database block

 

free space : update시 chaining 방지를 위해미리 비워둔 공간

(chaining : 공간이 모자라서 다른 블럭을 동시 사용(low하나 읽을때 block을 두개 읽어야 함))

 

 

select count(*), sum(blocks), sum(bytes)
from dba_extents where owner='SCOTT' and segment_name='X';

  COUNT(*) SUM(BLOCKS) SUM(BYTES)
---------- ----------- ----------
        17         256    2097152

 

update x set x=x||'123123';

65536 rows updated.

select count(*), sum(blocks), sum(bytes)
from dba_extents where owner='SCOTT' and segment_name='X';

 

  COUNT(*) SUM(BLOCKS) SUM(BYTES)
---------- ----------- ----------
        18         384    3145728

 

+1 => update시 공간(freeSpace)이 모자라서 migration 혹은 chaining가 일어나서 공간을 더 사용.

 

 

S SCOTT> select rowid "Address", dept.* from dept;

Address                DEPTNO DNAME          LOC
------------------ ---------- -------------- -------------
AAAMfKAAEAAAAAQAAA         10 ACCOUNTING     NEW YORK
AAAMfKAAEAAAAAQAAB         20 RESEARCH       DALLAS
AAAMfKAAEAAAAAQAAC         30 SALES          CHICAGO
AAAMfKAAEAAAAAQAAD         40 OPERATIONS     BOSTON

64진수 = A~Za~z0~9+/

rowid : 64진수(2^6) 숫자 18개로 구성 ==>2^(6*18)=2^(108)=2^100*2^8=(2^10)^10*2^8=1024^10*256=10^(3*10)*256=256*10^30

DB에 들어갈 수 있는 총 row 갯수

                      

                                                                                               

 객체번호   파일번호    블록 번호      블록내 몇번째 row

 

 

 

실제 사용블럭과 할당된 블럭 보는 방법

 

사용블럭

 

select count(distinct substr(rowid,1,15)) from x

COUNT(DISTINCTSUBSTR(ROWID,1,15))
---------------------------------
                              163

 

할당된 블럭

select count(*), sum(blocks), sum(bytes)
from dba_extents where owner='SCOTT' and segment_name='X';

  COUNT(*) SUM(BLOCKS) SUM(BYTES)
---------- ----------- ----------
        18         384    3145728

 

 

truncate table x;

 

select count(distinct substr(rowid,1,15)) from x;

COUNT(DISTINCTSUBSTR(ROWID,1,15))
---------------------------------
                                0         

 

select count(*), sum(blocks), sum(bytes)
from dba_extents where owner='SCOTT' and segment_name='X'

  COUNT(*) SUM(BLOCKS) SUM(BYTES)
---------- ----------- ----------
         1           8      65536       

 

 

insert into x values('adfadfadfadf');

cmmit;

select count(distinct substr(rowid,1,15)) from x

COUNT(DISTINCTSUBSTR(ROWID,1,15))
---------------------------------
                                1

 

select count(*), sum(blocks), sum(bytes)
from dba_extents where owner='SCOTT' and segment_name='X'

  COUNT(*) SUM(BLOCKS) SUM(BYTES)
---------- ----------- ----------
         1           8      65536        

 OMF (위치와 이름이 자동)

tablespace 만들때 경로 추가해주지 않아도

DB_CREATE_FILE_DEST 파라미터에 지정된 값으로 자동으로 잡아줌

 NAME                 VALUE
-------------------- ------------------------------------------------------
db_create_file_dest  /u01/app/oracle/oradata

 

 

TABLESPACE DATAFILENAME
---------- ----------------------------------------------------------------
TS1        /home/oracle/ts1.dbf
TS1        /u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts1_5177sh2w_.dbf
TS1        /home/oracle/ts1_2.dbf

 

 

 

ex) create tablespace ts1 size 10M;

 

 

 

Space Management in Tablespaces

Locally managed tablespace - extent 할당 크기가 자동

Dictionary managed tablespace - 과거 버전 호환성 

 

 

 

 

 

사이즈 수정

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ORCL/datafile/ts3.dbf' RESIZE 20M