Q
Problem solve Get help with specific problems with your technologies, process and projects.

Move table without MOVE TABLESPACE command

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.

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.
  1. Create the table in the other tablespace:
         CREATE TABLE new_table TABLESPACE new_ts
         AS SELECT * FROM old_table;
    
  2. Drop the original table:
         DROP TABLE old_table;
    
  3. 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.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close