共計(jì) 5956 個(gè)字符，預(yù)計(jì)需要花費(fèi) 15 分鐘才能閱讀完成。
丸趣 TV 小編給大家分享一下 impdp 如何自動創(chuàng)建用戶前提條件與應(yīng)用場景，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！
impdp 命令在導(dǎo)入數(shù)據(jù)時(shí), 如果用戶存在, 則會自動創(chuàng)建該用戶, 因?yàn)?expdp 導(dǎo)出的 dmp 文件中包含了創(chuàng)建用戶的腳本信息 (包括密碼, 缺省表空間, 臨時(shí)表空間等)。
impdp 自動創(chuàng)建用戶有一個(gè)前提條件，就是需要首先創(chuàng)建用戶的缺省表空間和臨時(shí)表空間，如果缺省表空間或者臨時(shí)表空間不存在，則自動創(chuàng)建用戶會失敗，導(dǎo)致導(dǎo)入數(shù)據(jù)的失敗。
下面通過實(shí)驗(yàn)來描述 impdp 自動創(chuàng)建用戶的前提條件和應(yī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ù), 測試是否可以自動創(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
可以看到自動創(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ù), 自動創(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ù), 自動創(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
是可以自動創(chuàng)建用戶, 但有一個(gè)前提條件: 用戶的缺省表空間和臨時(shí)表空間要先創(chuàng)建好 (存在).
使用 impdp 自動創(chuàng)建用戶應(yīng)用場景:
1. 數(shù)據(jù)庫數(shù)據(jù)遷移或者升級到另外一個(gè)數(shù)據(jù)庫環(huán)境, 并且數(shù)據(jù)庫中有很多用戶,DBA 無法知道每一個(gè)用戶的密碼, 只能查到用戶的缺省表空間和臨時(shí)表空間.
2.DBA 不能重設(shè)用戶的密碼, 重設(shè)密碼將會導(dǎo)致很多舊的應(yīng)用系統(tǒng)需要進(jìn)行配置, 存在一定的風(fēng)險(xiǎn).
3. 新的數(shù)據(jù)庫的 IP,PORT 都與舊的相同, 應(yīng)用系統(tǒng)機(jī)會不需要做任何修改.
通過 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 如何自動創(chuàng)建用戶前提條件與應(yīng)用場景”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學(xué)習(xí)更多知識，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道！
