Table : 테이블의 공간 할당과 해제

실습: 테이블에 대한 공간 할당과 해제

SQL> analyze table hr.employees compute statistics;

analyze table hr.employees compute statistics

*

ERROR at line 1:

ORA-01502: index 'HR.EMP_NAME_IX' or partition of such index is in unusable state

SQL> select table_name, index_name, status from dba_indexes

  2  where table_name = 'EMPLOYEES';

TABLE_NAME                     INDEX_NAME                     STATUS

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

EMPLOYEES                      EMP_EMAIL_UK                   UNUSABLE

EMPLOYEES                      EMP_EMP_ID_PK                  UNUSABLE

EMPLOYEES                      EMP_DEPARTMENT_IX              UNUSABLE

EMPLOYEES                      EMP_JOB_IX                     UNUSABLE

EMPLOYEES                      EMP_MANAGER_IX                 UNUSABLE

EMPLOYEES                      EMP_NAME_IX                    UNUSABLE

6 rows selected.

SQL> alter index hr.emp_email_uk rebuild;

Index altered.

SQL> alter index hr.emp_emp_id_pk rebuild;

Index altered.

SQL> alter index hr.emp_department_ix rebuild;

Index altered.

SQL> alter index hr.emp_job_ix rebuild;

Index altered.

SQL> alter index hr.emp_manager_ix rebuild;

Index altered.

SQL> alter index hr.emp_name_ix rebuild;

Index altered.

SQL> select table_name, index_name, status from dba_indexes

  2  where table_name = 'EMPLOYEES';

TABLE_NAME                     INDEX_NAME                     STATUS

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

EMPLOYEES                      EMP_EMAIL_UK                   VALID

EMPLOYEES                      EMP_EMP_ID_PK                  VALID

EMPLOYEES                      EMP_DEPARTMENT_IX              VALID

EMPLOYEES                      EMP_JOB_IX                     VALID

EMPLOYEES                      EMP_MANAGER_IX                 VALID

EMPLOYEES                      EMP_NAME_IX                    VALID

6 rows selected.

SQL> analyze table hr.employees compute statistics;

Table analyzed.

SQL> select num_rows, blocks,empty_blocks as empty, avg_space, avg_row_len

  2  from dba_tables

  3  where owner = 'HR' and table_name = 'EMPLOYEES';

  NUM_ROWS     BLOCKS      EMPTY  AVG_SPACE AVG_ROW_LEN

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

       107          5          3       4129          71

#NUM_ROWS : table에 저장된 row의 수

#BLOCKS : 사용중인 block (H/W 왼쪽 block의 수)

#EMPTY : 사용한 적이 없는 block (H/W 오른쪽 block의 수)

#AVG_SPACE : 사용중인 block의 평균 빈 공간 (Byte)

#AVG_ROW_LEN : row의 평균 길이

SQL> alter table hr.employees allocate extent (size 1m);

Table altered.

SQL> analyze table hr.employees compute statistics;

Table analyzed.

SQL> select num_rows, blocks,empty_blocks as empty, avg_space, avg_row_len

  2  from dba_tables

  3  where owner = 'HR' and table_name = 'EMPLOYEES';

  NUM_ROWS     BLOCKS      EMPTY  AVG_SPACE AVG_ROW_LEN

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

       107          5        251       4129          71

SQL> alter table hr.employees deallocate unused;

Table altered.

SQL> select num_rows, blocks,empty_blocks as empty, avg_space, avg_row_len

  2  from dba_tables

  3  where owner = 'HR' and table_name = 'EMPLOYEES';

  NUM_ROWS     BLOCKS      EMPTY  AVG_SPACE AVG_ROW_LEN

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

       107          5        251       4129          71

SQL> analyze table hr.employees compute statistics;

Table analyzed.

SQL> select num_rows, blocks,empty_blocks as empty, avg_space, avg_row_len

  2  from dba_tables

  3  where owner = 'HR' and table_name = 'EMPLOYEES';

  NUM_ROWS     BLOCKS      EMPTY  AVG_SPACE AVG_ROW_LEN

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

       107          5          3       4129          71

SQL> select table_name, initial_extent, min_extents          

  2  from dba_tables

  3  where owner = 'HR' and table_name = 'EMPLOYEES';

TABLE_NAME                     INITIAL_EXTENT MIN_EXTENTS

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

EMPLOYEES                               65536           1

SQL> select tablespace_name, block_size, initial_extent, min_extents

  2  from dba_tablespaces

  3  where tablespace_name = 'EXAMPLE';

TABLESPACE_NAME      BLOCK_SIZE INITIAL_EXTENT MIN_EXTENTS

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

EXAMPLE                    8192          65536           1

SQL>