Segment shrink reorganizes an eligible segment in place so fragmented free space can become reusable tablespace capacity. Choose it after measuring the reclaimable space, the table's expected growth, and the workload cost. Deleting many rows can leave a large allocation, and those gaps can also serve later inserts without a reorganization.
Oracle DBA Lesson 62 Part 1 — Reclaim Space from a Sparse Table
Follow the same rows through the two phases
A heap table stores rows in blocks. Its high water mark records a boundary of segment use. Deleting rows leaves gaps below that boundary. Four blocks and three stable keys show the relationship. They are not an exact extent layout, and they are not a prediction of byte savings.
- Before shrink. Surviving rows with IDs 10, 20, and 30 occupy scattered blocks. The segment keeps its allocation.
- COMPACT. Rows move into earlier blocks. The example moves ID 20 from block 3 into block 1, and ID 30 from block 4 into block 2. Their primary-key values stay unchanged. A heap row that moves between blocks can receive a new physical
ROWID. The old high water mark and the allocation remain after this phase. - Complete SHRINK SPACE. Oracle adjusts the high water mark and releases eligible unused allocation to the tablespace. The physical data-file size stays the same. Freed capacity can serve other segments in the tablespace.
Oracle can compact the rows, adjust the high water mark, and release space with SHRINK SPACE in one operation. COMPACT is optional. It separates the data movement from the later completion phase, which is useful when that final phase should run at another time. The packed result is saved. The later command completes the operation, and workload that arrives in between can change how much work remains.
Queries and DML can run during the data-movement phase. Concurrent DML is blocked briefly during final deallocation. Lock acquisition, concurrent work, and the object's size affect elapsed time, so plan and test the final phase for the workload. An online operation still has resource and concurrency costs.
See managing space for schema objects for the online shrink phases, maintained indexes, and the brief lock at final deallocation, and logical storage structures for blocks, extents, segments, ASSM, and physical location.
Eligibility comes before row movement
This case is Oracle Database 19c: an ordinary uncompressed, nonpartitioned heap table in a locally managed tablespace that uses automatic segment space management (ASSM). Confirm the release and RU, the deployment, the feature rights, the object definition, and the documented restrictions. The presence of the command is not an entitlement check. The licensing documentation's online table organization row describes MOVE ONLINE. That row alone does not determine segment-shrink entitlement.
The tablespace must show local extent management and automatic segment space management. Replace the placeholder with the tablespace name:
SELECT tablespace_name, extent_management, segment_space_management
FROM dba_tablespaces
WHERE tablespace_name = '<TARGET_TABLESPACE>';Require LOCAL and AUTO. Those values establish the tablespace properties. They do not clear every object restriction.
Restrictions include function-based, domain, and bitmap join indexes; clustered tables and LONG columns; rowid-based materialized-view dependencies and master tables of ON COMMIT materialized views; and special object and LOB restrictions. Read the current SQL reference for the specific object. Basic, warehouse, and archive table compression are excluded from this 19c shrink operation. Advanced Row Compression has supported shrink conditions, and it is outside this uncompressed case. A compressed table from an earlier lesson is not automatically a candidate for this shrink.
A ROWID encodes a physical row address from the data object, file, block, and row position. Use a stable primary key for application identity. Inventory stored rowids, rowid-based triggers, and dependent objects before enabling movement. Resolve those dependencies in the design. Disabling an application trigger does not remove a rowid dependency. ENABLE ROW MOVEMENT changes the permission. Later qualifying operations are what relocate rows. DISABLE ROW MOVEMENT afterward leaves addresses that already changed.
Supported indexes are maintained during shrink and remain usable. Check their status afterward. Other dependent objects can be invalidated, so check those objects and the application. Shrinking an index's own allocation is a separate action. CASCADE can extend shrink to supported dependent segments. This operation omits CASCADE. An index rebuild is not a routine prerequisite for a supported heap shrink.
See ALTER TABLE for shrink syntax, COMPACT, row movement, and object restrictions, ROWID pseudocolumn for the physical address and primary-key considerations, Oracle 19c Licensing Information for the deployment and feature rights, and DBA_TABLESPACES for extent and segment-space management.
A delete can leave the allocation in place
Deleting rows does not by itself return the segment's allocation. space_sample is an uncompressed heap with a numeric primary key and a 200-character payload. The insert creates 10,000 rows of 200-character ASCII payloads. The delete keeps IDs divisible by 10 and leaves 1,000 rows. space_before stores id, payload, and ROWIDTOCHAR(ROWID) as old_rowid so later checks can compare logical values and old addresses. That copy is a row snapshot for the comparison, not a database recovery backup. Where the rows sit in blocks, and how many bytes are actually released, depends on the environment. The CREATE TABLE statements commit.
CREATE TABLE space_sample (
id NUMBER PRIMARY KEY,
payload VARCHAR2(200)
) NOCOMPRESS;
INSERT INTO space_sample
SELECT LEVEL, RPAD('x', 200, 'x')
FROM dual CONNECT BY LEVEL <= 10000;
COMMIT;
DELETE FROM space_sample WHERE MOD(id, 10) <> 0;
COMMIT;
CREATE TABLE space_before AS
SELECT id, payload, ROWIDTOCHAR(ROWID) AS old_rowid
FROM space_sample;See CREATE TABLE for the table, constraint, and storage syntax.
Measure, compact, and release
Record these queries before movement, after COMPACT, and after the final release:
SELECT table_name, tablespace_name, row_movement, compression
FROM user_tables
WHERE table_name = 'SPACE_SAMPLE';
SELECT segment_name, bytes, blocks, extents
FROM user_segments
WHERE segment_name = 'SPACE_SAMPLE'
AND segment_type = 'TABLE';
SELECT index_name, index_type, status
FROM user_indexes
WHERE table_name = 'SPACE_SAMPLE';
SELECT COUNT(*) AS rows_n, COUNT(DISTINCT id) AS distinct_ids,
SUM(id) AS id_total
FROM space_sample;USER_SEGMENTS.BYTES is allocated segment storage. It is not the row payload, and it is not the physical file length. Record the unrounded values and use the same scope each time. These table-heap figures exclude separate indexes and other segments. For this prepared set, the expected row count and distinct ID count are 1,000, and the sum of the IDs is 5,005,000. Reconcile the detailed values as well. Note the ROW_MOVEMENT setting so any later return to the original setting is deliberate.
After eligibility and rowid dependencies are confirmed:
ALTER TABLE space_sample ENABLE ROW MOVEMENT;
ALTER TABLE space_sample SHRINK SPACE COMPACT;Run the baseline queries again. Rows may already have different physical addresses, while the allocation stays with the segment. Complete the release in the planned window:
ALTER TABLE space_sample SHRINK SPACE;Repeat the measurements. Reclaimed allocation is the before bytes minus the final bytes. Report that value, including zero. A small segment may have no whole eligible allocation to return, and a successful statement does not by itself quantify the benefit. When a high water mark or free-space breakdown is needed, use DBMS_SPACE.SPACE_USAGE or DBMS_SPACE.UNUSED_SPACE with the correct segment type and privileges. Those metrics and the allocation bytes describe different aspects of space use. Do not infer an exact high water mark from a count of live rows or from a stale optimizer statistic.
See USER_TABLES and USER_SEGMENTS for the inspection fields, and DBMS_SPACE for space-use and high water mark interfaces.
Confirm the logical rows and the indexes
Both selected-value difference counts must be zero:
SELECT COUNT(*) AS before_minus_after
FROM (
SELECT id, payload FROM space_before
MINUS
SELECT id, payload FROM space_sample
);
SELECT COUNT(*) AS after_minus_before
FROM (
SELECT id, payload FROM space_sample
MINUS
SELECT id, payload FROM space_before
);The primary key is unique and not null. With matching counts, this comparison of the two scalar columns fits this table. It is not a general duplicate-sensitive comparison, and it is not a method for datatypes the set operators do not support.
Inspect moved addresses on their own:
SELECT s.id, b.old_rowid, ROWIDTOCHAR(s.ROWID) AS new_rowid
FROM space_sample s
JOIN space_before b ON b.id = s.id
WHERE b.old_rowid <> ROWIDTOCHAR(s.ROWID)
ORDER BY s.id;
SELECT index_name, status
FROM user_indexes
WHERE table_name = 'SPACE_SAMPLE';
SELECT object_name, object_type, status
FROM user_objects
WHERE object_name IN ('SPACE_SAMPLE', 'SPACE_BEFORE');A changed address with an unchanged ID shows that the logical row moved. Not every row has to move, so do not require every ROWID to differ. Review dependencies in the appropriate dictionary scope, and test the application statements that use the table. The two-object status query checks SPACE_SAMPLE and SPACE_BEFORE. It is not an inventory of every dependency in other schemas.
See USER_INDEXES and USER_OBJECTS for status, ownership, and visibility.
The data file stays the same size
Allocation returned by shrink stays inside the physical data file on disk. Resizing that file is a separate operation. It needs a suitable unused end of the file, plus its own capacity and recovery checks. This shrink does not resize the file. A smaller table segment can coexist with the original file size.
Disabling movement leaves the new addresses
Record the original ROW_MOVEMENT setting. If it was DISABLED, and restoring that setting is appropriate:
ALTER TABLE space_sample DISABLE ROW MOVEMENT;This restores the permission setting. It does not restore old row locations. ROLLBACK cannot undo this completed DDL reorganization.
Recap
Segment shrink reorganizes an eligible heap in place. Gaps left by deletes sit below the high water mark until rows are packed forward. SHRINK SPACE COMPACT moves rows into earlier blocks and saves that result. Primary-key values stay the same, and a heap row's physical ROWID can change. The high water mark and the allocation remain. SHRINK SPACE then adjusts the high water mark and releases eligible unused allocation to the tablespace. One SHRINK SPACE can do both phases. The data file does not get smaller, and the freed space can serve other segments in that tablespace. Queries and DML can run during compaction. Final deallocation blocks concurrent DML briefly.
The 19c case is an uncompressed, nonpartitioned heap in a locally managed ASSM tablespace (LOCAL and AUTO). Basic, warehouse, and archive compression are excluded. Function-based, domain, and bitmap join indexes, clustered tables, LONG columns, and the documented materialized-view and LOB restrictions still apply. The licensing row for MOVE ONLINE does not by itself settle shrink entitlement. ENABLE ROW MOVEMENT permits relocation. Later operations move the rows, and DISABLE ROW MOVEMENT does not put old addresses back. ROLLBACK cannot undo the DDL. Supported indexes are maintained. CASCADE is optional and is omitted here. An index rebuild is not a routine prerequisite. On the prepared table, 10,000 inserted rows and a delete that keeps IDs divisible by 10 leave 1,000 rows, 1,000 distinct IDs, and an ID sum of 5,005,000. Report USER_SEGMENTS.BYTES before minus after, including zero, and use DBMS_SPACE when a high water mark breakdown is required. Both id and payload difference counts should be zero. A changed ROWID with the same ID shows relocation, and not every row must move.
No comments:
Post a Comment