– 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

The table TRANSACTION_LOGS will store information like transaction ID, account number, transaction amount, and date.

1
2
3
4
5
6
7
8
9
10
11

CREATE TABLE TRANSACTION_LOGS ( 

    TRANSACTION_ID NUMBER PRIMARY KEY, 

    ACCOUNT_NO     VARCHAR2(20), 

    AMOUNT         NUMBER(12,2), 

    TRANSACTION_DATE DATE 

);

Insert 1 million rows to simulate a large amount of data.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

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; 

/

Assume the system retains only transactions from the last 6 months. Transactions older than 6 months are deleted. 1. This deletion will create a lot of unused space in the table, as a  significant number of rows are deleted. 2. Analyze the Current Space Usage : Use the following query to check the allocated space and how much is actually used before shrinking:

1
2
3
4
5
6
7
8
9
10
11

DELETE FROM TRANSACTION_LOGS  

WHERE TRANSACTION_DATE < ADD_MONTHS(SYSDATE, -6); 

COMMIT;

SELECT table_name, blocks, num_rows 

FROM user_tables 

WHERE table_name = 'TRANSACTION_LOGS';

Before shrinking the table, enable row movement to allow Oracle to change the physical location of rows.

1

ALTER TABLE TRANSACTION_LOGS ENABLE ROW MOVEMENT;

Now, shrink the table to reclaim unused space. This operation will compact the data and release the free space back to the system. The CASCADE option ensures that indexes on the table are also compacted.

1

ALTER TABLE TRANSACTION_LOGS SHRINK SPACE CASCADE;

After the shrink operation, check the space usage again.

1
2
3
4
5

SELECT table_name, blocks, num_rows 

FROM user_tables 

WHERE table_name = 'TRANSACTION_LOGS';

If row movement is no longer required, it can be disabled.

1

ALTER TABLE TRANSACTION_LOGS DISABLE ROW MOVEMENT;