RDBMS Version: 11.2.0.4
OS : RHEL 6.6
Due to space shortage, DBA team has been asked to free up space from segments.
Question1. Moving segment to another tablespace and moving it back is an equivalent of below SHRINK commands. Right ?
alter table emp shrink space compact;
alter table emp shrink space;
Question2. In production, we don't have enough space in 'other' tablespaces. So, can I move the segment to same tablespace ?
When I tested moving the table to the same tablespace, it didn't throw any error. But, can this do what the above SHRINK commands achieve ?
SQL> conn tst_user/tiger
Connected.
SQL>
SQL>
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST3 TABLE
SQL> alter table test3 enable row movement;
Table altered.
SQL> select SEGMENT_NAME , TABLESPACE_NAME from user_segments where segment_name = 'TEST3';
SEGMENT_NAME TABLESPACE_NAME
---------------------------------------- ------------------------------
TEST3 BRC_DATA
--- Moving table to the same tablespace seems to work fine
SQL> ALTER TABLE TEST3 MOVE TABLESPACE BRC_DATA ;
Table altered.