Method

Executing table move to the existing tablespace

1. Create a TEST Tablespace

CREATE TABLESPACE TEST_TS DATAFILE '/home/tibero7/datafile/test_ts.dtf' SIZE 2G AUTOEXTEND OFF;

Tablespace 'TEST_TS' created.

2. Create TABLE and insert data

SQL> CREATE TABLE TEST_TABLE (
   ID NUMBER PRIMARY KEY,
   NAME VARCHAR2(100),
   DATA VARCHAR2(1010),
   BIRTH DATE
) TABLESPACE TEST_TS;

Table 'TEST2_TABLE' created.

SQL> BEGIN
   FOR i IN 1..300000 LOOP
       INSERT INTO TEST2_TABLE VALUES (i, 'NAME_' || i, RPAD('DATA', 1000, 'X'), SYSDATE);
   END LOOP;
   COMMIT;
END;
/  

3. Check free space in the existing tablespace

SQL> SELECT tablespace_name, file_id, block_id, bytes, blocks
FROM dba_free_space
WHERE tablespace_name = 'TEST_TS';

TABLESPACE_NAME    FILE_ID     BLOCK_ID           BYTES        BLOCKS
ㅡㅡㅡㅡㅡㅡㅡㅡㅡ  ㅡㅡㅡㅡ   ㅡㅡㅡㅡㅡ     ㅡㅡㅡㅡㅡㅡ   ㅡㅡㅡㅡㅡㅡ
TEST_TS                 5         48391       1751121920       213760

1 row selected.

4. Move the table to the existing tablespace and rebuild the index

SQL> ALTER TABLE TEST_TABLE MOVE TABLESPACE TEST_TS;

Table 'TEST_TABLE' altered.

SQL> ALTER INDEX _TEST_CON121000125 REBUILD TABLESPACE TEST_TS;

Index '_TEST_CON121000125' altered.

5. Check free space in the existing tablespace again

SQL> SELECT tablespace_name, file_id, block_id, bytes, blocks
FROM dba_free_space
WHERE tablespace_name = 'TEST_TS';

TABLESPACE_NAME    FILE_ID   BLOCK_ID     BYTES          BLOCKS
ㅡㅡㅡㅡㅡㅡㅡㅡㅡ  ㅡㅡㅡㅡㅡ  ㅡㅡㅡㅡㅡ   ㅡㅡㅡㅡㅡㅡㅡ   ㅡㅡㅡㅡㅡㅡ
TEST_TS            5          23         131072          16
TEST_TS            5          135        262144          32
TEST_TS            5          311        131072          16
TEST_TS            5          583        131072          16
TEST_TS            5          727        131072          16
TEST_TS            5          871        389283840       47520
TEST_TS            5          92423      1390411776      169728

7 rows selected.

• Sum = 1,780,482,048
• When moving the table to the existing tablespace, the free space value increased from 1,751,121,920 to 1,780,482,048, but data fragmentation occurred.

Executing table move to a new tablespace

1. Create a new TEST Tablespace

CREATE TABLESPACE NEW_TEST DATAFILE '/home/tibero7/datafile/new_test.dtf' SIZE 2G AUTOEXTEND OFF;

2. Re-execute TABLE MOVE and Index Rebuild

ALTER TABLE TEST_TABLE MOVE TABLESPACE NEW_TEST;

Table 'TEST_TABLE' altered.

ALTER INDEX _TEST_CON121000125 REBUILD TABLESPACE NEW_TEST;

Index '_TEST_CON121000125' altered.

3. Check free space in the new tablespace after moving

SELECT tablespace_name, file_id, block_id, bytes, blocks
FROM dba_free_space
WHERE tablespace_name = 'NEW_TEST';

TABLESPACE_NAME    FILE_ID   BLOCK_ID      BYTES       BLOCKS
ㅡㅡㅡㅡㅡㅡㅡㅡㅡ  ㅡㅡㅡㅡㅡ  ㅡㅡㅡㅡㅡ   ㅡㅡㅡㅡㅡㅡ   ㅡㅡㅡㅡㅡㅡ
NEW_TEST           7         44807       1780482048    217344

1 row selected.

• Sum = 1,780,482,048
• When moving the table to the new tablespace, the free space bytes value is 1,780,482,048, which is the same as the value when moved to the existing tablespace, but no data fragmentation occurred.