While Oracle has been moving in the direction of locally managed extents in tablespaces several of us are still working with dictionary managed tablespaces. Here’s a good trick for adjusting table storage clauses in dictionary managed tablespaces.

Note: “compressing” extents is not a way to make data take up less space, but rather a way to take a fragmented (for lack of a better word) table and re-ogranize it into a more contiguous area of storage.

In the older dictionary managed method a storage clause in the table creation command controls how much space was initially allocated to that table (the initial extent) and how much would additionally be allocated if it filled the initial space (the next extent). If no storage clause was specified an often absurd default would be used.

This leaves us with two problems: First, we may have a very low value for the next extent. This can leave us creating extents of, for instance, 32KB even though each row may be 50KB.

Second, we may have tables which already contain a ridiculous number of extents and possibly chained rows. (Chained rows are a topic for another day.)

For my example I will use a table owned by system called session_audit.

To view the storage parameters on a table we can query the dba_tables view:

SQL > SELECT initial_extent, next_extent
FROM dba_tables
WHERE table_name='SESSION_AUDIT' AND owner='SYSTEM';

INITIAL_EXTENT NEXT_EXTENT
-------------- -----------
131072 65536

These numbers represent size in bytes, so dividing by 1024 we see that this table is configured to have an initial extent of 128KB and a next extent of 64KB.

We can also count the current number of extents whch make up this table with the following SQL command:

SQL> SELECT count(*) FROM dba_extents
WHERE segment_name='SESSION_AUDIT' AND
OWNER='SYSTEM';

COUNT(*)
--------
77

It may seem the right method is to change the initial and next values with an ALTER TABLE command like this:

SQL> ALTER TABLE system.session_audit
STORAGE (INITIAL 10M NEXT 5M);
ALTER TABLE SYSTEM.SESSION_AUDIT
*
ERROR at line 1:
ORA-02203: INITIAL storage options not allowed

If we dropped the change to the initial extent we could use this command to change the next extent. If we want to update the initial extent we will need to get sneaky. We could create a new table with the desired storage settings, move the data, then rename the new table to the old name, but this would cause the table to be temporarily unavailable and have the unfortunate side-effect of invalidating any of this tables dependencies. Alternately we could export and import the table but this would lead to the same problem.

Instead, using the ‘ALTER TABLE MOVE’ command, we can tell Oracle to move this table into a different tablespace while at the same time sneaking a new storage clause in.

SQL> ALTER TABLE system.session_audit
MOVE TABLESPACE system
STORAGE (INITIAL 10M NEXT 5M);

Table altered.

We now have our table in the new tablespace with the desired storage clause. This also creates an initial extent of the specified size instead of the smaller extents we had previously. If we want to move the table back to it’s original tablespace we can just issue the same command with the original tablespace.

NOTE: I was also able to tell Oracle to “move” the table to the same tablespace it was originally in. This had the same affect of changing the storage clause and compressing the extents without needing a foster tablespace.

We can now re-check the number of extents and storage clause for the table:

SQL> SELECT count(*) FROM dba_extents
WHERE segment_name='SESSION_AUDIT' AND
OWNER='SYSTEM';

COUNT(*)
--------
1

SQL > SELECT initial_extent, next_extent
FROM dba_tables
WHERE table_name='SESSION_AUDIT' AND owner='SYSTEM';

INITIAL_EXTENT NEXT_EXTENT
-------------- -----------
10485760 5242880

We see that the number of extents currently being used by the table has been reduced to 1 and our next extent will be 5MB in size.

oracle, database, database administrator, oracle database, dba, sql, database tuning