Monday, April 20, 2009

how to reorganize segments online

In an Oracle 10g database, for data segments contained in a tablespace which is created with EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO, the storage can be adjusted online:

ALTER TABLE employees ENABLE ROW MOVEMENT;

ALTER TABLE employees SHRINK SPACE;

or

ALTER TABLE employees SHRINK SPACE CASCADE; --CASCADE includes indexes on the table;

To check if the table is a cadicate:

select tablespace_name, extent_management, segment_space_management

from dba_tablespaces

where tablespace_name = 'TS_EXP'

tablespace_name extent_management segment_space_management

TS_EXP LOCAL AUTO

No comments:

Post a Comment