大型数据库管理与应用实训王扬扬

1、1、建立一个表空间，表空间名为oracle_data,数据文件名为oracle.dbf，大小为20m，存在D盘根下create tablespace oracle_datadatafile 'd:oracle.dbf' size 20Mextent management local;2、建立一个用户，用户名是xuesheng，密码是xuesheng，用户表空间为oracle_dataCREATE USER xueshengIDENTIFIED BY xueshengDEFAULT TABLESPACE oracle_data;3、 将dba的权限赋给xuesheng用户GRAN

2、T DBA TO xuesheng;问题：如何将dba的权限的sql语句解决方法：上网查4、 将所给shixun.dmp文件存在D盘根目录下，用学生用户导入该文件D:>imp userid=xuesheng/xuesheng full=y file=shixun.dmp问题；ORA-01658: 无法为表空间ORACLE_DATA中的段创建 INITIAL 区解决方法：扩展表空间alter databasedatafile 'd:oracle.dbf'resize 800m; 5、 以xuesheng用户连接数据库，建立一个表，表结构如下：表名：student字段：id

3、numberName varchar2(2;Age varchar2(2要求 1）将id设置成为自动增长列（用序列、触发器）2）向表中插入记录： 李明 23王勇 22刘江飞 19建表studentcreate table student ( id number not null primary key, Name varchar2(2 not null, Age varchar2(2 not null ;创建序列 create sequence student1increment by 1 start with 1 nomaxvalue nocycle ;创建触发器create or repl

4、ace trigger student2before insert on student for each rowbeginselect student1.nextval into:new.id from dual; end;/改变表Name的数据类型alter table student modify name varchar2(10;插入数据insert into student (Name,Age values ('李明','23' insert into student (Name,Age values ('王勇','22'

5、; insert into student (Name,Age values ('刘江飞','19'问题 ：插入的值对于列过大 解决: 改变表Name的数据类型alter table student modify name varchar2(10;6、首先为student表增加2列：score (用于存放百分制成绩，grade (用于存放5分制ABCDE成绩，并依次为每个人录入92，88，75的百分制成绩增加列alter table student add score number check(score between 0 and 100;alter tabl

6、e student add grade varchar2(3;添加数据update student set score=92 where id =1;update student set score=88 where id =2;update student set score=75 where id =3;7、阅读以下语句，分析其实现了什么功能：update studentset grade = (select grade from(select id,case when score> 90 then 'a'when score > 80 then 'b&

7、#39;when score > 70 then 'c'else 'd' end gradefrom student awhere a.id = student.id;看能不能对上述语句进行优化/简化？功能：根据id的值来更改student表中grade列的值，当score的值大于90小于等于100 时输出a，当score的值大于80时，输出b，当score的值大于70时，输出c，其他时候输出d。优化后update studentset grade = (select grade from(select id,case when score between

8、 90 and 100 then 'a' when score between 80 and 89 then 'b' when score between 70 and 79 then 'c'when score between 60 and 69 then 'd' else 'e' end gradefrom student awhere a.id = student.id;8、逻辑备份用户xuesheng的数据，文件为 xuesheng.dmpexp userid=xuesheng/xuesheng file=

9、'd:xuesheng.dmp'9、 删除oracle_data表空间及其数据文件，按照1的要求重建表空间(数据文件无限扩展删除oracle_data表空间及其数据文件drop tablespace oracle_data including contents and datafiles;重建表空间create tablespace oracle_datadatafile 'd:oracle.dbf' size 20M AUTOEXTEND ON MAXSIZE UNLIMITED;10、从xuesheng.dmp中导入表 tb_score_info,tb_st

10、u_info,tb_learning_infoImp userid=xuesheng/xuesheng tables=TB_LEARNING_INFO,TB_SCORE_INFO,TB_STU_INFO file='xuesheng.dmp'、11、执行select * from tb_score_info where score_id=1记录执行时间想办法提高执行速度set timing onselect * from tb_score_info where score_id=1;创建索引create index score_id_index on tb_score_info

11、(score_id;select * from tb_score_info where score_id=1;12、Tb_stu_info 字段含义Stu_id学号birth出生日期Tb_learning_info 字段含义Stu_id学号Eval_flag标记（0不合格，1合格）Class_id班级代号要求根据表tb_stu_info 的 birth字段，查出邮政远程网学员的年龄结构：Age人数30-3940-50<30>50（1） 使用select 单个语句完成本题功能（2） 编写存储过程proc_count_age完成本题功能（3） 使用select 单个语句完成如下要求的统

12、计：age30-394050<30>50人数8006957337685747813使用select 单个语句完成本题功能select (case when a.age>=30 and a.age<=39 then '30-39'when a.age>=40 and a.age<=50 then '40-50'when a.age<30 then '<30'when a.age>50 then '>50'endas age,count(* as count from (se

13、lect to_number(to_char(sysdate,'yyyy'-to_number(substr(birth,1,4age from tb_stu_info,dual agroup by (case when a.age>=30 and a.age<=39 then '30-39'when a.age>=40 and a.age<=50 then '40-50'when a.age<30 then '<30'when a.age>50 then '>50'

14、end;编写存储过程proc_count_age完成本题功能create or replace procedure proc_count_ageas count1 tb_stu_info%rowtype;begindeclare cursor proc_count is select(case when a.age>=30 and a.age<=39 then '30-39'when a.age>=40 and a.age<=50 then '40-50'when a.age<30 then '<30'when

15、 a.age>50 then '>50'end as age,count(* as countfrom (select to_number(to_char(sysdate,'yyyy'-to_number(substr(birth,1,4 age from tb_stu_info,dual agroup by (case when a.age>=30 and a.age <=39 then '30-39'when a.age>=40 and a.age <=50 then '40-50'when

16、 a.age<30 then '<30'when a.age>50 then '>50'end;begindbms_output.put_line('年龄段 总人数'for count1 in proc_count loopdbms_output.put_line(count1.age|' '|count1.count;end loop;end;end;/SQL> set serveroutput onSQL> exec proc_count_age使用select 单个语句完成如下要求的统计：

17、age30-394050<30>50人数8006957337685747813select sum(count(case when a.age>=30 and a.age<=39 then '30-39' else null end "30-39",sum(count(case when a.age>=40 and a.age<=50 then '30-39' else null end "40-50",sum(count(case when a.age<30 then '

18、<30' else null end "<30",sum(count(case when a.age>50 then '<30' else null end ">50" from ( select to_number(to_char(sysdate,'yyyy'-to_number(substr(birth,0,4 age from tb_stu_info,dual agroup by (case when a.age>=30 and a.age<=39 then '

19、;30-39'when a.age>=40 and a.age<=50 then '40-50'when a.age<30 then '<30'when a.age>50 then '>50'end;13、根据tb_stu_info ,tb_learning_info 查找没有参加培训班学习的人员数量。利用not in 、not exists、外连接三种方法，并比较性能Not inselect count(stu_id from tb_stu_info where stu_id not in ( sel

20、ect stu_id from tb_learning_info where stu_id=tb_stu_info.stu_id;not existsselect count(stu_id from tb_stu_infowhere not exists (select stu_id from tb_learning_info where stu_id=tb_stu_info.stu_id;外连接select count(a.stu_id from tb_stu_info a,tb_learning_info b where a.stu_id=b.stu_id(+ and b.stu_id i

21、s null;总结： 根据查询的时间的显示 ，很容易看出 not exists和外连接性能好，not in 次之。14、将年龄大于50的人的合格标识（tb_learning_info表的eval_flag字段）置为1update tb_learning_info set eval_flag=1 where STU_ID in ( select stu_id from tb_stu_info where to_number(to_char(sysdate,'yyyy'-to_number(substr(birth,0,4 >50;15、使用insert 语句将tb_lear

22、ning_info拆成多个表：早期学员信息表：包括学习号在5000以前的学员的学号和班级代号07zx0731班级表：包括该班的学员学号 一条intsert语句完成创建早期学员信息表create table tb1( STU_ID VARCHAR2(7,CLASS_ID VARCHAR2(8;创建07zx0731班级表create table tb2( STU_ID VARCHAR2(7;用insert语句拆分Insert all when learning_id<=5000 then into tb1 values(stu_id,class_idwhen class_id='07

23、zx0731' theninto tb2 values(stu_idselect learning_id,stu_id,class_id from tb_learning_info;16、Rman的备份与恢复按照课本步骤建立catalog数据库，数据库名为catadb_姓名缩写配置控制文件自动备份做一个全备份0级备份1级备份备份新建表空间备份控制文件创建oracle_data表空间对应数据文件的备份，存放在D盘下的oraclebackup下做一个0级备份的备份脚本，要求将备份文件存放在D盘下的oraclebackup下，而每次备份生成的文件名是唯一不重复的，要手工分配通道。Rman恢复

24、spfile 恢复controlfile 恢复datafile 恢复tablespace 恢复基于时间点的恢复将数据库改为存档模式，自动存档启用alter system set log_archive_start=true scope=spfile;shut immediatestartup mountalter database archivelog;alter database open;创建rman_ts表空间create tablespace rman_tsdatafile 'd:rman_ts.dbf' size 200M AUTOEXTEND ON MAXSIZE

25、UNLIMITED;创建用户rman_01create user rman_01identified by rman_01default tablespace rman_tsquota unlimited on rman_ts;给rman_01授权grant recovery_catalog_owner to rman_01;grant connect,resource to rman_01;给rman_01解锁alter user rman_01 account unlock;创建恢复目录RMAN> connect catalog rman_01/rman_01RMAN> cre

26、ate catalog tablespace rman_ts;连接到目标数据库和恢复目录数据库D:>rman target / catalog rman_01/rman_01注册目标数据库register database;配置控制文件自动备份RMAN> configure controlfile autobackup on;做一个全备份run allocate channel a1 type disk;allocate channel a2 type disk;backup databaseformat 'd:backfull_%U.bak' filesperse

27、t=4;0级备份runallocate channel b1 type disk;allocate channel b2 type disk;backup incremental level 0 databaseformat 'd:backlevel0_%U.bak' filesperset=4;1级备份runallocate channel d1 type disk;allocate channel d2 type disk;backup incremental level 1 databaseformat 'd:backlevel1_%U.bak' file

28、sperset=4;备份新建表空间用report schema命令查看表空间runallocate channel e1 type disk;backup tablespace rman_tsformat 'd:backxjbkj_%U.bak' filesperset=4;备份控制文件run allocate channel f1 type disk;backup current controlfile format 'd:backcontrol_%U.bak' filesperset=4;创建oracle_data表空间对应数据文件的备份，存放在D盘下的or

29、aclebackup下查询oracle_data表空间对应的数据文件：select file_id from dba_data_files where tablespace_name='ORACLE_DATA'run allocate channel C1 type disk; COPY DATAFILE 11 TO 'D:oraclebackupORACLE_%U'做一个0级备份的备份脚本，要求将备份文件存放在D盘下的oraclebackup下，create script oraclebackup_level0allocate channel d1 type

30、disk;allocate channel d2 type disk;backup incremental level 0 database format 'd:oraclebackup%U'执行runexecute script oraclebackup_level0;controlfile 恢复在sqlplus中select dbid from v$database；shut immediate在rman中set dbid = 4101728657startup nomount;restore controlfile from autobackup;alter databa

31、se mount;recover database;alter database open resetlogs;datafile 恢复在rman中sql "alter database datafile ''d:oracle.dbf'' offline"restore datafile 'd:oracle.dbf'recover datafile 'd:oracle.dbf'sql "alter database datafile ''d:oracle.dbf'' on

32、line"tablespace 恢复sql 'alter tablespace oracle_data offline'restore tablespace oracle_data;recover tablespace oracle_data;sql 'alter tablespace oracle_data online'17、将test.dmp导入到scott用户，共有7个表imp scott/tiger full=y file='d:test9i.dmp'18“全国邮政支局长第二轮远程培训” 有学习记录但没有考试记录的学员姓名姓名

33、select from test_tb_learning_info a,(select distinct stu_id,class_id from test_tb_classrcd_info b,(select distinct stu_id,class_id from test_tb_score_info c,test_tb_class_info d,test_tb_stu_info ewhere a.class_id=d.class_id and a.stu_id=e.stu_id and a.class_id=b.class_id(+ and a.stu_id=b.stu_id(+ and a.class_id=c.class_id(+ and a.stu_id=c.stu_id(+ and b.stu_id is not null and c.stu_id is null and d.class_name='全国邮政支局长第二轮远程培训'19查询参加了全部班级课程考试的学员信息和班级名称。（例如 某班排了3门课，学员在这三门都考了试，不管合格与否）姓名单位班级名称select ,c.unit,d.class_namefrom (select class_id,coun