To see the fragmentation within a tablespace, you can run the script shown in Listing 7.4.

Listing 7.4 tsfrag.sql shows a tablespace map.

REM written by Don Burleson

SET LINESIZE 132;
SET PAGES 999;

REM SET FEEDBACK OFF;
REM SET VERIFY OFF;
REM SET HEADING OFF;
REM SET TERMOUT OFF;

BREAK ON file_id SKIP PAGE;
BREAK ON FREE SKIP 1;
COMPUTE SUM OF KB ON FREE;

SPOOL tsfrag;

COLUMN owner           FORMAT a10;
COLUMN segment_name    FORMAT a10;
COLUMN tablespace_name FORMAT a14;
COLUMN file_id         FORMAT 99 heading ID;
COLUMN end             FORMAT 999999;
COLUMN KB              FORMAT 9999999;
COLUMN begin           FORMAT 999999;
COLUMN blocks          FORMAT 999999;

SELECT
  tablespace_name,
   file_id,
   owner,
   segment_name,
   block_id begin,
   blocks,
   block_id+blocks-1 end,
   bytes/1024 KB,
   '' free
FROM sys.dba_extents
WHERE tablespace_name NOT IN ('RBS','SYSTEM','TEMP','TOOLS','USER')
UNION
SELECT
   tablespace_name,
   file_id,
   '' owner,
   '' segment_name,
   block_id begin,
   blocks,
   block_id+blocks+1 end,
   bytes/1023 KB,
   'F' free
FROM sys.dba_free_space
WHERE tablespace_name NOT IN ('RBS','SYSTEM','TEMP','TOOLS','USER')
ORDER BY 1, 2, 5
;

/

SPOOL OFF;

!cat tsfrag.lst

Listing 7.5 shows the the output of tsfrag.sql.

Listing 7.5 The results of the tsfrag.sql script.

TS_NAME        ID OWNER    SEGMENT_NA BEGIN  BLOCKS   END       KB   F
-------------- -- -------- ---------- ------ ------ ------ -------- ---
MASTER3_STAT_1 15  RPT     ZIP_UPS_ZO     2       5      6       20
                            NE_XREF
MASTER3_STAT_1 15  RPT     ACHG_TY        7       2      8        8
MASTER3_STAT_1 15  RPT     BUSN_UNIT      9      35     43      140
MASTER3_STAT_1                           44       2     45        8  F
MASTER3_STAT_1 15  RPT     PLANT         46       3     48       12
MASTER3_STAT_1                           49      10     58       40  F
MASTER3_STAT_1  15 RPT     RPT_TABLES    59       4     62       16
MASTER3_STAT_1  15 RPT     ZONE          63       2     64        8
                                                               -------- *
                                                                252  s

MASTER3_STAT_1  15                       65    1216    1282    4869  F
                                                               -------- *
                                                               4869  s

In Listing 7.5, we see two discontiguous chunks of free space, as indicated by the “F” column on the far right-hand side of the report. Here, we see that blocks 44–45 are free, as are blocks 49–58.

Oracle version 7.3 will automatically detect and coalesce tablespaces—provided that all affected tablespaces’ default storage clauses have PCTINCREASE set to 1. The coalesce mechanism for tablespace coalescing is the SMON process, which periodically wakes up to coalesce free space. Between SMON coalesces, any transaction that requires an extent that is larger than any available free extent, will trigger a coalesce on the tablespace to move all free space into a single chunk—hopefully making room for the required extent.

Also in Oracle 7.3 is a new dictionary view, DBA_FREE_SPACE_COALESCED, which provides details about the number of extents, bytes, and blocks that have been coalesced in each tablespace.

The following query will display coalesce information:

SELECT
    tablespace_name,
    bytes_coalesced,
    extents_coalesced,
    percent_extents_coalesced,
    blocks_coalesced,
    percent_blocks_coalesced
FROM
    sys.dba_free_space_coalesced
ORDER BY
    tablespace_name;

To change all tablespaces’ PCTINCREASE from 0 to 1 so that tables will automatically coalesce, run the script in Listing 7.6.

Listing 7.6 coalesce.sql changes all tablespaces with PCTINCREASE not equal to 1.

REM written by Don Burleson

SET LINESIZE 132;
SET PAGESIZE 999;
SET FEEDBACK OFF;
SET VERIFY OFF;
SET HEADING OFF;
SET TERMOUT OFF;

SPOOL COALESCE;

SELECT
   'alter tablespace '
    ||tablespace_name||
   ' storage ( pctincrease 1 );'
FROM dba_tablespaces
WHERE
 tablespace_name NOT IN ('RBS','SYSTEM','TEMP','TOOLS','USER')
AND
 pct_increase = 0;

SPOOL OFF;

SET FEEDBACK ON;
SET VERIFY ON;
SET HEADING ON;
SET TERMOUT ON;

@coalesce.lst

Manual Tablespace Coalesce

If you detect that a single tablespace has fragmented, you can quickly coalesce it with the following procedures:

1.  Alter session by retrieving the tablespace number from sys.ts$:

SELECT * FROM sys.ts$:

2.  IN SQL*DBA, issue the following command:

ALTER SESSION SET EVENTS immediate trace name coalesce level
&tsnum;

(where tsnum is the tablespace number from step 1)

3.  Manual coalesce, from SQL*Plus enter:

ALTER TABLESPACE <xxxx> COALESCE;

Full Export/Import

While many vendor tools can aid in a database reorganization, many Oracle administrators will write a script that will export all tables within the tablespace, re-create the tablespace, and then import all of the tables and indexes to compress them into a single extent. However, be forewarned that referential integrity may make it difficult to drop a tablespace. This occurs when one tablespace contains a table that has a foreign-key constraint from a table within another tablespace.

Most often, the administrator will reorganize the entire database, performing the following steps:

1.  Export the full database.

2.  Generate the create database script.

3.  Generate a list of data files to remove.

4.  Remove the data files.

5.  Create the database.

6.  Import the full database.

7.  Bounce the database and optionally turn on archive logging.