Oracle alter table shrink space commands

Home
E-mail Us
Oracle Articles
New Oracle Articles

Oracle Training
Oracle Tips
Oracle Forum
Class Catalog

Remote DBA
Oracle Tuning
Emergency 911
RAC Support
Apps Support
Analysis
Design
Implementation
Oracle Support

SQL Tuning
Security
Oracle UNIX
Oracle Linux
Monitoring
Remote support
Remote plans
Remote services
Application Server
Applications
Oracle Forms
Oracle Portal
App Upgrades
SQL Server
Oracle Concepts
Software Support
Remote Support
Development
Implementation

Consulting Staff
Consulting Prices
Help Wanted!

Oracle Posters
Oracle Books
Oracle Scripts
Ion
Excel-DB

Don Burleson Blog

Oracle table shrink space commands

Oracle Database Tips by Donald Burleson

Oracle has several commands to reclaim unused disk space for objects (tables and indexes). Using the "alter table xxx shrink space compact" command also has the benefit of making full-table scans run faster, as less block accesses are required. With standard Oracle tables, you can reclaim space with the "alter table shrink space" command:

SQL> alter table mytable enable row movement;
Table altered

SQL> alter table mytable shrink space;
Table altered

SQL> Alter table mytable shrink space cascade;
Table altered.

Note that the cascade option option will only recover space for the object and all dependent objects, and it does not alter the indexes.

However if you are deleting a large percentage of rows and you do not expect to re-use the space, then I would consider rebuilding the indexes to reclaim free space within the index.

Note: Using the "alter table xxx shrink space" command will change the clustering_factor for the primary index on the table. This, in turn, could cause dynamic statistics to generate different SQL execution plans. Hence, you should rebuild or coalesce your indexes whenever you issue the alter table shrink space command. This will ensure that no SQL changes plans.

There is also a performance issue with the shrink space command because the operation cannot be parallelized. If the table needs reorganization and the table is very large, consider the dbms_redefinition approach to shrinking space.

Finding tables and indexes for shrinking

The Oracle segment advisor will recommend tables that will benefit from shrinking and indexes that require rebuilding (to reclaim space). You can see the recommendations by querying dba_advisor_findings. See my notes on the segment advisor here:

Alter table shrink space compact

With the introduction of the alter table xxx shrink space compact syntax, the DBA gets a powerful tool for effective and easy database space management. However, the DBA needs to know what data segments experience high space waste in order to reclaim free space to the database and shrink segments. The awr_list_seg_block_space.sql script below reports percentages of free space for data segments:

< awr_list_seg_block_space.sql

drop type BlckFreeSpaceSet;

drop type BlckFreeSpace;

create type BlckFreeSpace as object

(

seg_owner varchar2(30),

seg_type varchar2(30),

seg_name varchar2(100),

fs1 number,

fs2 number,

fs3 number,

fs4 number,

fb number

);

create type BlckFreeSpaceSet as table of BlckFreeSpace;

create or replace function BlckFreeSpaceFunc (seg_owner IN varchar2, seg_type in varchar2 default null) return BlckFreeSpaceSet

pipelined

outRec BlckFreeSpace := BlckFreeSpace(null,null,null,null,null,null,null,null);

fs1_b number;

fs2_b number;

fs3_b number;

fs4_b number;

fs1_bl number;

fs2_bl number;

fs3_bl number;

fs4_bl number;

fulb number;

fulbl number;

u_b number;

u_bl number;

begin

for rec in (select s.owner,s.segment_name,s.segment_type from dba_segments s where owner = seg_owner and segment_type = nvl(seg_type,segment_type) )

SEE CODE DEPOT FOR FULL SCRIPT

dbms_space.space_usage(

segment_owner => rec.owner,

segment_name => rec.segment_name,

segment_type => rec.segment_type,

fs1_bytes => fs1_b,

fs1_blocks => fs1_bl,

fs2_bytes => fs2_b,

fs2_blocks => fs2_bl,

fs3_bytes => fs3_b,

fs3_blocks => fs3_bl,

fs4_bytes => fs4_b,

fs4_blocks => fs4_bl,

full_bytes => fulb,

full_blocks => fulbl,

unformatted_blocks => u_bl,

unformatted_bytes => u_b

);

outRec.seg_owner := rec.owner;

outRec.seg_type := rec.segment_type;

outRec.seg_name := rec.segment_name;

outRec.fs1 := fs1_bl;

outRec.fs2 := fs2_bl;

outRec.fs3 := fs3_bl;

outRec.fs4 := fs4_bl;

outRec.fb := fulbl;

Pipe Row (outRec);

end loop;

return;

end;

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

��