Oracle实验指导书和实验报告.doc

中北大学软件学院Oracle数据库实验报告说明：此版本实验报告分为实验指导书和实验报告两部分，实验指导书在前，截图和代码在后。在目录中按Ctrl键可以快速到达对应位置目录实验一 Oracle数据库的体系结构逻辑结构（2学时）3Oracle实验1运行结果截图6实验二 Oracle数据库的体系结构物理结构（2学时）9Oracle实验2截图14实验三 常用SQL语言的使用（2学时）24Oracle实验3截图29实验四 扩展SQL语言的使用（2学时）38Oracle实验4运行结果截图42实验五 PL/SQL-过程（2学时）62Oracle实验5运行结果截图66实验六 PL/SQL-函数和包（2学时）77Oracle实验6运行结果截图78实验七 PL/SQL-游标（2学时）80Oracle 实验7运行结果截图86实验八 数据库的备份和恢复（2学时）101Oracle实验8 运行结果截图1011.闪回数据库1012.闪回表1043.闪回回收站1054.闪回查询1075.闪回版本查询1086.闪回事务查询1107.导出1108.导入数据库112实验一 Oracle数据库的体系结构逻辑结构（2学时）实验目的:在理解在Oracle数据库的体系结构的基础上,在sqlplus环境下查看oracle的逻辑体系结构中括表空间、段、区、块的信息，熟练掌握各种查看语句。实验内容：1.通过sql语句查看表空间信息 2. 通过sql语句查看段信息3. 通过sql语句查看区信息4. 通过sql语句查看块信息1.理解逻辑体系结构2.表空间的查看2.1表空间内涵理解公司 表空间 一个楼有多个公司 一个数据库有多个表空间 一个公司必须占一个房间 一个表空间必须有一个数据文件 一个公司所占面积是房间和 表空间大小是数据文件和 公司可以自由安排上下班，但为其服务的食堂等则不能 System必须在线 一个公司可以占多个房间，房间数可固定，也可扩展或缩小，但应事先规划 表空间有多个数据文件，可以增加数据文件 每个公司的设备、员工都应安排在相应的位置， 数据对象应存储在相应的段中，如数据段、索引段等 仓库里的东西堆不下，可以放到别的房间 一个数据文件放不下，可以放到别的数据文件中，只要是一个表空间即可 一个员工可以为本公司服务，业务需求也可能与别的公司服务 一个用户默认一个表空间，但其拥有的对象可以放在不同表空间，如create table指定表空间 一个用户拥有的办公空间是定额的 一个用户使用的表空间是有限制的，不能超出 盖大楼时，可以根据需要盖不同功能的房间，如办公楼，商铺，娱乐区，进驻大楼的公司可以是永久的，可以是临时的 一个数据库有多个表空间，表空间有不同类型 为了更好交流，将相近办公室放在一起， 将相关的数据文件放在一个表空间中 2.2通过Oracle视图查看表空间信息段 类 型 说 明 V$TABLESPACE 控制文件中保存的所有表空间的名称和数量 DBA_TABLESPACES 所有表空间的属性和在线状态信息 USER_TABLESPACES 所有用户可访问表空间的描述信息 DBA_TABLESPACE_GROUPS 所有表空间组及其所属的表空间信息 DBA_SEGMENTS 所有表空间中的区间信息 USER_SEGMENTS 所有用户表空间中的区间信息 DBA_FREE_SPACE 所有表空间中的空闲区间信息 USER_FREE_SPACE 所有用户表空间中的空闲区间信息 V$DATAFILE 所有数据文件信息 V$TEMPFILE 所有临时文件信息 DBA_DATA_FILES 显示所有属于表空间的数据文件信息 DBA_TEMP_FILES 显示所有属于临时表空间的临时文件信息 （1）使用V$TABLESPACE视图查看表空间信息SELECT * FROM V$TABLESPACE;（2）查看表空间的属性dba_tablespacesSELECT TABLESPACE_NAME,CONTENTS, STATUS FROM DBA_TABLESPACES;（3）查看表空间组及其所属的表空间信息dba_tablespace_groups（4）查看表空间中所包含的段信息dba_segments Select segment_name,segment_type,extents,tablespace_name from dba_segments where tablespace_name=SYSTEM;Select * from v$rollname ;查看回滚段的名称列表 Select * from v$rollstat ;查看回滚段的统计信息； Select segment_name,tablespace_name,bytes,blocks,segment_type from dba_segments where segment_type=ROLLBACK（5）查看表空间中空闲区间的信息dba_free_spaceselect * from dba_segments where user=SCOTT;SELECT TABLESPACE_NAME, FILE_ID, BYTES, BLOCKS FROM DBA_FREE_SPACE;Oracle实验1运行结果截图（1）使用V$TABLESPACE视图查看表空间信息SELECT * FROM V$TABLESPACE;（2）查看表空间的属性dba_tablespacesSELECT TABLESPACE_NAME,CONTENTS, STATUS FROM DBA_TABLESPACES;（3）查看表空间组及其所属的表空间信息dba_tablespace_groups（4）查看表空间中所包含的段信息dba_segments (内容太多，只截取部分图片)Select segment_name,segment_type,extents,tablespace_name from dba_segments where tablespace_name=SYSTEM;Select * from v$rollname ;查看回滚段的名称列表Select * from v$rollstat ;查看回滚段的统计信息；Select segment_name,tablespace_name,bytes,blocks,segment_type from dba_segments where segment_type=ROLLBACK（5）查看表空间中空闲区间的信息dba_free_spaceselect * from dba_segments where user=SCOTT;SELECT TABLESPACE_NAME, FILE_ID, BYTES, BLOCKS FROM DBA_FREE_SPACE;实验二 Oracle数据库的体系结构物理结构（2学时）实验目的:在理解在Oracle数据库的体系结构的基础上,在sqlplus环境下对oracle的物理体系结构的操作，其中包括控制文件、数据文件、日志文件，熟练掌握以上操作的各种语句。实验内容：1. 控制文件操作 1.1 查看控制文件 1.2 复制控制文件 1.3 新建控制文件 2. 数据文件 2.1 查看数据文件 2.2 新建控制文件2.3 修改数据文件3.日志文件 3.1查看日志文件 3.2 新建日志文件、日志组 3.3 删除日志文件、日志组1.理解物理体系结构2.实际体验物理体系结构 Drop table t Create table t as select * from all_objects; Create index object_id_idx on t(object_id); Set autotrace on Set timing on Select object_name from t where object_id=29;Select /*full(t)*/ object_name from t where object_id=29时间 物理读降低3.查看物理体系结构各部分的内容 Show parameter sga Show parameter pga Show parameter shared_pool_size; Show parameter db_cache_size;数据缓冲池 Show parameter log_buffer;日志缓冲区 4.控制文件4.1从视图V$CONTROLFILE中查询控制文件的名称列表SELECT NAME FROM V$CONTROLFILE;4.2从视图V$CONTROLFILE_RECORD_SECTION中查询到控制文件中保存数据的记录类型、记录大小、记录总数量、使用记录数量等信息 SELECT TYPE,RECORD_SIZE,RECORDS_TOTAL,RECORDS_USED FROM V$CONTROLFILE_RECORD_SECTION;4.3 创建控制文件 （1）创建初始控制文件 （2）创建新的控制文件 了解当前数据库日志文件和数据文件的情况 查看当前数据库中日志文件的列表 SELECT MEMBER FROM V$LOGFILE; 查看当前数据库中数据文件的列表 根据日志文件和数据文件列表设计CREATE CONTROLFILE语句CREATE CONTROLFILE DATABASE ORCLLOGFILE GROUP 1 ('D:appAdministratororadataorclredo01.log'), GROUP 2 ('D:appAdministratororadataorclredo02.log'), GROUP 3 ('D:appAdministratororadataorclredo03.log')NORESETLOGSDATAFILE 'D:appAdministratororadataorclsystem01.dbf', 'D:appAdministratororadataorclsysaux01.dbf', 'D:appAdministratororadataorclundotbs01.dbf', 'D:appAdministratororadataorclusers01.dbf', 'D:appAdministratororadataorclorcltbs01.dbf'MAXLOGFILES 50MAXLOGMEMBERS 3MAXLOGHISTORY 400MAXDATAFILES 200MAXINSTANCES 6ARCHIVELOG; 关闭数据库实例SHUTDOWN NORMAL 备份原来的文件 启动数据库实例，但不加载数据库STARTUP NOMOUNT 创建控制文件 执行前面设计的CREATE CONTROLFILE语句，创建控制文件。 备份控制文件为了保证新的数据库文件不被破坏，建议将新的控制文件备份到其他不在线的存储介质中，如U盘、移动硬盘或磁带等 修改初始化参数 如果新建的控制文件与CONTROL_FILE参数中定义的控制文件不同，则根据实际情况修改CONTROL_FILE参数；如果修改了数据库名称，则还需要修改DB_NAME参数。5.数据文件51查看数据文件信息5.1.1从视图V$DATAFILE中查看数据文件的信息SELECT NAME, STATUS, BYTES FROM V$DATAFILE;5. 2 创建数据文件 CREATE TABLESPACE 表空间名DATAFILE 数据文件名 SIZE 数据文件大小;【例】 创建表空间MyTbs，同时创建一个50MB的数据文件，代码如下：CREATE TABLESPACE MyTbs DATAFILE 'D:APPADMINISTRATORORADATAORCLMyDataFile01.DBF' SIZE 50M; 【例】 创建表空间TempTbs，同时创建一个10MB的临时文件，代码如下：CREATE TEMPORARY TABLESPACE TempTbs TEMPFILE 'D:APPADMINISTRATORORADATAORCLMyTempFile01.DBF' SIZE 50M EXTENT MANAGEMENT LOCAL; 使用ALTER TABLESPACE语句修改表空间时，可以使用ADD DATAFIEL关键字向表空间中添加数据文件。 【例】 向表空间MyTbs中添加一个数据文件MyDataFile02.DBF，大小为50MB，代码如下：ALTER TABLESPACE MyTbs ADD DATAFILE 'F:APPADMINISTRATORORADATAORCLMyDataFile02.DBF' SIZE 50M; 5.3修改数据文件的大小 使用ALTER DATABASE语句可以修改数据文件的大小，语法如下：ALTER DATABASE DATAFILE 数据文件名 RESIZE 数据文件大小; 【例】 将数据文件D:APPADMINISTRATORORADATAORCLUSERS01.DBF的大小修改为100M，代码如下：ALTER DATABASE DATAFILE 'D:APPADMINISTRATORORADATAORCLUSERS01.DBF' RESIZE 100M; 可以执行下面的语句查看当前数据库中数据文件的大小。SELECT NAME, BYTES FROM V$DATAFILE; 54 修改数据文件的在线状态 ALTER DATABASE DATAFILE 数据文件名 ONLINE | OFFLINE; 【例】 将数据文件D:APPADMINISTRATORORADATAORCLUSERS01.DBF的在线状态修改为脱机，代码如下：ALTER DATABASE DATAFILE 'D:APPADMINISTRATORORADATAORCLUSERS01.DBF' OFFLINE; 也可以设置指定表空间中所有数据文件的在线状态，语法如下： ALTER TABLESPACE 表空间名DATAFILE ONLINE | OFFLINE;【例】 将表空间MYTBS中所有数据文件设置为联机状态，代码如下：ALTER TABLESPACE MYTBS DATAFILE ONLINE; 55删除数据文件 【例】 删除表空间MyTbs，同时删除其中数据文件的代码如下：DROP TABLESPACE MyTbs INCLUDING CONTENTS CASCADE CONSTRAINTS; 也可以使用ALTER DATABASE命令删除指定的数据文件。 D:APPADMINISTRATORORADATAORCLMyDataFile01.DBF的语句如下：ALTER DATABASE DATAFILE 'F:APPADMINISTRATORORADATAORCLMyDataFile01.DBF' OFFLINE 6.日志文件6.1查看日志文件信息 查询视图V$LOGFILE，显示重做日志的成员文件，语句如下：SELECT GROUP#,MEMBER FROM V$LOGFILE; 查询视图V$LOG，显示控制文件中重做日志组的信息，语句如下：SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;6.2 创建日志组在ALTER DATABASE语句中使用ADD LOGFILE子句创建重做日志组： 添加重做日志文件log1c.rdo和log2c.rdo，初始大小为5M，代码如下：ALTER DATABASE ADD LOGFILE ('log1c.rdo', 'log2c.rdo') SIZE 5000k;Select * from v$logfile; 【例】 创建重做日志组10，其中包含重做日志文件log1c.rdo和log2c.rdo，语句如下：ALTER DATABASE ADD LOGFILE GROUP 10 ('log1a.rdo', 'log2a.rdo') SIZE 5000k;Select * from v$logfile;6.3 创建重做日志成员 【例】 将重做日志文件log3a.rdo添加到编号为10的重做日志组中，语句如下：ALTER DATABASE ADD LOGFILE MEMBER 'log3a.rdo' TO GROUP 10;6.4 删除日志组 删除编号为10的重做日志组，语句如下：ALTER DATABASE DROP LOGFILE GROUP 10;6.5删除重做日志成员 【例5.47】 删除重做日志文件log1a.rdo，语句如下：ALTER DATABASE DROP LOGFILE MEMBER 'log1a.rdo'Oracle实验2截图1.实际体验物理体系结构Drop table t;Create table t as select * from all_objects;Create index object_id_idx on t(object_id);Set autotrace onSet timing onSelect object_name from t where object_id=29;Select /*full(t)*/ object_name from t where object_id=29从统计信息看，第二次查询比第一次查询的时间和物理读都有所降低。2.查看物理体系结构各部分的内容Show parameter sgaShow parameter pgaShow parameter shared_pool_size;Show parameter db_cache_size;数据缓冲池Show parameter log_buffer;日志缓冲区3.控制文件3.1从视图V$CONTROLFILE中查询控制文件的名称列表SELECT NAME FROM V$CONTROLFILE;3.2从视图V$CONTROLFILE_RECORD_SECTION中查询到控制文件中保存数据的记录类型、记录大小、记录总数量、使用记录数量等信息SELECT TYPE,RECORD_SIZE,RECORDS_TOTAL,RECORDS_USED FROM V$CONTROLFILE_RECORD_SECTION;4.数据文件4.1查看数据文件信息4.1.1从视图V$DATAFILE中查看数据文件的信息SELECT NAME, STATUS, BYTES FROM V$DATAFILE;4.2 创建数据文件 【例】 创建表空间MyTbs，同时创建一个50MB的数据文件，代码如下：CREATE TABLESPACE MyTbs DATAFILE ' C:APPWKORADATAORCLMyDataFile01.DBF' SIZE 50M;【例】 创建表空间TempTbs，同时创建一个10MB的临时文件，代码如下：CREATE TEMPORARY TABLESPACE TempTbs TEMPFILE 'C:APPWKORADATAORCLMyTempFile01.DBF' SIZE 50M EXTENT MANAGEMENT LOCAL;【例】 向表空间MyTbs中添加一个数据文件MyDataFile02.DBF，大小为50MB，代码如下：ALTER TABLESPACE MyTbs ADD DATAFILE ' C:APPWKORADATAORCLMyDataFile02.DBF' SIZE 50M; 4.3修改数据文件的大小 【例】 将数据文件C:APPWKORADATAORCLUSERS01.DBF的大小修改为100M，代码如下：ALTER DATABASE DATAFILE 'C:APPWKORADATAORCLUSERS01.DBF'RESIZE 100M; 可以执行下面的语句查看当前数据库中数据文件的大小。SELECT NAME, BYTES FROM V$DATAFILE; 4.4 修改数据文件的在线状态 【例】 将数据文件C:APPWKORADATAORCLUSERS01.DBF的在线状态修改为脱机，代码如下：ALTER DATABASE DATAFILE 'C:APPWKORADATAORCLUSERS01.DBF' OFFLINE;【例】 将表空间MYTBS中所有数据文件设置为联机状态，代码如下：ALTER TABLESPACE MYTBS DATAFILE ONLINE;4.5删除数据文件 【例】 删除表空间MyTbs，同时删除其中数据文件的代码如下：DROP TABLESPACE MyTbs INCLUDING CONTENTS CASCADE CONSTRAINTS; 也可以使用ALTER DATABASE命令删除指定的数据文件。5.日志文件5.1查看日志文件信息 查询视图V$LOGFILE，显示重做日志的成员文件，语句如下：SELECT GROUP#,MEMBER FROM V$LOGFILE; 查询视图V$LOG，显示控制文件中重做日志组的信息，语句如下：SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;5.2 创建日志组在ALTER DATABASE语句中使用ADD LOGFILE子句创建重做日志组： 添加重做日志文件log1c.rdo和log2c.rdo，初始大小为5M，代码如下：ALTER DATABASE ADD LOGFILE ('log1c.rdo', 'log2c.rdo') SIZE 5000k;Select * from v$logfile;【例】 创建重做日志组10，其中包含重做日志文件log1c.rdo和log2c.rdo，语句如下：ALTER DATABASE ADD LOGFILE GROUP 10 ('log1a.rdo','log2a.rdo') SIZE 5000k;Select * from v$logfile;5.3 创建重做日志成员【例】 将重做日志文件log3a.rdo添加到编号为10的重做日志组中，语句如下：ALTER DATABASE ADD LOGFILE MEMBER 'log3a.rdo' TO GROUP 10;5.4 删除日志组删除编号为10的重做日志组，语句如下：ALTER DATABASE DROP LOGFILE GROUP 10;5.5删除重做日志成员【例5.47】 删除重做日志文件log1f.rdo，语句如下：ALTER DATABASE DROP LOGFILE MEMBER 'log1f.rdo'实验三 常用SQL语言的使用（2学时）实验目的:熟悉常用的sql语句。实验内容：1. 查询语句：查询所有、指定段、使用别名、模糊查询、排序查询 2. 单行函数的使用3. 多表查询4. 子查询实验三、四的要求：本实验以题目的方式进行。考虑到大家在数据库概论中学过部分sql语句，简单的sql语句在实验手册中没写出sql语句，请大家自己思考，自己书写，复杂的sql语句已经给出，请大家思考后练习1.常用查询语句看emp表的所有信息看有哪些部门，部门有哪些人、每个人的工作是什么老板要给每个员工多发600元，看每个员工要发多少钱如果要给老板打印一份员工的工资表，使用上述查询语句老板可能看不懂ename,job,sal，因此需给老板一个看懂的工资表，因此要对列使用别名如果老板要求文档的列标为 Employee' Salary,而且数据为* annual salary is *，如SMITH annual salary is 15600 从emp表中查询有几个部门如果想查询每个部门中的岗位有哪些？查找smith的薪水，工作，所在部门查找在82年1月1号入职的员工姓名、雇佣日期查找薪水在20003000的雇员姓名和薪水显示首字母为S的员工的姓名和工资显示第三个字符为O的所有员工姓名和工资 如果老板让你查找没有绩效的员工名称如果老板让你查找员工姓名、工资、绩效和年收入查找员工岗位是 SALESMAN、CLERK、MANAGER的员工信息查找员工岗位不是SALESMAN、CLERK、MANAGER的员工信息按sal升序排列，如果有工资相同的，按姓名字符从低到高排序，如成绩排序选择在部门 30 中员工的所有信息列出职位为（MANAGER）的员工的编号，姓名找出奖金高于工资的员工找出每个员工奖金和工资的总和找出部门 10 中的经理(MANAGER)和部门 20 中的普通员工(CLERK) 找出部门 10 中既不是经理也不是普通员工，而且工资大于等于 2000 的员工找出没有奖金的不同工作找出没有奖金或者奖金低于 500 的员工2.单行函数2.1字符型函数 Upper Lower Initcap Concat Substr Length Replace instr2.2数字型函数 ROUND(表达式,n):四舍五入 TRUNC(表达式,n)：不四舍五入 MOD(m,n)：取余数 select round(168.888,1) ,trunc(168.888,1) ,mod(1900,400) from dual; 结果为：168.9,168.8,300 mod(300,400)余数为300，应记住oracle的这个规定2.3日期型函数 Months_between() Select ename,job,hiredate,months_between(sysdate,hiredate) from emp; Add_months() select add_months(sysdate,1) from dual; Next_day() select next_day(sysdate,'星期一') from dual;系统日期的下一个星期一 Last_day select last_day(sysdate) from dual; 2.4转换函数 1、to_char (日期,fmt):日期转换为字符 fmt为格式，如：'dd-mm-yyyy'，yyyy/mm/dd select to_char(sysdate,'dd-mm-yyyy') from dual;结果为14-08-2014 select to_char(sysdate, 'yyyy/mm/dd ') from dual; 结果为2014/08/14 2、to_char (数字,'fmt'):数字转换为字符串 select to_char(sal*12,'L99999.00') from emp; ￥81468.00 3、to_date(字符串,'fmt')： select to_date('16-6月-03') -sysdate from dual; 课后练习： 所有员工名字前加上 Dear ,并且名字首字母大写 找出姓名为 6 个字母的员工 找出姓名中不带 R 这个字母的员工 显示所有员工的姓名的第一个字 假设一个月为 30 天，找出所有员工的日薪，不计小数 找到 2 月份受雇的员工 列出员工加入公司的天数(四舍五入） 分别用 case 和 decode 函数列出员工所在的部门，deptno=10 显示'部门 10', deptno=20 显示'部门 20' deptno=30 显示'部门 30' deptno=40 显示'部门 40' 否则为'其他部门3. 分组函数3.1 count： select count(*) from emp; 公司中有多少员工由经理管理（即不属于高级管理成层）； select count(mgr) from emp; 查看部门号为10的员工数 select count(*) from emp where deptno=10; 查看有多少个岗位select count(distict job) from emp;3.2 avg select avg(sal) from emp; 平均工资3.3 sum 查看这个月工人的工资支出 select sum(sal+nvl(comm,0) from emp; 3.4 min select min(sal) from emp;最少工资3.5 max select max(sal) from emp;最多工资 查找雇佣第一员工和最迟雇佣员工 Select min(hiredate),max(hiredate) from emp;3.6 group by：一般与分组函数一起使用 Select deptno,avg(sal) from emp group by deptno; select job,avg(sal) from emp having avg(sal) >2000 group by job; 3.8分组函数嵌套 查找工作不是president的员工中按工作分类不同工作的最低平均工资和最高平均工资 select min(avg(sal),max(avg(sal) from emp where job not like 'PRE%' group by job; 课后练习： 分组统计各部门下工资>600 的员工的平均工资 统计各部门下平均工资大于 1600 的部门 算出部门 30 中得到最多奖金的员工奖金 算出每个职位的员工数和最低工资 显示每个部门的平均工资和最高工资 查询最高工资的员工姓名、岗位、工资 查询出高于平均工资的员工的信息 查出高于本部门员工平均工资的员工信息4.多表查询 4.1 相等连接 查询每个员工所属部门和所在的具体地点 select emp.ename,dept.dname,dept.loc from emp.dept where emp.deptno=dept.deptno; select a.ename,b.dname,b.loc from emp a,dept b where a.deptno=b.deptno; 公司高级管理层想知道工资为1600元或以上的员工所属的部门和地点 select emp.ename,dept.dname,dept.loc from emp.dept where emp.deptno=dept.deptno and emp.sal>=1600 4.2 自连接 老板要找每个分析员的上司谈话，要查询每个分析员及上司的姓名 select w.ename "雇员名",w.job "雇员工作",m.ename "经理姓名", m.job "经理工作"from emp w,emp m where w.mgr=m.empno and w.job='ANALYST' 4.3 不等连接 查询显示工资级别在3-5级之间的所有员工 select e.empno, e.ename, e.job, e.sal, s.grade from emp e, salgrade s where( e.sal between s.losal and s.hisal) and( s.grade>2 and s.grade<6); 查询员工姓名、工资、工资级别 select a1.ename,a1.sal,a2.grade from emp a1,salgrade a2 where a1.sal between a2.losal and a2.hisal; 4.4 外连接 select * from dept 发现有4个部门，编号为40的部门在emp表中并没有见过，想查出所有部门的名称、地点和员工信息，怎么办 select a.*,b.* from emp a, dept b where a.deptno(+)=b.deptno; Select emp.empno,emp.ename,emp.sal,dept.deptno,loc from dept left outer join emp on (emp.deptno=dept.deptno); Select emp.empno,emp.ename,dept.deptno,dept.loc from emp right outer join dept on (emp.deptno=dept.deptno);4.5 表连接特殊语法using子句 Select e.empno,e.ename,e.sal,d.loc from emp e jo