Q

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
    
This was first published in May 2007

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close