shrink space

from:

http://battleman.itpub.net/

shrink space

shrink space compcat 保持hwm
shrink space 回缩表，降低hwm
shrink space cascade 回缩表及相关索引。

Oracle 10g features- ---alter table ... shrink space

Ora Ora Oracle
Welcome to the world of Oracle enthusiasts
Free mail magazine for the people who want to know more about Oracle


Oracle 10g 
Hello. In this issue, I will start a new topic, analysis of new features in Oracle 10g.
New features and enhancements of Oracle 10g are geared towards grid computing which is an extension of the clustering features (i.e. Real Application Clusters.) Also, Oracle 10g enhances its automatic management functions.

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 wner = 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 wner = 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 wner = 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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/230160/viewspace-677806/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/230160/viewspace-677806/