Storage Structures

36.1 Tablespaces

Auxiliary (AWR, OEM, Text, etc.) Sorts, hash joins, GTTs USERS / application tablespaces

Smallfile vs bigfile tablespaces. Oracle Managed Files (OMF).

Default permanent and temporary tablespaces. Temporary tablespace groups.

Shrink tablespace (23ai) and ALTER TABLESPACE ... SHRINK SPACE for temp.

CREATE TABLESPACE app_data
  DATAFILE '/u02/oradata/ORCL/app_data01.dbf' SIZE 1G AUTOEXTEND ON NEXT 256M MAXSIZE 32G
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
  SEGMENT SPACE MANAGEMENT AUTO;

CREATE BIGFILE TABLESPACE big_data DATAFILE SIZE 100G;      -- with OMF (DB_CREATE_FILE_DEST)
CREATE TEMPORARY TABLESPACE temp2 TEMPFILE SIZE 10G;
CREATE UNDO TABLESPACE undotbs2 DATAFILE SIZE 5G;

ALTER TABLESPACE app_data ADD DATAFILE SIZE 1G;
ALTER DATABASE DATAFILE '/u02/.../app_data01.dbf' RESIZE 2G;
ALTER TABLESPACE app_data READ ONLY;
ALTER TABLESPACE app_data OFFLINE;
ALTER DATABASE MOVE DATAFILE '/old/path.dbf' TO '/new/path.dbf';     -- online (12c)
ALTER USER hr DEFAULT TABLESPACE app_data QUOTA UNLIMITED ON app_data;
DROP TABLESPACE app_data INCLUDING CONTENTS AND DATAFILES;

SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024) mb FROM dba_data_files GROUP BY tablespace_name;
SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024) free_mb FROM dba_free_space GROUP BY tablespace_name;
SELECT * FROM dba_tablespace_usage_metrics;

36.2 Segments, Extents and Blocks

Segment types: table, index, LOB, undo, temporary, partition, cluster.

Extents: contiguous blocks allocated to a segment (locally managed: AUTOALLOCATE or UNIFORM).

Data block: header, table directory, row directory, free space, row data. Sizes 2K–32K (8K default). DB_nK_CACHE_SIZE supports non-default block sizes.

Block parameters: PCTFREE (space reserved for updates), PCTUSED (manual segment space management only), INITRANS (ITL slots).

ASSM (Automatic Segment Space Management, bitmap-based) vs MSSM (freelists).

High-water mark (HWM): full scans read up to the HWM. Reset it with ALTER TABLE ... MOVE or SHRINK SPACE.

Row chaining (the row is bigger than a block) vs row migration (an update no longer fits, so the row moves and leaves a pointer).

Deferred segment creation (11gR2): no segment until the first insert.

SELECT segment_name, segment_type, bytes/1024/1024 mb, extents FROM user_segments ORDER BY bytes DESC;
ALTER TABLE orders ENABLE ROW MOVEMENT;
ALTER TABLE orders SHRINK SPACE CASCADE;
ALTER TABLE orders MOVE ONLINE TABLESPACE app_data;     -- 12.2 online move
EXEC DBMS_SPACE.SPACE_USAGE(...);  -- Segment Advisor: DBMS_ADVISOR