oracle 10g shrink space

oracle 10g shrink space[@more@]

1. use show_space package to mark table aa,index ind_aa high-water
SQL> exec show_space(p_segname_1 => 'aa',p_space => 'auto');

Total Blocks............................120
Total Bytes.............................983040
Unused Blocks...........................4
Unused Bytes............................32768
Last Used Ext FileId....................17
Last Used Ext BlockId...................57377
Last Used Block.........................4

PL/SQL procedure successfully completed

SQL> exec show_space(p_segname_1 => 'ind_aa',p_type_1 => 'index',p_space => 'auto');

Total Blocks............................256
Total Bytes.............................2097152
Unused Blocks...........................64
Unused Bytes............................524288
Last Used Ext FileId....................19
Last Used Ext BlockId...................54409
Last Used Block.........................64

2. show table aa,index ind_aa high-water after shrink table aa without cascade

SQL> exec show_space(p_segname_1 => 'aa',p_space => 'auto');

Total Blocks............................104
Total Bytes.............................851968
Unused Blocks...........................6
Unused Bytes............................49152
Last Used Ext FileId....................17
Last Used Ext BlockId...................57361
Last Used Block.........................2

PL/SQL procedure successfully completed


SQL> exec show_space(p_segname_1 => 'ind_aa',p_type_1 => 'index',p_space => 'auto');

Total Blocks............................256
Total Bytes.............................2097152
Unused Blocks...........................89
Unused Bytes............................729088
Last Used Ext FileId....................19
Last Used Ext BlockId...................54409
Last Used Block.........................39

3. show table aa,index ind_aa high-water after shrink table aa with cascade

SQL> exec show_space(p_segname_1 => 'aa',p_space => 'auto');

Total Blocks............................104
Total Bytes.............................851968
Unused Blocks...........................6
Unused Bytes............................49152
Last Used Ext FileId....................17
Last Used Ext BlockId...................57361
Last Used Block.........................2

SQL> exec show_space(p_segname_1 => 'ind_aa',p_type_1 => 'index',p_space => 'auto');

Total Blocks............................152
Total Bytes.............................1245184
Unused Blocks...........................5
Unused Bytes............................40960
Last Used Ext FileId....................19
Last Used Ext BlockId...................54409
Last Used Block.........................19

first time table and index is not free space or take from high-water after delete the rows of table aa
second time we find out ind_aa that is not been shrinked after not used cascade option.
in the end ,shrink the space of table and index with cascade option.

E文比较烂，有什么语法问题请多多见谅

补充：

next test: shrink space compact clause
1. without compact clause
session 672: delete from aa where id between 100000 and 117000;
session 699: alter table aa shrink space;
session 885:
SELECT /*+no_merge(a) no_merge(b) */
(select username from v$session where sid=a.sid) blocker,
a.sid, 'is blocking',
(select username from v$session where sid=b.sid) blockee,
b.sid
from v$lock a,v$lock b
where a.block=1 and b.request>0
and a.id1=b.id1
and a.id2=b.id2;

output:

BLOCKER SID 'ISBLOCKING' BLOCKEE SID
------------------------------ ---------- ------------ ------------------------------ ----------
SILENCE 672 is blocking SILENCE 699

result: shrink operation will been locked by DML

2. with compact clause
session 672: delete from aa where id between 100000 and 117000;
session 699: alter table aa shrink space compact;
session 885: there are not any lock in the database

oracle document:
With Oracle Database 10g, you can now shrink segments directly, without taking the tablespaces or the objects offline.
The process of shrinking a segment includes two key phases:

* Segment data is compacted. Through a series of INSERT and DELETE statements
(during which DML-compatible locks are held on individual rows or blocks of the table),
the segment data is moved as far to the beginning of the segment as possible. Given that rowids change,
you must enable row movement and also disable any triggers based on rowid for table segments you want to shrink.

* High-water mark (HWM) is adjusted to an appropriate location (exclusive locks are held on the data at this point),
and unused space is deallocated from the segment, so it is available for the tablespace to reallocate to other objects
as needed.

You can also use the optional COMPACT clause in conjunction with the SHRINK SPACE clause
to perform just the first phase—the compacting—by itself, to defer the locking of the second phase,
for example, and then issue the SHRINK SPACE clause (without COMPACT) later to complete the process, as in

ALTER TABLE KIMBERLY.PRODUCT
SHRINK SPACE COMPACT

and later, run

ALTER TABLE KIMBERLY.PRODUCT
SHRINK SPACE


还鬼子写的东西工整。。。。

引用：

http://www.oracle.com/technology/oramag/oracle/05-may/o35tuning.html

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

转载于:http://blog.itpub.net/96123/viewspace-1011122/