2010. 3. 4. 11:30
실습: 테이블에 대한 공간 할당과 해제
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>
'Oracle 10g > 10g - 실습' 카테고리의 다른 글
| Constraints 실습 (제약 조건 테스트) (0) | 2010.03.05 |
|---|---|
| Table : Partitioned Table (0) | 2010.03.04 |
| Table : Row Migration과 Chaining (0) | 2010.03.04 |
| Undo Tablespace 생성과 관리 (9i) (0) | 2010.03.02 |
| Temporary Tablespace 생성과 Default Temporary Tablespace 지정 (0) | 2010.03.02 |