oracle如何导入dmp大文件？

1、创建表空间(因导入dmp超大，且根目录空间不足，表空间挂载在data5磁盘)：

create tablespace ssxt datafile ‘/data5/oracle/oradata/cdb/dzzwpt01.dbf’ size 2048m autoextend on next 200m maxsize 26480m extent management local;

(增加一个数据文件，解决oracle ora-01653: unable to extend table 报错)

alter tablespace ssxt add datafile ‘/data5/oracle/oradata/cdb/dzzwpt02.dbf’ size 2048m autoextend on maxsize 20g;

2、创建用户：

create user zhouyuan identified by 12345678 default tablespace ssxt;

授权：

grant connect,resource,dba to zhouyuan;

3、导入oracle命令(ssxt_20170928.dmp 放在data_pump_dir对应的目录下)：

impdp zhouyuan/12345678@ip:port/cdb directory=data_pump_dir dumpfile=ssxt_20170928.dmp remap_schema=zhouyuan:zhouyuan;

3、查看oracle的编码

select * from nls_database_parameters where parameter =’nls_characterset’;

4、(修改字符集，解决ora-02374 ora-12899 ora-02372报错)

sql>shutdown immediate;

sql>startup mount exclusive;

sql>alter system enable restricted session;

sql>alter system set job_queue_processes=0;

sql>alter system set aq_tm_processes=0;

sql>alter database open;

sql>alter database national character set internal_use zhs16gbk;

sql>shutdown immediate;

sql>startup;

5、(解决ora-00959表空间不存在的问题)

如当前用户默认的表空间为test,现在改为需要的表空间ssxt

alter tablespace test rename to ssxt

6、(解决ora-39006 ora-39065 ora-04063错误)

数据库的某些或对象失效

sql> spool catalog.log

sql> @?/rdbms/admin/catalog

sql> spool off

sql> spool catproc.log

sql> @?/rdbms/admin/catproc

sql> spool off

sql> spool utlrp.log

sql> @?/rdbms/admin/utlrp

sql> spool off

7、(删除后重新导入)

删除用户和表空间

drop user zhouyuan cascade;

drop tablespace ssxt including contents and datafiles;