oracle expdp 表数据库,Oracle常用数据库impdp&expdp示例

【说明】

示例脚本，完成如下功能：

1)创建表空间EAS_D_HFY121009_STANDARD和临时表空间EAS_T_HFY121009_STANDARD、用户hfy120910；

2)创建用于备份还原的目录zdir；

3)完成从源数据库hfy备份并还原到新的数据库hfy120910；

【脚本】

------ begin 创建用户和表空间 ------------

CREATE TABLESPACE "EAS_D_HFY121009_STANDARD" DATAFILE '/oracle/oradata/dbeas/EAS_D_HFY121009_STANDARD.ORA' SIZE 2000M;

ALTER DATABASE DATAFILE '/oracle/oradata/dbeas/EAS_D_HFY121009_STANDARD.ORA' AUTOEXTEND ON;

CREATE TEMPORARY TABLESPACE "EAS_T_HFY121009_STANDARD" TEMPFILE '/oracle/oradata/dbeas/EAS_T_HFY121009_STANDARD.DBF' SIZE 500M;

ALTER DATABASE TEMPFILE '/oracle/oradata/dbeas/EAS_T_HFY121009_STANDARD.DBF' AUTOEXTEND ON;

CREATE USER hfy121009 IDENTIFIED BY hfy121009 DEFAULT TABLESPACE EAS_D_HFY121009_STANDARD TEMPORARY TABLESPACE EAS_T_HFY121009_STANDARD;

grant dba to hfy121009;

------ end 创建用户和表空间 ------------

------ begin 创建directory ------------

Copyright (c) 1982, 2009, Oracle. All rights reserved.

SQL> conn /as sysdba

Connected.

SQL> create directory zdir as '/oradata';

Directory created.

SQL> grant read,write on directory zdir to hfy;

Grant succeeded.

SQL> quit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

$

------ end 创建directory ------------

------ begin 备份还原 ------------

$ expdp hfy/*** directory=zdir schemas=hfy dumpfile=hfy121009.dmp logfile=hfy121009exp.log

$ impdp hfy121009/*** directory=zdir schemas=hfy remap_schema=hfy:hfy121009 remap_tablespace=EAS_D_HFY_STANDARD:EAS_D_HFY121009_STANDARD dumpfile=hfy121009.dmp logfile=hfy121009imp.log

------ end 备份还原 ------------ 注意：在数据库还原时，如果目标数据库没有表空间EAS_D_HFY_STANDARD，则一定要加上参数：remap_tablespace=EAS_D_HFY_STANDARD:EAS_D_HFY121009_STANDAR，否则会报错：找不到表空间。