Shulou(Shulou.com)06/01 Report--

Use imp to import tables and indexes to different table space method is what, believe that many people without experience at a loss about this, for this reason this article summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

A method of importing tables and indexes into different tablespaces using expdp/impdp

Export:

expdp test/test directory=dump_dir dumpfile=test.dmp logfile=test_expdp.log tables=t

Import table data:

impdp test/test directory=dump_dir dumpfile=test.dmp logfile=impdp_data.log remap_tablespace=system:datatb exclude=index

Import index data:

impdp test/test directory=dump_dir dumpfile=test.dmp logfile=impdp_index.log remap_tablespace=system:idxtb include=index

Query statement:

select segment_name,tablespace_name from dba_segments where owner='TEST';

This article records the exp/imp method to import tables and indexes into different tablespaces. First, explain the reasons:

Because the application data needs to be migrated, the application data in the original database needs to be migrated to the target database through import and export. At the same time, due to the new planning requirements, the data in the table needs to be distinguished from the table space where the index is stored. The database version before and after migration is Oracle 10g. This operation could have been done using the above expdp/impdp method, but since there is not enough space in the source database to store the exported dmp file, it is necessary to use other methods to complete the data migration.

Let's start with the general exp/imp migration method:

(For convenience of explanation, the following example is to import the data under user a to user b, and at the same time, the data in the table and the index table space need to be migrated to different table spaces)

When migrating data in exp/imp mode, if you want to change the table space where the data is stored, you need to pay attention to two points:

(1) Import user does not have DBA authority

(2) Import user does not have unlimited database permission

In this case, the imported data will be stored in its default table space. For details, please refer to the article http://www.eygle.com/archives/2005/04/ecineeeiaeioae.html by master eygle.

However, it is impossible to import the data and indexes in the table into different tablespaces. If you want to import the indexes into other tablespaces, the method is as follows:

Export data under a user

exp a/a file=exp_test.dmp log=exp_test.log

First do not import index, only import table data part:

imp b/b file=exp_test.dmp log=imp_test_data.log fromuser=a touser=b indexes=n

After that, save the SQL statement that created the index to the text through the indexfile parameter:

imp b/b file=exp_test.dmp log=imp_test_index.log fromuser=a touser=b indexfile=index.sql

Finally, the actual import requirement is accomplished by modifying and executing the SQL statement index.sql created by the index.

The above methods can complete the requirements, but the operation is more troublesome, especially the following:

(1) Apply the default table space imported by the user and the permissions associated with that user.

(2) The method of modifying the text of index.sql is troublesome.

The following is my exp/imp migration method for Oracle 10g, which is also the actual method used for this data migration:

First migrate metadata using the expdp/impdp method:

Export:

expdp system/oracle directory=dump_dir dumpfile=expdp_test.dmp logfile=expdp_test.log CONTENT=METADATA_ONLY schemas=a

Import metadata, specifying different tablespaces.

impdp system/oracle directory=dump_dir dumpfile=expdp_test.dmp logfile=impdp_test_data.log remap_schema=a:b remap_tablespace=splex:datatb exclude=index

impdp system/oracle directory=dump_dir dumpfile=expdp_test.dmp logfile=impdp_test_index.log remap_schema=a:b remap_tablespace=splex:proftb include=index

After that, migrate the application data by exp/imp. Note that the character set of the source database, the character set of the target database and the character set of the client should be consistent to avoid character conversion:

exp a/a file=exp_test.dmp log=exp_test.log STATISTICS=none buffer=52428800

imp b/b file=exp_test.dmp log=imp_test.log fromuser=a touser=b ignore=y commit=y buffer=52428800 indexes=n grants=n constraints=n

In the above way, exp/imp migration data method can be completed.

Other export methods for data pumps:

Although this data migration is not used, it is still recorded.

In fact, the data pump can export data on the client side, that is, specify the NETWORK_LINK parameter:

expdp system/password NETWORK_LINK=db_link_name DIRECTORY=data_pump_dir DUMPFILE=server.dmp TABLES=employees

This method involves two database instances, which is equivalent to the database on the client side accessing the database on the server side by using database link, and exporting the data on the server side by data pump.

After reading the above, do you know how to import tables and indexes into different tablespaces using imp? If you still want to learn more skills or want to know more related content, welcome to pay attention to the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.