大型数据库管理与应用实训王扬扬.docx
- 文档编号:9131595
- 上传时间:2023-02-03
- 格式:DOCX
- 页数:30
- 大小:1.01MB
大型数据库管理与应用实训王扬扬.docx
《大型数据库管理与应用实训王扬扬.docx》由会员分享,可在线阅读,更多相关《大型数据库管理与应用实训王扬扬.docx(30页珍藏版)》请在冰豆网上搜索。
大型数据库管理与应用实训王扬扬
1、建立一个表空间,表空间名为oracle_data,数据文件名为oracle.dbf,大小为20m,存在D盘根下
createtablespaceoracle_data
datafile'd:
\oracle.dbf'size20M
extentmanagementlocal;
2、建立一个用户,用户名是xuesheng,密码是xuesheng,用户表空间为oracle_data
CREATEUSERxuesheng
IDENTIFIEDBYxuesheng
DEFAULTTABLESPACEoracle_data;
3、将dba的权限赋给xuesheng用户
GRANTDBATOxuesheng;
问题:
如何将dba的权限的sql语句
解决方法:
上网查
4、将所给shixun.dmp文件存在D盘根目录下,用学生用户导入该文件
D:
\>impuserid=xuesheng/xueshengfull=yfile=shixun.dmp
问题;
ORA-01658:
无法为表空间ORACLE_DATA中的段创建INITIAL区
解决方法:
扩展表空间
alterdatabase
datafile'd:
\oracle.dbf'
resize800m;
5、以xuesheng用户连接数据库,建立一个表,表结构如下:
表名:
student
字段:
idnumber
Namevarchar2(2;
Agevarchar2(2
要求1)将id设置成为自动增长列(用序列、触发器)
2)向表中插入记录:
李明23
王勇22
刘江飞19
建表student
createtablestudent
(
idnumbernotnullprimarykey,
Namevarchar2(2notnull,
Agevarchar2(2notnull
;
创建序列
createsequencestudent1
incrementby1
startwith1
nomaxvalue
nocycle;
创建触发器
createorreplacetriggerstudent2
beforeinsertonstudentforeachrow
begin
selectstudent1.nextvalinto:
new.idfromdual;
end;
/
改变表Name的数据类型
altertablestudentmodifynamevarchar2(10;
插入数据
insertintostudent(Name,Agevalues('李明','23';
insertintostudent(Name,Agevalues('王勇','22';
insertintostudent(Name,Agevalues('刘江飞','19';
问题:
插入的值对于列过大
解决:
改变表Name的数据类型
altertablestudentmodifynamevarchar2(10;
6、首先为student表增加2列:
score(用于存放百分制成绩,grade(用于存放5分制ABCDE成绩,并依次为每个人录入92,88,75的百分制成绩
增加列
altertablestudentaddscorenumbercheck(scorebetween0and100;
altertablestudentaddgradevarchar2(3;
添加数据
updatestudentsetscore=92whereid=1;
updatestudentsetscore=88whereid=2;
updatestudentsetscore=75whereid=3;
7、阅读以下语句,分析其实现了什么功能:
updatestudent
setgrade=(
selectgradefrom
(
selectid,
casewhenscore>90then'a'
whenscore>80then'b'
whenscore>70then'c'
else'd'endgrade
fromstudent
a
wherea.id=student.id
;
看能不能对上述语句进行优化/简化?
功能:
根据id的值来更改student表中grade列的值,当score的值大于90小于等于100时输出a,当score的值大于80时,输出b,当score的值大于70时,输出c,其他时候输出d。
优化后
updatestudent
setgrade=(
selectgradefrom
(
selectid,
casewhenscorebetween90and100then'a'
whenscorebetween80and89then'b'
whenscorebetween70and79then'c'
whenscorebetween60and69then'd'
else'e'endgrade
fromstudent
a
wherea.id=student.id
;
8、逻辑备份用户xuesheng的数据,文件为xuesheng.dmp
expuserid=xuesheng/xueshengfile='d:
\xuesheng.dmp'
9、删除oracle_data表空间及其数据文件,按照1的要求重建表空间(数据文件无限扩展
删除oracle_data表空间及其数据文件
droptablespaceoracle_data
includingcontentsanddatafiles;
重建表空间
createtablespaceoracle_data
datafile'd:
\oracle.dbf'size20MAUTOEXTENDONMAXSIZEUNLIMITED;
10、从xuesheng.dmp中导入表tb_score_info,tb_stu_info,tb_learning_info
Impuserid=xuesheng/xueshengtables=TB_LEARNING_INFO,TB_SCORE_INFO,TB_STU_INFOfile='xuesheng.dmp'
、
11、执行select*fromtb_score_infowherescore_id=1
记录执行时间
想办法提高执行速度
settimingon
select*fromtb_score_infowherescore_id=1;
创建索引
createindexscore_id_indexontb_score_info(score_id;
select*fromtb_score_infowherescore_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-39
40-50
<30
>50
(1)使用select单个语句完成本题功能
(2)编写存储过程proc_count_age完成本题功能
(3)使用select单个语句完成如下要求的统计:
age
30-39
40~50
<30
>50
人数
80069
57337
68574
7813
使用select单个语句完成本题功能
select(casewhena.age>=30anda.age<=39then'30-39'
whena.age>=40anda.age<=50then'40-50'
whena.age<30then'<30'
whena.age>50then'>50'
end
asage,count(*ascount
from(selectto_number(to_char(sysdate,'yyyy'-to_number(substr(birth,1,4
agefromtb_stu_info,duala
groupby(casewhena.age>=30anda.age<=39then'30-39'
whena.age>=40anda.age<=50then'40-50'
whena.age<30then'<30'
whena.age>50then'>50'
end
;
编写存储过程proc_count_age完成本题功能
createorreplaceprocedureproc_count_age
ascount1tb_stu_info%rowtype;
begin
declarecursorproc_countisselect(
casewhena.age>=30anda.age<=39then'30-39'
whena.age>=40anda.age<=50then'40-50'
whena.age<30then'<30'
whena.age>50then'>50'
endasage,count(*ascount
from(selectto_number(to_char(sysdate,'yyyy'-to_number(substr(birth,1,
4agefromtb_stu_info,duala
groupby(casewhena.age>=30anda.age<=39then'30-39'
whena.age>=40anda.age<=50then'40-50'
whena.age<30then'<30'
whena.age>50then'>50'
end;
begin
dbms_output.put_line('年龄段总人数';
forcount1inproc_countloop
dbms_output.put_line(count1.age||''||count1.count;
endloop;
end;
end;
/
SQL>setserveroutputon
SQL>execproc_count_age
使用select单个语句完成如下要求的统计:
age
30-39
40~50
<30
>50
人数
80069
57337
68574
7813
selectsum(count(casewhena.age>=30anda.age<=39then'30-39'elsenullend"30-39",
sum(count(casewhena.age>=40anda.age<=50then'30-39'elsenullend"40-50",
sum(count(casewhena.age<30then'<30'elsenullend"<30",
sum(count(casewhena.age>50then'<30'elsenullend">50"
from(selectto_number(to_char(sysdate,'yyyy'-to_number(substr(birth,0,4age
fromtb_stu_info,duala
groupby(casewhena.age>=30anda.age<=39then'30-39'
whena.age>=40anda.age<=50then'40-50'
whena.age<30then'<30'
whena.age>50then'>50'
end
;
13、根据tb_stu_info,tb_learning_info查找没有参加培训班学习的人员数量。
利用notin、notexists、外连接三种方法,并比较性能
Notin
selectcount(stu_idfromtb_stu_info
wherestu_idnotin(selectstu_idfromtb_learning_infowherestu_id=tb_stu_info.stu_id;
notexists
selectcount(stu_idfromtb_stu_info
wherenotexists(selectstu_idfromtb_learning_infowherestu_id=tb_stu_info.stu_id;
外连接
selectcount(a.stu_idfromtb_stu_infoa,tb_learning_infobwherea.stu_id=b.stu_id(+andb.stu_idisnull;
总结:
根据查询的时间的显示,很容易看出notexists和外连接性能好,notin次之。
14、将年龄大于50的人的合格标识(tb_learning_info表的eval_flag字段)置为1
updatetb_learning_infoseteval_flag=1whereSTU_IDin(selectstu_id
fromtb_stu_infowhereto_number(to_char(sysdate,'yyyy'-to_number(substr(birth,0,4>50;
15、使用insert语句将tb_learning_info拆成多个表:
早期学员信息表:
包括学习号在5000以前的学员的学号和班级代号
07zx0731班级表:
包括该班的学员学号一条intsert语句完成
创建早期学员信息表
createtabletb1
(STU_IDVARCHAR2(7,
CLASS_IDVARCHAR2(8;
创建07zx0731班级表
createtabletb2
(STU_IDVARCHAR2(7;
用insert语句拆分
Insertallwhenlearning_id<=5000then
intotb1values(stu_id,class_id
whenclass_id='07zx0731'then
intotb2values(stu_id
selectlearning_id,stu_id,class_idfromtb_learning_info;
16、Rman的备份与恢复
按照课本步骤建立catalog数据库,数据库名为catadb_姓名缩写
配置控制文件自动备份
做一个全备份
0级备份
1级备份
备份新建表空间
备份控制文件
创建oracle_data表空间对应数据文件的备份,存放在D盘下的oraclebackup下
做一个0级备份的备份脚本,要求将备份文件存放在D盘下的oraclebackup下,而每次备份生成的文件名是唯一不重复的,要手工分配通道。
Rman恢复
spfile恢复
controlfile恢复
datafile恢复
tablespace恢复
基于时间点的恢复
将数据库改为存档模式,自动存档启用
altersystemsetlog_archive_start=truescope=spfile;
shutimmediate
startupmount
alterdatabasearchivelog;
alterdatabaseopen;
创建rman_ts表空间
createtablespacerman_ts
datafile'd:
\rman_ts.dbf'size200MAUTOEXTENDONMAXSIZEUNLIMITED;
创建用户rman_01
createuserrman_01
identifiedbyrman_01
defaulttablespacerman_ts
quotaunlimitedonrman_ts;
给rman_01授权
grantrecovery_catalog_ownertorman_01;
grantconnect,resourcetorman_01;
给rman_01解锁
alteruserrman_01accountunlock;
创建恢复目录
RMAN>connectcatalogrman_01/rman_01
RMAN>createcatalogtablespacerman_ts;
连接到目标数据库和恢复目录数据库
D:
\>rmantarget/catalogrman_01/rman_01
注册目标数据库
registerdatabase;
配置控制文件自动备份
RMAN>configurecontrolfileautobackupon;
做一个全备份
run{
allocatechannela1typedisk;
allocatechannela2typedisk;
backupdatabase
format'd:
\back\full_%U.bak'filesperset=4;
}
0级备份
run{
allocatechannelb1typedisk;
allocatechannelb2typedisk;
backupincrementallevel0database
format'd:
\back\level0_%U.bak'filesperset=4;
}
1级备份
run{
allocatechanneld1typedisk;
allocatechanneld2typedisk;
backupincrementallevel1database
format'd:
\back\level1_%U.bak'filesperset=4;
}
备份新建表空间
用reportschema命令查看表空间
run{
allocatechannele1typedisk;
backuptablespacerman_ts
format'd:
\back\xjbkj_%U.bak'filesperset=4;
}
}
备份控制文件
run{
allocatechannelf1typedisk;
backupcurrentcontrolfile
format'd:
\back\control_%U.bak'filesperset=4;
}
创建oracle_data表空间对应数据文件的备份,存放在D盘下的oraclebackup下
查询oracle_data表空间对应的数据文件:
selectfile_idfromdba_data_fileswheretablespace_name='ORACLE_DATA';
run{
allocatechannelC1typedisk;
COPYDATAFILE11TO'D:
\oraclebackup\ORACLE_%U';
}
做一个0级备份的备份脚本,要求将备份文件存放在D盘下的oraclebackup下,
createscriptoraclebackup_level0
{
allocatechanneld1typedisk;
allocatechanneld2typedisk;
backupincrementallevel0databaseformat'd:
\oraclebackup\%U';
}
执行run{executescriptoraclebackup_level0;}
controlfile恢复
在sqlplus中
selectdbidfromv$database;
shutimmediate
在rman中
setdbid=4101728657
startupnomount;
restorecontrolfilefromautobackup;
alterdatabasemount;
recoverdatabase;
alterdatabaseopenresetlogs;
datafile恢复
在rman中
sql"alterdatabasedatafile''d:
\oracle.dbf''offline";
restoredatafile'd:
\oracle.dbf';
recoverdatafile'd:
\oracle.dbf';
sql"alterdatabasedatafile''d:
\oracle.dbf''online";
tablespace恢复
sql'altertablespaceoracle_dataoffline';
restoretablespaceoracle_data;
recovertablespaceoracle_data;
sql'altertablespaceoracle_dataonline';
17、将test.dmp导入到scott用户,共有7个表
impscott/tigerfull=yfile='d:
\test9i.dmp'
18“全国邮政支局长第二轮远程培训”有学习记录但没有考试记录的学员姓名
姓名
selecte.name
from
test_tb_learning_infoa,
(selectdistinctstu_id,class_idfromtest_tb_classrcd_infob,
(selectdistinctstu_id,class_idfromtest_tb_score_infoc,
test_tb_clas
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 大型 数据库 管理 应用 实训王 扬扬
![提示](https://static.bdocx.com/images/bang_tan.gif)