Oracle 導入資料的前提是有一個已經使用expdp的資料泵指令導出的資料檔案。這裡以BJ_Person.DMP為例子。
資料泵導入的步驟:
1.建立導入和導出目錄:#mkdir /u01/app/oracle/admin/cdb1/dpdump/
2.$sqlplus / as sysdba
3.建立導入檔案目錄:
SQL> create or replace directory DATA_PUMP_DIR as '/u01/app/oracle/admin/cdb1/dpdump/';
4.建立臨時表空間(TEMP預設一般不用建立):
CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE
'/u01/app/oracle/oradata/CDB1/temp01.dbf' SIZE 33554432
AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576
5.建立資料表空間(表空間名稱NCMS):
CREATE TABLESPACE "CNMS" DATAFILE
'/u01/app/oracle/oradata/CDB1/CNMS.dbf' SIZE 10737418240
AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
6.建立使用者sjpt解鎖并指明資料表空間和臨時表空間:
SQL> create user sjpt identified by ahsxnh2013jqkj account unlock default tablespace NCMS temporary tablespace TEMP;
7.授權使用者:
SQL> grant resource,connect,dba to sjpt;
8.授權導入目錄的讀寫權限:
SQL> grant read,write on directory DATA_PUMP_DIR to sjpt;
9.資料泵進行資料導入:
$impdp sjpt/ahsxnh2013jqkj directory=dpdump dumpfile=BJ_Person.DMP FULL=y remap_schema=ncms:sjpt remap_tablespace=NCMS:NCMS logfile=aaa.log
10.查詢資料庫導入的表資訊,進行驗證:
SQL> select count(*) from BJ2019_20200414;
卑微時不墜淩雲之志,飛騰日莫忘貧賤之交!