实现数据的备份和恢复 Oracle实训

实现数据的备份和恢复 Oracle实训

Oracle的备份与恢复一般只有数据库管理员DBA才会进行。

任务要求如下：

①新增加数据库用户me

②为该用户授予各种需要的权限

③使用该用户口令进入数据库

④在该用户下创建表并且并表中插入若干数据

⑤备份该数据库的内容

⑥将数据库的内容删除

⑦恢复该数据库中的内容

①新增加数据库用户me

SQL>create user me identified by me123;

②为该用户授予各种需要的权限

--**授予用户创建session的权限，即登陆权限，允许用户登录数据库**
grant create seeion to me;
--授予用户创建表的权限
grant create table to me;
--授予用户连接
grant connect,resource to me;


--授权系统权限
grant   all privileges to [用户名] identified  by me123;
with admin option;

③使用该用户口令进入数据库

SQL>conn me/me123;

④在该用户下创建表并且并表中插入若干数据

--(1)
CREATE TABLE EMP(
Empno  varchar2(5) Primary Key,
Ename  varchar2(20) not null,
hire_date  date , --雇佣日期
job     varchar2(20),
sal     number(10) not null,--工资
mgr  varchar2(5), --经理
comm  number(10),--通讯电话
deptno  varchar2(2) not null --部门代号
);
--2
CREATE TABLE DEPT(
Deptno varchar2(2) Primary Key,
Dname  varchar2(20) not null,
Dloc  varchar2(20) --location 部门位置
);
INSERT ALL
INTO EMP VALUES('E01','LIBAI',TO_DATE('2010-01-01','yyyy-mm-dd'),'BOSS','30000','NO','134134','D1')
INTO EMP VALUES('E02','DUFU',TO_DATE('2010-01-01','yyyy-mm-dd'),'MANAGER','10000','YES','137134','D1')
INTO EMP VALUES('E03','BAIJUYI',TO_DATE('2010-01-01','yyyy-mm-dd'),'HR','5000','NO','145145','D2')
INTO EMP VALUES('E04','WANGWEI',TO_DATE('2010-01-01','yyyy-mm-dd'),'HR','5000','NO','134154','D2')
INTO EMP VALUES('E05','MENGHAORANG',TO_DATE('2011-01-01','yyyy-mm-dd'),'TREASURER','8000','NO','134199','D4')
INTO EMP VALUES('E06','XINQIJI',TO_DATE('2010-11-01','yyyy-mm-dd'),'PROGRAMMER','7000','NO','134155','D3')
INTO EMP VALUES('E07','LIUYUXI',TO_DATE('2013-01-01','yyyy-mm-dd'),'SALEMAN','10000','NO','134655','D5')
INTO EMP VALUES('E08','WANGBO',TO_DATE('2014-01-01','yyyy-mm-dd'),'BOSS','5000','NO','111223','D1')
INTO EMP VALUES('E09','YANGJIONG',TO_DATE('2010-01-01','yyyy-mm-dd'),'MANAGER','10000','YES','124568','D1')
INTO EMP VALUES('E10','LUZHAOLING',TO_DATE('2010-10-10','yyyy-mm-dd'),'SALEMAN','10000','NO','134987','D5')
INTO EMP VALUES('E11','LUBINWANG',TO_DATE('2010-03-01','yyyy-mm-dd'),'SALEMAN','9000','NO','15581','D5')
INTO EMP VALUES('E12','HEZHIHZANG',TO_DATE('2011-01-01','yyyy-mm-dd'),'PROGRAMMER','8000','NO','100645','D3')
INTO EMP VALUES('E13','DUMU',TO_DATE('2011-01-01','yyyy-mm-dd'),'PROGRAMMER','20000','NO','194497','D3')
INTO EMP VALUES('E14','LISHANGYING',TO_DATE('2012-01-01','yyyy-mm-dd'),'SALEMAN','6000','NO','145150','D5')
INTO EMP VALUES('E15','WANGCHANGLING',TO_DATE('2015-01-01','yyyy-mm-dd'),'HR','5000','NO','155655','D2')
INTO DEPT VALUES('D1','MANAGER','SHENZHEN')
INTO DEPT VALUES('D2','HR','SHENZHEN')
INTO DEPT VALUES('D3','TECHNICAL DEPARTMENT','SHENZHEN')
INTO DEPT VALUES('D4','INANCE DEPARTMENT','SHENZHEN')
INTO DEPT VALUES('D5','SALES DEPARTMENT','SHENZHEN')
SELECT *FROM DUAL;

commit;

⑤备份该数据库的内容

D:\backup>
D:\backup>exp me\me123 file=expdat.dmp

在这里插入图片描述

成功导出EXPDAT.EMP文件

⑥将数据库的内容删除

--进入me数据库中，将emp表的内容删除
SQL>conn me\me123
SQL>drop table emp purge; 

⑦恢复该数据库中的内容

--使用前面备份的数据进行数据库的恢复
D:>backup>imp me/me123 file=expdat.dmp

小功告成😄！！