Hi Brian, I read your answers to questions about moving a table to another tablespace. You said to use ALTER TALBLE....MOVE TABLESPACE. The problem I have is we here still use Oracle 8.1.5 and it doesn't have MOVE TABLESPACE. I was thinking about doing a cumbersome process whereby I create a temp table (called TEMPTAB) from the one to be moved (called OLDTAB), drop OLDTAB and re-create OLDTAB from TEMPTAB in the desired tablespace. Does it sound OK?
I'd modify your process just a bit. I'd make use of the ability to rename a table, which does exist in Oracle 8i.
- Create the table in the other tablespace:
CREATE TABLE new_table TABLESPACE new_ts
AS SELECT * FROM old_table;
- Drop the original table:
DROP TABLE old_table;
- Rename the new table to the old one's name:
ALTER TABLE new_table RENAME TO old_table;
You'll need to re-create the indexes, constraints and grants with this method though.