ITPub博客

首页 > 数据库 > Oracle > 【Oracle】将hr用户下所有对象导入到新建aa用户

【Oracle】将hr用户下所有对象导入到新建aa用户

Oracle 作者:村木直 时间:2013-12-08 13:02:11 0 删除 编辑

起创始建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
  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>>
存储位置普通,到此真验结束。

 


<!-- 正文结束 -->

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23702883/viewspace-1115457/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2010-04-11