To transfer a user's table to another table space
(1) Exp. export all data by user or by other means
EXP Owner=user1 file=user1db. CM7
(2) If the table space is replaced in the original instance, delete all the tables for that user first.
Select ' Drop table ' | | user_tables| | '; ' from User_tables; A statement that generates a delete table can also delete all tables using Enterprise Manager
(3) using IMP to create a table structure script
IMP full=y file=user1db. DMP Indexfile=user1. Sql
This step does not import data, but will user1db. DMP User1 User's table structure, index and other information health become User1. Sql.
Remove the User1. All the comments and quotes in the SQL script, and then modify all the table spaces in the script to specify the table space for the transfer.
(4) Call execution User1. SQL Script Program. Creates all tables, indexes, and so on in the newly specified table space.
(5) Import data.
IMP full=y file=user1db. DMP Ignore=y
IMP full=y indexes=n file=user1db. DMP ignore=y If an index import problem occurs, the Indexes=n does not import the index.
Imp market/market@orawww160_55 fromuser=marketsys indexes=n file=expdat. DMP ignore=y log=imp1.log tables= (doctrack,newsinfo)
Select ' Drop table ' | | table_name | | '; ' from User_tables;