Oracle provides a large amount of storage options to reduce the space required for data storage and increase the speed of retrieval.   This article provides a brief overview of space management in an Oracle 12c database with some general suggestions to increase flexibility and performance.

 

Local Managed Tablespaces:

You should always use locally managed tablespaces in Oracle, this is where bitmaps in the tablespace to manage all extents and not external in the data dictionary.  Any tablespace can be locally managed, including SYSTEM and SYSAUX.   Advantage of local managed tablespaces:

1. Allocation and deallocation of space is local managed, resulting in higher performance.
2. Local managed temporary tablespaces do not generate undo or redo.
3. The AUTOALLOCATE clause allows for automatically extent sizing.
4. Reduce contention on data dictionary resulting from no access by tablespace.
5. No need to coalesce free extents.

DBMS_SPACE_ADMIN is a package containing procedures specific to locally managed tablespaces.

Use the ‘EXTENT MANAGEMENT LOCAL AUTOALLOCATE’ is the clause to specify local management with auto select of extent sizes which is the default.  To have uniformed extent sizes you must specify, ‘UNIFORM’

Example:

 

CREATE TABLESPACE data1

DATAFILE ‘/ u02/ oracle/ data/ orcl01. dbf’

SIZE 50M

EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

 

CREATE TABLESPACE data1 DATAFILE ‘/ u02/ oracle/ data/ orcl01. dbf’ SIZE 50M;

 

Two methods you can use for segment space in locally managed tablespace.  Manual and Automatic:  Manual uses a free lists to manage free space in segments.   Automatic is the default method and it uses bitmaps to manage free space and is more efficient.   You can explicitly specify clause:  ‘SEGMENT SPACE MANAGEMENT AUTO’

Example:

 

CREATE TABLESPACE data1

DATAFILE ‘/ u02/ oracle/ data/ orcl01. dbf’

SIZE 50M

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

SEGMENT SPACE MANAGEMENT AUTO;

 

 

BASIC TABLE COMPRESSION

Is the lowest level compression and does not compress data in a table from INSERT/UPDATE.   Data is only compressed during bulk loads.   Basic compression is available in EE license and its format is the same as Advance Compression.  It is possible to convert from Basic to Advance, by simply changing the storage definition of the table or partition.

 

Advance Row Compression

Originally called OLTP Table compression in 11g, Advance Row compression compresses data regardless how it arrived.  It also minimizes overhead in write operations by eliminating duplicates in data blocks even across rows.  Advance Compression is an additional licensing option.

 

Hybrid Columnar Compression

Hybrid Columnar Compression (HCC) is a method of organizing data within a set of database blocks.   The underlying OS must support HCC and requires the use of Oracle Storage Exadata, Pillar Axiom or Sun ZFS Storage Appliance.  It stores multiple rows by columns, in what is call compression units.  Columns of like data are stored together similar data types, thus can achieve compression of x6 x15.

 

Tables created in tablespace with compression option will be compressed unless you specify no compression or another compression method in the CREATE TABLE clause.

 

Segment Shrink

Online segment shrink allows you to reclaim fragmented free space below high watermark in a segment.  With the following benefits:

1. Compacting provides better cache use.
2. Compact data has less data blocks to be scanned.

Segment shrink is performed online without DML operations interference.    Concurrent DML operations are blocked at end of shrink for space deallocation.  Indexes are maintained and usable after shrink.   Unused disk space is reclaimed above and below high water mark.  No additional space is needed during shrink.  By default shrink operation compact data, move the high water mark and release unused space.

Segment shrink requires row movement and thus requires you to enable row movement and disable rowed triggers.  Shrink operations can only happen on segments using locally managed tablespace with ASSM Automatic segment space management.  In ASSM all segment types are shrinkable except:

1. IOT mapping tables
2. Tables with rowed based materialized views.
3. Tables with function-based indexes.
4. SECUREFILE LOBs
5. Compressed tables.

 

Invoking Online Segment Shrink – SHRINK SPACE

You can shrink space in the following by use of SHRINK SPACE clause

1. Table – with ALTER TABLE..
2. Index-Organized Table – with ALTER TABLE
3. Index – with ALTER INDEX
4. Partition – with ALTER TABLE
5. Sub-partition – with ALTER TABLE
6. Materialized view – with ALTER MATERIALIZED VIEW
7. Materialized view log – with ALTER MATERIALIZED VIEW LOG

 

Two operations with SHRINK SPACE clause.

1. COMPACT – shrink operation divided into two sections. First phase:  defragments and compacts rows, does not reset high water mark or release space.  You can re-issue shrink operation without the COMPACT operation during off hours to complete deallocation of space and movement of high water mark.
2. CASCADE – This extends shrink operation to all dependent objects.

 

Example shrink a table and all of its dependent segments:

 

ALTER TABLE employees SHRINK SPACE CASCADE;

 

Example shrink a single partition of a partitioned table:

 

ALTER TABLE customers MODIFY PARTITION cust_P1 SHRINK SPACE;

 

 

 

Larry Catt

OCP