oracle数据的备份及导入导出

最新推荐文章于 2024-08-15 11:27:44 发布

time丶sand

最新推荐文章于 2024-08-15 11:27:44 发布

阅读量860

点赞数

分类专栏： oracle 文章标签： oracle dba 数据库

本文链接：https://blog.csdn.net/weixin_44681259/article/details/123136718

版权

oracle 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

在这里插入图片描述

--0.创建表空间
CREATE TABLESPACE SICP_DATA
         LOGGING
         DATAFILE 'E:\oracle11g\oradata\orcl\SICP_DATA.DBF'
         SIZE 32M
         AUTOEXTEND ON
         NEXT 32M MAXSIZE UNLIMITED
         EXTENT MANAGEMENT LOCAL;
CREATE TABLESPACE SICP_INDX
         LOGGING
         DATAFILE 'E:\oracle11g\oradata\orcl\SICP_INDX.DBF'
         SIZE 32M
         AUTOEXTEND ON
         NEXT 32M MAXSIZE UNLIMITED
         EXTENT MANAGEMENT LOCAL;
--查看表空间        
select * from dba_data_files

--1.删除用户以及所有资源
drop user drgsplatform cascade;
drop user drgsta cascade;
--2.创建用户
CREATE USER drgsplatform IDENTIFIED BY drgsplatform
         ACCOUNT UNLOCK
         DEFAULT TABLESPACE SICP_DATA
         TEMPORARY TABLESPACE TEMP;
         
CREATE USER drgsta IDENTIFIED BY drgsta
         ACCOUNT UNLOCK
         DEFAULT TABLESPACE SICP_DATA
         TEMPORARY TABLESPACE TEMP;
--3.给用户授予操作权限
GRANT CONNECT TO drgsplatform;
GRANT RESOURCE TO drgsplatform;
GRANT CREATE ANY VIEW TO drgsplatform;
GRANT CREATE DATABASE LINK TO drgsplatform;
GRANT CREATE ANY SYNONYM TO drgsplatform;
GRANT DEBUG CONNECT SESSION TO drgsplatform;
GRANT CREATE JOB       TO drgsplatform;
GRANT SCHEDULER_ADMIN  TO drgsplatform;
GRANT MANAGE SCHEDULER TO drgsplatform;

GRANT CONNECT TO drgsta;
GRANT RESOURCE TO drgsta;
GRANT CREATE ANY VIEW TO drgsta;
GRANT CREATE DATABASE LINK TO drgsta;
GRANT CREATE ANY SYNONYM TO drgsta;
GRANT DEBUG CONNECT SESSION TO drgsta;
GRANT CREATE JOB       TO drgsta;
GRANT SCHEDULER_ADMIN  TO drgsta;
GRANT MANAGE SCHEDULER TO drgsta;

--4.创建虚拟路径
create directory dpdata  as 'D:\work\dbtemp';
--查看虚拟路径是否存在
select  *  from  dba_directories;
--5.授予虚拟目录操作权限
grant read,write on directory DPDATA to drgsplatform;
grant read,write on directory DPDATA to drgsta;



—-给用户授权DBA
GRANT DBA TO drgsplatform_kg;


--查询版本号
select *  from v$version;

--cmd执行
1.su - oracle  ：登陆oracledrgsplatform
2.导出导入：
导入：impdp drgsplatform/drgsplatform@orcl  directory=dpdata dumpfile=drgsplatform_20181114.dmp  remap_schema=scott:yang
      scott：导出的用户，yang：导入的用户
      impdp drgsta/drgsta@orcl  directory=dpdata dumpfile=drgsta_20181114.dmp


导出：expdp drgsta/drgsta@testdb  directory=dpdata  dumpfile=drgsta_20190102.dmp
      expdp drgsplatform/drgsplatform@testdb  directory=dpdata  dumpfile=drgsplatform_20190102.dmp

如果表重复就替换
impdp drgsplatform_zsyb_cs/drgsplatform_zsyb_cs directory=dpdata dumpfile=drgsplatform_zsyb_20210127.dmp table_exists_action=replace remap_schema=drgsplatform_zsyb:drgsplatform_zsyb_cs