impdp如何自動創建用戶前提條件與應用場景

小編給大家分享一下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如何自動創建用戶前提條件與應用場景”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！