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>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;
/
'스터디북' 카테고리의 다른 글
[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 |