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;