[8i] 스토리지 구조(블록, 익스텐트, 세그먼트) 및 관련성(OLN)

/********************************************************************************************
-- Title : [8i] 스토리지 구조(블록, 익스텐트, 세그먼트) 및 관련성(OLN)
-- Reference : OLN
-- Key word : block segment extent ********************************************************************************************/

/****************************************************************************************
-- 스토리지 관련 데이터 딕셔너리 뷰
****************************************************************************************/

  

/****************************************************************************************
-- 세그먼트 정보 뷰
****************************************************************************************/
   :: DBA_SEGMENTS
      일반적인 정보
        - owner
        - segment_name
        - segment_type
        - tablespace_name
      크기
        - extents
        - blocks
      스토리지 설정
        - initial_extent
        - next_extent
        - min_extents
        - max_extents
        - pct_increase

SQL> SELECT segment_name, tablespace_name, extents, blocks
  2  FROM dba_segments
  3  WHERE owner='SCOTT';

SEGMENT_NAME      TABLESPACE_NAME           EXTENTS  BLOCKS
----------------- ------------------------- -------- -------
DEPT              SYSTEM                           1       8
EMP               SYSTEM                           1       8
BONUS             SYSTEM                           1       8
SALGRADE          SYSTEM                           1       8
...          /* 샘플 테이블 만들때 테이블스페이스 지정 오류... 그래서 SYSTEM TS에 설정됨 */

/****************************************************************************************
-- 사용중인 익스텐트 정보 뷰
****************************************************************************************/
   :: DBA_EXTENTS
      식별
        - owner
        - segment_name
        - extent_id
      위치와 크기
        - tablespace_name
        - relative_fno
        - file_id
        - block_id
        - blocks

SQL> SELECT extent_id, file_id, block_id, blocks
  2  FROM dba_extents
  3  WHERE owner = 'SCOTT'
  4  AND segment_name = 'EMP';

 EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ---------- ----------
         0          1       6961          8
        
        
/****************************************************************************************
-- 사용 가능한 익스텐트 정보 뷰
****************************************************************************************/ 
   :: DBA_FREE_SPACE
      위치와 크기
        - tablespace_name
        - relative_fno
        - file_id
        - block_id
        - blocks
       
SQL> SELECT tablespace_name, count(*), max(blocks), sum(blocks)
  2  FROM dba_free_space
  3  GROUP BY tablespace_name;

TABLESPACE_NAME                  COUNT(*) MAX(BLOCKS) SUM(BLOCKS)
------------------------------ ---------- ----------- -----------
INDX                                    2        6863        6879
RBS                                     2       62335       62463
SYSTEM                                  3       26192       26208
TEMP                                    1        8655        8655
TOOLS                                   1        1023        1023
USERS                                   1       13311       13311
USER_DATA                               1        5760        5760

7 rows selected.

/****************************************************************************************
-- 익스텐트 할당 및 Free Space 검사
****************************************************************************************/
SQL> select extent_id, block_id, bytes, blocks
  2  from dba_extents
  3  where segment_name = 'TEST' and owner = 'MAPBAK';

 EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
---------- ---------- ---------- ----------
         0       5473      32768          4
         1       5477      32768          4
         2       5481      32768          4
         3       5485      65536          8
         4       5493      65536          8
         5       5501      98304         12
        
SQL> select * from dba_free_space
  2  where tablespace_name = 'USERS';

TABLESPACE_N    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------------ ---------- ---------- ---------- ---------- ------------
USERS                14          2    2088960        255           14

/****************************************************************************************
-- 관리용
****************************************************************************************/
-- segment.sql
select tablespace_name, owner, segment_name, segment_type, bytes, blocks
from dba_segments
where owner not in ('SYS','SYSTEM','ORANGE','OUTLN')
/

-- free_space_coalesced.sql
SELECT tablespace_name, total_extents, percent_extents_coalesced
FROM dba_free_space_coalesced
WHERE percent_extents_coalesced <> 100
/

/****************************************************************************************
-- 결합할 수 있는 익스텐트 조회 후 공간 모으기
****************************************************************************************/
SVRMGR> SELECT tablespace_name, total_extents,
     2> percent_extents_coalesced
     3> FROM dba_free_space_coalesced
     4> WHERE percent_extents_coalesced <> 100;
    
TABLESPACE_NAME          TOTAL_EXTE            PERCENT_EX
---------------          ----------            -----------
RBS                               3                     33
DATA01                            9                     22

2 rows selected.

SVRMGR> ALTER TABLESPACE DATA01 COALESCE;

Tablespace altered.

/****************************************************************************************
-- TABLESPACE 내 EXTENT의 할당 및 FRAGMENTATION 제거
****************************************************************************************/
-- 테이블스페이스 생성
SQL> create tablespace users
  2  datafile '$ORACLE_HOME/disk2/user01.dbf' size 2m
  3  autoextend on next 512k maxsize 5m;

Tablespace created.

-- Free Space 조회
SQL> col tablespace_name format a12
SQL> select * from dba_free_space where tablespace_name = 'USERS';

TABLESPACE_N    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------------ ---------- ---------- ---------- ---------- ------------
USERS                14          2    2088960        255           14

-- 스토리지 할당위한 테이블 생성
SQL> create table test (name varchar2(100))
  2  storage (initial 10k next 10k minextents 2 pctincrease 50);

Table created.

-- 데이터 입력
SQL> insert into test values('aaaaaaaaaa          bbbbbbbbbb          cccccccccc');

1 row created.

SQL> insert into test select * from test;

1 row created.

SQL> /

2 rows created.

SQL> /

4 rows created.

SQL> /

8 rows created.

SQL> /

16 rows created.

-- 익스텐트 할당 조사
SQL> select extent_id, block_id, bytes, blocks
  2  from dba_extents
  3  where segment_name = 'TEST' and owner = 'MAPBAK';

 EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
---------- ---------- ---------- ----------
         0       5473      32768          4
         1       5477      32768          4
         2       5481      32768          4
         3       5485      65536          8
         4       5493      65536          8
         5       5501      98304         12
        
-- 다시 free space 조사
SQL> select * from dba_free_space
  2  where tablespace_name = 'USERS';

TABLESPACE_N    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------------ ---------- ---------- ---------- ---------- ------------
USERS                14          2    2088960        255           14

-- 삭제 후 free space 조사
SQL> drop table test;

Table dropped.

SQL> select * from dba_free_space
  2  where tablespace_name = 'USERS';

TABLESPACE_N    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------------ ---------- ---------- ---------- ---------- ------------
USERS                14          2    2088960        255           14

-- 다시 테이블 생성
SQL> create table test (name varchar2(100))
  2  storage (initial 10k next 10k minextents 1 pctincrease 50);

Table created.

SQL> create table test1 storage (initial 4k) as select * from test;

Table created.

SQL> create table test2 storage (initial 10k next 10k pctincrease 0)
  2  as select * from test;

Table created.

-- 데이터 저장(여러번)
SQL> insert into test2 select * from test;

-- fragmentation 조사
SQL> select extent_id, block_id, bytes, blocks
  2  from dba_extents
  3  where segment_name = 'TEST2' and owner = 'MAPBAK';

 EXTENT_ID   BLOCK_ID      BYTES     BLOCKS
---------- ---------- ---------- ----------
         0       5481      32768          4
         1       5537      32768          4
         2       5541      32768          4
         3       5545      32768          4
         4       5549      32768          4
         5       5553      32768          4
         6       5557      32768          4
         7       5561      32768          4
         8       5565      32768          4
         9       5569      32768          4
        10       5573      32768          4

11 rows selected.

-- fragmentation 생성
SQL> drop table test2;

Table dropped.

-- 연속적인 공간 병합
SQL> alter tablespace users coalesce;

Tablespace altered.

SQL> select * from dba_free_space
  2  where tablespace_name = 'USERS';