Moving table(s) to a different tablespace

Q: What happens when u move a table to a different Tablespace?
Q: How can u move table to a different Tablespace which have long datatype?
Q: How can u move table to a different Tablespace with indexes?

There are a couple of ways in which a table can be moved to a different tablespace:

1) One of them is to perform export/import.
2) Another approach is to use the 'alter table' command with 'move tablespace' clause.

Practice:
CREATE TABLE TEST (
TEST_ID NUMBER(9) NOT NULL, TEST_DESC VARCHAR(10),
CONSTRAINT PK_TEST PRIMARY KEY(TEST_ID));

CREATE INDEX TEST_IND_1 ON TEST(TEST_DESC);

Above SQLs creates table and index in user's default tablespace. In our case, the default tablespace is "USERS". Run following SQL to check where these objects are created-

SELECT TABLE_NAME,TABLESPACE_NAME,STATUS FROM USER_TABLES
WHERE TABLE_NAME = 'TEST'
UNION ALL
SELECT INDEX_NAME,TABLESPACE_NAME,STATUS FROM USER_TABLES
WHERE TABLE_NAME = 'TEST';

TABLE_NAME TABLESPACE_NAME----------STATUS
----------------------- ----------------------------------------------------------
TEST -------------------- USERS -------------------------- VALID
TEST_IND_1 -------- USERS -------------------------- VALID
PK_TEST -------------- USERS -------------------------- VALID

INSERT INTO TEST VALUES(1,'Hello'); commit;

SQL> SELECT ROWID, TEST_ID FROM TEST;----------- [Retrieve value for TEST_ID and ROWID]

ROWID ----------------------------TEST_ID
-------------------------------------------------------------
AAAQ+eAAEAAAA3tAAA -------------1

SQL> ALTER TABLE TEST MOVE TABLESPACE SLMDATA; [Move TEST table to SLMDATA]

SQL> SELECT ROWID, TEST_ID FROM TEST;--------------------------[Check ROWID value again]

ROWID -------------------------- TEST_ID
--------------------------------------------------------
AAAQ+hAAHAAAAAsAAA ---------- 1

Moving table to new tablespace will make all the indexes on the table unusable.

TABLE_NAME-------------------------- TABLESPACE_NAME ----------STATUS
--------------------------------------------------- -----------------------------------------------------------------------------------------------
TEST ------------------------------------------------SLMDATA ---------------------VALID
TEST_IND_1 ------------------------------------USERS --------------------------UNUSABLE
PK_TEST ------------------------------------------USERS --------------------------UNUSABLE

As, indexes still point to older tablespace and are in 'unusable' status. Indexes in unusable state will prevent any DML activity on the table.

INSERT INTO TEST VALUES(2,'World!');
ERROR at line 1:
ORA-01502: index ‘DECIPHER.PK_TEST’ or partition of such index is in unusable State

Why this happened?
When we moved table to new tablespace, each row of the table got moved and got new ROWID. If we compare the ROWID values before and after the move, we'll realize that ROWID for the same TEST_ID is different. Indexes point to the previous location of the row and not to the current location. If we want to keep the index in the current tablespace (USERS), we just need to issue only rebuild clause. If we also want to move index to the new tablespace then we have to include tablespace clause. Following is the example of both.

ALTER INDEX PK_TEST REBUILD;
ALTER INDEX TEST_IND_1 REBUILD TABLESPACE SLMDATA;

First statement only rebuilds the primary key index without moving it to new tablespace.
Second statement rebuilds the index and also moves it to the other tablespace.

SELECT INDEX_NAME,TABLESPACE_NAME,STATUS FROM User_Indexes
WHERE TABLE_NAME = 'TEST';

INDEX_NAME TABLESPACE_NAME ---------------------STATUS
--------------------------------------------------------------------------------------------------
TEST_IND_1 SLMDATA ---------------------------------------------VALID
PK_TEST USERS --------------------------------------------------------VALID

One thing to remember is that 'MOVE TABLESPACE' does not work if table contains column with LONG or LONG RAW data type. You will run into ‘ORA-00997: illegal use of LONG datatype error. Such tables can be moved to new tablespace using exp/imp command.