Oracle12cpdb使用expdp/impdp導(dǎo)入導(dǎo)出
12c推出了可插拔數(shù)據(jù)庫(kù)，在一個(gè)容器cdb中以多租戶的形式同時(shí)存在多個(gè)數(shù)據(jù)庫(kù)pdb。在為pdb做數(shù)據(jù)泵導(dǎo)入導(dǎo)出時(shí)和傳統(tǒng)的數(shù)據(jù)庫(kù)有少許不同。
網(wǎng)站建設(shè)公司,為您提供網(wǎng)站建設(shè),網(wǎng)站制作,網(wǎng)頁(yè)設(shè)計(jì)及定制網(wǎng)站建設(shè)服務(wù),專注于成都定制網(wǎng)頁(yè)設(shè)計(jì),高端網(wǎng)頁(yè)制作,對(duì)茶藝設(shè)計(jì)等多個(gè)行業(yè)擁有豐富的網(wǎng)站建設(shè)經(jīng)驗(yàn)的網(wǎng)站建設(shè)公司。專業(yè)網(wǎng)站設(shè)計(jì),網(wǎng)站優(yōu)化推廣哪家好,專業(yè)seo優(yōu)化排名優(yōu)化,H5建站,響應(yīng)式網(wǎng)站。
1，需要為pdb添加tansnames
2，導(dǎo)入導(dǎo)出時(shí)需要在userid參數(shù)內(nèi)指定其tansnames的值，比如 userid=user/pwd@tnsname
數(shù)據(jù)泵導(dǎo)出
1、查看當(dāng)前的SID，查看pdb并切換到容器數(shù)據(jù)庫(kù),這里的pluggable數(shù)據(jù)庫(kù)是pdborcl
[oracle@test admin]$ echo $ORACLE_SID [oracle@test admin]orcl
登錄cdb，查看pdb,
SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- PDB$SEED READ ONLY NO PDBORCL MOUNTED SQL> alter pluggable database all open; Pluggable database altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- PDB$SEED READ ONLY NO PDBORCL READ WRITE NO
切換到pdborcl
SQL> alter session set container=pdborcl; Session altered. SQL>
2、查看示例用戶scott，以后的schema級(jí)別導(dǎo)入導(dǎo)出就使用該用戶的數(shù)據(jù)。
SQL> select owner, table_name from dba_tables where owner='SCOTT'; OWNER TABLE_NAME ------------------------------ ---------------------------------------- SCOTT SALGRADE SCOTT BONUS SCOTT EMP SCOTT DEPT
3、單獨(dú)創(chuàng)建一個(gè)dba權(quán)限的數(shù)據(jù)泵用戶
SQL> grant dba to dp identified by dp; Grant succeeded.
4、創(chuàng)建一個(gè)數(shù)據(jù)泵目錄dp_dir，路徑為oracle家目錄
SQL> create or replace directory dp_dir as '/home/oracle'; Directory created. SQL> exit
5、授予dp用戶在數(shù)據(jù)泵路徑有讀寫權(quán)限
（如果是dba權(quán)限的這一步可以省略，為了試驗(yàn)的完整性這里保留）
SQL> grant read,write on directory dp_dir to dp; Grant succeeded.
6、設(shè)置tnsnames.ora，增加pdborocl。SERVICE_NAME為pdb的實(shí)例名，這里為pdborcl
[oracle@xqzt admin]$ pwd /data/app/oracle/product/12.1.0/dbhome_1/network/admin [oracle@xqzt admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /data/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xqzt)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) PDBORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xqzt)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =pdborcl) ) )
7、測(cè)試tnsnames.ora的有效性，如果返回OK (0 msec)表示配置成功
[oracle@xqzt admin]$ tnsping pdborcl TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 10-DEC-2015 09:10:34 Copyright (c) 1997, 2014, Oracle. All rights reserved. Used parameter files: /data/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xqzt)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =pdborcl))) OK (0 msec)
8、數(shù)據(jù)泵導(dǎo)出
用戶名密碼為dp/dp，并且通過(guò)tnsnames指向pdborcl
數(shù)據(jù)泵目錄為：dp_dir, OS路徑是/home/oracle
導(dǎo)出文件為：/home/oracle/scott_pdborcl.dmp
導(dǎo)出日志為：/home/oracle/scott_pdborcl.log
導(dǎo)出模式為scheme，也可以理解為用戶：scott
[oracle@xqzt ~]$ expdp dp/dp@pdborcl directory=dp_dir dumpfile=scott_pdborcl.dmp logfile=scott_pdborcl.log schemas=scott Export: Release 12.1.0.2.0 - Production on Thu Dec 10 09:32:05 2015Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Starting "DP"."SYS_EXPORT_SCHEMA_01": dp/********@pdborcl directory=dp_dir dumpfile=scott_pdborcl.dmp logfile=scott_pdborcl.log schemas=scott Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 192 KB 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/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/STATISTICS/MARKER . . exported "SCOTT"."DEPT" 6.023 KB 4 rows . . exported "SCOTT"."EMP" 8.773 KB 14 rows . . exported "SCOTT"."SALGRADE" 6.023 KB 10 rows . . exported "SCOTT"."BONUS" 0 KB 0 rows Master table "DP"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for DP.SYS_EXPORT_SCHEMA_01 is: /home/oracle/scott_pdborcl.dmp Job "DP"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Dec 10 09:32:29 2015 elapsed 0 00:00:21 [oracle@xqzt ~]$
10、查看導(dǎo)出文件
[oracle@xqzt ~]$ ls -l scott_pdborcl.dmp scott_pdborcl.log -rw-r----- 1 oracle oinstall 356352 12月 10 09:32 scott_pdborcl.dmp -rw-r--r-- 1 oracle oinstall 1960 12月 10 09:32 scott_pdborcl.log
11、為了測(cè)試導(dǎo)出文件是否能夠正常導(dǎo)入，我們先刪除pdborcl的scott用戶
SQL> select count(*) from scott.DEPT; COUNT(*) ---------- SQL> drop user scott cascade ; User dropped. SQL>
此時(shí)訪問(wèn)該用戶的表已經(jīng)不存在了
SQL> select count(*) from scott.DEPT; select count(*) from scott.DEPT *ERROR at line 1: ORA-00942: table or view does not exist
12、 導(dǎo)入scott用戶
[oracle@xqzt ~]$ impdp dp/dp@pdborcl directory=dp_dir dumpfile=scott_pdborcl.dmp logfile=scott_pdborcl_imp.log schemas=scott Import: Release 12.1.0.2.0 - Production on Thu Dec 10 09:39:02 2015Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Master table "DP"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded Starting "DP"."SYS_IMPORT_SCHEMA_01": dp/********@pdborcl directory=dp_dir dumpfile=scott_pdborcl.dmp logfile=scott_pdborcl_imp.log schemas=scott 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 "SCOTT"."DEPT" 6.023 KB 4 rows . . imported "SCOTT"."EMP" 8.773 KB 14 rows . . imported "SCOTT"."SALGRADE" 6.023 KB 10 rows . . imported "SCOTT"."BONUS" 0 KB 0 rows Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/STATISTICS/MARKER Job "DP"."SYS_IMPORT_SCHEMA_01" successfully completed at Thu Dec 10 09:39:06 2015 elapsed 0 00:00:04 [oracle@xqzt ~]$
13、 測(cè)試導(dǎo)入結(jié)果
SQL> select count(*) from scott.DEPT; COUNT(*) ---------- 4
導(dǎo)入成功！
網(wǎng)站欄目：Oracle12cpdb使用expdp/impdp導(dǎo)入導(dǎo)出
分享地址：http://bm7419.com/article38/iihipp.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供做網(wǎng)站、ChatGPT、微信小程序、網(wǎng)站維護(hù)、網(wǎng)站設(shè)計(jì)、搜索引擎優(yōu)化
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)
- 深圳第三方微信營(yíng)銷平臺(tái)，微信公眾號(hào)如何做營(yíng)銷推廣呢？ 2021-11-21
- 企業(yè)微信公眾號(hào)建設(shè)：微信圖文信息編輯要注意的幾個(gè)要點(diǎn) 2021-07-23
- 微信公眾號(hào)粉絲翻倍的技巧是什么？ 2014-05-26
- 微信公眾號(hào)的文章如何蹭熱度，熱點(diǎn)文章應(yīng)該怎么寫？ 2014-05-24
- 關(guān)于微信公眾號(hào)技巧 2022-05-30
- 微信公眾號(hào)運(yùn)營(yíng)的幾個(gè)小技巧 2022-08-08
- 公眾號(hào)營(yíng)銷軟文技巧! 2015-06-23
- 成都微信公眾號(hào)開發(fā)公司講述什么是網(wǎng)絡(luò)營(yíng)銷 2022-07-09
- 『微信公眾號(hào)運(yùn)營(yíng)技巧』新媒體人必備干貨分享 2022-06-28
- 微信公眾號(hào)推送內(nèi)容的素材應(yīng)該如何搜集？ 2014-05-18
- 微信公眾號(hào)軟文廣告營(yíng)銷策略 2022-12-01
- 微信公眾號(hào)如何正確擺脫被封號(hào)危機(jī) 2022-09-01