1、配置新的服务名连接串:NEW_YXDB =4)测试新的服务名连接串的可用性在oracle用户下,执行 tnspingnew_yxdb如下表示正常状态:oraclejamesadmin$tnspingnew_yxdbTNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 18-MAY-2016 11:51:32Copyright (c) 1997, 2013, Oracle. All rights reserved.Used parameter files:/oracle/app/oracle/product/11.2.0/d
2、b_1/network/admin/sqlnet.oraUsed TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521) (CONNECT_DATA = (SERVICE_NAME = yxdb)OK (0 msec)注释:2.根据需求输出相应结果集(group by,多表链接等)例1:分组函数group by 计算按照工作职位分类最高平均工资和
3、最低平均工资数。SQL SELECT MAX(AVG(sal),MIN(AVG(sal) FROM EMP GROUP BY JOB;例2:多表链接查询职员名称,组织编号,组织名称,公司位置。select e.ename,d.deptno,d.dname,d.loc from deptd,emp e where d.deptno = e.deptno;3.根据要求编写存储过程,函数,视图编写存储过程:创建一个存储过程,查询员工姓名,员工岗位,雇佣日期和薪水。CREATE OR REPLACE PROCEDURE selectemp(employeeno IN INTEGER)ISemploye
4、ename varchar2(20);employeejob varchar2(9);employeehiredate date;employeesal number(7,2);BEGIN select ename,job,hiredate,sal INTO employeename,employeejob,employeehiredate,employeesal FROM empWHERE empno = employeeno;DBMS_OUTPUT.put_line (员工姓名 |employeename |员工岗位 |employeejob雇佣日期 |employeehiredate薪水
5、 |employeesal);EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line(ERRORS!);END;/编写函数:创建一个函数,返回3.14*(f*f)的值。CREATE OR REPLACE FUNCTION area(f float) RETURN float RETURN 3.14*(f*f);END area;例3:编写视图:创建一个视图,可以查询员工的姓名,工作,雇佣日期,工资,组织名称。create view accounting_view asselect e.ename employee_name,e.job jobe.hire
6、date hiredatee.sal salaryd.dname dep_namefrom deptd,emp ewhere e.deptno =d.deptno; 4.数据库存储管理操作步骤:假设存在表空间TEST,要求给该表空间添加数据文件。1)确认表空间TEST已存在的数据文件路径和大小 create tablespace test add datafile /oracle/app/oracle/oradata/yxdb/test01.dbf size 2M; select file_name,tablespace_name,bytes/1024/1024 from dba_data_f
7、iles where tablespace_name=TEST;FILE_NAME TABLESPACE_NAME BYTES/1024/1024- - -/oracle/app/oracle/oradata/yxdb/test01.dbf TEST 22)按照需求添加数据文件,大小为2M,不开启自动扩展 alter tablespace test add datafile /oracle/app/oracle/oradata/yxdb/test02.dbf size 2M autoextend off;Tablespace altered5.数据库闪回操作假设数据表被误删除,利用闪回恢复特性
8、恢复误删数据表。1)确认是否已经开启闪回 select flashback_on from v$database;FLASHBACK_ON-NO2) 开启闪回功能shutdown immediate;startup mount;alter database flashback on;alter database open;3)确认闪回路径和空间大小NAME TYPE VALUE- - -db_recovery_file_deststring /oracle/app/oracle/fast_recovery_areadb_recovery_file_dest_size big integer 4
9、182M4)模拟用户误删除数据库表,利用闪回特性恢复误删数据表,并重新命名create user test identified by test default tablespace test;User created. grant dba to test;Grant succeeded. conn test/test;Connected. create table test as select * from dba_objects where rownum 18000; drop table test;Table dropped. select object_name,original_na
10、me,ts_name,createtime,droptime from recyclebin;OBJECT_NAME ORIGINAL_NAME TS_NAME CREATETIME DROPTIME- - - - -BIN$Mxf36aYJUX7gU2U4qMAgfA=$0 TEST TEST 2016-05-18:13:16:19 2016-05-18:17:05 select * from test;select * from test *ERROR at line 1:ORA-00942: table or view does not exist flashback table BIN
11、$Mxf36aYJUX7gU2U4qMAgfA=$0 to before drop rename to test1;Flashback complete. select count(*) from test1;COUNT(*)- 179996.Impdp/expdb导入导出数据操作假设需求是导出用户test的表t1,并导入到用户test1中。1) 创建模拟数据环境,并创建数据泵目录,以及授权。 create table test.t1 tablespace test as select * from dba_objects where rownum 1000;Table created. create user test1 identified by test1 default tablespace test; grant dba to test1; !-创建dump目录oraclejames$mkdir dumporaclejames$pwd/home/oracleoraclejames$ cd dumporaclejamesdump$pwd/home/oracle/dumporaclejamesdump$ exitexit-创建数据泵目录,并授权 create or replace directory dump as /home/ora
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1