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

Exporting a schema to a new tablespace

I took the export of one schema and I want to import it into another schema. When I import the dumpfile it is going to the old tablespace only. Because of this I need to increase the old tablespace.

I took the export of one schema and I want to import it into another schema. The user is created on a different tablespace, but when I import the dumpfile it is going to the old tablespace (the tablespace on which my exported schema resides) only. Because of this I need to increase the old tablespace. Please help me.
If you look at the contents of the dump file (with the INDEXFILE command of the imp utility), you will see that the CREATE TABLE and CREATE INDEX statements include the old tablespace name. This is not desirable for your situation. To resolve this, follow these steps to import the schema's contents into the NEW_USER schema.

  1. Ensure that the new user cannot create objects in the old tablespace:
       REVOKE UNLIMITED TABLESPACE FROM new_user;
       ALTER USER new_user QUOTA 0 ON old_tablespace;
    
  2. Ensure that the new user can create objects in the new tablespace:
       ALTER USER new_user QUOTA unlimited ON new_tablespace;
    
  3. Make the new tablespace the new user's default tablespace:
       ALTER USER new_user DEFAULT TABLESPACE new_tablespace;
    
  4. Import the objects from the dump file.
       imp userid=system file=mydump.dmp fromuser=old_user touser=new_user
    
    This step works because any CREATE TABLE or CREATE INDEX statements on import will not be able to create in the old tablespace. Those objects will then be created in the user's default tablespace.
This was last published in October 2005

Dig Deeper on Oracle database backup and recovery

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.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close