Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In this article, we will discuss in detail the table Move processes and how Shrink operations are performed.
Sometimes we may need to move a table to another tablespace. For example, we may want to do Shrink operation.
However, in order to perform the shrink operation, the “SEGMENT SPACE MANAGEMENT” value of the table must be AUTO.
Therefore, the table can be moved to another tablespace whose “SEGMENT SPACE MANAGEMENT” value is AUTO.
For example, in order to shrink a table, the steps to be done in the table are as follows.
1. Row moving is activated in the table.
1 2 3 | SQL> ALTER TABLE ACCOUNT ENABLE ROW MOVEMENT; Table altered. |
2. Shrink is desired.
1 2 3 4 5 | SQL> ALTER TABLE ACCOUNT SHRINK SPACE; ALTER TABLE ACCOUNT SHRINK SPACE * ERROR at line 1: ORA-10635: Invalid segment or tablespace type |
3. The “SEGMENT SPACE MANAGEMENT” value of the table is learned.
1 2 3 4 5 6 | SQL> SELECT TBS.SEGMENT_SPACE_MANAGEMENT FROM DBA_TABLES T, DBA_TABLESPACES TBS WHERE T.TABLESPACE_NAME=TBS.TABLESPACE_NAME AND T.OWNER='SYS' AND T.TABLE_NAME='ACCOUNT'; SEGMEN ------ MANUAL |
4. Since it is manual, the table is moved to another tablespace.
1 2 3 | SQL> ALTER TABLE ACCOUNT MOVE TABLESPACE USERS; Table altered. |
5. The “SEGMENT SPACE MANAGEMENT” value is learned again.
1 2 3 4 5 6 | SQL> SELECT TBS.SEGMENT_SPACE_MANAGEMENT FROM DBA_TABLES T, DBA_TABLESPACES TBS WHERE T.TABLESPACE_NAME=TBS.TABLESPACE_NAME AND T.OWNER='SYS' AND T.TABLE_NAME='ACCOUNT'; SEGMEN ------ AUTO |
6. We do the shrink process.
1 2 3 | SQL> ALTER TABLE ACCOUNT SHRINK SPACE; Table altered. |
We can also narrow down the index segments used by the table by using the word “CASCADE”.
1 2 3 | SQL> ALTER TABLE ACCOUNT SHRINK SPACE CASCADE; Table altered. |
If we do not want the table’s “High-Water Mark” to be readjusted,
1 2 3 | SQL> ALTER TABLE ACCOUNT SHRINK SPACE COMPACT; Table altered. |
