How to select a tablespace when Oracle uses imp to import user tables

Http://hi.baidu.com/cat_june/blog/item/0573a945fd8ead36879473e4.html

I think I still have a good grasp of Oracle, but yesterday, my colleagues in the lab next door asked me a question, which made me depressed. In A database, she exports A user A's table on tablespace AA to exp using exp. in the dmp file, import it to the database on another machine. In this case, user B is used, and the default tablespace is BB, however, when using imp for import, we found that the table was imported to user B, but there is still an AA tablespace, but she needs to import it to user B. Then we tried it several times, this is the case. I found that I used to copy the database and did not change the tablespace. So I checked it online. Fortunately, there is a solution for the good guys.

SQL> create user myhuang identified by myhuang default tablespace myhuang; // create a user first

SQL> grant resource, connect to myhuang;

SQL> grant dba to myhuang; // grant DBA Permissions

SQL> revoke unlimited tablespace from myhuang; // revoke this permission

SQL> alter user myhuang quota 0 on system; // set the user's quota in the System tablespace to 0

SQL> alter user myhuang quota unlimited on myhuang; // the user's quota in the myhuang tablespace is not limited.

After the preceding settings, you can use imp to import data into the specified myhuang tablespace:

C: \ Documents ents and Settings \ myhuang> imp system/123456 @ vdb fromuser = lnxh tous

Er = myhuang file = G: \ myhuang \ lnxh. dmp ignore = y grants = n

By the way, there are two minor issues:

(1) IMP-00003: ORACLE error 1658

ORA-01658: Unable to create INITIAL partition for segments in tablespace MYHUANG

Generally, this problem can be solved by increasing the size of the tablespace data file by Resize.

(2) Delete a tablespace

SQL> drop tablespace myhuang including contents and datafiles;

 

[Personal supplement]

In case of IMP-00003: ORACLE error 1950 encountered.

ORA-01950: no privileges on tablespace 'xxxxxxx'

It indicates that your table has fields such as BLOB, and such tables cannot be imported into new tablespaces.

There is no perfect solution to this problem in 9i. It is estimated that only one tablespace with the same name can be created first, and then the table can be moved to the new tablespace.

I don't know what is like in 10g and 11G. I haven't tested it.