Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Move tables from system tablespace to users tablespace

How to move tables from the system tablespace to the users tablespace in Oracle 8.0.6?

How to move tables from the system tablespace to the users tablespace in Oracle 8.0.6?
If memory serves me correctly, the ALTER TABLE MOVE command is not available in Oracle 8.0.6. I do not have a version this old to double-check. If the ALTER TABLE MOVE command is available in this version, you can use it to move the table to a different tablespace. Otherwise, you can move the table with the export and import utilities.
  1. Export the table.
    exp userid=system/manager file=t.dmp tables=my_table
  2. Use the dump file to create a SQL file to re-create the table.
    imp userid=system/manager file=t.dmp tables=my_table show=y log=t.sql
  3. Step 2 will create a SQL script (t.sql). Edit this script to contain just the CREATE TABLE command. Modify the TABLESPACE parameter to create the table in the correct tablespace.
  4. Drop the table.
  5. Precreate the table by running the script you modified in step 3.
  6. Import the table's contents.
    imp userid=system/manager file=t.dmp tables=my_table ignore=y

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.