[Db Admin] 9.Storage Structure and Relationships

1. Coalescing Free Space
   - tablespace에 연속해서 있는 빈 공간을 합쳐 준다.
   - 떨어져 있는 공간은 붙일 수 없다.
   - SMON이 작동한다.
   - PERCENT_EXTENTS가 100%가 될때까지 계속 실행 해준다.
   - PERCENT_EXTENTS가 100%가 되면 coalescing 할 수 없다.
   
   SVRMGR> ALTER TABLESPACE data01 COALESCE ;
   
   * coalescing 상황을 보여주는 view (DBA_FREE_SPACE_COALESCED)
     SVRMGR> SELECT tablespace_name, total_extents,
          2         percent_extents_coalesced
          3  FROM dba_free_space_coalesced
          4  WHERE percent_extents_coalesced <> 100 ;
          
2. Database Block Contents

HEADER	TS
FREE SPACE
DATA

TS : Transaction Slot

3. Block Space를 사용하기 위한 Parameter들
   - INITRANS : transaction slot을 초기값으로 몇개를 만드나? default 1
   - MAXTRANS : transaction slot의 max값. default 255
   - PCTFREE  : 전체 database block 중에 설정한 값% 이외의 부분만 쓸 수 있게 하겠다. default 10
   - PCTUSED  : 사용된 공간이 설정한 값% 만큼 되었을때만 공간을 쓰겠다.
                설정한 값% 이상이면 못 쓴다. default 40
                
4. Storage Structure에 관한 정보를 갖고있는 Data Dictionary Views

   DBA_TABLESPACES : tablespace 정보
   DBA_SEGMENTS    : segment 정보
   DBA_DATA_FILES  : data file 정보
   DBA_EXTENTS     : used extent 정보
   DBA_FREE_SPACE  : free extent 정보
   
5. DBA_SEGMENTS view : querying segment 정보
   - General information column : OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME
   - size에관한 column : EXTENTS, BLOCKS
   - storage setting에 대한 정보 column : INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS,
                                          PCT_INCREASE
   
   SVRMGR> SELECT segment_name, tablespace_name, extents, blocks
        2  FROM dba_segments
        3  WHERE owner = 'SCOTT' ;
        
6. DBA_EXTENTS view : used extent 정보
   column : owner, segment_name, extent_id, tablespace_name, relative_fno, file_id, block_id,
            blocks
   
   SVRMGR> SELECT extent_id, file_id, block_id, blocks
        2  FROM dba_extents
        3  WHERE owner='SCOTT'
        4  AND segment_name='EMP' ;
        
7. DBA_FREE_SPACE view : free extent 정보
   column : tablespace_name, relative_fno, file_id, block_id, blocks
   
   SVRMGR> SELECT tablespace_name, count(*),
        2         max(blocks), sum(blocks)
        3  FROM dba_free_space
        4  GROUP BY tablespace_name ;

---

[ Lab ]

---

1. 테이블과 인덱스를 생성하기 위해서 system 사용자로 스크립트 cr_segs.sql을 실행하십시오.

     --- cr_seqs.sql ---

-- Script cr_segs.sql to create segments -- for Lab11, Q1 O8DBA class -- Created : 14-OCT-97 -- Author  : vvijayan -- Dependencies : -- needs SYSTEM account to run -- needs DATA01 tablespace with exactly 2M free space -- needs INDX01 tablespace with at least 100K free space connect system/manager CREATE TABLE emp( empno NUMBER(4), ename VARCHAR2(30), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2)) TABLESPACE data01 STORAGE (INITIAL 100K         NEXT 100K         PCTINCREASE 0         MINEXTENTS 8         MAXEXTENTS 10) / CREATE TABLE fragment1( a NUMBER) TABLESPACE data01 STORAGE(INITIAL 10K) / CREATE TABLE dept( deptno NUMBER, dname VARCHAR2(15), loc VARCHAR2(20)) TABLESPACE data01 STORAGE(INITIAL 50K NEXT 50K) / CREATE TABLE fragment2( a NUMBER) TABLESPACE data01 STORAGE(INITIAL 8K) / CREATE TABLE big_emp( empno NUMBER(4), ename VARCHAR2(30)) TABLESPACE data01 STORAGE (INITIAL 1M         NEXT 1M         MAXEXTENTS 10) / CREATE INDEX i_e_empno         ON emp(ename)         TABLESPACE indx01         STORAGE(INITIAL 50K                 NEXT 50K) / DROP TABLE fragment1 / DROP TABLE fragment2 /

SQL> connect system/manager
SQL> @ cr_segs

2. 데이터베이스에 있는 세그먼트의 type을 조회해 보십시오.
SQL> select distinct segment_type
  2  from dba_segments ;
  
3. 앞으로 최대 5개 이상의 extent를 가질 수 없는 세그먼트를 검사하기 위한 질의를 작성하십시오. (bootstrap 세그먼트는 무시하십시오.)
SQL> select segment_name, segment_type, max_extents, extents
  2  from dba_segments
  3  where extents + 5 > max_extents
  4    and segment_type <> 'CACHE' ;
  
4. 어느 파일에 EMP 테이블을 위한 공간이 할당되었습니까?
SQL> select distinct f.file_name, e.owner
  2  from dba_extents e, dba_data_files f
  3  where e.segment_name = 'EMP'
  4    and e.file_id = f.file_id ;
  
5.
a) EMP 테이블에 헤더의 File Number와 Block Number를 구하기 위한 질의를 작성하십시오.
   SQL> select relative_fno, header_block, owner, extents
     2  from dba_segments
     3  where segment_name = 'EMP'
     4    and owner = 'SYSTEM' ;
  
b) [문제a]와는 반대로, file number 와 block number를 input으로 받아서, 그 블록을 사용하는 세그먼트의 이름과 유형을 리턴하는 질의를 작성하십시오.(이 때, [문제a]에서 조회한 번호를 입력하여 segment이름이 EMP로 나오는지 확인하십시오)
   SQL> select segment_name, segment_type, blocks, owner
     2  from dba_extents
     3  where file_id = &file_number
     4    and &block_number between block_id and (block_id + blocks - 1) ;
  
6. 테이블스페이스의 이용가능한 free space를 질의하십시오. 이 때, fragment의 개수, total free space, 가장 큰 free extent를 디스플레이 해야 합니다.
SQL> select tablespace_name, count(*) as fragments,
  2         sum(bytes) as total, max(bytes) as largest
  3  from dba_free_space
  4  group by tablespace_name ;
  
7. cr_flags.sql 스크립트를 실행하고, 데이터베이스에 어떤 인접한 free extent가 있는지를 검사하십시오. 만약 있다면 coalesce 한 후에, 다시 검증하십시오.

    --- cr_flags.sql ---

connect sys/oracle as sysdba; set echo off; CREATE TABLE frag21(a NUMBER)  TABLESPACE data02; CREATE TABLE frag22(a NUMBER)  TABLESPACE data02; CREATE TABLE frag23(a NUMBER)  TABLESPACE data02; CREATE TABLE frag24(a NUMBER)  TABLESPACE data02; CREATE TABLE frag25(a NUMBER)  TABLESPACE data02; DROP TABLE frag21; DROP TABLE frag22; DROP TABLE frag23; DROP TABLE frag24; DROP TABLE frag25;

SQL> @ cr_frags
SQL> select tablespace_name, total_blocks, blocks_coalesced,
  2         percent_blocks_coalesced
  3  from dba_free_space_coalesced
  4  where percent_blocks_coalesced <> 100 ;
  
SQL> alter tablespace data02 coalesce ;
SQL> select tablespace_name, total_blocks, blocks_coalesced,
  2         percent_blocks_coalesced
  3  from dba_free_space_coalesced
  4  where percent_blocks_coalesced <> 100 ;
  
8. 추가적인 extent를 할당하려고 시도할 때 공간이 부족하여 에러를 발생시킬수 있는 세그먼트를 나열하십시오.
SQL> select s.segment_name, s.segment_type,
  2         s.tablespace_name, s.next_extent
  3  from dba_segments s
  4  where exists
  5        (select 1 from dba_free_space f
  6         where s.tablespace_name = f.tablespace_name
  7         having max(f.bytes) <= s.next_extent) ;