小編給大家分享一下impdp如何自動(dòng)創(chuàng)建用戶前提條件與應(yīng)用場(chǎng)景，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！
網(wǎng)站建設(shè)哪家好，找成都創(chuàng)新互聯(lián)公司！專注于網(wǎng)頁(yè)設(shè)計(jì)、網(wǎng)站建設(shè)、微信開發(fā)、小程序設(shè)計(jì)、集團(tuán)企業(yè)網(wǎng)站建設(shè)等服務(wù)項(xiàng)目。為回饋新老客戶創(chuàng)新互聯(lián)還提供了海興免費(fèi)建站歡迎大家使用！
impdp命令在導(dǎo)入數(shù)據(jù)時(shí),如果用戶存在,則會(huì)自動(dòng)創(chuàng)建該用戶,因?yàn)閑xpdp導(dǎo)出的dmp文件中包含了創(chuàng)建用戶的腳本信息(包括密碼,缺省表空間,臨時(shí)表空間等)。
impdp自動(dòng)創(chuàng)建用戶有一個(gè)前提條件，就是需要首先創(chuàng)建用戶的缺省表空間和臨時(shí)表空間，如果缺省表空間或者臨時(shí)表空間不存在，則自動(dòng)創(chuàng)建用戶會(huì)失敗，導(dǎo)致導(dǎo)入數(shù)據(jù)的失敗。
下面通過實(shí)驗(yàn)來描述impdp自動(dòng)創(chuàng)建用戶的前提條件和應(yīng)用場(chǎng)景
[@more@]
一. 創(chuàng)建表空間和用戶
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.
二.創(chuàng)建DIRECTORY,導(dǎo)出用戶的數(shù)據(jù)
##為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
三.刪除用戶,刪除用戶的表空間和臨時(shí)表空間
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
四.導(dǎo)入用戶數(shù)據(jù),測(cè)試是否可以自動(dòng)創(chuàng)建用戶
[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
可以看到自動(dòng)創(chuàng)建用戶時(shí),因?yàn)橛脩舯砜臻g和臨時(shí)表空間不存在,所以創(chuàng)建用戶失敗,報(bào)錯(cuò)為:
Failing sql is:
CREATE USER "AIDU" IDENTIFIED BY VALUES 'FBF36F881A20141D' DEFAULT TABLESPACE "AIDU" TEMPORARY TABLESPACE "TEMP2"
筆者曾經(jīng)嘗試只建立用戶表空間aidu,但不去建立臨時(shí)表空間,嘗試導(dǎo)入用戶數(shù)據(jù),自動(dòng)創(chuàng)建用戶仍然失敗.所以用戶表空間和臨時(shí)表空間都需要在導(dǎo)入前存在.
五.創(chuàng)建用戶表空間和臨時(shí)表空間,為導(dǎo)入數(shù)據(jù)做好準(zhǔn)備
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導(dǎo)入用戶數(shù)據(jù),自動(dòng)創(chuàng)建用戶成功.
[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
總結(jié):
impdp 是可以自動(dòng)創(chuàng)建用戶,但有一個(gè)前提條件:用戶的缺省表空間和臨時(shí)表空間要先創(chuàng)建好(存在).
使用impdp自動(dòng)創(chuàng)建用戶應(yīng)用場(chǎng)景:
1.數(shù)據(jù)庫(kù)數(shù)據(jù)遷移或者升級(jí)到另外一個(gè)數(shù)據(jù)庫(kù)環(huán)境,并且數(shù)據(jù)庫(kù)中有很多用戶,DBA無法知道每一個(gè)用戶的密碼,只能查到用戶的缺省表空間和臨時(shí)表空間.
2.DBA不能重設(shè)用戶的密碼,重設(shè)密碼將會(huì)導(dǎo)致很多舊的應(yīng)用系統(tǒng)需要進(jìn)行配置,存在一定的風(fēng)險(xiǎn).
3.新的數(shù)據(jù)庫(kù)的IP,PORT都與舊的相同,應(yīng)用系統(tǒng)機(jī)會(huì)不需要做任何修改.
通過impdp創(chuàng)建用戶可以保留用戶的密碼信息,應(yīng)用系統(tǒng)不需要進(jìn)行配置.
附查詢用戶缺省表空間與臨時(shí)表空間的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如何自動(dòng)創(chuàng)建用戶前提條件與應(yīng)用場(chǎng)景”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道！
重慶分公司
重慶分公司
