《实验四Oracle大数据库对象.docx》由会员分享，可在线阅读，更多相关《实验四Oracle大数据库对象.docx（16页珍藏版）》请在三一办公上搜索。
1、实验四Oracle数据库对象一、实验目的了解Oracle的表、视图、索引、序列、同义词的基本概念和基本用法。二、实验内容和步骤1、查看用户具有的对象和可访问的对象（操作结果截图填入空白处）以SCOTT连接数据库。（1）查看用户模式中各种类型的对象数量select object_type,count（*） from user_objects group by object_typeSQL conn scott/tiger已莲接。SQL select object_type,countron user_objects group by object_type；OBJECT_TPECOUNTINDE
2、X2TABLE7（2）确定用户模式有权访问的对象总数select object_type,count(*) from all_objects group by object_typeSQL select object_type,count from all_objects group by object_type；OBJECT_TPECOUNTEDITIONCONSUMER GROUPSEQUENCESCHEDULE PROCEDURE OPERATORDESTINATIONUINDOUSCHEDULER GROUP PACKAGE PROGRAM13439311OBJECT_TPECOUNT
3、12527699JAUA RESOURCEXML SCHEMAJOB CLASSTABLESYNONYM(3) 确定谁拥有用户能够看见的对象select distinct owner from all_objects2、创建基本表(填入代码)SQL desc Ithb 名称是否为空？类型KHBHNOT NULL VARCHAK2 (8KMCVARCHAK2 (12)KHXBCHAR (3)CSRQCHAR (8)JTDZVARCHAE2 (30)LXDHVARCHAK2 (14)创建上图基本表KHB，要求表创建在表空间USER_DATASQL create table KHB2 khbh ua
4、rchar2 not null primary key,3 khmc uarchar2 ,4 khxb char,5 csrq char,6 jtdz uarchar2,7 Ixdh uarchar28 9 tablespace USER_DflTfl:表已创建.3、创建临时表（填入代码）（1）创建一个基于会话的临时表。a）创建临时表tmp_khb_1 （与KHB同结构）;SQL create global temporarij table tmp_khb_l2 khbh uarchar2 not null pFiinary key,3 khmc uarchar2 ,4 khxb char,5
5、 csrq char,6 jtda uarchar2,7 Ixdh uarchar28 9 on commit preserue rows；表已创建.b）插入一条记录 khbh= 00000001，khmc= aaa ;SQL insert into tnp_klib_l ualues ；已创建1行。SQL select * from khh；卜选定行SQL select * from tmp_khb_l；KHBH KHMCKHX CSRQ JTDZLXDH00000001 aaac）以SCOTT用户启动第2个SQL*PLUS会话；d）在第2个会话中查询tmp_khb_1 ；（操作代码及操作结
6、果截图）SQL select * from tmp_khb_l；KHBH KHMCKHK CSRQ JTDZLKDH00000001 aaaSQL .ISQL select * from tmp_khb_l ；床选定行观察是否可以看见第1个会话插入的数据，为什么？不能，因为基于会话的临时表只对当前会话可见。每个会话只能查看和修改自 己的数据。e）插入一条记录 khbh= 00000002，khmc= bbb ;f）在第1个会话中查看第2个会话的数据；（操作代码及操作结果截图）观察是否可以看见第2个会话插入的数据，为什么？不能，因为基于会话的临时表只对当前会话可见。每个会话只能查看和修改自 己的
7、数据。g）在第1个会话中，将表截断；SQL truncate table tmp_khb_l；表被截断.h）在第2个会话中，观察表中是否仍然存在记录；（操作代码及操作结果截图）SQL select * from tmp_khb_l； “HBH KHMCKHX CSRQ JTDZLXDH00000002 bbb表中是否仍然存在记录？为什么？是，因为基于会话的临时表只对当前会话可见。每个会话只能查看和修改自己 的数据。（2）创建一个基于事务的临时表a）创建临时表tmp_khb_2 （与KHB同结构）;SQL create global temporary table2 khbh uarchar2
8、not null primariF key,3 khmc uarchar2 ,4 khxb char,5 csrq char,6 jtd2 uarchar2,7 Ixdh uaFchar28 9 on commit delete rows；表已创建.b）插入一条记录 khbh= 00000001，khmc= aaa ;QL insert into tnip_khb_2 values ；已创建1行.c）查询tmp_khb_2；（操作代码及操作结果截图）ISQL select * fromtmp_khb_2;KHBHKHMCKHX CSRQJTDZLXDH00000001 aaad）事务提交com
9、mit;SQL commit；提交完成e）再次查询tmp_khb_2；（操作代码及操作结果截图）提父无成SQL select * from tmp_khb_2；未选定行是否能查询到数据？为什么？不能，因为基于事务的临时表中的数据仅在事物过程中有效，当事物提交后， 临时表的暂时段将被自动截断4、创建分区表（填入代码）（1）创建范围分区a）创建范围分区表range_testrange_test （ t_id number primary key,item_id number（8） not null,item_description varchar2（300）分区字段为t_id，以值10、20及 m
10、axvalue分为3个分区表，分别存放到不同 的表空间中。SQL create table i*ange_test2 t_id nLimber primary key,3 item_id nLimbei not null,4 item_description uarchai25 part itionrange7 part ition pl values8 part ition p2 ualues9 part ition p3 valuesless than tablespace part_01,less than tablespace part_02, less than tablespace
11、 part_0310 ；表已创建，b)插入三条记录insert into range_test( t_id, item_id,item_description) values(9,100,aaa );insert into range_test( t_id, item_id,item_description) values(19,200, bbb);insert into range_test( t_id, item_id,item_description) values(29,300,ccc);c)查询前2个分区的记录(操作代码及操作结果截图)d）删除第2个分区SQL alter table
12、 range_test drop partition p2；表已更改。e）查询表全部数据（操作代码及操作结果截图）SQL select * from range_test 2:T_IDITEM_IDITEM_DESCRIPTION9100aaa29300ccca)创建 list_test (结构如 PPT)7PARTITIONpart018PARTITIONpart029PARTITIONpart0310；2 qq_id uarchar2 ,3 city uarchar24 5 tablespace USER_DATA6 PARTITION BY LISTtcityXSQL create ta
13、ble list_test ualues, valuesbeijingf,t ianJingJ , values表已创建.b)插入几条数据INSERT INTO list_test values(01,shanghai);INSERT INTO list_test values(02,beijing);INSERT INTO list test values(02,suzhou);数据能否插入？为什么？第一和第二个可以插入，第三个不行，因为插入的分区中没有Fuzhou这个 关键字。c）查询分区1数据（操作代码及操作结果截图）&QL select * from list_test part it
14、 ion ；OQ_IDCIT01shanghaiRQL5、启用 oracle 的 execution plan（1）以sysdba身份连接数据库；conn / as sysdba（2）创建 plan_table 表，执行 utlxplan.sql 脚本；?/rdbms/admin/utlxplan.sql（3）创建同义词为多个用户共享一个plan_table，并授权给public；create public synonym plan_table for plan_table;grant all on plan_table to public;（注意，执行过程中，若出现同义词名称已存在，请先删除
15、该同义词）drop public synonym plan_table;（4）创建plustrace角色?/sqlplus/admin/plustrce.sql（5）将角色权限授予publicgrant plustrace to public;完成授权后，就可以开启执行计划模式，可以采用以下几种方式：set timing on 显示执行时间set autotrace on 查看执行计划和查询信息set autotrace traceonly 只显示执行计划，不显示查询输出6、创建物化视图（填入代码）（1）为scott用户授权&QL grant create materialized uiew
16、to scott ；功成功.&QL grant query rewrite to scott ；授权成功。（2）创建基于emp,dept表的物化视图日志SQL conn scott/tigerR茬接。bQL create materialized uieu log on emp viith roviid；*体化视图日志已创建.bQL create materialized uieu log on dept viith roviid；实体化视图日志已创建。（3）创建基于emp,dept两张表链接的物化视图SQL create materialized uiew mu_test refresh f
17、ast on commit enable query2 rewrite3 as4 select e.rowid Memp_rowidM,e.empno,d.rouid Mdept_rowidM,d.deptno,5 e .enamej.e . job,e .mgr,e .sal6 from emp e,dept d where e.deptno=d.deptno；实体化视图已创建.（4）查询视图（开启执行计划）（操作代码及操作结果截图）SQL select * pom mu_test；DEPTNOENA MEJOBemp_rowidEMPNO dept_rowidMGRSALAAAR3sAAE
18、AAAACXAAA 79028007369AAAR3qAAEAAAACHAAB20SMITHCLERKAAAR3sAAEAAAACXAAB 769816007499AAAR3qAAEAAAACHAAC30ALLENSALESMANAAAR3sAAEAAAACXAAC 769812507521AAAR3qAAEAAAACHAAC30UARDSALESMANemp_rowidEMPNOdept_rowidDEPTNOENAMEJOBMGRSALAAAR3sAAEAAAACXAAD783929757566AAAR3qAAEAAAACHAAB20JONESMANAGERAAAR3sAAEAAAACXA
19、AE7654AAAR3qAAEAAAACHAAC30MARTINSALESMAN7、创建索引（代码及截图）SQL select INDEX _NA ME,INDEX _T PE,T A BLE_NA ME,T A BLES PA CE_NA ME from USER-INDEXES；INDEX_NAMEINDEX-TVPETABLE_NAMETABLESPACE_NAMESS_C0011124NORMALTMP_KHB_2SS_C0011122NORMALTMP_KHB_1SS_C0011120NORMALKHBUSER_DATAPK_EMPNORMALEMPUSERSPK_DEPTNORMA
20、LDEPTUSERSSQL(2)创建位图索引a)创建基于emp(job)的位图索引;&QL create BITMAP index job_idx on emp；索引已创建.b)开启执行计划，查询表记录，看是执行全表查询，还是基于索引的查询 select count(*) from emp where job=SALESMAN;QL select count from emp where job=,SALESMANJ；COUNT4SOL(3) 创建函数索引a)创建基于emp的trunc(sal)索引SQL create index sal_index on emptrunc；索引已创建。b)在
21、开启了执行计划的状态下，分别进行如下查询，看查询扫描的范围是全表还是基于索引；select ename, sal from emp where sal2000SQL selectename,sal from emp where sal2000；ENAMESALJONES2975BLAKE2850CLARK245QSCOTTKINGFORD3000已选择6行.SQL select2:ename,sal from emp where trunc2000ENAMESALCLARK2450BLAKE2850JONES2975SCOTT3000FORD3000KING5000代码)select enam
22、e, sal from emp where trunc(sal)20008、创建序列(填入(1) 创建一个表create table tmp_sq(id int null,t_name varchar(20);(2) 创建一个序列，初始值1,增量1,无最大值，序列不循环。/r心qi jSQL create sequence SEQ_TEST2 start with 13 increment by 1；序列已创建。(3) 插入数据insert into TMP_SQ(id,t_name) values(SEQ_TEST.nextval , aaa );insert into TMP_SQ(id,
23、t_name) values(SEQ_TEST.nextval , bbb );(4) 建立触发器，当有数据插入表TMP_SQ时，使用Oracle序列为其插入递增的 主键值。create trigger TRG_TESTbefore insert on TMP_SQ for each rowbeginselect SEQ_TEST.nextval into :new.ID from dual;end;(5) 删除数据 t_name= bbbQL delete from tmp_sq where t_name=, bbbJ； 已删除1行。(6) 插入新数据t_name= cccQL insert
24、 into TMP_SQualues；已创建1行。（7）显示当前表中数据（操作代码及操作结果截图）&QL select * from tmp_sq 2；ID T_NAME1 aaa2 cccPQL9、创建同义词（填入代码）（1）公有同义词a）以system用户连接数据库b）创建EMP表的公有同义词Ec）以scott连接数据库d）访问同义词E （操作代码及操作结果截图）SQL create public synonym E 同义词已创建。for scott.emp；SALCOMMSQL conn scott/tiger日连接0SQL select * from E；EMPNO ENAMEJOBM
25、GRHIREDATEDEPTNO736920SMITHCLERK790217-121-80800749930ALLENSALESMAN7698况-2月-811600300752130UARDSALESMAN7698找-2月-811250500scott用户能访问system用户创建的公共同义词E吗？ 可以（2）私有同义词a）以system用户连接数据库conn sytem/toorb）创建DEPT表的私有同义词D_Screate synonym d_s for scott.dept;c）以scott用户连接数据库conn scott/tigerd）访问同义词D_S （操作代码及操作结果截图）何
26、连接SQL create gyrwnym d_s for scott. dept ；同义词已创建.SQL conn scott/tiger归连接。bQL select * from d_s；select * from d_sb i行出现ORA-00942 :表或视图不存在scott用户能访问system用户创建的私有同义词D_S吗？ _不能三、实验思考1、两个表经常进行连接操作，为了提高连接的效率，可以对这两个表建立什么结构？2、序列的作用？序列生成了，怎么使用呢？当你需要一个自动增长的id或别的什么主键的时候，你可以用一个序列来解决问题。3、位图索引能加快检索的原因？索引就是通过事先排好序，从而在查找时可以应用二分查找等高效率的算法。一般的顺序查找，复杂度为O(n)，而二分查找复杂度为O(log2n)。当n很大时，二者 的效率相差及其悬殊。
