【Oracle】将hr用户下所有对象导入…

首先创建aa用户：

sys@ORCL> create user aa identified by aa;

User created.

用hr用户登录查看用户权限：

sys@ORCL> conn hr/hr
Connected.
hr@ORCL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

14 rows selected.

hr@ORCL>
用sys用户将这些权限赋予aa用户：

sys@ORCL> grant CREATE SESSION,ALTER SESSION,UNLIMITED TABLESPACE,CREATE TABLE,CREATE CLUSTER,CREATE SYNONYM,CREATE VIEW,CREATE SEQUENCE,CREATE DATABASE LINK,CREATE PROCEDURE,CREATE TRIGGER,CREATE TYPE,CREATE OPERATOR,CREATE INDEXTYPE to aa;

Grant succeeded.

sys@ORCL>

为aa用户建立表空间

sys@ORCL> create tablespace aa
  2  datafile '/u01/app/oracle/oradata/ORCL/aa01.dbf' size 20m;

Tablespace created.

 

sys@ORCL> alter user aa default tablespace aa quota unlimited on aa;     

User altered.

用户创建完毕并赋予权限后开始从hr用户导出数据：

[oracle@oracle ~]$ exp hr/hr file=hr_aa_130906.dmp log=hr_aa_130906.log

Export: Release 10.2.0.1.0 - Production on Sun Sep 8 15:13:57 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

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 US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user HR
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user HR
About to export HR's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export HR's tables via Conventional Path ...
. . exporting table                      COUNTRIES         25 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                    DEPARTMENTS         27 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                      EMPLOYEES        107 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                           JOBS         19 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                    JOB_HISTORY         10 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                      LOCATIONS         23 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                        REGIONS          4 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.
[oracle@oracle ~]$

将数据导入到aa用户中：
[oracle@oracle ~]$ exp hr/hr file=hr_aa_130906.dmp log=hr_aa_130906.log

Export: Release 10.2.0.1.0 - Production on Sun Sep 8 15:13:57 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

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 US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user HR
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user HR
About to export HR's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export HR's tables via Conventional Path ...
. . exporting table                      COUNTRIES         25 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                    DEPARTMENTS         27 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                      EMPLOYEES        107 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                           JOBS         19 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                    JOB_HISTORY         10 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                      LOCATIONS         23 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                        REGIONS          4 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.

导出成功，开始导入
[oracle@oracle ~]$ imp system/oracle fromuser=hr touser=aa file=hr_aa_130906.dmp log=hr_aa_130906_imp.log;

Import: Release 10.2.0.1.0 - Production on Sun Sep 8 15:26:11 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

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 HR, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing HR's objects into AA
. . importing table                    "COUNTRIES"         25 rows imported
. . importing table                  "DEPARTMENTS"         27 rows imported
. . importing table                    "EMPLOYEES"        107 rows imported
. . importing table                         "JOBS"         19 rows imported
. . importing table                  "JOB_HISTORY"         10 rows imported
. . importing table                    "LOCATIONS"         23 rows imported
. . importing table                      "REGIONS"          4 rows imported
About to enable constraints...
Import terminated successfully without warnings.
[oracle@oracle ~]$

导入成功，但是并不算完成 我们来看一下aa用户的默认表空间和导入数据的存放表空间

aa@ORCL> select username,default_tablespace from user_users;

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
AA                             AA

aa@ORCL> select table_name,tablespace_name from user_tables;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
DEPARTMENTS                    EXAMPLE
EMPLOYEES                      EXAMPLE
JOBS                           EXAMPLE
JOB_HISTORY                    EXAMPLE
LOCATIONS                      EXAMPLE
REGIONS                        EXAMPLE
COUNTRIES

7 rows selected.

aa@ORCL>
可以看到导入的表被放在了example表空间，这是因为imp导入的时候会首先按照原来的存储结构建立表，hr用户的表是放在example表空间中的，导入数据也会自动放到同一位置，而aa用户又拥有unlimited tablespace权限所以就可以正常在example表空间中插入数据，要解决这个问题要进行以下操作，收回aa用户的unlimited tablespace权限：

sys@ORCL> revoke unlimited tablespace from aa;

Revoke succeeded.

sys@ORCL>
然后删除用户下导入的所有对象，重新导入内容后再查询表放在哪个表空间：
aa@ORCL> select table_name,tablespace_name from user_tables;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
DEPARTMENTS                    AA
EMPLOYEES                      AA
JOBS                           AA
JOB_HISTORY                    AA
LOCATIONS                      AA
REGIONS                        AA
COUNTRIES

7 rows selected.

aa@ORCL>
存储位置正常，到此实验结束。