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