본문 바로가기
스터디북

[10/16] 실습 - 2 Shrink Space

by 파이어볼러 2015. 10. 16.

SYS@orcl>grant select on hr.employees to scott;


Grant succeeded.


SYS@orcl>conn scott/tiger;

Connected.

SCOTT@orcl>create table employees as select * from hr.employees;


Table created.


SCOTT@orcl>insert into employees select * from employees;


107 rows created.


SCOTT@orcl>/


214 rows created.


SCOTT@orcl>/


428 rows created.


SCOTT@orcl>/


856 rows created.


SCOTT@orcl>/


1712 rows created.


SCOTT@orcl>/


3424 rows created.


SCOTT@orcl>/


6848 rows created.


SCOTT@orcl>/


13696 rows created.


SCOTT@orcl>/


27392 rows created.


SCOTT@orcl>select count(distinct dbms_rowid.rowid_block_number(rowid)) blocks from employees;

    BLOCKS
----------
       556

SCOTT@orcl>select blocks, extents from user_segments where segment_name='EMPLOYEES';

    BLOCKS    EXTENTS
---------- ----------
       640         20

SCOTT@orcl>delete employees where department_id = 50;

23040 rows deleted.

SCOTT@orcl>commit;

Commit complete.

SCOTT@orcl>alter table employees shrink space compact;
alter table employees shrink space compact
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled


SCOTT@orcl>alter table employees enable row movement;

Table altered.

SCOTT@orcl>alter table employees shrink space compact;

Table altered.

SCOTT@orcl>select count(distinct dbms_rowid.rowid_block_number(rowid)) blocks from employees;

    BLOCKS
----------
       329

SCOTT@orcl>select blocks, extents from user_segments where segment_name='EMPLOYEES';

    BLOCKS    EXTENTS
---------- ----------
       640         20

SCOTT@orcl>alter table employees shrink space;


Table altered.


SCOTT@orcl>select blocks, extents from user_segments where segment_name='EMPLOYEES';


    BLOCKS    EXTENTS

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

       344         18


=======================================

조회하는 또 다른 방법

=======================================


SYSTEM@orcl>create table t_shrink tablespace users

  2  as select * from hr.employees;


Table created.


SYSTEM@orcl>insert into t_shrink select * from t_shrink;


107 rows created.


SYSTEM@orcl>/


214 rows created.


SYSTEM@orcl>/


428 rows created.


SYSTEM@orcl>commit;


Commit complete.


SYSTEM@orcl>alter table t_shrink enable row movement;


Table altered.


SYSTEM@orcl>set serveroutput on

SYSTEM@orcl>!vi shr.sql



declare

  v_unformatted_blocks number;

  v_unformatted_bytes number;

  v_fs1_blocks number;

  v_fs1_bytes number;

  v_fs2_blocks number;

  v_fs2_bytes number;

  v_fs3_blocks number;

  v_fs3_bytes number;

  v_fs4_blocks number;

  v_fs4_bytes number;

  v_full_blocks number;

  v_full_bytes number;

begin

 dbms_space.space_usage ('SYSTEM', 'T_SHRINK', 'TABLE', v_unformatted_blocks,

  v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,

  v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);

 dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);

 dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);

 dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);

 dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);

 dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);

 dbms_output.put_line('Full Blocks = '||v_full_blocks);

  end;

/


SYSTEM@orcl>@shr.sql
Unformatted Blocks = 0
FS1 Blocks = 0
FS2 Blocks = 1
FS3 Blocks = 0
FS4 Blocks = 3
Full Blocks = 9

PL/SQL procedure successfully completed.

SYSTEM@orcl>alter table t_shrink shrink space;

Table altered.

SYSTEM@orcl>@shr.sql
Unformatted Blocks = 0
FS1 Blocks = 0
FS2 Blocks = 1
FS3 Blocks = 0
FS4 Blocks = 2
Full Blocks = 9

PL/SQL procedure successfully completed.


'스터디북' 카테고리의 다른 글

[10/16] 정리  (0) 2015.10.16
[10/16] 실습 - 3 resumable space allocation  (0) 2015.10.16
[10/16] 실습 - 1 RowMigration&&chaining  (0) 2015.10.16
[10/15] 실습 - 1 list_advise_repair failure  (0) 2015.10.15
[10/15] 정리  (0) 2015.10.15