Pages

Monday, July 27, 2015

SHRINK and MOVE Table by example

==============================
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.

D. ALTER TABLE MY_TABLE SHRINK SPACE CHECK
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

This is not an error message, but a confirmation message about the possibility of shrinking the segment.


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 .

- time to complete
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
=================
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;
ALTER TABLE SUBSCRIBER COMPRESS FOR ALL OPERATIONS;

=====================
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.

No comments:

Post a Comment