《oracle基础入门》PPT课件.ppt

1,ORACLE数据库入门,安徽省邮通科技有限责任公司,一、Oracle体系结构,存储结构（MemoryStructure）操作系统文件（Files）后台进程（BackgroundProcesses）前台进程（ForegroundProcesses）实例（Instance）连接（Session）交易事务（Transaction）监听进程SQL语句怎样在Oracle内部工作的,1、操作系统文件,datafiles,redologfiles,controlfile,parameterfiles,*.dbf,*.log,Initoraid.ora,*.ctl,datafilesredologfilescontrolfilesparameterfile数据文件日志文件控制文件参数文件,Trace-以系统管理员身份登录。SVRMGRLstartup-启动instance其它命令：SVRMGRLstartupnomount;SVRMGRLstartupmount;SVRMGRLalterdatabaseopen;,1、启动,$svrmgrl-(V7.3.2.0以上)唤醒SVRMGRL状态SVRMGRLconnectinternal;SVRMGRLshutdown.,2、关闭,如果不小心物理上删除了一Oracle的数据文件，比如说，某应用表空间所对应数据文件“adc.dbf”，Oracle读控制文件时，和打开数据库时所面对的参数不一致，Oracle数据库将启动不了，解决这种问题的方法是把其对应的表空间先卸下，再删除，以保证控制文件描述和物理上存在文件一致。$svrmgrlSVRMGRconnectinternalSVRMGRstartupmountSVRMGRalterdatabasedatafile/directory/abc.dbfofflinedrop;SVRMGRalterdatabaseopen;SVRMGRdroptablespaceabc;,3、启动时文件损坏,Oracle数据存储单位block结构行链行迁移控制block空间使用的几个参数决定extent的参数段(segment)的分类表(Table)的存储表空间(Tablespace),三、Oracle数据的存储结构,block数据块：2k最小的I-O单位，伴随database产生而产生，不可变extent一组连续的数据块：是用户所能分配存储的最小单位segment段：有共同结构的一个或几个区域(extent)tablespace表空间：一组物理数据的逻辑组合，(象逻辑间数据仓库)file文件：属于某个表空间的物理文件database数据库：一组表空间所构成的逻辑的可共享的数据。,1、Oracle数据存储单位,commonandvariableheader块地址，段类型85-100bytesTabledirectory簇中的表信息，用于簇表段Rowdirectory块中的行信息Freespace用于insertupdataRowdata存储数据、索引,block结构,(系统管理员能在SQLDBA状态，查看视图dba-extents,dba-segments,dba-tablespace,dba-data-files查看所有的extent,segment,tablespace和datafile),2、Block结构,行链,1block,2block,当要存储的数据无法在一个数据块中存放时，需分配两个或多个数据块，标志这几个datablock连接关系的存储信息,3、行链,行链,1block,2block,当一个datablock中的某部分数据经修改后增涨太快，无法继续放在本数据块中，从需把它搬迁到另一个datablock，以优化存储结构，标志这个datablock迁移的存储信息称为行迁移。,4、行迁移,pctfree20%左右它们是互相消涨的pctused40%左右Inittrans在单一块中最初活动的交易事务数Maxtrans在单一块中最大交易事务数,5、控制block空间使用的几个参数,Pctfree,低,高,1、可把块填得较满2、如果重组数据，代价较高3、易引起行迁移,1、剩下多的空间给以后修改用2、需更多的块存数据3、减少行链和重组数据的代价,5.1、Pctfree,当一个DatabaseBlock的剩余空间低于此设置值时（默认值10），此DatabaseBlock会被移出FreeBlock列表，不再允许放入新增的row，所留下来的剩余空间用于容纳目前存在此Block内的数据更新而增加的数据长度以及BlockHeader数据的成长。,Pctused,低,高,1、使重组数据时，代价较低。2、增加了未用的空间数,1、增加空间使用率2、但使重组数据时，代价较高,5.2、Pctused,当一个DatabaseBlock引起内部所存放的数据行被删除，或数据行的长度因更新耳变短，会使此Block的剩余空间逐渐增加。当一Block的使用空间低于此设置值时（默认值40），此DatabaseBlock会被收回FreeBlock列表，重新允许放入新增的数据行。,决定extent的参数pctincrease,initial最初分配的空间数(缺省为10k,5个数据块)next下一步分配的空间数maxextents最大分配的extent数minextents最小分配的extnet数，所有重做日志回滚段的存储结构，必须成对地分配extentpctincrease增长率，指数级增长，optimal尽量设小，或为0(缺省为空，仅用于回滚段)freelist,100,(,),n,1+,6、决定extent的参数,存储参数的设定规则：,1、在对象级的存储参数设置值覆盖表空间级的设置2、未在对象级设置存储参数，由表空间级数设置决定3、未在表空间级设置存储参数，由Oracle数据库级参数设置决定4、若存储参数改变后，新的选项只针对未分配的extents有效。,段的分类,7、段(segment)的分类,data数据段存储对象object(table,cluster,view,index,sequence.)index索引段temporary临时段用做(join,groupby,orderbysorting操作)rollback回滚段用于记录修改前后信息，以便数据库系统rollback,read-consistencybootstrap启动段存储数据字典系统信息不能读写，放在系统表空间内，数据库管理员可不于理会。,datasegment中可能有十种不同类型的存储对象,(1)table(2)view实为一个select语句(3)index(一个表不多于3个)(4)cluster(簇)(5)sequence序列(6)synonme用于定义某个远程数据库同义词，实现分式数据库管理透明(7)snapsot快照(8)storedprocedure(function)(9)package程序包(10)dbtrigger数据触发器，处理updata,delete,insert中可能出现的问题,table存储，按行格式,1,2,3,5,4,6,.,1、rowoverhead行头2、rowpiece列数3、clusterkey(不一定有)4、rowid行的唯一标记(有行链时，记录其它block的rowid5、length6、value,8、表(Table)的存储,当字段值为null时，length为0，无6字value部分,所以某字段常为空，应放table最后；尽量不设为null的字段,rowid:xxxxxxxx.xxxx.xxxx块物理地址该row在块中的行号块所属的文件号,8、表(Table)的存储-1,表空间tablespace,system,rbs,nosystem,把system表空间单独放在一个硬盘上,把system表空间单独放在一个硬盘上,做成一组镜像(如下附图),tools,userstempapplicationdataapplicationindex,分开存放减少争用,9、表空间(Tablespace),NLS参数的默认值NLS参数的主要内容用户的NLS参数设置其它NLS环境变量设置,四、Oracle国家语言支持,Oracle提供完整的国家语言支持(NationalLanguageSupport，简称NLS)，允许不同国家的用户可在数据库中直接使用其熟悉的母语文字，解决在存取或处理这些文字内码时可能引发的冲突问题。,NLS参数是一组参数，主要有两个：nls_language和nls_territory，默认值为AMERICAN和AMERICA。查看命令：showparameternls（或select*fromnls_instance_parameter），版本7不用这个命令。与数据库联机的用户程序如果没有在环境变量中设置NLS参数，则使用Oracle实例中使用的参数，即参数文件中设置的参数。对于中文，参数文件中有：nls_language=“SIMPLIFIEDCHINESE”nls_territory=“CHINA”,1、NLS参数的默认值,2、NLS参数的主要内容,连接数据库的用户进程需要把NLS参数设置在“NLS_LANG”这个环境变量中，如果不设置，数据库自动以自身的帮用户设置。格式：NLS_LANG=language_territory.charset如中文可设为“SIMPLIFIEDChinese_CHINA.ZHS16GBK”。nls_characterset用于设置数据库存储文字时所使用的内码，数据库创建时便已决定。用户设置NLS_LANG中的charset必须和数据库的nls_characterset保持一致，否则在存取数据过程中，可能会因重码而产生不可预期的情况。这样也可以：American_America.ZHS16GBK,3、用户的NLS参数设置,数据库系统根据NLS_LANG设置NLS配套参数，如对于中文设置得到日期格式为“DD-MON-YY”的配套设置，如果不满意可以在环境变量中对各NLS参数重新设置，例如：NLS_DATE_FORMAT=“YYYY-MM-DD”NLS参数都有一定的意义，到底有哪些，可以通过数据字典中的v$nls_valid_values视图获取这些信息。本次联机(session)NLS参数nls_session_parameters视图数据库实例NLS参数nls_instance_parameters视图数据库NLS参数nls_database_parameters视图,4、其它NLS参数设置,参数文件数据库对象定义数据存储结构表空间的类型表空间的规划数据字典,五、规划数据库实例,Oracle完成软件安装后，接下来就要规划数据库实例，然后在这个实例下建造一个规划好了的数据库文件存储结构并挂在其下面运行。,数据库名称SID（不可超过8个字符）db_name=ora8控制文件control_files=(/vol1/control1,/vol2/control2)数据块大小db_block_size=8192数据块缓冲数db_block_buffers=3200回滚段rollback_segments=(rbs01,rbs02,rbs03,rbs04,rbs05,rbs06,rbs07,rbs08,rbs09,rbs10)参数搭配：样本参数文件中准备了大中小三组。,1、参数文件(ParameterFile),参数文件准备好以后就可以启动实例，启动前检查一下oracle用户是否为dba组，环境变量是否正确设置。用svrmgrl启动实例。命令：SVRMGRLconnect/assysdba;SVRMGRLstartupnomount查看：SVRMGRLselect*fromv$parameter;(v$sga),2、数据库对象定义,OracleDatabase是由一些数据库对象构建而成，这些对象包括实际的存储空间以及抽象的逻辑存储单元。,2、数据存储结构,HardDisk,OracleDatabase,Tablespace,OSFile,OSBlock,UsedExtentFree,DatabaseBlock,SegmentDataIndexClusterRollbackTemporaryCache,PhysicalStructure,LogicalStructure,3、表空间的类型,Oracle表空间有各种不同类型的表空间组成，要建立一个高效的数据库在规划时必须先了解各种表空间的特性，并依据其特性在存储空间的规划上作一妥善的安排，才能减少会降低执行效率的DiskI/O竞争和存储空间的不连贯，才能长期维持数据库效率。,规划要点减少磁盘存储空间部连贯(ReduceFragment)减少产生DiskI/O竞争的机会(ReduceContention)同一表空间所存放的内容应尽量简单化,4、表空间的规划,建造数据库步骤（SID=TEST）设置数据库管理员的操作系统环境变量：ORACLE_HOME,ORACLE_SID,NLS_LANG等编辑参数文件initTEST.ora编辑创建数据库的SQL指令的程序文件create_db_TEST.sql在nomount启动模式下启动数据库实例，然后执行create_db_TEST.sql执行额外的Oracle公用程序catalog.sql,catproc.sql等查看数据库：文件是否正常、能否启动、能否连接、查看相关数据字典,5、数据字典,Oracle的数据字典由一群只读的表(Table)和视图(View)组成，Oracle提供一张名为“dictionary”的表（简称“dict”）来说明这些表的名称和用途。对任一张数据字典的表而言，通常只能让用户读取其中的部分数据，Oracle根据用户类型，将用户感兴趣的部分建成视图，以便查询。视图分类如下：,安全性领域(SecurityDomain)创建用户(CreateUsers)修改用户(AlterUsers)删除用户(DropUsers)查看用户信息(QuaryUserInf.)切断用户联机(KillUserSessions),六、Oracle的用户管理,Authentication(认证)/Identification(识别)：数据库认证和操作系统认证DefaultTablespace：创建用户是设置（不设置则系统指定system）TemoraryTablespace：创建用户是设置（不设置则系统指定system）PrivilegesandRoles：通过grant、revoke授权和取消，自动赋予connectQuotas(使用量)forAccessibleTablespaceSystemResourceLimits：通过Profile限制用户对系统资源的占用,1、安全性领域,针对数据库不同层面的安全考虑，Oracle数据库系统提供6种使用权限的设置，让管理员从不同的安全性领域角度来限制用户，以避免用户不当且与数据或过度使用系统资源。,管理员必须设置每个用户的DefaultTablespace和TemoraryTablespace以避免用户使用“SYSTEM”表空间存放用户数据和临时数据，影响数据库的稳定性和执行效率。,1、数据库认证方式Createusersunnyidentifiedbymypassdefaulttablespaceuserstemporarytablespacetempquotaunlimitedonusersquato150Monbbsprofiledefault;Grantcreatesession,createtabletosunny;用户被授予createsession权限后才可以跟数据库联机。Grantselectonsys.basictosunny;2、操作系统认证方式这种方式让数据库直接辨识操作系统的用户账号，并直接将其转换为数据库用户，信任操作系统的密码辨认。联机时账号和密码直接按回车。A、参数文件中：os_authent_prefix=OPS$（可以为其他符号或没有）B、创建对应数据库用户，名字为OPS$+操作系统用户名，密码部分固定为“externally”以示区别。如：CreateuserOPS$user1identifiedexternally;,2、创建用户,SQLcreateuserusernameidentifiedbypassworddefaulttablespacetablespace_nametemporarytablespacetempquotaunlimitedontablespace_namequota0konsystemquota0konother_tablespace_name;给用户赋权限SQLgrantconnect,resourcetousername;查看当前用户的权限角色SQLselect*fromuser_role_privs;查看当前用户的系统权限和表级权限SQLselect*fromuser_sys_privs;SQLselect*fromuser_tab_privs;,2、创建用户-1,可以改变老用户的密码,缺省表空间,临时表空间,空间限额.,SQLalteruserusernameidentifiedbypassworddefaulttablespacetablespace_nametemporarytablespacetempquotaunlimitedontablespace_namequota0konsystemquota0konother_tablespace_name;撤销用户的角色或权限SQLrevokerole_nameorpriv_namefromusername;注意事项撤消用户的角色dba时,同时撤消了用户unlimitedtablespace的系统权限,切记要再次赋予resource角色给此用户SQLgrantresourcetousername;,3、修改用户,如果用户下没有任何数据对象SQLdropuserusername;如果用户下有数据对象SQLdropuserusernamecascade;注意事项如果用户下有含clob,blob字段的表,应该先删除这些表后,才能用cascade选项完全删除。用户被删除时，其拥有的所有数据库对象以一并被删除。如果想删除用户又想保留其拥有的数据库对象，只要去掉该用户的“createsession”权限，不让其联上数据库就够了。,4、删除用户,查看当前数据库的用户信息SQLselectusername,default_tablespace,temporary_tablespacefromdba_users;查看在线用户信息SQLselectcount(*)currentusernumber,usernamecurrentusernamefromv$sessiongroupbyusername;用户查看自己的缺省表空间SQLselectusername,default_tablespacefromuser_users;,5、查看用户信息,可以使用shutdownimmediate指令强迫所有的用户脱机并关闭数据库。如果只想强迫一两个联机用户脱机呢？Altersystemkillsessionuser_session_id,user_serial_number;查看用户的user_session_id,user_serial_numberselectsid,serial#,usernamefromv$session;切断用户联机后，用户正在进行的交易会立即中断，数据回滚，并解除用户对所有table的锁定，用户所占用的系统资源也会全部释放出来。,6、切断用户联机,系统特权(SystemPrivilege)对象特权(ObjectPrivilege)角色(Role)角色管理(RoleManagement)Oracle预定义的角色权限分类和视图,七、数据库权限管理,授权用户建立自己的对象：createtable,cluster,sequence等授权用户可对某一类型所有对象进行操作：createanyindex,procedure授权用户对数据库系统操作：createsession,tablespace;altersystem等,1、系统特权(SystemPrivilege),Oracle数据库系统共约有90种不同的系统特权（数目随版本而异），大致可以分为3类：,数据字典dba_sys_privs视图详细记录了每个用户和角色被授予的系统特权。授予和取消用户（或角色）系统特权的命令如下：GRANTsystem_privileges|rolesTOusers|roles|PUBLICWITHADMINOPTION;REVOKEsystem_privileges|rolesFROMusers|roles|PUBLIC;其中PUBLIC这个保留字用于泛指所有用户，尾部加上WITHADMINOPTION表示用户不仅获此权限，和可以将此权限授予他人。,2、对象特权(ObjectPrivilege),数据字典dba_tab_privs视图详细记录了每个用户和角色被授予的对象特权。授予和取消用户（或角色）对象特权的命令如下：GRANTobject_privileges|ALL(columns)ONschema.objectTOusers|roles|PUBLICWITHGRANTOPTION;REVOKEsystem_privilegesONschema.objectFROMusers|roles|PUBLICCASCADECONSTRAINTS;其中ALL是泛指所有对象特权的简写，尾部加上WITHGRANTOPTION表示用户不仅获此权限，和可以将此权限授予他人。,Oracle数据库的对象特权为对Table、View、Sequence、Snapshot、StoreProcedure、Trigger等数据对象执行select、update、insert、alter、delete、execute、index、reference等8种SQL指令权限，当然不同的对象可搭配的可执行的SQL指令不尽相同。,在oracle数据库系统中，通过定义一些角色（role），以间接授权的方式来简化权限的授予。,3、角色(Role),角色不是一种数据库对象，所以也不属于任何一个数据库用户；角色的定义存储在数据库的数据字典中；角色的定义内容可同时包含许多系统特权和对象特权；角色授权给用户时，该用户就获得此角色定义的所有权限；惧色权限可以授予用户，也可以授予另一个角色；用户可打开或关闭已获得授权的角色权限；当用户打开一角色权限时，可要求输入该角色所设置的密码。,必须拥有“CREATEROLE”系统权限的数据库用户，才能执行createrole指令建立一个新的角色。角色建立后，管理员就可以使用GRANT指令给该角色授权。Createrolesalesnotidentified;Createrolemanageridentifiedby5325180;Alterrolemanagernotidentified;Droprolemanager;,4、角色管理(RoleManagement),4、角色管理-1,常用的几个角色role(权限的集合),connect连上Oracle,做最基本操作resource具有程序开发最基本的权限dba数据库管理员所有权限exp-full-database可把数据库整个备份输出的权限imp-full-datsabase可把数据库整个备份恢复输入的权限,5、Oracle预定义的角色,操作系统角色,OSDBA、SYSDBA所有系统特权及其管理权OSOPER、SYSOPER除了执行createdatabase以及time-basedrecovery以外的所有系统特权。,权限的分类,systemprivilege针对整个系统操作的权限objectprivilege针对整个具体object操作的权限,可查看,Dba_sys_privs,Dba_tab_privs;视图Dba_col_privs;,6、权限分类和视图,八、Oracle存储空间的管理,表空间的管理回滚段的管理表段的管理索引段的管理,创建表空间：Createtablespaceusersdatafile/vol2/users.datsize500m;修改表空间：Altertablespaceusersadddatafile/vol2/users1.datsize500m;Altertablespaceusersonline;Altertablespaceusersoffline;Altertablespaceusersreadwrite;Altertablespaceusersreadonly;Selecttablespace_name,statusfromdbatablespace;-查询状态,1、表空间的管理,删除表空间：Altertablespaceusersoffline;-先离线Droptablespaceusersincludingcontents;-再删除本指令只删除数据字典中的文件指针，其所属的数据文件必须另行在操作系统下执行删除指令（rm）来清除，以释放该表空间所占用的磁盘空间。rmusers.dat,1、表空间的管理-1,回滚段（rollbacksegment）是数据库系统用来存放交易进行中，数据被修改前内容的一种数据对象，数据库系统使用数据来进行交易回滚、维持读取一致性以及数据库数据恢复等操作。参数文件中：rollback_segments=(rbs01,rbs02,rbs03,rbs04)创建回滚段：Createrollbacksegmentrbs01tablespacerbsstorage(initial1Mnext1Mmaxextents200optimal2M;查询回滚段：Selectsegment_name,tablespace_name,statusfromdba_rollback_segs;,2、回滚段的管理,修改回滚段：联机：alterrollbacksegmentrbs01online;脱机：alterrollbacksegmentrbs01offline;回收空间（必须联机状态）：Alterrollbacksegmentrbs01shrinkto2M;删除回滚段（必须脱机状态）：Alterrollbacksegmentrbs01offline;Droprollbacksegmentrbs01;,2、回滚段的管理-1,大交易（Long-Transaction）：如果要在一笔交易中更改非常大量的数据，这种交易称为Long-Transaction。建立一个大型回滚段（命名为rbs99）Createtablespacerollhugedatafile/vol1/rollback.dbfsize500M;Createrollbacksegmentrbs99tablespacerollhugestorage(initial50Mnext50Mminextents3optimal150M;交易前先联机（alterrollbacksegmentrbs99online;）指定用它作为回滚表空间（settransactionuserollbacksegmentrbs99;）交易完成后再脱机（alterrollbacksegmentrbs99offline;）。,2、回滚段的管理-2,表段（Tablesegment，简称“表”）是数据库系统用来存放关联式表结构数据的一种数据对象，用以存储用户的应用系统数据和系统本身的数据字典内容。表中每一笔数据被称为一个数据行，只要空间足够，数据行数并无限制。创建表段：Createtableexternal(external_ipvarchar2(15)notnull,domain_namevarchar2(50),group_codevarchar2(50),descriptionvarchar2(50)Pctfree10pctuse40tablespaceusersunrecoverablestorage(initial1Mnext512Kminextent5pctincrease0);,3、表段的管理,3、表段的管理-1,修改表段：altertable指令可用于修改原有的存储空间参数值，或手动方式给表配置一个新的extent。如果表空间有两个数据文件可以指定数据文件或由数据库系统自行决定。Altertablemytalbeallocateextent(size200kdatafile/vol2/mydata_dat);删除表段：Droptablesong.mytable;-无外键关联Droptablemytablecascadeconstraints;-有外键关联删除表以后数据库系统完成删除所有的数据行、数据字典中的定义、此表的索引和触发器，但对此表建立的视图和同义词定义仍然存在，待表重建后仍然可以使用。,索引段（Indexsegment，简称“索引”）是根据Table的列值建立的索引表，其所根据的列值可以是单一列也可以是复合列。创建索引段：Createindexmydata_person_idonmytable(person_id)storage(initial1Mnext512Kminextent5pctincrease0)tablespaceuser_idx;建议索引名称使用惯例：tablename_fieldname，不可对数据类型为long和longraw的表列建立索引。建立索引时可以设置UNIQUE(createuniqueindex)，保证索引没有重复的值。,4、索引段的管理,4、索引段的管理-1,修改索引段：alterindex指令除了可用于修改原有的存储空间参数值外，还可以：Alterindexmytalbe_iddeallocateunused;-回收Alterindexmytalbe_idrebuildtablespaceuser_idx;-重建删除表段：Dropindexsong.mytable_id;删除索引时，如索引列建有“UNIQUE”或其他数据库完整性限制，则数据库可能不允许删除这个索引段，除非你把所有的数据删掉。,备份与恢复的基本概念备份分类,四、Oracle数据的备份和恢复,数据错误的原因：指令执行中发生错误、用户程序执行时崩溃、OracleInstance执行中崩溃、用户操作错误、存储数据毁损等。数据备份的形式：主要为实体数据备份和逻辑数据备份两种形式。归档日志执行模式：数据库执行有归档和不归档两种模式，对于不能停机的数据库，一般采用归档模式。,1、备份与恢复的基本概念,(1)逻辑备份用Export/Import实用工具(2)物理备份操作系统下的备份,归档方式不关闭database状态不归档方式关闭database后，把物理文件进行备份(cptar),逻辑备份仅能恢复到上次的备份点物理备份中的不归档备份可恢复到上次备份点物理备份中的归档备份恢复到failure的前一刻,2、备份与恢复的策略,脱机执行数据库整体备份将数据库正常关闭以后，将数据库执行时所需要的所有文件（包括数据文件、日志文件和控制文件）完整拷贝一份至其他存储媒体上。注意不可漏掉任何一个文件，可以通过指令查询数据字典(v$datafile、v$logfile和v$controlfile)确认有哪些相关文件。联机执行数据库备份要求归档模式，以表空间为单位逐个进行，对数据文件进行备份。Altertablespaceusersbeginbackup;-进入