Click here to subscribe to Ora! Ora! Oracle (Chinese edition) 竟然还有中文版本。
注意到本期介绍了Oracle 10g features的alter table .. shrink space 特性。可惜没有10g环境，无法测试他的性能和对索引维护的影响。
1. Table
2. Index
3. Materialized view
4. Materialized view log
我倒是对这个alter Materialized view log.. shrink space 特别期待 ：）
Oracle 10g features
When you delete large amount of data from a table, what do you do to reduce high water mark (HWM)?
The answers may be:
1. exp/imp
2. alter table ... move
In addition to the above, alter table ... shrink space command has been newly introduced in Oracle 10g.
This shrink command enables recovering space and amending the high water mark. You can use this command to the following objects:
1. Table
2. Index
3. Materialized view
4. Materialized view log
The objects need to be stored in locally managed tablespace with automatic segment space management.
Now, I issue alter table .. shrink space command.
Testing environment
Linux 2.4.9-e.24enterprise
Oracle10g EE Release 10.1.0.2.0
Using the shrink command
SQL> select owner,segment_name,bytes,blocks,extents from dba_segments
where segment_name = 'EMP';
OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS
----- ------------ ---------- ---------- ----------
SCOTT EMP 53477376 6528 66
SQL> alter table emp shrink space;
ORA-10636: ROW MOVEMENT is not enabled
Execution of the shrink command requires row movement. Thus, it is necessary to enable row movement in advance.
SQL> alter table emp enable row movement;
Table altered.
SQL> alter table emp shrink space;
Table altered.
SQL> select owner,segment_name,bytes,blocks,extents from dba_segments
where segment_name = 'EMP';
OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS
----- ------------ ---------- ---------- ----------
SCOTT EMP 65536 8 1
This looks like alter table ... move command, but actually it is different in Oracle 10g.
alter table ... shrink space VS. alter table ... move
(1)Shrink command can be executed online
SES1>alter table emp move;
SES2>select l.oracle_username,o.name objname,l.locked_mode from
v$locked_object l,obj$ o where l.object_id=o.obj#;
ORACLE_USERNAME OBJNAME LOCKED_MODE
------------------------- ------------------------------ -----------
SCOTT EMP 6
SES2>select rownum from scott.emp where rownum=1 for update nowait;
ORA-00054: Resource busy, NOWAIT is specified.
SES1> alter table emp shrink space;
SES2> select l.oracle_username,o.name objname,l.locked_mode from
v$locked_object l,obj$ o where l.object_id=o.obj#;
ORACLE_USERNAME OBJNAME LOCKED_MODE
------------------------- ------------------------------ -----------
SCOTT EMP 3
SES2>select rownum from scott.emp where rownum=1 for update nowait;
ROWNUM
----------
1
The difference between shrink command and move command is that the shrink command does not lock the object in exclusive mode.
move command is executed with LOCKED_MODE=6 (exclusive mode). shrink command, on the other hand, is executed with LOCKED_MODE=3 (row lock mode), which enables recovering without stopping operations.
Segment is shrunk even though the command is stopped in the middle of the execution
Shrink SCOTT.EMP
*dbms_space.space_usage procedure to determine the value that is
not yet shrunk
Segment Owner = SCOTT
Segment Name = EMP
Unformatted Blocks = 16
0 - 25% free blocks= 0
25- 50% free blocks= 6366
50- 75% free blocks= 0
75-100% free blocks= 36
Full Blocks = 0
*Forcefully terminate while executing the shrink command
SQL> alter table emp shrink space;
ORA-00028: your session has been killed
*Determine the value after the command is forcefully terminated
Segment Owner = SCOTT
Segment Name = EMP
Unformatted Blocks = 16
0 - 25% free blocks= 1
25- 50% free blocks= 2808
50- 75% free blocks= 0
75-100% free blocks= 1004
Full Blocks = 2553
*Execute the shrink command again and determine the value
after the execution is completed properly
Segment Owner = SCOTT
Segment Name = EMP
Unformatted Blocks = 0
0 - 25% free blocks= 1
25- 50% free blocks= 2
50- 75% free blocks= 0
75-100% free blocks= 0
Full Blocks = 4567
Take a look at the changes in the value determined from dbms_space.space_usage procedure.
1. Before executing the shrink command
There are no full blocks, which means that most of the blocks have sufficient free space.
2. Forcefully terminating the process while the command is executed
**% free blocks decrease and full blocks increase instead. This means that the shrink process is being executed.
3. After executing the shrink command
There are few **% free blocks. Also, the total percentage of the blocks drops to 70%, which means that the high water mark is reduced.
Even when you don't have much time to do the database maintenance, you can repeat the process above several times to recover space.
That's it for today.
Takuya Kishimoto