Oracle导入dmp文件还原数据库

-------------------------------------进去oracle---------------------------------------------------------------
CMD 【进去dos】
sqlplus /nolog 【进去oracl-plus】
conn /as sysdba 【用sysdba身份连接】

-------------------------------------------创建表空间及账户-----------------------------------------------
【创建表空间】CREATE TABLESPACE NNC_DATA01 DATAFILE 'D:\oracle\home\oradata\orcl\nnc_data01.dbf' SIZE 500M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K ;
【表空间放的路径】D:\oracle\home\oradata\orcl\nnc_data01.dbf
【创建表空间】CREATE TABLESPACE NNC_INDEX01 DATAFILE 'D:\oracle\home\oradata\orcl\nnc_index01.dbf' SIZE 500M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K ;
【表空间放的路径】D:\oracle\home\oradata\orcl\nnc_index01.dbf

【创建用户】CREATE USER nc631 IDENTIFIED BY nc631 DEFAULT TABLESPACE NNC_DATA01 TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON NNC_DATA01;
【授权】
GRANT CREATE SESSION TO nc631;
GRANT CREATE TABLE TO nc631;
GRANT CREATE TRIGGER TO nc631;
GRANT CREATE VIEW TO nc631;
GRANT UNLIMITED TABLESPACE TO nc631;
grant connect,dba to nc631;

-----------------------------------------------创建数据泵路径--------------------------------------------------------
【建立数据泵路径】create or replace directory shujubeng as 'd:\';
【开通用户的数据泵数据权限】grant read,write on directory shujubeng to nc631;
【导出数据在dos命令行窗口执行,NC633GOLD/NC633GOLD是账户密码,schemas=syync61是导出数据的账户,dumpfile=NC633GOLD.dmp是要导出的数据包名,directory=shujubeng是数据泵路径,logfile=NC633.log是导完生成的日志名字可随意】
impdp nc631/nc631@orcl directory=shujubeng dumpfile=PMM_98_20160523.DMP remap_schema=NC633:nc631 TABLE_EXISTS_ACTION=REPLACE logfile=nc631.log

【另一种方式】
【导入数据在外面CMD执行,ncdata/ncdata是账户密码,directory=shujubeng是数据泵路径,dumpfile=NC633GOLD.dmp是要导入的数据包, remap_schema=NC633GOLD:ncdata是指旧账号到新账号 ,logfile=NC633.log是导完生成的日志名字可随意】
impdp ncdata4/ncdata4@orcl directory=shujubengf dumpfile=NC633.DMP remap_schema=ncdata4:nchsh TABLE_EXISTS_ACTION=REPLACE logfile=NC633.log

【如果impdp导入失败,可以尝试 imp命令导入
imp nc631/nc631@orcl file=D:\oracle\home\admin\orcl\dpdump\nc631.dmp full=y;

或者:
impdp nchsh2/nchsh2@orcl directory=shujubeng dumpfile=NC633.DMP remap_schema=ncdata4:nchsh2 TABLE_EXISTS_ACTION=REPLACE logfile=NCHSH.log

或者:
impdp nc65test/nc65test@orcl DUMPFILE=nc65test.dmp schemas=nc65test
remap_schema=nc65:nc65test remap_tablespace=NNC_DATA01:XB_DATA02 TABLE_EXISTS_ACTION=REPLACE logfile=nc65test.log
remap_schema=nc65/nc65test 是指旧账号到新账号
remap_tablespace=NNC_DATA01:XB_DATA02 是指旧表空间到新表空间

--------------------------------------------------------------------------删除导入的东西-------------------------------------------------------
【删除用户及用户下面数据】drop user nchsh2 cascade;
【删除表空间及数据文件】
drop tablespace nnc_data01 including contents and datafiles;
drop tablespace nnc_index01 including contents and datafiles;
相关文章
相关标签/搜索