Oracle数据库技术与实验指导.docx
《Oracle数据库技术与实验指导.docx》由会员分享，可在线阅读，更多相关《Oracle数据库技术与实验指导.docx（66页珍藏版）》请在冰豆网上搜索。
Oracle数据库技术与实验指导
Oracle数据库技术与实验指导
2011.08
第0章有用Oracle数据库技术
Oracle10g/11g数据库都分为标准版（StandardEdition）、标准版1（StandardEditionOne）以及企业版（EnterpriseEdition）。
可从如下网址下载、学习或试用Oracle：
SQL*PlusInstantClient（SQL*Plus即时客户端）
实验1数据库系统基础操作
OracleDatabase11g第2版（11.2.0.1.0）的下载地址：
适用于MicrosoftWindows（32位）的OracleDatabase11g第2版（11.2.0.1.0）的下载地址：
实验2数据库基础操作
手工建库
手工建库须要通过几个步骤，每一个步骤都专门关键。
它包括：
1、创建有关名目（数据文件和跟踪文件）（假设要创建KCGL数据库，Oracle已安装于“c:
\app\qxz”名目）
在c:
\app\qxz\admin那个名目之下创建KCGL文件夹；
在C:
\app\qxz\admin\KCGL那个名目之下创建adump文件夹；
在C:
\app\qxz\admin\KCGL那个名目之下创建dpdump文件夹；
在C:
\app\qxz\admin\KCGL那个名目之下创建pfile文件夹；
在C:
\app\qxz\oradata那个名目之下创建KCGL文件夹；
2、创建初始化参数文件
通过复制现有的初始化参数文件C:
\app\qxz\admin\orcl\pfile那个名目下的参数文件“init.ora.*”（*为数字扩展名）到C:
\app\qxz\product\11.2.0\dbhome_1\database那个名目，修改名为initKCGL.ora,最后用记事本打开那个参数文件，修改如下几个参数的值：
audit_file_dest=C:
\app\qxz\admin\KCGL\adump
db_name=KCGL
control_files=（"C:
\app\qxz\oradata\KCGL\control01.ctl","C:
\app\qxz\oradata\KCGL\control02.ctl"）
3、打开DOS窗口，设置环境变量：
Setoracle_sid=KCGL
4、创建服务：
Oradim-new-sidKCGL
5、创建口令文件
Orapwdfile=C:
\app\qxz\product\11.2.0\dbhome_1\database\pwdKCGL.ora
Password=12345
6、启动服务器：
Sqlplus/nolog
Conn/assysdba
Startupnomount
7、执行建库脚本：
CREATEDATABASEKCGL
datafile'c:
\app\qxz\oradata\KCGL\system01.dbf'size300m
autoextendonnext10mextentmanagementlocal
Sysauxdatafile'c:
\app\qxz\oradata\KCGL\sysaux01.dbf'size120m
undotablespaceundotbs1
datafile'c:
\app\qxz\oradata\KCGL\undotbs01.dbf'size100m
defaulttemporarytablespacetemptbs1
tempfile'c:
\app\qxz\oradata\KCGL\temp01.dbf'size50m
logfilegroup1（'c:
\app\qxz\oradata\KCGL\redo01.log'）size50m,
group2（'c:
\app\qxz\oradata\KCGL\redo02.log'）size50m,
group3（'c:
\app\qxz\oradata\KCGL\redo03.log'）size50m;
用记事本编辑以上内容，假定储存为C:
\CREATEKCGL.sql文件，然后执行那个脚本。
StartC:
\CREATEKCGL.sql
不管显现哪种错误，都要删除C:
\app\qxz\oradata\KCGL名目下创建的所有文件，改正错误后，重新启动实例，再执行建库脚本。
8、创建数据字典和包
StartC:
\app\qxz\product\11.2.0\dbhome_1\RDBMS\ADMIN\catalog
StartC:
\app\qxz\product\11.2.0\dbhome_1\RDBMS\ADMIN\catproc
9、执行pupbld.sql脚本文件
切换成system用户执行如下命令：
Connsystem/manager
StartC:
\app\qxz\product\11.2.0\dbhome_1\sqlplus\admin\pupbld
10、执行scott脚本创建scott方案
StartC:
\app\qxz\product\11.2.0\dbhome_1\RDBMS\ADMIN\scott.sql
这时需要修改密码：
Conn/assysdba
Alteruserscottidentifiedbytiger;
再连接scott：
Connscott/tiger
11、select*fromdept;
能显示出dept表的结果，表示新数据库KCGL已安装成功了。
2.2查看数据库
1、查看表空间的名称及大小
selecttablespace_name,min_extents,max_extents,pct_increase,statusfromdba_tablespaces;
selecttablespace_name,initial_extent,next_extent,contents,logging,
extent_management,allocation_typefromdba_tablespacesorderbytablespace_name;
selectt.tablespace_name,round（sum（bytes/（1024*1024））,0）ts_sizefromdba_tablespacest,dba_data_filesdwheret.tablespace_name=d.tablespace_name
groupbyt.tablespace_name;
2、查看表空间物理文件的名称及大小
columndb_block_sizenew_valueblksznoprint
selectvaluedb_block_sizefromv$parameterwherename='db_block_size';
columntablespace_nameformata16;
columnfile_nameformata60;
setlinesize160;--为sqlplus命令
selectfile_name,round（bytes/（1024*1024）,0）total_space,autoextensible,increment_by*&blksz/（1024*1024）asincement,maxbytes/（1024*1024）asmaxsizefromdba_data_filesorderbytablespace_name;--blksz一样为8192
selecttablespace_name,file_id,file_name,round（bytes/（1024*1024）,0）total_space
fromdba_data_filesorderbytablespace_name;
3、查看回滚段名称及大小
selecta.owner||'.'||a.segment_nameroll_name,a.tablespace_nametablespace,to_char（a.initial_extent）||'/'||to_char（a.next_extent）in_extents,to_char（a.min_extents）||'/'||to_char（a.max_extents）m_extents,a.statusstatus,b.bytesbytes,b.extentsextents,d.shrinksshrinks,d.wrapswraps,d.optsizeoptfromdba_rollback_segsa,dba_segmentsb,v$rollnamec,v$rollstatdwherea.segment_name=b.segment_nameanda.segment_name=c.name（+）andc.usn=d.usn（+）orderbya.segment_name;
selectsegment_name,tablespace_name,r.status,（initial_extent/1024）InitialExtent,（next_extent/1024）NextExtent,max_extents,v.curextCurExtentFromdba_rollback_segsr,v$rollstatvWherer.segment_id=v.usn（+）orderbysegment_name;
4、查看操纵文件
selectnamefromv$controlfile;
5、查看日志文件
selectmemberfromv$logfile;
6、查看表空间的使用情形
select*from（selectsum（bytes）/（1024*1024）as"free_space（m）",tablespace_namefromdba_free_spacegroupbytablespace_name）orderby"free_space（m）";
SELECTA.TABLESPACE_NAME,A.BYTESTOTAL,B.BYTESUSED,C.BYTESFREE,（B.BYTES*100）/A.BYTES"%USED",（C.BYTES*100）/A.BYTES"%FREE"FROMSYS.SM$TS_AVAILA,SYS.SM$TS_USEDB,SYS.SM$TS_FREECWHEREA.TABLESPACE_NAME=B.TABLESPACE_NAMEANDA.TABLESPACE_NAME=C.TABLESPACE_NAME;
7、查看数据库库对象
selectowner,object_type,status,count（*）count#fromall_objectsgroupbyowner,object_type,status;
8、查看数据库的版本
select*fromv$version;
SelectversionFROMProduct_component_versionWhereSUBSTR（PRODUCT,1,6）='Oracle';
9、查看数据库的创建日期和归档方式
selectcreated,log_mode,log_modefromv$database;
10、查看临时数据库文件
selectstatus,enabled,namefromv$tempfile;
常用数据库信息查看命令
（1）oracle中如何样查看总共有哪些用户
select*fromall_users;
（2）查看oracle当前连接数
如何样查看oracle当前的连接数呢？
只需要用下面的SQL语句查询一下就能够了。
select*fromv$sessionwhereusernameisnotnullselectusername,count（username）fromv$sessionwhereusernameisnotnullgroupbyusername#查看不同用户的连接数
selectcount（*）fromv$session#连接数
Selectcount（*）fromv$sessionwherestatus='ACTIVE' #并发连接数
（3）列出当前数据库建立的会话情形
selectsid,serial#,username,program,machine,statusfromv$session;
实验3表与视图的基础操作
3.1创建差不多表
例3-1创建学生、课程、选课三个表，在SQLPLUS的启动界面输入以下代码：
SQL>CreateTableS（SnoVarchar2（10）PrimaryKey,SnameVarchar2（10）NotNull,SsexChar
（2）,SageNumber,SdeptVarchar2（40））;
SQL>CreateTableCourse（CnoVarchar2（10）,CnameVarchar2（50）,CcreditNumber,ConstraintPk_CPrimaryKey（Cno））;
SQL>CreateTableSC（SnoVarchar2（10）, Cno Varchar2（10）,ScoreNumberDefault0Check（ScoreBetween0And100）,ConstraintPk_SPrimaryKey（Sno,Cno））TABLESPACE"Testspace" ;--使用Testspace表空间
3.2修改表
1、修改表空间的有关操作
1）增加表空间中的数据文件
Alter Tablespace TestspaceAdd Datafile 'c:
\app\qxz\file_3.dbf'size100m;
2）删除表空间中的数据文件
Alter Tablespace TestspaceDrop Datafile 'c:
\app\qxz\file_3.dbf';
3）修改表空间文件的数据文件大小
Alter Database Datafile 'c:
\app\qxz\file_2.dbf' Resize 50m;
4）修改表空间数据文件的自动增长属性。
AlterDatabaseDatafile'c:
\app\qxz\file_1.dbf'AutoextendOff;--Off不能自动增长
2、修改表结构的有关操作：
1）插入属性
例3-2在S表插入新属性地址。
SQL>AlterTableSAdd（ AddressVarchar（100））;
2）修改属性
例3-3对上述性别属性的数据类型进行修改，同时默认值为“男”。
SQL>AlterTableSModify（SsexVarchar2
（2）Default'男'）;
3）删除表属性
例3-4删除上述表中的地址属性。
命令为：
SQL>AlterTableSDrop（Address）;
注意：
通常在系统不忙的时候删除不使用的字段，能够先设置字段为unused；
AlterTableSSetUnusedColumnAddress;
系统不忙时再执行删除：
AlterTableSDropUnusedColumn;
4）表重命名
例3-5把表SC改名为Learn。
命令为：
SQL>RenameScToLearn;
5）清空表中的数据
例3-6清空学生表的信息。
命令为：
SQL>TruncateTableS;
6）给表增加注释
例3-7对表S添加注释为'thisIsATestTable'
SQL>CommentOnTableSIs'ThisIsATestTable';
7）给列添加注释
例3-8对表S的Sno属性添加‘学号’的注释。
SQL>CommentOnColumnS.SnoIs'学号';
3.3删除表
例3-9删除Course表。
命令为：
SQL>DropTableCourse;
3.5创建和治理视图
1、创建视图
例3-10在S表中创建以学号、姓名、系别的新视图。
SQL>CreateOrReplaceViewV_S（Num,Name,Sdept）AsSelectSno,Sname,SdeptFromS;
例3-11在SC上定义新视图，当用update修改数据时，必须满足视图score>60的条件，不满足则不能被改变。
SQL>CreateOrReplaceViewV_SCAsSelect*FromSCWhereScore>60WithCheckOption;
例3-12创建新视图，按照学号分组显示学生的最高、最低分和平均成绩。
SQL>CreateViewV_S_SC（Num,Smin,Smax,Savg）AsSelectD.Sno,Min（E.Score）,Max（E.Score）,Avg（E.Score）FromSCE,SDWhereE.Sno=D.SnoGroupByD.Sno;
2、查询视图
例3-13查询上述建立的视图。
命令为：
SQL>Select*FromV_S_SC;
3、更新视图
例3-14把所有学号为08开头的学生的有关系别信息改为治理系。
SQL>UpdateV_SSetSdept='Management'WhereNumlike'08%';
3.6表或视图的导入与导出操作
1、Oracle数据间的导入导出imp/exp
下面是导入导出的实例，导入导出的其它例子或方法请参阅实验13。
（1）数据导出
1）将数据库orcl完全导出,用户名system密码orcl，导出到c:
\orcl.dmp中。
expsystem/orcl@orcl2file=c:
\orcl.dmpfull=y
2）将数据库中jxgl用户与scott用户的表导出。
expsystem/orcl@orcl2file=c:
\orcl_jxglscott.dmpowner=（jxgl,scott）
3）将数据库中jxgl用户的表student,sc导出。
expjxgl/jxgl@orcl2file=c:
\orcl_jxgl_studentsc.dmptables=（student,sc）
4）将数据库中jxgl用户的表student中年龄大于等于19的学生记录导出。
expjxgl/jxgl@orcl2file=c:
\orcl_jxgl_student_agege19.dmptables=（student）query=\"wheresage>=19\"
上面是常用的导出，关于压缩导出，只要在上面命令后面加上compress=y就能够了。
（2）数据的导入
1）将c:
\orcl.dmp中的数据导入orcl数据库中。
impsystem/orcl@orcl2 file=c:
\orcl.dmp
上面可能有点咨询题，因为有的表差不多存在，然后它就报错，对该表就不进行导入。
在后面加上ignore=y就能够了。
2）将c:
\orcl_jxgl_studentsc.dmp中的表sc导入。
impjxgl/jxgl@orcl2file=c:
\orcl_jxgl_studentsc.dmp tables=（sc）ignore=y
实验4SQL语言——SELECT查询操作
创建Student、SC、Course三表及添加表记录命令如下：
CreateTableStudent
（SnoCHAR（5）NOTNULL,
SnameVARCHAR（20）,
SageSMALLINTCHECK（Sage>=15ANDSage<=45）,
SsexCHAR
（2）DEFAULT'男'CHECK（Ssex='男'ORSsex='女'）,
SdeptCHAR
（2）,constraintpr_snoprimarykey（sno））;
CreateTableCourse（CnoCHAR
（2）NOTNULL,CnameVARCHAR（20）,CpnoCHAR
（2）,CcreditSMALLINT,constraintpr_Cnoprimarykey（Cno））;
CreateTableSC（SnoCHAR（5）NOTNULL,CnoCHAR
（2）NOTNULL,GradeSMALLINTCHECK（（GradeISNULL）OR（GradeBETWEEN0AND100））,PRIMARYKEY（Sno,Cno）,CONSTRAINTC_FFOREIGNKEY（Cno）REFERENCESCourse（Cno）,CONSTRAINTS_FFOREIGNKEY（Sno）REFERENCESStudent（Sno））;
INSERTINTOStudentVALUES（'98001','钞票横',18,'男','CS'）;
INSERTINTOStudentVALUES（'98002','王林',19,'女','CS'）;
INSERTINTOStudentVALUES（'98003','李民',20,'男','IS'）;
INSERTINTOStudentVALUES（'98004','赵三',16,'女','MA'）;
INSERTINTOCourseVALUES（'1','数据库系统','5',4）;
INSERTINTOCourseVALUES（'2','数学分析',null,2）;
INSERTINTOCourseVALUES（'3','信息系统导论','1',3）;
INSERTINTOCourseVALUES（'4','操作系统原理','6',3）;
INSERTINTOCourseVALUES（'5','数据结构','7',4）;
INSERTINTOCourseVALUES（'6','数据处理基础',null,4）;
INSERTINTOCourseVALUES（'7','C语言','6',3）;
INSERTINTOSCVALUES（'98001','1',87）;INSERTINTOSCVALUES（'98001','2',67）;IN
升级会员 