oracle数据库导出导入相关操作(一)

最新推荐文章于 2020-11-18 20:06:18 发布

ernesto_ji

最新推荐文章于 2020-11-18 20:06:18 发布

阅读量201

点赞数

分类专栏： # oracle 文章标签： oracle数据库导出导入相关操作

本文链接：https://blog.csdn.net/qq_38407462/article/details/89487948

版权

oracle 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

–当首次导入时创建表空间（运行脚本时会提示输入 &1 输入数据文件希望存放的物理地址例如 D:\OracleData）
create tablespace pt6 datafile ‘&1\pt6.dbf’ size 100m autoextend on next 10m;
create tablespace mpm datafile ‘&1\mpm.dbf’ size 100m autoextend on next 10m;

–删除用户
drop user mpm cascade;
drop user pt6 cascade;

–创建用户
create user pt6 identified by cape default tablespace PT6;
create user mpm identified by cape default tablespace MPM;
–赋权限
grant dba to pt6;
grant dba to mpm;

–导入
impdp pt6/cape@127.0.0.1/orcl dumpfile=MPMV6R32_20170309.expdb logfile=MPMV6R32_20170309.log schemas=(pt6,mpm)

–导出
expdp pt6/cape@10.216.60.77/mpmproduct dumpfile=expdp_2017-0303.expdp logfile=expdp_2017-0303.log schemas=(pt6,mpm)

mpm606OracleData

–创建同义词
CREATE OR REPLACE SYNONYM PT6.PM606_ASSEMBLE_TASK_INFO FOR MPM.PM606_ASSEMBLE_TASK_INFO;
GRANT ALL ON MPM.PM606_ASSEMBLE_TASK_INFO TO PT6 WITH GRANT OPTION;

impdp pt6/cape@127.0.0.1/mpmproduct dumpfile=MPMPRODUCT_20170613.EXPDP logfile=mpmproduct_20170613.log schemas=(pt6,mpm)

expdp pt6/cape@localhost/mpmsjzx dumpfile=mpmsjzx_20170721.expdp logfile=mpmsjzx_20170721.log schemas=(pt6,mpm)

=============================================================================================
–创建表空间 D:\app\hanyl\oradata
create tablespace PT6 datafile ‘f:\app\Administrator\oradata\pt6.dbf’ size 100m autoextend on next 10m;
create tablespace risk datafile ‘f:\app\Administrator\oradata\risk.dbf’ size 100m autoextend on next 10m;
create tablespace bibsc datafile ‘f:\app\Administrator\oradata\bibsc.dbf’ size 100m autoextend on next 10m;

–删除表空间
drop tablespace tablespace_name including contents and datafiles;

drop tablespace PT6 including contents and datafiles;
drop tablespace risk including contents and datafiles;

–删除用户
drop user pt6 cascade;
drop user risk cascade;
drop user bibsc cascade;

–创建用户
create user pt6 identified by cape default tablespace PT6;
create user risk identified by cape default tablespace risk;
create user bibsc identified by cape default tablespace bibsc;

–赋权
grant dba to pt6;
grant dba to risk;
grant dba to bibsc;

–导入
impdp pt6/cape@fxgkorcl dumpfile=RISK-2017-07-24.EXPDB schemas=(pt6,risk,bibsc)
–导出
expdp pt6/cape@orcl dumpfile=risk_2017-06-22-0000.EXPDB schemas=(pt6,risk,bibsc)

expdp pt6/cape@orcl dumpfile=risk-2017-07-15.EXPDB schemas=(pt6,risk,bibsc)

expdp pt6/cape@orcl dumpfile=risk_2017-06-22-test.EXPDB schemas=(pt6,risk,bibsc)
impdp pt6/cape@orcl dumpfile=risk_2017-06-22-0000.EXPDB schemas=(pt6,risk,bibsc)
sqlplus sys/aaa as sysdba;–以数据库管理员的身份登录. 如果在本机登录