Ask the Expert

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?

    Requires Free Membership to View

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

This was first published in May 2007

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: