oracle数据库基本知识重点.docx

oracle数据库基本知识重点.docx

《oracle数据库基本知识重点.docx》由会员分享，可在线阅读，更多相关《oracle数据库基本知识重点.docx（42页珍藏版）》请在冰豆网上搜索。

oracle数据库基本知识重点

数据库基本知识

一、几种常见数据库介绍及比较

二、Oracle安装及注意事项:

三、数据库的基本元素

四、数据库的基本维护

一、几种常见数据库介绍及比较

1.几种常见关系性数据库:

IBMDB2,ORACLE.MSSQL,SYBASE,Informix,MySQL

2.来自互联网方面的几种评价:

MSSQL：

秀才---把别人写的书看懂成为自己的东西，衣饰一般都比较考究，但是只懂一国语言，而且喜欢贬低别人。

SYBASE：

农夫---干活不要命，大有拼命三郎的气势，但是感觉总是差点文化涵养。

养鱼，养猪都可以但是却忘了打打广告。

ORACLE：

将军---有大家风范，对秀才和农夫的争论不屑一顾。

凡是有了战役舍我其谁。

但是别忘了还有的士兵也想当将军的。

Informix:

宰相---管你谁对谁错，反正我就是只顾一个皇帝UNIX，我的地位别人休想轻易替代。

MySQL:

韦小宝---我就是要在你们中间捣蛋，虽然年纪小，但是发育也很完善。

大家都喜欢。

3.2001年度全球数据库市场的份额:

DB2占到了34.6%（受收购Informix市场份额影响）,Oracle占32%、微软占16.4%、Sybase占2.6%、其他为14.4%）

二、Oracle安装及注意事项:

作为数据库服务器坚决不要安装金山毒霸和KV3000等防毒软件，可以使用NU2000

1.oracle常见版本:

7.34,8.05,8I,9I,10g

2.安装,不同版本会略有不同,主要以7.34和8I为例,并且在不同的操作系统上8I以后的安装和在NT/W2K上类似,在UNIX系统上安装前需要配置相应的环境变量,

NT、W2K下

（1）运行安装程序进行安装，7.34不能在W2K下安装，需要用第二种方式

（2）导入注册表，复制相应的目录

注意：

如果同时在一个操作系统上同时安装7.34和8I需要现安装7.34在安装8I

3.安装过程：

略

安装注意事项：

安装语言选择：

ENGLISH

DEFAULT_HOME：

DEFAULT_HOME默认8I和7.34同时安装需要修改8I的此项选项为一个其他的名字不能相同如叫8I等，8I安装后需要修改相应的注册表的字符集[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE]的NLS_LANG的值为：

AMERICAN_AMERICA.WE8ISO8859P1，否则sql语句查询的结果为乱码。

安装过程一般选择自定义安装，不要在建立相应的系统默认的数据库。

4.创建数据库7.34（实例管理器NTInstanceManager），8I（数据库助手DatabaseConfigurationAssistant）

5.书写相应的参数后选择advanced（高级）进入高级设置，选择OK进入数据库创建过程

6.设置相应的参数后选择OK返回

7.选择OK进行创建数据库

创建数据库注意事项：

7.34初始化文件一定要在制定的目录下存在并且相应的文件路径一定要存在。

并且初始化文件的回滚段一定要注释掉，等建立晚回滚段后在放开并且重新启动数据库后生效。

8I可以一切都不存在，并且自动创建30个回滚段SYSTEM和RBS0-RBS28，安装完成后运行CATALOG.SQL和CATPROC.SQL进行重新创建系统的同义词和过程函数等

三、数据库的基本元素

1.表空间：

相当于其他数据库的数据设备，用来存放数据库中其他存储元素如：

表、索引、回滚段等常用的表空间：

system（系统表空间，用于存储系统表、用户信息、过程、包、用户自定义函数、同义词、序列等，只要定义和创建将不再变化）、temp_spc（临时表空间，在进行数据查询时使用，尤其是使用子查询）、data_spc（数据表空间，用于存储用户表等数据信息）、indx_spc（索引表空间，用于存储用户表索引）、rbsg_spc（回滚表空间，用于存放回滚段）可以想象为：

用户数据存储的物理载体

（1）创建表空间createtablespacetemp_spc

datafile'd:

\mcht\data\tempmcht.odb'size100MAUTOEXTENDOFF|ON（可以依次有多个数据文件,文件名最好用1，2，3，等划分以便容易识别，中间用逗号分割）

DEFAULTSTORAGE（INITIAL64KNEXT64Kpctincrease0）

online。

如果没有制定数据文件的扩展名将使用.ORA，8I默认为.DBF

注意：

数据文件的大小AIX系统不能大一1G，NT/2K的FAT32分区格式不能大于4G，NTFS分区不受限制。

（2）为表空间增加数据文件

ALTERTABLESPACEtemp_spcadddatafile'd:

\mcht\data\tempmcht2.odb'size100Monline。

建议：

将表空间存放在不同的磁盘上，平衡I/O

2.用户：

数据库在使用的过程中，都要以某用户身份进行登陆，然后在进行此用户下的工作，相当于数据库管理员，拥有不同的权限：

创建数据库后默认的三个用户：

internal（超级用户，在用户管理中不可见）密码为建库时的密码，system密码为manager，sys用户密码为change_on_install，可以想象为：

用户数据存储的逻辑载体

（1）删除用户

dropuserdbauserpszxcascade。

删除用户后，属于该用户的所有数据均被删除如：

表、索引、序列、过程、函数、包等一定要注意另加小心

（2）创建用户

createuserdbauserpszxidentifiedbyfuturepszx

DEFAULTTABLESPACEdata_spc

TEMPORARYTABLESPACEtemp_spc

QUOTAUNLIMITEDONsystem（oracle8以后不再用）

QUOTAUNLIMITEDONtemp_spc

QUOTAUNLIMITEDONrbsg_spc

QUOTAUNLIMITEDONdata_spc

QUOTAUNLIMITEDONindx_spc

QUOTAUNLIMITEDONpers_spc。

（3）分配权限角色

GRANTDBAtodbauserpszx。

GRANTSELECTANYTABLETOdbauserpszx。

GRANTINSERTANYTABLETOdbauserpszx。

GRANTDELETEANYTABLETOdbauserpszx。

GRANTUPDATEANYTABLETOdbauserpszx。

GRANTSELECTANYSEQUENCETOdbauserpszx。

GRANTCREATEUSERTOdbauserpszx。

GRANTCREATEANYTABLETOdbauserpszx。

GRANTCREATEANYSEQUENCETOdbauserpszx。

GRANTCREATEANYINDEXTOdbauserpszx。

GRANTDROPUSERTOdbauserpszx。

GRANTDROPANYTABLETOdbauserpszx。

GRANTDROPANYSEQUENCETOdbauserpszx。

GRANTDROPANYINDEXTOdbauserpszx。

GRANTGRANTANYROLETOdbauserpszx。

GRANTEXECUTEANYPROCEDURETOdbauserpszx。

3.回滚段

（1）创建回滚段

createpublicrollbacksegmentfpos_rs2

tablespacerbsg_spc

storage（initial1Mnext1Moptimal10Mminextents2maxextents500）。

注意：

optimal回滚断最佳的大小

为减少系统资源竞争，改善系统性能一般采用多个回滚段，当多个事务并发进行时将产生多个回滚信息，系统事务对回滚段的调用是系统随机的除非特殊制定SETTRANSACTIONUSEROLLBACKSEGMENTfpos_rsl。

（2）回滚段状态（在线online，不在线offline）只有在线的回滚段段系统事务方可调用

ALTERROLLBACKfpos_rslONLINE|OFLINE。

修改初始化文件INITPFILE.ORA文件rollback_segments=（fpos_rs2,fpos_rs3,fpos_rs4,fpos_rs5,fpos_rs6,fpos_rs7,fpos_rs8,fpos_rs9,fpos_rs10,fpos_rs11,fpos_rs12,fpos_rs13,fpos_rs14,fpos_rs15,fpos_rs16,fpos_rsl）

4.数据库连接（DB_LINK）实现数据库的互联

（1）数据库的被连接端必须有相应的用户存在

（2）数据库的主联接端建立相应的连接对象

CREATEPUBLICDATABASELINKpszx_db.world

CONNECTTOdblinkusrIDENTIFIEDBYfuture19990501

USING'pszx_db'。

删除数据库连接DROPPUBLICDATABASELINKpszx_db.world。

数据库连接建立之后可以对连接里的内容进行访问了

SELECT*FROMSYS_GLJGFROMDBAUSETPSZX.SYS_GLJG@PSZX_DB

如要屏蔽访问的负杂性可以创建同义词

5.表：

数据库存放用户数据的最主要的方式

（1）创建表

CREATETABLEAPP_BINARY

（APPCHAR

（2）NOTNULL,

MODULEVARCHAR2（12）NOTNULL,

SEQNONUMBERNOTNULL,

LENGTHNUMBER,

BINLONGRAW,

CONSTRAINTPK_APP_BINARYPRIMARYKEY（APP,MODULE,SEQNO）

）

TABLESPACEDATA_SPC

PCTFREE10PCTUSED40INITRANS1MAXTRANS255

STORAGE（INITIAL24MNEXT64KMINEXTENTS1MAXEXTENTS505PCTINCREASE0）

ENABLECONSTRAINTPK_APP_BINARYUSINGINDEX

PCTFREE10INITRANS2MAXTRANS255

TABLESPACEINDX_SPC

STORAGE（INITIAL80KNEXT64KMINEXTENTS1MAXEXTENTS505PCTINCREASE0）。

说明：

PCTFREE10当表的数据块的使用超过90％是自动使用下一个数据块

PCTUSED40当表的数据块的使用小于40时系统不会分配新的数据块（大将会节省空间，增大insert和upate的系统消耗）

INITIAL初始空间大小

NEXT下一扩展空间的大小

MINEXTENTS初始分配的区域数目

MAXEXTENTS最大可分配的区域数目

PCTINCREASE数据段每个区域大小的增大比率

（2）删除表DROPTABLE表名；

（3）表改名RENAME表名TO新表名（也可以修改索引）

（4）增加列ALTERTABLE表名ADD字段名类型）；

（5）删除例ALTERTABLE表名DROP字段名；7.34不能删除列，8I的初始化参数compatible=8.1.0时才可删除列

（6）增加主键ALTERTABLE表名ADDCONSTRAINT主键名PRIMARY（字段,…）；

（7）分区建表（8的新功能）分区是将一个对象的数据分开存储的一种机制，减少磁盘I/O将一个较大的表中数据分成一些较小的表

CREATETABLEPERSONS

（NONUMBERPRIMARKKEY,

NAMEVARCHAR2（20）,

SEXCHAR

（1）,

BIRDAYDATE）

PARTITIONBYRANGE（NO）

（PARTTIONPART1VALUESLESSTHEN（98010）小于NO98010时用PART1

TABLESPACESZJ1,

PARTTIONPART2VALUESLESSTHEN（98020）小于NO98020时用PART2

TABLESPACESZJ2,

PARTTIONPART3VALUESLESSTHEN（98020）小于NO98030时用PART3

TABLESPACESZJ3,

PARTTIONPART4VALUESLESSTHEN（MAXVALUE）小于NO98030时用PART4）。

查询SELECT*FROMPERSONS（PART2,PART3）WHERENOBETWEEN98015AND98025

一般分区的表空间建立在不同的磁盘上

（8）常用字段数据类型

CHAR最长2000B

VARCHAR2（8I、9I将采用VARCHAR）4000B（7.342000B）

LONG变长字符2GB

NUMBER（s，d）数字最长38位

DATE日期

BLOB二进制大集4GB（RAW,LONGRAW将在未来版本中淘汰）

ROWID物理地址类型

（9）回滚（回退）COMMIT和提交ROLLBACK：

我们对数据库的操作是由事务来控制管理的，在一个事务中，应该及时对所作的操作进行提交，以方式操作无效，也可取消非法的操作数据库事务：

是一个逻辑的工作单元

回滚和提交到上次回滚或提交点的事务操作

注意事项：

有一些特殊的事务是回自动提交（隐式提交），在操作的过程种一定注意：

QUIT、EXIT、CREATETABLE、ALTERTABLE、DROPTABLE、CREATEVIEW、ALTERVIEW、DROPVIEW、GRANT、REVOKE、CONNECT、DISCONNECT、AUDIT（审计）、NOAUDIT.

（10）清空表：

TRUNCATETABLE表名；

注意事项：

清空表的操作不能回滚，所要清空的表不能被其他事务锁定，不能清空试图和同义词，清空表不触发触发器。

6.索引，为增加查询的速度，会影响数据的更新负荷（要适度的建立索引）

（1）创建索引

CREATEINDEXINDEX_JXC_COM_DAY_TRANSIDONJXC_COM_DAY（TRANS_ID）

TABLESPACEINDX_SPC

PCTFREE10INITRANS2MAXTRANS255

STORAGE（INITIAL20MNEXT5MMINEXTENTS1MAXEXTENTS505

PCTINCREASE0）。

创建索引PARALLEL（DEGREE5）并行的5个进程共同创建索引（不使用于创建主健）

NOLOGGING参数可选

Oracle8I支持函数索引

Oracle8I支持联机索引online参数创建索引（因为创建索引时表自动加锁不允许更新而使用ONLINE参数则可以联机创建索引了）

（2）查询中使用索引，oracle自动分析语句使用最佳的索引，主要依据sql查询语句的WHERE条件，也可强制使用索引如：

select--+INDEX（IDX_JMD_MFDATE）也可/*+INDEX（IDX_JMD_MFDATE）*/

JMDMFIDGZ,

ROUND（SUM（JCHSJJJE）,2）HSJE,

ROUND（SUM（JCBHSJJJE）,2）BHSJE

FROMJXCMFRAMEDAY

WHEREJMDWMIDIN（'1','2','3'）ANDJMDDATE=TO_DATE（'HSJSRQ','YYYYMMDD'）

GROUPBYJMDMFID

具体的查询语句用PL/SQL的EXPLANPLANWINDOW进行查询索引的使用情况，大数据量的表一定注意索引的使用情况

强制使用索引时尽量使用/*+INDEX（JXCMFRAMEDAYIDX_JMD_MFDATE）*/

避免所选择的语句被注释并且多表联立（或通过同义词访问）时尽量强制使用索引

多个表的索引使用格式/*+INDEX（表1索引1）INDEX（表2索引2）*/

（3）分区局部索引

CREATEINDEXPERSONS_NO

ONPERSONS（NO）

LOCAL

（PARTIONPART1TABLESPACESZJ1,

PARTIONPART2TABLESPACESZJ2,

PARTIONPART3TABLESPACESZJ3,

PARTIONPART4TABLESPACESZJ4）。

（4）分区全局索引

CREATEINDEXPERSONS_NO

ONPERSONS（NO）

GLOBALPARTIONBYRANGE（NO）

（PARTIONPART1VALUESLESSTHEN（98010）TABLESPACESZJ_INDEX1,

PARTIONPART2VALUESLESSTHEN（98020）TABLESPACESZJ_INDEX2,

PARTIONPART3VALUESLESSTHEN（98030）TABLESPACESZJ_INDEX3,

PARTIONPART4VALUESLESSTHEN（MAXVALUE）TABLESPACESZJ_INDEX4）。

（5）创建索引注意事项：

索引的表不能被其他事务锁定，8I支持ORACLE系统函数的结果当成字段进行索引。

7.34不支持

7.视图：

是包含一个或多个表（视图）中数据的简化描述执行一个查询结果，并将它看成一个表（虚拟表）

例：

CREATEVIEWSYS_GZASSelectCODE,NAME,SJCODE,ISSPB,ISWSFB,FLAG,TYPE,GDFL,BDFL,JHLRL,JHZZTS,YYMJ,SEQNO,STATUS,ISZG,ZGGYS,GYSSEQ,SYSREG,SUBJECT,ISXH,ISCS,ISMGYS

FROMsys_gljgWHEREflag='Y'ANDfgljgjcbm（code,1）=fgetmc。

Oracle8I支持实例化视图CREATEMATERIALIZEDVIEW**

TABLESPACEDATA_SPC

PCTFREE10INITRANS2MAXTRANS255

STORAGE（INITIAL20MNEXT5MMINEXTENTS1MAXEXTENTS505

PCTINCREASE0）。

materializedview还需要另外的写盘时间

8.同义词：

减少不同用户间数据访问的复杂性，增加远程分布数据的透明度和安全层

没有创建同义词时FUTUREPOS用户在对DBAUSERMCHT用户中的表SYS_GLJG进行访问的时候SELECT*FROMDBAUSERMCHT.SYS_GLJG。

建立同义词之后可以自由访问

CREATE【PUBLIC】SYNONYMPSZX_SYS_GLJGFORSYS_GLJG@PSZX_DB.WORLD。

创建了一个全局同义词。

那些可以建立同义词：

表，视图，序列，过程，函数，建立了数据库链接之后的以上内容均可

9.序列：

多用户产生的唯一整数的数据库对象，用户可用序列自动产生主关键字的值。

CREATESEQUENCESEQ_JXC_COM_DAY序列名称

INCREMENTBY1步长

MINVALUE1最小值

NOCYCLE没有周期

CACHE20高速缓存

ORDER。

序列的访问selectseq_jxc_com_day.nextvalintol_jxcseqfromdual。

语句运行后自动加一

seq_jxc_com_day.CURRVAL返回刚才的值不加1

10.自定义函数，编译后可想ORACLE自己的函数一样使用，函数必须有返回值，函数不能更新数据表的内容

例：

CREATEORREPLACEFUNCTIONf0Null（n1INNUMBER）

RETURNNumber

AS

BEGIN

IFn1=0THEN

ReturnNULL。

ELSE

Returnn1。

ENDIF。

END。

/

11.过程：

用来完成用户一系列的操作，过程可以调用其他的过程、函数、包等。

CREATEORREPLACEPROCEDUREsp_cmd_com_sc（v_codeinchar）

AS

lbzchar

（1）。

lnnumber。

BEGIN

--调用删除编码过程

deletefromqt_commodwherecode=v_code。

commit。

--是否日终处理命令

SELECTRTRIM（LTRIM（VALUE））INTOlbzFROMsys_paraWHEREcode='29'。

iflbz='Y'then

begin

selectseqnointolnfrompszx_command_rz

wherecomm='SCCOM'andpara=''''||v_code||''''。

exceptionwhenno_data_foundthen

INSERTINTOpszx_command_rz

VALUES（seq_pszx_command_rz.NEXTVAL,SYSDATE,'SCCOM',''''||v_code||''''）。

commit。

return。

end。

endif。

sp_scspbm（v_code）。

commit。

EXCEPTIONWHENOTHERSTHEN

sp_ht_error（'r','sp_cmd_com_sc',v_code,sqlcode,sqlerrm）。

raise。

ENDsp_cmd_com_sc。

/

12.包：

数据库中的实体，包含一系列的公共常量、变量、数据类型、光标（游标）过程及函数的定义，具有模块化的优点，包包括：

包头和包体

13.JOB：

为系统设定定时执行的过程

建立JOB

variablejobnonumber。

BEGINdbms_job.submit（:

jobno,'sp_pszx_rz_job。

',TRUNC（sysda