How to imp out of data into different table spaces

Source: Internet
Author: User
Tags oracle database

Author:kamus<?xml:namespace prefix = o ns = "Urn:schemas-microsoft-com:office:office"/>

Mail:kamus@itpub.net

Date:2004-1

People often ask: the original data in the Users table space, I want to put it imp into the app table space, I have modified the destination user's default table space, why the results or IMP to the users table space.

On this issue, the following explanations are given:

Oracle does not provide any parameters to specify which tablespace to import, and the data is imported by default into the tablespace in which the data was originally exported, but we can implement the import to a different table space in the following ways.

1. Use the Indexfile parameter when imp

When you assign a file name to this parameter, all of the index in IMP is not imported directly into the tablespace, but the script that creates the index is generated in the specified file.

Then open the file with a text editor, edit the storage parameter in the script directly, and modify the table space name you want to import.

Then, re-execute IMP, using the Indexs=n parameter to import objects except index.

Finally enter the Sql*plus, directly run the script just edited, build the index.

This method is useful for importing index and constraints into the specified tablespace.

2. Change the default table space for the destination user

This is the way that is often asked. But the problem was not successful because of the lack of the following steps.

First, retract the "unlimited tablespace" permission for the destination user:

Revoke unlimited tablespace from username;

Second, the quota of the destination user in the original data export table space is canceled, which forces imp to import the data into the user's default tablespace.

Then, set the table space you want to import as the default tablespace for the destination user and add quotas.

Finally, implement Imp.

3. Using Toad

Toad is a powerful Oracle database management software, the quest of Third-party software, we can use the Rebuild Multi objects tools to achieve the transfer of multiple objects to the specified table space.

So we can no matter 3,721, first imp, and then use Toad for later modification.

The use of Toad is not explained here in detail.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.