ORACLE数据库实操培训

1.使用tnsnames.ora文件配置本地服务名步骤：1)切换到oracle用户su-oracle2)进入配置目录cd$ORACLE_HOME/network/admin3)配置本地服务名tnsnames.ora配置文件用于配置本地服务名，可以手工修改文件配置，也可以通过netca工具配置。打开tnsnames.ora文件：vitnsnames.ora拷贝样例连接串：YXDB=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=james)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=yxdb)))配置新的服务名连接串：NEW_YXDB=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=james)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=yxdb)))4）测试新的服务名连接串的可用性在oracle用户下，执行tnspingnew_yxdb如下表示正常状态：[oracle@jamesadmin]$tnspingnew_yxdbTNSPingUtilityforLinux:Version11.2.0.4.0-Productionon18-MAY-201611:51:32Copyright(c)1997,2013,Oracle.Allrightsreserved.Usedparameterfiles:/oracle/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.oraUsedTNSNAMESadaptertoresolvethealiasAttemptingtocontact(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.101)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=yxdb)))OK(0msec)注释：2.根据需求输出相应结果集（groupby，多表链接等）例1：分组函数groupby计算按照工作职位分类最高平均工资和最低平均工资数。SQLSELECTMAX(AVG(sal)),MIN(AVG(sal))FROMEMPGROUPBYJOB;例2：多表链接查询职员名称，组织编号，组织名称，公司位置。selecte.ename,d.deptno,d.dname,d.locfromdeptd,empewhered.deptno=e.deptno;3.根据要求编写存储过程，函数，视图例1：编写存储过程：创建一个存储过程，查询员工姓名，员工岗位，雇佣日期和薪水。CREATEORREPLACEPROCEDUREselectemp(employeenoININTEGER)ISemployeenamevarchar2(20);employeejobvarchar2(9);employeehiredatedate;employeesalnumber(7,2);BEGINselectename,job,hiredate,salINTOemployeename,employeejob,employeehiredate,employeesalFROMempWHEREempno=employeeno;DBMS_OUTPUT.put_line('员工姓名'||employeename||'员工岗位'||employeejob||'雇佣日期'||employeehiredate||'薪水'||employeesal);EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.put_line('ERRORS!!!');END;/例2：编写函数：创建一个函数，返回3.14*(f*f)的值。CREATEORREPLACEFUNCTIONarea(ffloat)RETURNfloatISBEGINRETURN3.14*(f*f);ENDarea;例3：编写视图：创建一个视图，可以查询员工的姓名，工作，雇佣日期，工资，组织名称。createviewaccounting_viewasselecte.enameemployee_name,e.jobjob,e.hiredatehiredate,e.salsalary,d.dnamedep_namefromdeptd,empewheree.deptno=d.deptno;4.数据库存储管理操作步骤:假设存在表空间TEST，要求给该表空间添加数据文件。1）确认表空间TEST已存在的数据文件路径和大小SQLcreatetablespacetestadddatafile‘/oracle/app/oracle/oradata/yxdb/test01.dbf’size2M;SQLselectfile_name,tablespace_name,bytes/1024/1024fromdba_data_fileswheretablespace_name='TEST';FILE_NAMETABLESPACE_NAMEBYTES/1024/1024------------------------------------------------------------------------------------------/oracle/app/oracle/oradata/yxdb/test01.dbfTEST22）按照需求添加数据文件，大小为2M，不开启自动扩展SQLaltertablespacetestadddatafile'/oracle/app/oracle/oradata/yxdb/test02.dbf'size2Mautoextendoff;Tablespacealtered5.数据库闪回操作步骤：假设数据表被误删除，利用闪回恢复特性恢复误删数据表。1）确认是否已经开启闪回SQLselectflashback_onfromv$database;FLASHBACK_ON------------------NO2)开启闪回功能SQLshutdownimmediate;SQLstartupmount;SQLalterdatabaseflashbackon;SQLalterdatabaseopen;SQLselectflashback_onfromv$database;3）确认闪回路径和空间大小NAMETYPEVALUE----------------------------------------------------------------------------db_recovery_file_deststring/oracle/app/oracle/fast_recovery_areadb_recovery_file_dest_sizebiginteger4182M4）模拟用户误删除数据库表，利用闪回特性恢复误删数据表，并重新命名SQLcreateusertestidentifiedbytestdefaulttablespacetest;Usercreated.SQLgrantdbatotest;Grantsucceeded.SQLconntest/test;Connected.SQLcreatetabletestasselect*fromdba_objectswhererownum18000;SQLdroptabletest;Tabledropped.SQLselectobject_name,original_name,ts_name,createtime,droptimefromrecyclebin;OBJECT_NAMEORIGINAL_NAMETS_NAMECREATETIMEDROPTIME----------------------------------------------------------------------------------------------------------------------------------BIN$Mxf36aYJUX7gU2U4qMAgfA==$0TESTTEST2016-05-18:13:16:192016-05-18:13:17:05SQLselect*fromtest;select*fromtest*ERRORatline1:ORA-00942:tableorviewdoesnotexistSQLflashbacktableBIN$Mxf36aYJUX7gU2U4qMAgfA==$0tobeforedroprenametotest1;Flashbackcomplete.SQLselectcount(*)fromtest1;COUNT(*)----------179996.Impdp/expdb导入导出数据操作假设需求是导出用户test的表t1，并导入到用户test1中。步骤：1）创建模拟数据环境，并创建数据泵目录，以及授权。SQLcreatetabletest.t1tablespacetestasselect*fromdba_objectswhererownum1000;Tablecreated.SQLcreateusertest1identifiedbytest1defaulttablespacetest;Usercreated.SQLgrantdbatotest1;Grantsucceeded.SQL!---创建dump目录[oracle@james~]$mkdirdump[oracle@james~]$pwd/home/oracle[oracle@james~]$cddump[oracle@jamesdump]$pwd/home/oracle/dump[oracle@jamesdump]$exitexit---创建数据泵目录，并授权SQLcreateorreplacedirectorydumpas'/home/oracle/dump';Directorycreated.SQLgrantread,writeondirectorydumptopublic;Grantsucceeded.2）导出用户test的数据表t1expdp\/assysdba\directory=dumpdumpfile=t1.dmptables=test.t1logfile=t1.log3）导入用户test的数据表t1到用户test2里impdp\/assysdba\directory=dumpdumpfile=t1.dmpremap_schema=test:test1logfile=test1.log7.数据文件损坏的恢复操作（rman）假设数据文件损坏，需利用备份进行恢复。前提是使用RMAN进行全备份，确保备份集的完整。步骤：1）全备数据库mkdir-p/home/oracle/backuprmantarget/catalogrman/rman@catalogrun{backupascompressedbackupsetfulldatabaseformat'/home/oracle/backup/full_bk_%u%p%s.rmn'includecurrentcontrolfile;backupascompressedbackupsetarchivelogallformat'/home/oracle/backup/arch_bk_%u%p%s.rmn'deleteallinput;}2)模拟数据文件损坏[oracle@james~]$cd/oracle/app/oracle/oradata/yxdb[oracle@jamesyxdb]$lscontrol01.ctlredo01.logredo03