Oracle数据库应用技术实验指导书.docx

1、Oracle数据库应用技术实验指导书Oracle数据应用技术实验指导书湖南工程职业技术学院(刘静)2010年3月实验1 Oracle入门（2学时）一、目标1.能够使用Oracle Enterprise Manager Console2.能够创建表空间和用户账户3.能够使用Oracle Net Configuration Assistant配置网络服务名4.能够用SQL *Plus 连接Oracle服务器二、实验内容及要求1.实验内容（1）使用Oracle Enterprise Manager Console熟悉Oracle企业管理器控制台，能够通过图形化的用户界面查看Oracle数据库的物理组

2、件和逻辑组件，加深对Oracle体系结构的理解。分析：要熟悉Oracle数据库，必须先安装Oracle服务器软件，或者安装客户端软件，再通过网络配置链接到一个远程的Oracle服务器。假定已经在本机上安装了Oracle数据库，用户可以使用Oracle Enterprise Manager Console来管理数据库。使用企业管理器控制台可以执行各项任务，如管理Oracle实例、创建表空间、用户账户、表和同义词等。用户需要以管理员身份登陆到Oracle数据库进行练习。（2）创建表空间GITM公司想要在数据库总存储员工信息、工资记录、部门信息和产品信息，这些信息将存储在不同的表中。为了与其他建立在

3、此数据库上的应用系统实现存储空间的独立，需要建一个名为GITM的表空间，此应用系统的中的表都应该存储在此表空间中。（3）创建用户账户在数据库中已创建GITM表空间用于存储公司员工和部门信息表。现在需要一个特定用户，该用户能够访问相关信息，并可创建数据库对象，如表、视图和索引等。要求创建一个用户，并向其授予连接数据库、创建模式对象和使用表空间的权限。（4）使用Oracle Net Configuration AssistantOracle数据库服务器可以被多个用户访问以执行不同的操作。要求使用Oracle Net Configuration Assistant工具来配置网络组件，使用户能够访问远

4、程数据库。（5）使用SQL *Plus和iSQL *Plus连接Oracle服务器Oracle数据库网络配置成功之后，就可以使用Oracle工具连接和使用数据库了。SQL *Plus是最常用的Oracle查询工具，要求用前面练习创建的用户登录数据库，以创建应用系统的表。2. 实验要求根据实验内容指定的要去完成实训，并提交实训结果。实验2 SQL查询和SQL函数（2学时）一、目标1能够构造select语句2能够测试SQL操作符3能够使用SQL函数二、实验内容及要求1.实验内容（1）测试ROWID和ROWNUM伪列用户表因为没有建立主键约束而插入了重复的记录，为了解决数据冗余问题，必须删除表中重复

5、的记录。用户应用程序只关心查询结果集中的一部分，需要限制查询返回的行数。1)启动SQL *PLUS。创建表myt_est:CREATE TABLE my_test ( id NUMBER(3), name VARCHAR2(20) );2)向表中插入5条完全相同的命令。INSERT INTO my_test VALUES (1, This is line 1);INSERT INTO my_test VALUES (1, This is line 1);INSERT INTO my_test VALUES (1, This is line 1);INSERT INTO my_test VALU

6、ES (1, This is line 1);INSERT INTO my_test VALUES (1, This is line 1);COMMIT;查看表中记录： SELECT * FROM my_test;3)测试ROWID伪列。要求查看伪列、id、name，并利用伪列删除第三行记录，再执行查询语句:SELECT ROWNUM, id, name FROM my_test; 查看第三行记录是否被删除。4)测试ROWNUM伪列。使用ROWNUM限制查询ROWNUM lock table employee in share mode;2)打开SQL *PLUS的另一个实例，以SCOTT用户

7、身份登录，执行下列语句。SQLdelete from accp.employee where empno=E005;此时，SQL *PLUS停止响应删除操作等待释放锁。3)切换到第一个SQL *PLUS实例并提交事务。SQLCOMMIT;4)此时，SCOTT用户的删除操作得到响应，并成功返回。执行以下命令取消删除。SQLROLLBACK;5)以SCOTT用户身份锁定employee表。SQLLOCK TABLE accp.employee IN SHARE MODE;6)再以ACCP用户身份锁定employee表。SQLLOCK TABLE employee IN SHARE MODE;此时，

8、表employee被两个用户同时锁定，所有用户都不能在此表上执行更新操作。当所有用户执行COMMIT命令或ROLLBACK命令结束事务时，所才会被释放。（3）测试行共享锁案例：GITM公司的多个部门都需要访问和更新employee表，这些用户都有权限更新表的行，但是有的用户使用了独占锁定表，使其他用户无法更新此表。如何避免这种情况？分析：可以通过使用行共享锁来限制其他用户独占访问表。以行共享方式锁定表时，允许多个用户同时更新同一表中的不同行。1)在ACCP模式中执行下列命令，以行共享模式来锁定employee表。SQLLOCK TABLE employee IN ROW SHARE MODE;

9、2)通过输入下列命令来更新employee表。SQLUPDATE employee SET ename=pam WHERE empno=E001;3)打开SQL *PLUS的另一个实例SCOTT，然后更新employee表中的记录。SQLUPDATE employee SET ename=Sam WHERE empno=E003;4)执行以下语句，尝试独占锁定employee表。SQLLOCL TABLE ACCP.employee IN EXCLUSIVE MODE NOWAIT;（4）创建范围分区创建employee_details表，包含employee_id, ename, dateo

10、fjoining, deptname, branchname和branchcode等列，根据职员的就职日期对employee表进行分区。create table employee_details(emp_id varchar2(5),ename varchar2(25),job varchar2(20),dateofjoining date )partition by range(dateofjoining)( partiton doj1 values less than(to_date(01/04/2001,DD/MM/YYYY),partiton doj2 values less than

11、(to_date(01/07/2003,DD/MM/YYYY),partiton doj3 values less than(to_date(01/09/2004,DD/MM/YYYY),partiton doj4 values less than(to_date(01/10/2005,DD/MM/YYYY) );1)执行下列语句向分区表中插入测试记录。SQLINSERT INTO employee_details SELECT empno,ename,designation,dateofjoining FROM employee;2)执行下列语句查询分区doj4中的记录。SQLSELECT

12、* FROM employee_details partition(doj4);（5）创建散列分区问题：GITM公司的销售部需要根据产品编号分析每个月的销售记录。要求在monthly_sales表中创建散列分区快速完成次工作。create table monthly_sales(product_id varchar2(5) not null,sales_date date not null,sales_cost number )partiton by hash(product_id)( partiton pid1,partiton pid2,partiton pid3 );2. 实验要求根据实

13、验内容指定的要去完成实训，并提交实训结果。实验4 数据库对象（2学时）一、目标1能够创建和使用同义词2能够创建和使用序列3能够创建和使用视图4能够创建各种类型的索引二、实验内容及要求1.实验内容（1）创建同义词问题：为表“ticket_header”创建一个名为“tick”的同义词。1)创建表空间GITM，创建用户ACCP，密码ACCP。以ACCP用户身份登陆SQL *PLUS。2)在SQL提示符下执行语句：create table ticket_header( fleet_id number(5), ticket_no varchar2(6), origin varchar2(6), des

14、tination varchar2(6), adults number(1) );3)执行语句：create synonym tick for ticket_header;提示：create synonym为创建同义词的关键词。（2）创建公有同义词问题：要求所有用户都能够访问ticket_header表而不必知道该表属于哪个用户，请实现。分析：oracle支持两种类型的同义词：私有同义词、公有同义词。私有同义词只能由创建它的用户所使用；公有同义词可以由数据库中所有的用户使用。练习1中创建的是私有同义词。要创建公有同义词必须拥有相应的权限。1)在用户ACCP下执行语句：create public

15、 synonym pub_tick for ticket_header;提示：create public synonym为创建公有同义词的关键词。执行上面语句，oracle将显示“权限不足”的提示消息。2)再以system用户身份登陆到oracle数据库。执行语句：create public synonym pub_tick for accp.ticket_header;3)以ACCP用户登陆，执行语句：select * from pub_tick;（3）删除同义词问题：将练习2中创建的同义词tick删除1)以用户ACCP登陆，执行语句：drop public synonym pub_tick

16、;Oracle将提示“权限不足”。提示：这是因为删除公有同义词必须拥有“drop public synonym”权限。而ACCP用户不具有该权限。2)在ACCP用户下，执行语句：drop synonym tick;（4）创建序列问题：请创建一个名为“new_seq”的序列。要求：初始值为25，每次增加2，达到75时，又从25重新开始。提示：初始值25 start with 25 每次增加2 increment by 2 达到75，也就是说最大值为75 maxvalue 75那么最小值就是25 minvalue 25达到75后，又从25重新开始，也就是说该序列可以循环计数 cycle 执行下面语

17、句： create sequence new_seqincrement by 2start with 25maxvalue 75minvalue 25cyclenocache ;（5）访问序列提示：Oracle提供了两个伪列，可以用来访问伪列的值 currval和nextvalcurrval返回的是序列的当前值；nextval返回的是下一个序列值在练习4完成的基础上，执行语句：1)select new_seq.nextvalue from dual; 2)select new_seq.currvalue from dual; （6）更改序列1)在SQL提示符下执行下列语句：ALTER SEQU

18、ENCE new_seq INCREMENT BY 5;2)执行下列语句查看所作的修改：SELECT new_seq.NEXTVAL FROM dual;（7）删除序列DROP SEQUENCE new_seq;（8）创建视图问题：用户经常只需要显示“fleet_header”表的day列和route_id列，请给出解决方案。1)先创建基表CREATE TABLE fleet_header ( day date, name varchar2(20), route_id number(5) );2)创建视图CREATE VIEW fleet( day, route_id) AS SELECT day, route_id FROM fleet_header;3)访问视图SELECT * FROM fleet;（9）修改视图定义将练习8中所创建的视图，修改为显示“fleet_header”表中所有的列。CREATE OR REPLACE VIEW fleet AS SELECT * FROM fleet_header;（10）创建带有错误的视图在并不存在的表“product”上创建错误视图：CREATE FORCE VIEW myview AS SELECT * FROM produce;（11）删除视图DROP VIEW m