ORACLE数据库实操培训汇编Word文件下载.docx
- 文档编号:20974120
- 上传时间:2023-01-26
- 格式:DOCX
- 页数:19
- 大小:59.88KB
ORACLE数据库实操培训汇编Word文件下载.docx
《ORACLE数据库实操培训汇编Word文件下载.docx》由会员分享,可在线阅读,更多相关《ORACLE数据库实操培训汇编Word文件下载.docx(19页珍藏版)》请在冰豆网上搜索。
OK(0msec)
注释:
2.根据需求输出相应结果集(groupby,多表链接等)
例1:
分组函数groupby
计算按照工作职位分类最高平均工资和最低平均工资数。
SQL>
SELECTMAX(AVG(sal)),MIN(AVG(sal))FROMEMPGROUPBYJOB;
例2:
多表链接
查询职员名称,组织编号,组织名称,公司位置。
selecte.ename,d.deptno,d.dname,d.locfromdeptd,empewhered.deptno=e.deptno;
3.根据要求编写存储过程,函数,视图
编写存储过程:
创建一个存储过程,查询员工姓名,员工岗位,雇佣日期和薪水。
CREATEORREPLACEPROCEDUREselectemp(employeenoININTEGER)
IS
employeenamevarchar2(20);
employeejobvarchar2(9);
employeehiredatedate;
employeesalnumber(7,2);
BEGIN
selectename,job,hiredate,sal
INTOemployeename,employeejob,employeehiredate,employeesal
FROMemp
WHEREempno=employeeno;
DBMS_OUTPUT.put_line('
员工姓名'
||employeename
||'
员工岗位'
||employeejob
雇佣日期'
||employeehiredate
薪水'
||employeesal);
EXCEPTION
WHENOTHERS
THEN
DBMS_OUTPUT.put_line('
ERRORS!
!
'
);
END;
/
编写函数:
创建一个函数,返回3.14*(f*f)的值。
CREATEORREPLACEFUNCTIONarea(ffloat)
RETURNfloat
RETURN3.14*(f*f);
ENDarea;
例3:
编写视图:
创建一个视图,可以查询员工的姓名,工作,雇佣日期,工资,组织名称。
createviewaccounting_viewas
select
e.ename"
employee_name"
e.job"
job"
e.hiredate"
hiredate"
e.sal"
salary"
d.dname"
dep_name"
fromdeptd,empe
wheree.deptno=d.deptno;
4.数据库存储管理操作
步骤:
假设存在表空间TEST,要求给该表空间添加数据文件。
1)确认表空间TEST已存在的数据文件路径和大小
createtablespacetestadddatafile‘/oracle/app/oracle/oradata/yxdb/test01.dbf’size2M;
selectfile_name,tablespace_name,bytes/1024/1024fromdba_data_fileswheretablespace_name='
TEST'
;
FILE_NAMETABLESPACE_NAMEBYTES/1024/1024
------------------------------------------------------------------------------------------
/oracle/app/oracle/oradata/yxdb/test01.dbfTEST2
2)按照需求添加数据文件,大小为2M,不开启自动扩展
altertablespacetestadddatafile'
/oracle/app/oracle/oradata/yxdb/test02.dbf'
size2Mautoextendoff;
Tablespacealtered
5.数据库闪回操作
假设数据表被误删除,利用闪回恢复特性恢复误删数据表。
1)确认是否已经开启闪回
selectflashback_onfromv$database;
FLASHBACK_ON
------------------
NO
2)开启闪回功能
shutdownimmediate;
startupmount;
alterdatabaseflashbackon;
alterdatabaseopen;
3)确认闪回路径和空间大小
NAMETYPEVALUE
----------------------------------------------------------------------------
db_recovery_file_deststring/oracle/app/oracle/fast_recovery_area
db_recovery_file_dest_sizebiginteger4182M
4)模拟用户误删除数据库表,利用闪回特性恢复误删数据表,并重新命名
createusertestidentifiedbytestdefaulttablespacetest;
Usercreated.
grantdbatotest;
Grantsucceeded.
conntest/test;
Connected.
createtabletestasselect*fromdba_objectswhererownum<
18000;
droptabletest;
Tabledropped.
selectobject_name,original_name,ts_name,createtime,droptimefromrecyclebin;
OBJECT_NAMEORIGINAL_NAMETS_NAMECREATETIMEDROPTIME
----------------------------------------------------------------------------------------------------------------------------------
BIN$Mxf36aYJUX7gU2U4qMAgfA==$0TESTTEST2016-05-18:
13:
16:
192016-05-18:
17:
05
select*fromtest;
select*fromtest
*
ERRORatline1:
ORA-00942:
tableorviewdoesnotexist
flashbacktable"
BIN$Mxf36aYJUX7gU2U4qMAgfA==$0"
tobeforedroprenametotest1;
Flashbackcomplete.
selectcount(*)fromtest1;
COUNT(*)
----------
17999
6.Impdp/expdb导入导出数据操作
假设需求是导出用户test的表t1,并导入到用户test1中。
1)创建模拟数据环境,并创建数据泵目录,以及授权。
createtabletest.t1tablespacetestasselect*fromdba_objectswhererownum<
1000;
Tablecreated.
createusertest1identifiedbytest1defaulttablespacetest;
grantdbatotest1;
!
---创建dump目录
[oracle@james~]$mkdirdump
[oracle@james~]$pwd
/home/oracle
[oracle@james~]$cddump
[oracle@jamesdump]$pwd
/home/oracle/dump
[oracle@jamesdump]$exit
exit
---创建数据泵目录,并授权
createorreplacedirectorydumpas'
/home/oracle/dump'
Directorycreated.
grantread,writeondirectorydumptopublic;
2)导出用户test的数据表t1
expdp\"
/assysdba\"
directory=dumpdumpfile=t1.dmptables=test.t1logfile=t1.log
3)导入用户test的数据表t1到用户test2里
impdp\"
directory=dumpdumpfile=t1.dmpremap_schema=test:
test1logfile=test1.log
7.数据文件损坏的恢复操作(rman)
假设数据文件损坏,需利用备份进行恢复。
前提是使用RMAN进行全备份,确保备份集的完整。
1)全备数据库
mkdir-p/home/oracle/backup
rmantarget/catalogrman/rman@catalog
run{
backupascompressedbackupsetfulldatabase
format'
/home/oracle/backup/full_bk_%u%p%s.rmn'
includecurrentcontrolfile;
backupascompressedbackupsetarchivelogall
/home/oracle/backup/arch_bk_%u%p%s.rmn'
deleteallinput;
}
2)模拟数据文件损坏
[oracle@james~]$cd/oracle/app/oracle/oradata/yxdb
[oracle@jamesyxdb]$ls
control01.ctlredo01.logredo03.logsystem01.dbftest01.dbftest03.dbfundotbs01.dbf
example01.dbfredo02.logsysaux01.dbftemp01.dbftest02.dbftest04.dbfusers01.dbf
[oracle@jamesyxdb]$>
test01.dbf(直接清空数据文件)
shutdownimmediate
ORA-01115:
IOerrorreadingblockfromfile6(block#1)
ORA-01110:
datafile6:
'
/oracle/app/oracle/oradata/yxdb/test01.dbf'
ORA-27072:
FileI/Oerror
Additionalinformation:
4
1
3)利用RMAN备份恢复数据库
shutdownabort
ORACLEinstanceshutdown.
startupmount
ORACLEinstancestarted.
[oracle@jamesbackup]$rmantarget/
RMAN>
restoredatabase;
recoverdatabase;
alterdatabaseopen;
8.重做日志文件损坏的恢复操作(rman)
假设重做日志文件损坏,恢复重做日志需根据实际情况,采用非常规的方式进行修复。
RMAN的备份集只用于恢复数据和归档。
2)模拟当前重做日志文件损坏
selectgroup#,members,statusfromv$log;
GROUP#MEMBERSSTATUS
------------------------------------
11INACTIVE
21CURRENT
31INACTIVE
[oracle@jamesyxdb]$echo"
"
>
redo02.log(直接清空日志文件)
startup
TotalSystemGlobalArea313159680bytes
FixedSize2252824bytes
VariableSize171970536bytes
DatabaseBuffers134217728bytes
RedoBuffers4718592bytes
Databasemounted.
ORA-00313:
openfailedformembersofloggroup2ofthread1
ORA-00312:
onlinelog2thread1:
/oracle/app/oracle/oradata/yxdb/redo02.log'
ORA-27048:
skgfifi:
fileheaderinformationisinvalid
13
3)恢复重做日志
---设置隐含参数
altersystemset"
_allow_resetlogs_corruption"
=truescope=spfile;
Systemaltered.
ORA-01109:
databasenotopen
Databasedismounted.
startup
VariableSize176164840bytes
DatabaseBuffers130023424bytes
showparameterreset
_allow_resetlogs_corruptionbooleanTRUE
---进行不完全恢复
recoverdatabaseuntilcancel;
ORA-00279:
change739425generatedat05/20/201611:
48:
48neededforthread1
ORA-00289:
suggestion:
/home/oracle/flash/YXDB/archivelog/2016_05_20/o1_mf_1_50_%u_.arc
ORA-00280:
change739425forthread1isinsequence#50
Specifylog:
{<
RET>
=suggested|filename|AUTO|CANCEL}
ORA-00308:
cannotopenarchivedlog
/home/oracle/flash/YXDB/archivelog/2016_05_20/o1_mf_1_50_%u_.arc'
ORA-27037:
unabletoobtainfilestatus
Linux-x86_64Error:
2:
Nosuchfileordirectory
3
ORA-01547:
warning:
RECOVERsucceededbutOPENRESETLOGSwouldgeterrorbelow
ORA-01194:
file1needsmorerecoverytobeconsistent
datafile1:
/oracle/app/oracle/oradata/yxdb/system01.dbf'
alterdatabaseopen
*
ORA-01589:
mustuseRESETLOGSorNORESETLOGSoptionfordatabaseopen
alterdatabaseopenresetlogs;
alterdatabaseopenresetlogs
ORA-01092:
ORACLEinstanceterminated.Disconnectionforced
ORA-00600:
internalerrorcode,arguments:
[2662],[0],[739432],[0],
[740004],[4194432],[],[],[],[],[],[]
ProcessID:
9525
SessionID:
125Serialnumber:
5
[oracle@james~]$ps-ef|greppmon
oracle271910
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- ORACLE 数据库 培训 汇编