General
==============================
Sometimes there is needed to free up space that was used by a segment.
For example, a table went under huge delete, and now there is a need to lower the HWM to free up unused space.
There are four ways to do that:
A. Manually - via expdp/impdp, or by creating a temp table.
B. ALTER TABLE MOVE TABLESPACE.
C. ALTER TABLE SHRINK SPACE.
D. DBMS_REDEFINITION.
E. Move Datafile
==============================
ALTER TABLE SHRINK SPACE
==============================
ALTER TABLE SHRINK SPACE lets you manually shrink space in a table, and to lower the High Water Mark, HWM, thus releasing the space from the Segment.
The ALTER TABLE SHRINK SPACE algorithm starts from the bottom of the segment and starts moving rows to the beginning of the segment.
Shrink is a combination of DELETE/INSERT pair for every row movement and this generates many UNDO and REDO data.
Compacting the segment requires row movement. Therefore, you must enable row movement for the object you want to shrink before specifying this clause.
Further, if your application has any rowid-based triggers, you should disable them before issuing this clause.
ALTER TABLE SHRINK SPACE can be performed on a table, index-organized table or its overflow segment, index, partition, subpartition, LOB segment, materialized view, or materialized view log.
This clause is valid only for segments in tablespaces with automatic segment management.
By default, Oracle Database compacts the segment, adjusts the High Water Mark, and releases the recuperated space immediately.
The step to adjusts the high water mark is a DDL operation and requires a lock on the table. In high load DML Online environments, this might be an issue, and it would take a long time to perform this step.
==============================
ALTER TABLE SHRINK Options
==============================
ALTER TABLE MY_TABLE SHRINK is implemented in two phases
A. Segment data is compacted. Through a series of INSERT and DELETE statements.
B. High-water mark (HWM) is adjusted and unused space is deallocated from the segment.
It is optional to run the command in two steps: first with COMPACT option, and later on without, to adjust the HWM.
A. ALTER TABLE MY_TABLE SHRINK SPACE
This is the default - compact rows, and adjust HWM.
B. ALTER TABLE MY_TABLE SHRINK SPACE COMPACT
Compact rows, without adjusting HWM.
C. ALTER TABLE MY_TABLE SHRINK SPACE CASCADE
When you specify CASCADE, Oracle performs the same operations on all dependent objects of table, including secondary indexes on index-organized tables.
Without specifying the CASCADE option, need to manually rebuild the indexes on the segment.
This is Oracle internal option.
It is used to check for proper segment type and segment attributes
(e.g. row movement enabled) to allow shrink.
The statement performs the exact same validation as the "ALTER TABLE ... SHRINK SPACE", but it does not perform any actual shrinking on the segment.
For Example:
SQL> ALTER TABLE MY_TABLE SHRINK SPACE CHECK;
ALTER TABLE MY_TABLE SHRINK SPACE CHECK
*
ERROR at line 1:
ORA-10655: Segment can be shrunk
Why to use ALTER TABLE MY_TABLE SHRINK SPACE COMPACT?
Per Oracle Documentation:
When you specify
COMPACT, Oracle Database defragments the segment space and compacts the table rows but postpones the resetting of the high water mark and the deallocation of the space until a future time. This option is useful if you have long-running queries that might span the operation and attempt to read from blocks that have been reclaimed.
You can reissue the
SHRINK SPACE clause without the COMPACT clause during off-peak hours to complete the second phase.==========================================
ALTER TABLE SHRINK vs ALTER TABLE MOVE TABLESPACE
==========================================
- Way of operation
Shrink is a combination of DELETE/INSERT pair for every row movement and this generates many UNDO and REDO data.
MOVE TABLESPACE is simply a move of data blocks to another location without any delete/inserts .
ALTER TABLE SHRINK is expected to take longer than a move operation.
- limitations
ALTER TABLE SHRINK can be run while the segment is online.
MOVE TABLESPACE can be run while the segment is online, but is prone to locks if there are DML operations.
==============================
ALTER TABLE MOVE TABLESPACE
==============================
ALTER TABLE...MOVE enables you to relocate data of a non-partitioned table or of a partition of a partitioned table into a new segment, and optionally into a different tablespace.
This statement also lets you modify any of the storage attributes of the table or partition, including those which cannot be modified using ALTER TABLE.
You can also use the ALTER TABLE...MOVE statement with a COMPRESS clause to store the new segment using table compression.
The ALTER TABLE...MOVE statement does not permit DML against the table while the statement is executing.
==============================
ALTER TABLE SHRINK SPACE by Example 1
==============================
Step 1. - See which segment is using up space.
SELECT * FROM
(
SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024) AS MBs
FROM DBA_SEGMENTS
WHERE tablespace_name = 'MY_TBS'
GROUP BY owner, tablespace_name, segment_name
ORDER BY ROUND(SUM(bytes)/1024/1024) DESC
)
WHERE ROWNUM < 11
OWNER TABLESPACE_NAME SEGMENT_NAME MBS
-------------------- -------------------- -------------------- ----------
USER_A IGT_TABLE CUSTOMER_PROFILE 3671
USER_B IGT_TABLE SUBSCRIBER 1413
USER_D IGT_TABLE SUBSCRIBER 1350
USER_B IGT_TABLE SUBSCRIBER 656
USER_A IGT_TABLE SUBSCRIBER 472
USER_C IGT_TABLE VISITOR 256
USER_B IGT_TABLE LOCATION 96
USER_C IGT_TABLE LOCATION 96
USER_D IGT_TABLE LOCATION 96
USER_A IGT_TABLE LOCATION 88
SELECT COUNT(*) FROM CUSTOMER_PROFILE
COUNT(*)
----------
337
There are only 337 rows, but the table occupies 3.5 Gb!
This situation is due to past DML operations.
Since table CUSTOMER_PROFILE is a target of frequent Online DML operations, it cannot be taken offline.
The option to free space, is by using ALTER TABLE ... SHRINK SPACE.
Step 2. - run ALTER TABLE ... SHRINK SPACE.
ALTER TABLE CUSTOMER_PROFILE ENABLE ROW MOVEMENT;
> Table altered
ALTER TABLE CUSTOMER_PROFILE SHRINK SPACE;
> Table altered
-- It took about 3000 seconds to complete on a table with 300 rows!!!
-- The SQL spend most of the time waiting for a lock on the table.
ALTER TABLE CUSTOMER_PROFILE DISABLE ROW MOVEMENT;
> Table altered
Step 3. - Check again segments space usage.
Operation was successful - segment CUSTOMER_PROFILE is gone from top segments list.
OWNER TABLESPACE_NAME SEGMENT_NAME MBS
-------------------- -------------------- -------------------- ----------
USER_B IGT_TABLE SUBSCRIBER 1413
USER_D IGT_TABLE SUBSCRIBER 1350
USER_B IGT_TABLE SUBSCRIBER 656
USER_A IGT_TABLE SUBSCRIBER 472
USER_C IGT_TABLE VISITOR 256
USER_B IGT_TABLE LOCATION 96
USER_C IGT_TABLE LOCATION 96
USER_D IGT_TABLE LOCATION 96
USER_A IGT_TABLE LOCATION 88
USER_A IGT_TABLE VISITOR 64
Step 4. - Free up space from indexes on CUSTOMER_PROFILE table.
SELECT index_name FROM USER_INDEXES WHERE table_name = 'CUSTOMER_PROFILE';
INDEX_NAME
------------------------------
CP_FRWD_ID_IDX
CP_DATE_OF_CALL_IDX
CP_IS_PROCESSED_BMIDX
CP_PK
SELECT tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024) AS MBs
FROM USER_SEGMENTS
WHERE tablespace_name = 'TBS_INDEX'
AND segment_name IN ('CP_FRWD_ID_IDX','CP_DATE_OF_CALL_IDX', 'CP_IS_PROCESSED_BMIDX', 'CP_PK')
GROUP BY tablespace_name, segment_name;
TABLESPACE_NAME SEGMENT_NAME MBS
-------------------- -------------------- ----------
IGT_INDEX CP_PK 544
IGT_INDEX CP_IS_PROCESSED_BMIDX 277
IGT_INDEX CP_DATE_OF_CALL_IDX 480
IGT_INDEX CP_FRWD_ID_IDX 495
ALTER INDEX CP_FRWD_ID_IDX REBUILD ONLINE;
ALTER INDEX CP_DATE_OF_CALL_IDX REBUILD ONLINE;
ALTER INDEX CP_IS_PROCESSED_BMIDX REBUILD ONLINE;
ALTER INDEX CP_PK REBUILD ONLINE;
SELECT tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024) AS MBs
FROM USER_SEGMENTS
WHERE tablespace_name = 'TBS_INDEX'
AND segment_name IN ('CP_FRWD_ID_IDX','CP_DATE_OF_CALL_IDX', 'CP_IS_PROCESSED_BMIDX', 'CP_PK')
GROUP BY tablespace_name, segment_name;
TABLESPACE_NAME SEGMENT_NAME MBS
-------------------- -------------------- ----------
IGT_INDEX CP_PK 5
IGT_INDEX CP_IS_PROCESSED_BMIDX 5
IGT_INDEX CP_DATE_OF_CALL_IDX 5
IGT_INDEX CP_FRWD_ID_IDX 5
=================
Estimate space waste
=================
==============================
ALTER TABLE SHRINK SPACE by Example 2
==============================
The amount of saved space is about 80% of the original table size.
==============================SELECT
SEG.owner,
SEG.segment_name,
SEG.segment_type,
ROUND(SEG.bytes/1024/1024,0) total_mb,
ROUND((SEG.bytes-(TAB.num_rows*TAB.avg_row_len) )/1024/1024,0) estimate_waste
FROM DBA_SEGMENTS SEG,
DBA_TABLES TAB
WHERE SEG.owner=TAB.owner
AND SEG.owner not like 'SYS%'
AND SEG.segment_name = TAB.table_name
AND SEG.segment_type='TABLE'
GROUP BY SEG.owner, SEG.segment_name, SEG.segment_type, ROUND(SEG.bytes/1024/1024,0) , ROUND((SEG.bytes-(TAB.num_rows*TAB.avg_row_len) )/1024/1024,0)
HAVING ROUND(SEG.bytes/1024/1024,0) >100
ORDER BY ROUND(SEG.bytes/1024/1024,0) DESC ;
==============================
ALTER TABLE SHRINK SPACE by Example 2
==============================
VISITOR table is a main table in OLTP DB, which data being purged with simple DELETE commands.
The SHRINK SPACE command took approx 2 hours to complete, and was executed when OLTP system was up, but during off peak hours.The amount of saved space is about 80% of the original table size.
SELECT COUNT (*) FROM VISITOR;
COUNT(*)
----------
859532
SELECT owner, tablespace_name, segment_name, USED_MB
FROM
(
SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB
FROM DBA_SEGMENTS
WHERE tablespace_name = 'IGT_TABLE'
GROUP BY owner, tablespace_name,segment_name
ORDER BY ROUND(SUM(bytes)/1024/1024) DESC
)
WHERE ROWNUM < 3;
OWNER TABLESPACE_NAME SEGMENT_NAME USED_MB
--------- --------------- ---------------------- ----------
USER_A IGT_TABLE VISITOR 1856
USER_A IGT_TABLE SUBSCRIBER 54
2 rows selected
SQL> ALTER TABLE VISITOR ENABLE ROW MOVEMENT;
Table altered
SQL> ALTER TABLE VISITOR SHRINK SPACE;
Table altered
SELECT owner, tablespace_name, segment_name, USED_MB
FROM
(
SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB
FROM DBA_SEGMENTS
WHERE tablespace_name = 'IGT_TABLE'
GROUP BY owner, tablespace_name,segment_name
ORDER BY ROUND(SUM(bytes)/1024/1024) DESC
)
WHERE ROWNUM < 3;
OWNER TABLESPACE_NAME SEGMENT_NAME USED_MB
--------- --------------- ---------------------- ----------
USER_A IGT_TABLE VISITOR 349
USER_A IGT_TABLE SUBSCRIBER 54
2 rows selected
ALTER TABLE MOVE by Example
==============================
SELECT COUNT(*) FROM CUSTOMER_PROFILE;
Return approx 5,000,000 records.
SELECT SUM(bytes)/1024/1024 AS Mb
FROM USER_SEGMENTS
WHERE segment_name = 'CUSTOMER_PROFILE';
Return 16,500 Mb
ALTER TABLE CUSTOMER_PROFILE MOVE;
The command took about 30 minutes to complete.
During this time the application was down, and no DMLs were performed on the table.
SELECT SUM(bytes)/1024/1024 AS Mb
FROM USER_SEGMENTS
WHERE segment_name = 'CUSTOMER_PROFILE';
Return 7,000 Mb
==============================
Partition code examples
==============================
ALTER TABLE MY_TABLE MOVE PARTITION PART_01
ALTER TABLE MY_TABLE MODIFY PARTITION PART_01 SHRINK SPACE
==============================
Move Datafile
==============================
It is possible to move datafile from one filesystem to another.
For a complete reference: Renaming or Moving Oracle Files
It is a quick operation, taking few seconds, except for OS command for physically moving the datafile from one location to another.
This is move datafile by example:
SELECT name FROM DBA_DATA_FILES;
/oracle_db/db1/db_igt/ora_igt_table_01.dbf
/oracle_db/db1/db_igt/ora_igt_table_02.dbf
/oracle_db/db1/db_igt/ora_igt_table_03.dbf
/oracle_db/db2/db_igt/ora_igt_table_04.dbf
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
HOST Command:
mv /oracle_db/db2/db_igt/ora_igt_table_04.dbf /oracle_db/db1/db_igt/ora_igt_table_04.dbf
SQL> STARTUP MOUNT;
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size 2166536 bytes
Variable Size 2197815544 bytes
Database Buffers 2063597568 bytes
Redo Buffers 12201984 bytes
Database mounted.
SQL> ALTER DATABASE RENAME FILE '/oracle_db/db2/db_igt/ora_igt_table_04.dbf' TO '/oracle_db/db1/db_igt/ora_igt_table_04.dbf';
Database altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SELECT name FROM DBA_DATA_FILES;
/oracle_db/db1/db_igt/ora_igt_table_01.dbf
/oracle_db/db1/db_igt/ora_igt_table_02.dbf
/oracle_db/db1/db_igt/ora_igt_table_03.dbf
/oracle_db/db1/db_igt/ora_igt_table_04.dbf
SQL> ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_igt_table_04.dbf' RESIZE 20000M;
Database altered.
=====================
Dealing with ORA-00054 =====================
When issued a DDL command, such as ALTER TABLE SHRINK, an error ORA-00054 might be thrown:
ORA-00054 resource busy and acquire with NOWAIT specified
ALTER TABLE MY_TABLE ENABLE ROW MOVEMENT;
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
The solution, new from Oracle 11g is to increase the DDL_LOCK_TIMEOUT parameter.
It tells Oracle that the DDL would wait for the object to become available.
SQL> ALTER SESSION SET DDL_LOCK_TIMEOUT = 600;
Session altered.
SQL> ALTER TABLE MY_TABLE ENABLE ROW MOVEMENT;
Table altered.
Note:
This option is available only in Oracle 11g and higher
In lesser version, one would get error:
ORA-02248: invalid option for ALTER SESSION
Dealing with ORA-10635
=====================
ALTER TABLE SUBSCRIBER SHRINK SPACE;
ORA-10635: Invalid segment or tablespace type
SELECT compression, compress_for from USER_TABLES WHERE table_name = 'SUBSCRIBER'
The returned value is: ENABLED, FOR ALL OPERATIONS
Compressed tables cannot be shrinked.
The solution:
ALTER TABLE SUBSCRIBER NOCOMPRESS;
Then
ALTER TABLE SUBSCRIBER SHRINK SPACE;
=====================
PL/SQL Example for SHRINK TABLE
=====================
CREATE OR REPLACE PACKAGE ADMIN_SHRINK IS
PROCEDURE SHRINK_TABLE (p_table_name IN VARCHAR2 );
END ADMIN_SHRINK;
-----------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY ADMIN_SHRINK IS
-----------------------------------------------------------
PROCEDURE write_sga_w_log(p_module_name IN VARCHAR, p_msg_text IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO SGA_W_LOG(procedure_name, data, ts_last_modified)
VALUES ( p_module_name, p_msg_text, SYSDATE);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
-----------------------------------------------------------
PROCEDURE SHRINK_TABLE (p_table_name IN VARCHAR2 ) IS
v_proc_name VARCHAR2(30);
v_msg_txt VARCHAR2(1000);
v_sql_str VARCHAR2(1000);
v_row_movement VARCHAR2(1);
BEGIN
v_proc_name := 'ADMIN_SHRINK_TABLE';
v_msg_txt := 'Procedure Starting';
write_sga_w_log(v_proc_name,v_msg_txt);
SELECT DECODE(row_movement,'ENABLED','Y','N') INTO v_row_movement FROM USER_TABLES WHERE table_name = p_table_name;
v_sql_str := 'ALTER SESSION SET DDL_LOCK_TIMEOUT = 600';
v_msg_txt := 'Step: '||v_sql_str;
write_sga_w_log(v_proc_name,v_msg_txt);
EXECUTE IMMEDIATE v_sql_str;
IF v_row_movement = 'N' THEN
v_sql_str := 'ALTER TABLE '||p_table_name||' ENABLE ROW MOVEMENT';
v_msg_txt := 'Step: '||v_sql_str;
write_sga_w_log(v_proc_name,v_msg_txt);
EXECUTE IMMEDIATE v_sql_str;
END IF;
v_sql_str := 'ALTER TABLE '||p_table_name||' SHRINK SPACE';
v_msg_txt := 'Step: '||v_sql_str;
write_sga_w_log(v_proc_name,v_msg_txt);
EXECUTE IMMEDIATE v_sql_str;
IF v_row_movement = 'N' THEN
v_sql_str := 'ALTER TABLE '||p_table_name||' DISABLE ROW MOVEMENT';
v_msg_txt := 'Step: '||v_sql_str;
write_sga_w_log(v_proc_name,v_msg_txt);
EXECUTE IMMEDIATE v_sql_str;
END IF;
v_msg_txt := 'Procedure Finished';
write_sga_w_log(v_proc_name,v_msg_txt);
EXCEPTION
WHEN OTHERS THEN
v_msg_txt := 'Unexpected Error in Step '||v_sql_str||' Error Details: '||SQLERRM;
write_sga_w_log(v_proc_name,v_msg_txt);
END SHRINK_TABLE;
-----------------------------------------------------------
END ADMIN_SHRINK;
==============================
Reference
==============================
PL/SQL Example for SHRINK TABLE
=====================
CREATE OR REPLACE PACKAGE ADMIN_SHRINK IS
PROCEDURE SHRINK_TABLE (p_table_name IN VARCHAR2 );
END ADMIN_SHRINK;
-----------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY ADMIN_SHRINK IS
-----------------------------------------------------------
PROCEDURE write_sga_w_log(p_module_name IN VARCHAR, p_msg_text IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO SGA_W_LOG(procedure_name, data, ts_last_modified)
VALUES ( p_module_name, p_msg_text, SYSDATE);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
-----------------------------------------------------------
PROCEDURE SHRINK_TABLE (p_table_name IN VARCHAR2 ) IS
v_proc_name VARCHAR2(30);
v_msg_txt VARCHAR2(1000);
v_sql_str VARCHAR2(1000);
v_row_movement VARCHAR2(1);
BEGIN
v_proc_name := 'ADMIN_SHRINK_TABLE';
v_msg_txt := 'Procedure Starting';
write_sga_w_log(v_proc_name,v_msg_txt);
SELECT DECODE(row_movement,'ENABLED','Y','N') INTO v_row_movement FROM USER_TABLES WHERE table_name = p_table_name;
v_sql_str := 'ALTER SESSION SET DDL_LOCK_TIMEOUT = 600';
v_msg_txt := 'Step: '||v_sql_str;
write_sga_w_log(v_proc_name,v_msg_txt);
EXECUTE IMMEDIATE v_sql_str;
IF v_row_movement = 'N' THEN
v_sql_str := 'ALTER TABLE '||p_table_name||' ENABLE ROW MOVEMENT';
v_msg_txt := 'Step: '||v_sql_str;
write_sga_w_log(v_proc_name,v_msg_txt);
EXECUTE IMMEDIATE v_sql_str;
END IF;
v_sql_str := 'ALTER TABLE '||p_table_name||' SHRINK SPACE';
v_msg_txt := 'Step: '||v_sql_str;
write_sga_w_log(v_proc_name,v_msg_txt);
EXECUTE IMMEDIATE v_sql_str;
IF v_row_movement = 'N' THEN
v_sql_str := 'ALTER TABLE '||p_table_name||' DISABLE ROW MOVEMENT';
v_msg_txt := 'Step: '||v_sql_str;
write_sga_w_log(v_proc_name,v_msg_txt);
EXECUTE IMMEDIATE v_sql_str;
END IF;
v_msg_txt := 'Procedure Finished';
write_sga_w_log(v_proc_name,v_msg_txt);
EXCEPTION
WHEN OTHERS THEN
v_msg_txt := 'Unexpected Error in Step '||v_sql_str||' Error Details: '||SQLERRM;
write_sga_w_log(v_proc_name,v_msg_txt);
END SHRINK_TABLE;
-----------------------------------------------------------
END ADMIN_SHRINK;
==============================
Reference
==============================
ALTER TABLE SHRINK Oracle Reference
Oracle DBA by Example, DBMS_REDEFINITION Explanation and Example.
