Alter table shrink space Vs alter table move in Oracle

 

Alter table shrink space Vs alter table move in Oracle:-

 

Alter table shrink space à Scenario

 

SQL> set time on
08:41:52 SQL> set timing on
08:41:56 SQL>
08:41:56 SQL>
08:41:56 SQL>
08:41:57 SQL>
08:41:57 SQL> select distinct sid from v$mystat;
 
       SID
----------
         7
 
Elapsed: 00:00:00.01
08:42:53 SQL>
 
08:42:54 SQL> alter table SAMIK.EMP enable row movement;
 
Table altered.
 
Elapsed: 00:00:00.02
08:43:42 SQL>
 
08:44:07 SQL>
08:44:07 SQL> alter table SAMIK.EMP shrink space cascade;
 
Table altered.
 
Elapsed: 00:02:05.57
08:46:28 SQL>
08:47:02 SQL> alter table SAMIK.EMP disable row movement;
 
Table altered.
 

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SAMIK','EMP',CASCADE=>TRUE,ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 254');
 
PL/SQL procedure successfully completed.
 
                                                                 
OWNER    TABLE_NAME      Size(G)  Actual(G) LAST ANAL    Diff(G)  % Frag
-------- ------------ ---------- ---------- ----------- --------  ------
SAMIK      EMP         3.76287842          0 15-NOV-20          3     100
                                                                 
 

08:47:07 SQL> select OWNER,table_name,INDEX_NAME,STATUS,index_type from dba_indexes where owner='SAMIK' and table_name='EMP';
 
OWNER      TABLE_NAME           INDEX_NAME         STATUS   INDEX_TYPE
---------- ----------- ----------------------     --------  --------------------
SAMIK        EMP       SYS_IL0000830597C00013$$       VALID    LOB
SAMIK        EMP       PK_EMP_ID                      VALID    NORMAL
 
Elapsed: 00:00:00.01
 
Note: When “alter table shrink” is executed then index status will be remain VALID
 
 
 
 
 
Alter table move à Scenario
 
 
08:59:26 SQL> alter table SAMIK.EMP move;
 
Table altered.
 
Elapsed: 00:00:13.74
 

OWNER   SEGMENT_NAME   TABLESPACE_NAME   Size in MB SEGMENT_TYPE
------- -------------- ---------------- ---------- -------------
SAMIK     EMP          SAMIK_D               17     TABLE
 
 

OWNER    TABLE_NAME     Size(G)   Actual(G) LAST ANAL    Diff(G)    % Frag
-------- -----------   --------- ---------- ------------ -------   ----------
SAMIK      EMP        .056640625 .012233344 16-NOV-20         0    78.4018204
 

Note: When table is moved using “alter table move” command, we have got good benefit as compare to shrink table command.

 
 
SQL> select OWNER,table_name,INDEX_NAME,STATUS,index_type from dba_indexes where owner='SAMIK' and table_name='EMP';
 
OWNER      TABLE_NAME           INDEX_NAME         STATUS   INDEX_TYPE
---------- ----------- ----------------------     --------  --------------------
SAMIK        EMP       SYS_IL0000830597C00013$$       VALID       LOB
SAMIK        EMP       PK_EMP_ID                      UNUSABLE     NORMAL

 
Note: When table is moved using “alter table move” command index status would be USUSABLE but it is LOB segment then status would be VALID only.

 
SQL> alter index SAMIK. PK_EMP_ID rebuild online; 
 
Index altered.
 
Elapsed: 00:00:00.61
 
 
 
SQL> select OWNER,table_name,INDEX_NAME,STATUS,index_type from dba_indexes where owner='SAMIK' and table_name='EMP';
 
OWNER      TABLE_NAME           INDEX_NAME         STATUS   INDEX_TYPE
---------- ----------- ----------------------     --------  --------------------
SAMIK        EMP       SYS_IL0000830597C00013$$       VALID       LOB
SAMIK        EMP       PK_EMP_ID                      VALID       NORMAL