1) One of them is to perform export/import.
2) Another approach is to use the ‘alter table’ command with ‘move tablespace’ clause.
Let us start by creating a table. Connect to SQL*Plus using proper credentials and run the following SQLs:
CREATE TABLE SAMPLE
(
SAMPLE_ID NUMBER(9) NOT NULL,
SAMPLE_DESC VARCHAR(10),
CONSTRAINT PK_SAMPLE PRIMARY KEY(SAMPLE_ID)
)
/
CREATE INDEX SAMPLE_IND_1 ON SAMPLE(SAMPLE_DESC)
/
Above SQLs creates table and index in user’s default tablespace. In our case it is ‘users’ tablespace. Run following SQL to check where these objects are created.
SELECT TABLE_NAME,TABLESPACE_NAME,STATUS
FROM User_Tables
WHERE TABLE_NAME = ‘SAMPLE’
UNION ALL
SELECT INDEX_NAME,TABLESPACE_NAME,STATUS
FROM User_Indexes
WHERE TABLE_NAME = ‘SAMPLE’
/
And the result is
TABLE_NAME TABLESPACE_NAME STATUS
—————————— —————————— ——–
SAMPLE USERS VALID
SAMPLE_IND_1 USERS VALID
PK_SAMPLE USERS VALID
Since we are using UNION ALL, two indexes PK_SAMPLE (Primary key index) and SAMPLE_IND_1 are also shown under TABLE_NAME column. Result shows that table and its corresponding indexes are crated in USERS tablespace and in valid status. Now let us insert one record into the table.
INSERT INTO SAMPLE VALUES(1,’Hello’);
COMMIT;
Now let us retrieve value for SAMPLE_ID column along with value of pseudo column ROWID. We will explain you later in the article, why we are retrieving ROWID. Following is the SQL followed by result.
SQL> SELECT ROWID, SAMPLE_ID FROM SAMPLE;
ROWID SAMPLE_ID
—————— ———-
AAAQ+eAAEAAAA3tAAA 1
Now we will move the SAMPLE table to another tablespace ‘TEST’. We already have another tablespace TEST in place. If you don’t have another tablespace where you can move the table, please create the tablespace first for testing purpose. Following command will move table SAMPLE to new tablespace TEST.
SQL> ALTER TABLE SAMPLE MOVE TABLESPACE TEST;
Above command will move table to new tablespace TEST. Now let us check ROWID value again by re-issuing previous SQL.
SQL> SELECT ROWID, SAMPLE_ID FROM SAMPLE;
ROWID SAMPLE_ID
—————— ———-
AAAQ+hAAHAAAAAsAAA 1
So far we have moved only table. Moving table to new tablespace will make all the indexes on the table unusable. We can verify it by issuing the SQL we executed initially after creation of table and index.
TABLE_NAME TABLESPACE_NAME STATUS
—————————— —————————— ——–
SAMPLE TEST VALID
SAMPLE_IND_1 USERS UNUSABLE
PK_SAMPLE USERS UNUSABLE
Result shows that table has been moved to new tablespace TEST and is in ‘valid’ status while indexes still point to older tablespace and are in ‘unusable’ status. Indexes in unusable state will prevent any DML activity on the table. We can verify this by inserting new record into SAMPLE table.
INSERT INTO SAMPLE VALUES(2,’World!’);
Running above SQL will result in following error.
ERROR at line 1:
ORA-01502: index ‘DECIPHER.PK_SAMPLE’ 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 you compare the ROWID values before and after the move, you will realize that ROWID for the same SAMPLE_ID is different. Indexes point to the previous location of the row and not to the current location. This warrants for rebuilding of an index. If you also want to move index to the new tablespace then you can include tablespace clause. If you want to keep the index in the same tablespace where it is currently, you just need to issue only rebuild clause. Following is the example of both.
ALTER INDEX PK_SAMPLE REBUILD;
ALTER INDEX SAMPLE_IND_1 REBUILD TABLESPACE TEST;
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. Verify it by issuing following SQL.
SELECT INDEX_NAME,TABLESPACE_NAME,STATUS
FROM User_Indexes
WHERE TABLE_NAME = ‘SAMPLE’;
Result is
INDEX_NAME TABLESPACE_NAME STATUS
—————————— —————————— ——–
SAMPLE_IND_1 TEST VALID
PK_SAMPLE USERS VALID
Once indexes are rebuilt, all DML operations can be resumed on the table. If you try to insert the record, which gave error previously, should work fine after rebuilding all the indexes on the table. 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.
