小编给大家分享一下impdp如何自动创建用户前提条件与应用场景，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！
impdp命令在导入数据时,如果用户存在,则会自动创建该用户,因为expdp导出的dmp文件中包含了创建用户的脚本信息(包括密码,缺省表空间,临时表空间等)。
impdp自动创建用户有一个前提条件，就是需要首先创建用户的缺省表空间和临时表空间，如果缺省表空间或者临时表空间不存在，则自动创建用户会失败，导致导入数据的失败。
下面通过实验来描述impdp自动创建用户的前提条件和应用场景
[@more@]
一.
创建表空间和用户
SQL> create
tablespace aidu datafile '/oradata/gridctl/aidu01.dbf' size 128m extent
management local segment space management auto logging;
Tablespace
created.
SQL> create
temporary tablespace temp2 tempfile '/oradata/gridctl/temp021.dbf' size 128m
extent management local;
Tablespace
created.
SQL> create user
aidu profile default identified by "aidutest" default tablespace aidu
temporary tablespace temp2 account unlock;
User
created.
SQL> grant
resource,connect to aidu;
Grant succeeded.
SQL> conn
aidu/aidutest
Connected.
SQL> create table
test (id number(10) not null,name varchar2(20));
Table created.
SQL> insert into
test values(1,'first');
1 row created.
SQL> insert into
test select id+1,name from test;
1 row created.
SQL> insert into
test select id+2,name from test;
2 rows created.
SQL> select * from
test;
ID NAME
----------
--------------------
1 first
2 first
3 first
4 first
SQL> commit;
Commit
complete.
二.创建DIRECTORY,导出用户的数据
##为expdp,impdp建立directory
SQL>create
directory impdp as '/oradata/gridctl' ;
SQL>grant
read,write on directory impdp to aidu;
[oracle@primarydb ~]$
expdp system/****** schemas=aidu directory=impdp dumpfile=aidu2.dmp
Export:
Release 10.2.0.4.0 - 64bit Production on Friday, 11 February, 2011 8:36:18
Copyright
(c) 2003, 2007, Oracle. All rights
reserved.
Connected
to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
Production
With
the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting
"SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** schemas=aidu directory=impdp
dumpfile=aidu.dmp
Estimate
in progress using BLOCKS method...
......
Processing
object type SCHEMA_EXPORT/TABLE/TABLE
. .
exported "AIDU"."TEST" 5.304 KB 4 rows
Master
table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully
loaded/unloaded
******************************************************************************
Dump
file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/oradata/aidu.dmp
Job
"SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at
08:36:36
[oracle@primarydb
oradata]$ ls -lt aidu*
-rw-r-----
1 oracle oinstall 155648 Feb 11 08:36 aidu.dmp
三.删除用户,删除用户的表空间和临时表空间
SQL> drop user
aidu cascade;
User dropped.
SQL> drop
tablespace aidu including contents;
Tablespace dropped.
SQL> drop
tablespace temp2 including contents;
Tablespace dropped.
SQL> exit
四.导入用户数据,测试是否可以自动创建用户
[oracle@primarydb
oradata]$ impdp system/****** directory=impdp dumpfile=aidu.dmp
。。。。。。
Master
table "SYSTEM"."SYS_IMPORT_FULL_01" successfully
loaded/unloaded
Starting
"SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=impdp
dumpfile=aidu2.dmp
Processing
object type SCHEMA_EXPORT/USER
ORA-39083:
Object type USER failed to create with error:
ORA-00959:
tablespace 'AIDU' does not exist
Failing sql
is:
CREATE USER "AIDU" IDENTIFIED BY
VALUES 'FBF36F881A20141D' DEFAULT TABLESPACE "AIDU" TEMPORARY
TABLESPACE "TEMP2"
Processing
object type SCHEMA_EXPORT/SYSTEM_GRANT
ORA-39083:
Object type SYSTEM_GRANT failed to create with error:
ORA-01917:
user or role 'AIDU' does not exist
Failing
sql is:
GRANT
UNLIMITED TABLESPACE TO "AIDU"
。。。。。。
Processing
object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39083:
Object type TABLE failed to create with error:
ORA-01918:
user 'AIDU' does not exist
Failing
sql is:
CREATE
TABLE "AIDU"."TEST" ("ID" NUMBER(10,0) NOT NULL
ENABLE, "NAME" VARCHAR2(20)) PCTFREE 10 PCTUSED 40 INITRANS 1
MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1
MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT) TABLESPACE "AIDU"
Processing
object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Job
"SYSTEM"."SYS_IMPORT_FULL_01" completed with 7 error(s) at
09:26:51
可以看到自动创建用户时,因为用户表空间和临时表空间不存在,所以创建用户失败,报错为:
Failing sql
is:
CREATE USER "AIDU" IDENTIFIED BY
VALUES 'FBF36F881A20141D' DEFAULT TABLESPACE "AIDU" TEMPORARY
TABLESPACE "TEMP2"
笔者曾经尝试只建立用户表空间aidu,但不去建立临时表空间,尝试导入用户数据,自动创建用户仍然失败.所以用户表空间和临时表空间都需要在导入前存在.
五.创建用户表空间和临时表空间,为导入数据做好准备
SQL> create
tablespace aidu datafile '/oradata/gridctl/aidu01.dbf' size 128m reuse extent
management local segment space management auto logging;
Tablespace created.
SQL> create
temporary tablespace temp2 tempfile '/oradata/gridctl/temp021.dbf' size reuse
128m extent management local;
Tablespace created.
六.使用impdp导入用户数据,自动创建用户成功.
[oracle@primarydb
oradata]$ impdp system/aidu2010 directory=impdp dumpfile=aidu.dmp
。。。。。。
Master
table "SYSTEM"."SYS_IMPORT_FULL_01" successfully
loaded/unloaded
Starting
"SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=impdp
dumpfile=aidu.dmp
Processing
object type SCHEMA_EXPORT/USER
Processing
object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing
object type SCHEMA_EXPORT/ROLE_GRANT
Processing
object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing
object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing
object type SCHEMA_EXPORT/TABLE/TABLE
Processing
object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. .
imported "AIDU"."TEST" 5.304 KB 4 rows
Job
"SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at
08:49:42
[oracle@primarydb
oradata]$ sqlplus aidu/aidutest
SQL>
select * from test;
ID NAME
----------
--------------------
1 first
2 first
3 first
4 first
总结:
impdp
是可以自动创建用户,但有一个前提条件:用户的缺省表空间和临时表空间要先创建好(存在).
使用impdp自动创建用户应用场景:
1.数据库数据迁移或者升级到另外一个数据库环境,并且数据库中有很多用户,DBA无法知道每一个用户的密码,只能查到用户的缺省表空间和临时表空间.
2.DBA不能重设用户的密码,重设密码将会导致很多旧的应用系统需要进行配置,存在一定的风险.
3.新的数据库的IP,PORT都与旧的相同,应用系统机会不需要做任何修改.
通过impdp创建用户可以保留用户的密码信息,应用系统不需要进行配置.
附查询用户缺省表空间与临时表空间的SQL语句:
SQL> select
username,default_tablespace,temporary_tablespace from dba_users;
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------
------------------------------ ------------------------------
AIDU AIDU TEMP2
OUTLN SYSTEM TEMPTS1
SYS SYSTEM TEMPTS1
SYSTEM SYSTEM TEMPTS1
DBSNMP SYSAUX TEMPTS1
MGMT_VIEW MGMT_TABLESPACE TEMPTS1
SYSMAN MGMT_TABLESPACE TEMPTS1
WMSYS SYSAUX TEMPTS1
TSMSYS USERS TEMPTS1
DIP USERS TEMPTS1
ORACLE_OCM USERS TEMPTS1
以上是“impdp如何自动创建用户前提条件与应用场景”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注天达云行业资讯频道！