Segment Shrink in 10g

DML activities like updates and deletes will make segements have chunks of free space within their data blocks.This will make the particular object fragmented
This can lead to very poor performance because select statements that does full table scans will have to scan more blocks to retrieve data.

In oracle 9i, to reclaim free space we can do the following
1.Drop the table,recreate it and then load back the data or
2.Use the 'ALTER TABLE MOVE' command to move the table to a new tablespace
3.Online table reorganization

In Oracle 10g, you are able to shrink segments with commands directly.

During this operation
-Segment data is compacted.
-High-water mark (HWM) is brought down.
-Unused space is released back to the tablespace containing the segment.

e.g
-- Enable row movement.
ALTER TABLE ABU.ORDERS ENABLE ROW MOVEMENT;

-- Recover space and amend the high water mark (HWM).
ALTER TABLE ABU.ORDERS SHRINK SPACE;

-- Recover space, but don't amend the high water mark (HWM).
ALTER TABLE ABU.ORDERS SHRINK SPACE COMPACT;

-- Recover space for the object and all dependant objects.
ALTER TABLE ABU.ORDERS SHRINK SPACE CASCADE;

The shrink is accomplished by moving rows between blocks, hence the requirement for row movement to be enabled for the shrink to take place.
For these operations to work, the objects needs to be in a tablespace with automatic segment-space management enabled.
You need not make a tablespace read-only, offline, or autoextensible before shrinking any of the segments in that tablespace.

Temporary tablespace groups in 10g

The main idea behind temporary tablespace groups is to improve the scalability of operations that require more temporary space. This is a new feature introduced in 10g.

There must be a minimum of one tablespace in a temporary tablespace group and groups cannot exist without at least one temporary tablespace.

The group will not exist when the last tablespace member is removed from it. For a user to be able to use the group, assign the tablespace group name when assigning temporary tablespace to users.

There is no explicit limit on the maximum number of tablespaces that are contained in a group.

EXAMPLES
– create a temporary tablespace without a group

CREATE TEMPORARY TABLESPACE temp1
TEMPFILE ‘C:/oracle/10g/temp1.dbf ‘ SIZE 250M
TABLESPACE GROUP ‘’; -- same as not specifying a group



– create a temporary tablespace group with one tablespace (TEMP1) in it:

CREATE TEMPORARY TABLESPACE TEMP1
TEMPFILE ‘C:/oracle/10g/temp1.dbf ‘ SIZE 250M
TABLESPACE GROUP TEMPGRP1;

If the tablespace group does not already exist, then Oracle Database creates it during execution of this statement:



– user TEMPGRP1 as the default temporary tablespace for all new users in the database:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPGRP1;


- Make user ALI use the temporary tablespace group

ALTER USER ALI TEMPORARY TABLESPACE TEMPGRP1;