Import/Export(??EXP-00091) - 数据库编程

(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL "
"DEFAULT) LOGGING NOCOMPRESS"
Import terminated successfully with warnings.
导入表结构到指定用户：

修改test用户的默认表空间为test：
SQL> create user test identified by oracle;

User created.
SQL> select username,default_tablespace from user_users;

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEST USERS（默认情况）

SQL> grant dba to test;

Grant succeeded.
SQL> create tablespace test
2 datafile '/u01/app/oracle/oradata/orcl/tests.dbf'
3 size 50m;

Tablespace created.
SQL> alter user test default tablespace test
2 ;

User altered.
SQL> select username,default_tablespace from user_users;

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEST TEST
测试：
[oracle@linux5 orcl]$ export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
[oracle@linux5 orcl]$ echo $NLS_LANG
AMERICAN_AMERICA.WE8ISO8859P1
[oracle@linux5 orcl]$ exp scott/oracle tables=dept,emp file=/u01/imp_exp/dept_emp.dmp log=/u01/imp_exp/dept_emp.log

Export: Release 10.2.0.1.0 - Production on Thu Apr 10 18:14:00 2014

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table DEPT 4 rows exported
. . exporting table EMP 14 rows exported
Export terminated successfully without warnings.
导入：
[oracle@linux5 orcl]$ imp test/oracle fromuser=scott touser=test file=/u01/imp_exp/dept_emp.dmp log=/u01/imp_exp/scott_to_test.log

Import: Release 10.2.0.1.0 - Production on Thu Apr 10 18:14:44 2014

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by SCOTT, not by you

import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into TEST
. . importing table "DEPT" 4 rows imported
. . importing table "EMP" 14 rows imported
About to enable constraints...
Import terminated successfully without warnings.
10g用户的默认表空间是USERS表空间：
SQL> select username,default_tablespace from user_users;

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEST TEST

SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
EMP USERS
DEPT USERS

SQL> conn scott/oracle
Connected.
SQL> select table_name,tablespace_name from user_tables where table_name in ('emp','dept');

no rows selected

SQL> select table_name,tablespace_name from user_tables where table_name in ('EMP','DEPT');

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
DEPT USERS
EMP USERS
解决：
删除之前的导入数据，显示授权用户指定表空间的存储权限，并回收Unlimited Tablespace权限：
例如：授予Test用户无限操作Test表空间权限，并收回UT权限：
SQL> conn test/oracle
Connected.
SQL> drop table emp purge;

Table dropped.

SQL> drop table dept purge;

Table dropped.

SQL> select * from tab;

no rows selected

SQL> conn / as sysdba
Connected.
SQL> alter user test quota unlimited on t

Import/Export(??EXP-00091)(二)