Oracle基础知识培训

ORACLE基础知识介绍新大陆软件2011年8月目录ORACLE体系结构ORACLE存储结构ORACLE的启动与关闭监听器配置ORACLE共享服务器用户管理用户权限管理表空间管理SQL执行与优化OracleInstanceManagementSystemMonitorSMONDatabaseWriterDBW0LogWriterLGWRProcessMonitorPMONArchiverARC0SGAJavaPoolSharedPoolLargePoolStreamsPoolDatabaseBufferCacheRedoLogBufferCheckpointCKPT内存区域后台进程数据结构客户连接ORACLE体系结构—数据结构数据文件(Datafiles)包含用户数据控制文件(Controlfiles)包含数据库自身的数据，是数据库正常运行必不可少的文件联机重做日志文件(Onlineredologfiles)包含数据库中的事务日志，用户在数据库崩溃时恢复用户数据参数文件用于控制数据启动时的参数配置口令文件用于验证远程用户,连接到数据库并进行管理归档重做日志文件(Onlineredologfiles)联机日志的备份。用于数据库恢复ORACLE体系结构—内存结构（1）SGA(SystemGlobalArea)•在系统启动时分配的一段内存，是ORACLE实例的基本组件•被所有的服务器进程和后台进程共享•包含以下组件-BufferCache-RedoLogBuffer-SharedPool-LargePool-JavaPool-StreamsPoolPGA(ProgramGlobalArea)•每个服务器进程和后台进程独享•包含用户执行的SQL的信息•包含用户SESSION的信息ORACLE体系结构—后台进程DatabaseWriter(DBWR)把内存中修改过的数据块写入到磁盘上的数据文件中Checkpoint(CKPT)在产生检查点时触发DBWR工作更新数据文件和控制文件中的检查点信息ORACLE存储结构ORACLE存储结构-表空间管理（1）创建表空间：CREATETABLESPACEtablespace_nameDATAFILEfile_nameSIZEnEXTENTMANAGEMENTLOCALUNIFORMSIZE256K删除表空间：DROPTABLESPACEtablespace_nameINCLUDINGCONTENTSANDDATAFILES修改表空间–新增数据文件ALTERTABLESPACEtablespace_nameADDDATAFILEfile_nameSIZEnM修改表空间–删除数据文件ALTERTABLESPACEtablespace_nameDROPDATAFILEfile_nameORACLE存储结构-表空间管理（2）脱机表空间：ALTERTABLESPACEtablespace_nameOFFLINE联机表空间：ALTERTABLESPACEtablespace_nameONLINE把表空间设置为只读：ALTERTABLESPACEtablespace_nameREADONLY把表空间设置为读写：ALTERTABLESPACEtablespace_nameREADWRITEORACLE存储结构-表空间管理（4）获取表空间信息–DBA_TABLESPACES–V$TABLESPACE获取数据文件信息–DBA_DATA_FILES–V$DATAFILE获取临时文件信息–DBA_TEMP_FILES–V$TEMPFILEORACLE的启动与关闭ORACLE的启动与关闭（2）启动STARTUPSTARTUPNOMOUNTSTARTUPMOUNTSTRATUPFORCESTARTUPRESTRICT关闭SHUTDOWNNORMALSHUTDOWNIMMEDIATESHUTDOWMTRANSACTIONALSHUTDOWNABORTORACLE监听器配置ORACLE监听器配置（1）监听器配置文件SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=PLSExtProc)(ORACLE_HOME=D:\Tools\Dev\Ora10g)(PROGRAM=extproc)))LISTENER=(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.32.112.28)(PORT=1521))(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0))))ORACLE监听器配置（2）启动监听器LSNRCTLSTART停止监听器LSNRCTLSTOP查看监听器状态LSNRCTLSTATUS查看注册到监听器的服务LSNRCTLSERVICEORACLE用户管理用户管理用户模式管理用户角色管理用户权限管理用户管理创建用户：CREATEUSERusernmaeIDENTIFIEDBYpwsswordDEFAULTTABLESPACEtablesspace_nameTEMPORARYTABLESPACETEMPQUOTAnMONtablespace_namePASSWORDEXPIRE修改用户密码：ALTERUSERusernameIDENTIFIEDBYpwssword删除用户：DROPUSERusernameCASCADE用户模式管理用户模式：用户模式管理（2）对象SQL语句需要的权限表CREATETABLE…CREATETABLE索引CREATEINDEX…CREATEINDEX视图CREATEORREPLACEVIEW…CREATEVIEW序列CREATESEQUENCE…CREATESEQUENCE同义词CREATESYNONYM…CREATESYNONYM数据库链CREATEDATABASELINK…CREATEDATABASELINK用户可以创建如下的对象用户模式管理（3）创建表CREATETABLEtable_name（col_namecol_type,…)TABLESPACEtablespace_name创建索引CREATEINDEXindex_nameONtable_name(col_name)TABLESPACEtablespace_name创建视图CREATEORREPLACEVIEWview_nameASSELECTcol_nameFROMtable_name用户模式管理（4）创建序列CREATESEQUENCEsequence_nameMINVALUEnMAXVALUEmSTARTWITHnINCREMENTBYnCACHEn创建同义词CREATESYNONYMsynonym_nameFORtable_name创建数据库链CREATEDATABASELINKdblink_nameCONNECTTOusernameIDENTIFIEDBYpasswordUSING‘tns_name’用户角色管理用户角色管理用户角色管理（2）创建角色CREATEROLErole_nameIDENTIFIEDBYpasswordGRANTprivilege_nameTOrole_name给角色授权把角色授给用户GRANTrole_nametousername收回角色REVOKErole_nameFROMusername用户角色管理（3）启用默认角色ALTERUSERusernameDEFAULTROLErole_nameALTERUSERusernameDEFAULTROLEALL;ALTERUSERusernameDEFAULTROLEALLEXCEPTrole_nameALTERUSERusernameDEFAULTROLENONE启用角色SETROLErole_nameSETROLErole_nameIDENTIFIEDBYpwsswordSETROLEALLEXCEPTrole_name用户角色管理（4）查询角色信息用户角色管理（5）系统预定义角色用户权限管理系统权限系统权限允许用户执行特定的数据库操作。比如创建表空间就是一个系统级的权限对象权限对象权限允许用户在某个对象上执行特定的操作，比如查看某张表的权限，查看某个视图的权限等等。用户权限管理（2）系统权限用户权限管理（3）授权系统权限GRANTCREATETABLESPACETOusername[WITHADMINOPTION]GRANTCREATE[ANY]TABLETOusername[WITHADMINOPTION]GRANTCREATEANYINDEXTOusername[WITHADMINOPTION]GRANTCREATESESSIONTOusername[WITHADMINOPTION]回收系统权限REVOKECREATETABLESPACEFROMusernameREVOKECREATE[ANY]TABLEFROMusernameREVOKECREATEANYINDEXFROMusernameREVOKECREATESESSIONFROMusername用户权限管理（4）对象权限用户权限管理（5）表权限GRANTSELECT,INSERT,DELETE,UPDATEONtable_nameTOuser_name[WITHGRANTOPTION]GRANTALLONtable_nameTOuser_name[WITHGRANTOPTION]GRANTSELECT,INSERT,DELETE,UPDATEONview_nameTOuser_name[WITHGRANTOPTION]GRANTALLONview_nameTOuser_name[WITHGRANTOPTION]GRANTSELECTONsequence_nameTOusername[WITHGRANTOPTION]GRANTEXECUTEONprocedure_nameTOusername[WITHGRANTOPTION]视图权限序列权限过程权限SQL语句分析阶段1.解析阶段此时数据库对提交的SQL语句进行语法和语义上分析，检查是否存在语法错误和语义上的不明确，语法错误比如错把关键字”from‘写成‘fron‘，此时解析器就会给出错误提示，语义错误比如选择的一个不存在的列，此时解析器也会给出相应的错误提示。2,优化器根据统计信息选择合适的执行计划并进行变量绑定,解释阶段执行阶段3、按照执行计划执行SQL语句，返回执行结果SQL语句执行流程一次解析，多次执行•是指对于语义上同样的SQL语句，我们要做到解析一次，多次执行，以降低SQL的执行时间和资源消耗。•当我们需要执行一条SQL语句时，解析器会对SQL语句进行分析，并在共享池中查找是否存在同样的语句，如果该语句之前我们已经执行过了一次了，并且在共享池找找到该语句的信息，那么Oracle就会直接从共享池获取该语句的执行计划，然后执行，跳过了产生执行计划的阶段，大大提高的SQL的执行时间，同时也降低了系统的资源占用•那么Oracle是怎么判断两个语句是否一致呢，其实很简单，Oracle只是从字面去判断两条语句是否一致，并且区分大小写，所以：•Select*froma和Select*fromA不是同一条语句，他们不能互相共享执行计划什么是一次解析，多次执行？使用绑定变量使用直接量语句Select*fromempwherea=?Seta=1Seta=2Select*fromempwherea=1;Select*fromempwherea=2;解析次数12执行次数22绑定变量的作用考虑下面的情况：假设我们前台有个功能，允许客户输入自己的编码，查询自己的一些信息，在我们的代码里面，我们使用如下的代码：String