Extent와 Segment

SQL> show parameter db_block_size;

NAME     TYPE VALUE

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

db_block_size     integer 8192

SQL> create tablespace test_extent

  2  datafile '/app/oracle/oradata/testdb/ex_test01.dbf' size 5M;

Tablespace created.

SQL> create table ex_table01

  2  (no number, name varchar2(10)) tablespace test_extent;

Table created.

SQL> @extend

SP2-0246: Illegal FORMAT string "15"

TABLESPACE_NAME EXTENT_ID     BLOCKS  BYTES

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

TEST_EXTENT 0   8  65536

SQL> begin

  2  for i in 1..10000 loop

  3  insert into ex_table01 values(i,'AAAA');

  4  end loop;

  5  commit;

  6  end;

  7  /

PL/SQL procedure successfully completed.

SQL> @extend

TABLESPACE_NAME EXTENT_ID     BLOCKS  BYTES

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

TEST_EXTENT 0   8  65536

TEST_EXTENT 1   8  65536

TEST_EXTENT 2   8  65536

SQL> !cat extend.sql

col tablespace_name for a15

select tablespace_name, extent_id, blocks, bytes

from user_extents

where segment_name='EX_TABLE01';

EXTEND를 수동으로 추가 할당

SQL> alter table ex_table01

  2  allocate extent ( size 100K

  3  datafile '/app/oracle/oradata/testdb/ex_test01.dbf');

Table altered.

SQL> @extend

TABLESPACE_NAME EXTENT_ID     BLOCKS  BYTES

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

TEST_EXTENT 0   8  65536

TEST_EXTENT 1   8  65536

TEST_EXTENT 2   8  65536

TEST_EXTENT 3   8  65536

TEST_EXTENT 4   8  65536

EXTEND 반환(한번 사용한 경우라면 반환 안됨)

SQL> alter table ex_table01 deallocate unused;

Table altered.

SQL> @extend

TABLESPACE_NAME  EXTENT_ID     BLOCKS   BYTES

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

TEST_EXTENT 0    8   65536

TEST_EXTENT 1    8   65536

TEST_EXTENT 2    8   65536