Oracle Logical Storage Architecture
Oracle's logical storage management consists of four hierarchical structures: tablespaces, segments, extents, and blocks.
Database Blocks
The block is Oracle's smallest storage unit, typically 8KB in size. All I/O operations occur at the block level. The block size is determined by the DB_BLOCK_SIZE parameter:
SQL> show parameter db_block_size
NAME TYPE VALUE
-------------------- ----------- ---------
db_block_size integer 8192
Extents
An extent comprises multiple contiguous blocks and represents Oracle's basic unit of space allocation. When a table requires additional space, Oracle allocates new extents rather than individual blocks. The DBA_EXTENTS view provides detailed information about extent allocation.
Segments
A segment consists of one or more extents and corresponds to a database object (table, index, etc.). When creating an object, Oracle automatically assigns a segment. The DBA_SEGMENTS view contains segment metadata.
Tablespaces
Tablespaces represent the highest logical storage level, containing one or more datafiles. Datafiles can span multiple physical storage devices. Object placement within specific datafiles cannot be controlled, necessitating careful tablespace design for I/O distribution. Tables and indexes should typically reside in separate tablespaces for optimal performance.
High Water Mark (HWM) Concept
When a table is created, its HWM is at the lowest position. As data is inserted, the HWM rises. However, DELETE operations do not lower the HWM - it only increases during space allocation. Oracle typically increases the HWM by 5 blocks at a time.
Full table scans read all blocks below the HWM, regardless of whether they contain data. This behavior explains why a table with few rows after massive deletions can still exhibit slow query performance - the scanner still processes all blocks up to the HWM.
TRUNCATE operations reset the HWM to zero, making it preferable over DELETE for clearing large temporary tables.
HWM Variations
In Manual Segment Space Management (MSSM), segments maintain a single HWM. Oracle 9iR1 introduced Automatic Segment Space Management (ASSM), which adds a Low HWM concept. In ASSM, newly allocated blocks aren't formatted until first accessed, necessitating a separate marker to track formatted blocks. The Low HWM is always less than or equal to the High HWM.
HWM Reduction Techniques
ALTER TABLE MOVE
ALTER TABLE table_name MOVE;
ALTER TABLE table_name MOVE TABLESPACE new_tablespace;
MOVE operations relocate data to new blocks, effectively reducing the HWM. Indexes must be rebuilt after MOVE operations. The released space remains available only for the same table.
ALTER TABLE SHRINK SPACE (Oracle 10g+)
ALTER TABLE table_name ENABLE ROW MOVEMENT;
ALTER TABLE table_name SHRINK SPACE;
ALTER TABLE table_name SHRINK SPACE CASCADE;
The SHRINK command compacts data and lowers the HWM. CASCADE maintains index synchronization during the process. SHRINK has limitations: unsupported for clusters, LOB segments, function-based indexes, bitmap join indexes, index-organized tables, or compressed tables.
Table Reconstruction
CREATE TABLE temp_table AS SELECT * FROM original_table;
DROP TABLE original_table;
RENAME temp_table TO original_table;
Export/Import
Using Oracle's EXP/IMP utilities recreates the table structure and data.
DEALLOCATE UNUSED
ALTER TABLE table_name DEALLOCATE UNUSED;
Releases space above the HWM but doesn't lower the HWM itself or free space below it.
TRUNCATE
TRUNCATE TABLE table_name;
Completely removes all data and resets the HWM to zero.
HWM Characteristics
- Delineates used versus unused blocks within a segment
- Increases during space allocation but never decreases during DELETE operations
- Metadata stored in the segment header
- Determines scan range for full table scans
- Direct-path inserts (APPEND hint) place data above the HWM
Practical Demonstration
Test Table Creation
SQL> CREATE TABLE test_data (id NUMBER);
Table created.
SQL> SELECT segment_name, blocks FROM dba_segments WHERE segment_name = 'TEST_DATA';
SEGMENT_NAME BLOCKS
-------------- --------
TEST_DATA 8
Data Insertion
SQL> BEGIN
FOR i IN 1..10000 LOOP
INSERT INTO test_data VALUES (i);
END LOOP;
COMMIT;
END;
/
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST_DATA');
SQL> SELECT table_name, num_rows, blocks FROM user_tables WHERE table_name = 'TEST_DATA';
TABLE_NAME NUM_ROWS BLOCKS
----------- --------- ----------
TEST_DATA 10000 20
Delete Impact on HWM
SQL> DELETE FROM test_data;
10000 rows deleted.
SQL> COMMIT;
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST_DATA');
SQL> SELECT table_name, num_rows, blocks FROM user_tables WHERE table_name = 'TEST_DATA';
TABLE_NAME NUM_ROWS BLOCKS
----------- --------- ----------
TEST_DATA 0 20
TRUNCATE Impact on HWM
SQL> TRUNCATE TABLE test_data;
Table truncated.
SQL> SELECT segment_name, blocks FROM dba_segments WHERE segment_name = 'TEST_DATA';
SEGMENT_NAME BLOCKS
-------------- --------
TEST_DATA 8
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST_DATA');
SQL> SELECT table_name, num_rows, blocks FROM user_tables WHERE table_name = 'TEST_DATA';
TABLE_NAME NUM_ROWS BLOCKS
----------- --------- ----------
TEST_DATA 0 0
SHRINK vs MOVE Comparison
SHRINK Operation
ALTER TABLE test_data ENABLE ROW MOVEMENT;
ALTER TABLE test_data SHRINK SPACE CASCADE;
- Compacts data within existing blocks
- Generates significant undo/redo
- Online operation with DML availability
- Doesn't release space to tablespace
MOVE Operation
ALTER TABLE test_data MOVE;
ALTER INDEX index_name REBUILD;
- Relocates data to new segments
- Requires temporary space
- Invalidates indexes (rebuild required)
- More efficient for large operations
LOB Segment Handling
For tables with LOB columns:
ALTER TABLE schema.table_name MOVE
TABLESPACE new_tablespace
LOB (lob_column) STORE AS (TABLESPACE lob_tablespace);
Index status verification after MOVE:
SELECT index_name, status FROM dba_indexes
WHERE table_name = 'TABLE_NAME';
Rebuild invalid indexes:
ALTER INDEX index_name REBUILD ONLINE;