- 29 Sep, 2024
- 0 Comments
- 4 Mins Read
Oracle 19c Table Shrink Space
Oracle 19c Table Shrink Space
Shrinking a table in an Oracle database is typically done to reclaim unused space and optimize storage. Over time, as data is inserted, updated, and deleted, tables can become fragmented or contain unused space, which can impact performance and increase storage costs. Shrinking the table compacts the data, improving efficiency.
Here are the primary reasons for shrinking a table:
- Reclaiming Unused Space
– When rows are deleted or updated, Oracle marks the old space as “free” within the table, but the table’s size on disk doesn’t decrease automatically.
– Over time, as more rows are deleted or updated, the amount of unused space can grow significantly, especially in tables with frequent transactional changes.
– Shrinking the table reclaims this free space and returns it to the tablespace, making it available for other objects.
- Improving Performance
– Shrinking the table reduces fragmentation. When rows are scattered across multiple blocks, Oracle needs to read more blocks to satisfy queries, which can slow down performance.
– By compacting the table, Oracle reduces the number of blocks it needs to read, potentially improving query performance and buffer cache efficiency.
- Optimizing Storage Costs
– Reclaiming space helps manage disk usage more efficiently, which is especially important in large databases where storage is costly.
– This is particularly useful in environments with limited storage, as it reduces the need to add more space or purchase additional storage hardware.
- Tables with High Transactional Activity
– Tables that undergo frequent INSERT, UPDATE, and DELETE operations (e.g., log tables, transactional data tables) tend to have more unused space and fragmentation.
– Shrinking these tables can help maintain their efficiency by keeping the storage footprint aligned with the actual data size.
- Reducing Backup Size
– Shrinking a table reduces its size on disk, which in turn decreases the amount of space required for backups. Smaller tables result in smaller backup files, which is useful for optimizing backup windows and storage costs.
- Reducing Table Scans
– A fragmented table with many sparsely populated blocks can increase the number of blocks Oracle must scan during full table scans or range queries.
– Shrinking the table reorganizes rows into fewer blocks, minimizing the number of blocks scanned during such operations.
7. Freeing Tablespace for Other Objects
– Shrinking the table can free up space in a tablespace, allowing other objects (tables, indexes, etc.) to use that freed space, improving overall database space utilization.
Example of When Shrinking Is Beneficial:
Consider a scenario where a table has been storing transaction logs for years. After a data retention policy is implemented, the older records are deleted to free up storage. Despite the deletion, the table’s size on disk remains the same, and there is unused space.
– In this case, shrinking the table will reorganize the remaining data and reclaim the unused space, making it available for other tables or indexes.
Shrink Operation: Steps and Considerations
– Enable Row Movement: Before shrinking, you must allow Oracle to move rows around within the table by enabling row movement.
– Online Operation: Shrinking can be done online, meaning the table can still be used for queries and transactions while the operation is ongoing.
– Impact on Performance: Although the shrink operation is generally safe, it can cause a temporary performance impact as Oracle moves rows and adjusts the table structure.
– Index Rebuild: Shrinking may require rebuilding the table’s indexes to avoid fragmentation or invalidation, especially when moving data across blocks.
When Not to Shrink:
– Low transactional activity: If a table rarely sees deletes or updates, shrinking is usually not necessary.
– Performance-sensitive systems: If your system cannot tolerate even temporary performance hits, consider scheduling shrinking operations during off-peak hours.
Oracle Database 19c offers powerful commands to manage the storage space of tables efficiently. Among these commands are the ENABLE ROW MOVEMENT, SHRINK SPACE, and CASCADE options. Let’s delve into each of these commands and understand their significance:
Example for Table Shrinking
Step-by-Step Guide:
- Create the Table: The table TRANSACTION_LOGS will store information like transaction ID, account number, transaction amount, and date.
CREATE TABLE TRANSACTION_LOGS ( TRANSACTION_ID NUMBER PRIMARY KEY, ACCOUNT_NO VARCHAR2(20), AMOUNT NUMBER(12,2), TRANSACTION_DATE DATE );
Insert Large Amount of Data: Insert 1 million rows to simulate a large amount of data.
BEGIN FOR i IN 1..1000000 LOOP INSERT INTO TRANSACTION_LOGS VALUES (i, 'ACC'||DBMS_RANDOM.STRING('X', 10), DBMS_RANDOM.VALUE(10, 10000), TRUNC(SYSDATE - DBMS_RANDOM.VALUE(0, 365))); END LOOP; COMMIT; END; /
Delete Old Data: Assume the system retains only transactions from the last 6 months. Transactions older than 6 months are deleted.
DELETE FROM TRANSACTION_LOGS WHERE TRANSACTION_DATE < ADD_MONTHS(SYSDATE, -6); COMMIT;
- This deletion will create a lot of unused space in the table, as a significant number of rows are deleted.
- Analyze the Current Space Usage: Use the following query to check the allocated space and how much is actually used before shrinking:
SELECT table_name, blocks, num_rows FROM user_tables WHERE table_name = 'TRANSACTION_LOGS';
3. Enable Row Movement: Before shrinking the table, enable row movement to allow Oracle to change the physical location of rows.
ALTER TABLE TRANSACTION_LOGS ENABLE ROW MOVEMENT;
4. Shrink the Table: Now, shrink the table to reclaim unused space. This operation will compact the data and release the free space back to the system.
ALTER TABLE TRANSACTION_LOGS SHRINK SPACE CASCADE;
The CASCADE option ensures that indexes on the table are also compacted.
5. Verify the Space Reclaimed: After the shrink operation, check the space usage again.
SELECT table_name, blocks, num_rows FROM user_tables WHERE table_name = 'TRANSACTION_LOGS';
6. Disable Row Movement (Optional): If row movement is no longer required, it can be disabled.
ALTER TABLE TRANSACTION_LOGS DISABLE ROW MOVEMENT;