ORACLE_的理论知识

ORACLE的理论知识余枫2001.122一、Oracle数据库体系结构1、物理结构datafilesredologfilescontrolfilesparameterfile数据文件日志文件控制文件参数文件*.dbf相关数据字典dba_data_files*.log相关数据字典v$logfilesInitoraid.ora*.ctl相关数据字典v$controlfile3本图为ORACLE数据库扩充前后在硬盘上存储结构的示意图：ORACLE数据库在物理上是存储于硬盘的各种文件。它是活动的，可扩充的，随着数据的添加和应用程序的增大而变化。4查看当前数据库的物理文件SQLcolumntablespace_nameformatA16;SQLcolumnfile_nameformatA46;SQLselectfile_name,round(bytes/(1024*1024),0)total_space,autoextensible,increment_by*4096/(1024*1024),maxbytes/(1024*1024)fromdba_data_filesorderbytablespace_name;52、逻辑结构ORACLE数据库在逻辑上是由许多表空间构成。主要分为系统表空间和非系统表空间。系统表空间存数据字典,非系统表空间内存储着各项应用的数据、索引、程序等相关信息。6常见的表空间名称表空间tablespacesystemrbsnosystemtoolsusersoem_repositorytempapplicationdataapplicationindex分开存放减少争用存放回滚段存放数据字典信息,不要放用户的数据用在排序和集合运算等7查看当前数据库表空间的情况SQLselecttablespace_name,max_extents,pct_increase,statusfromdba_tablespaces;创建新表空间的命令SQLcreatetablespacetablespace_namedatafile'/mountdirectory/tablespace_datafile_name.dbf'sizennnMdefaultstorage(initial128knext128kminextents1maxextentsunlimitedpctincrease1);8查看当前数据库表空间使用情况SQLselect*from(selectsum(bytes)/(1024*1024)asfree_space(M),tablespace_namefromdba_free_spacegroupbytablespace_name)orderbyfree_space(M);93、ORACLE的进程说明数据库正常运行时可能出现的进程名#ps–aef|greporacleappdb是数据库sid/opt/oracle/product/8.1.6/bin/tnslsnrlistener–inheritora_pmon_appdbora_dbw0_appdbora_lgwr_appdbora_ckpt_appdbora_smon_appdbora_reco_appdbora_snp0_appdbora_s000_appdbora_d000_appdb10ORACLE的进程的详细说明tnslsnrlistener*ORACLE网络监听进程,处理客户端的连接请求PMON做程序的清洁工作，处理一些不正常退出的事件．SMON做系统的清洁工作，执行系统出错后自动恢复工作．LCKNOracle系统表级或行级加锁的进程.RECO恢复进程．DBWRN写数据文件的进程LGWRN写日志文件的进程CKPT检测点ARCH归档方式备份进程SNAPN管理快照复制等的进程SNNNMTSServer进程DNNNMTSDispatcher进程11Listener监听进程常用命令$listenerstatus显示ORACLE网络监听进程的状态$listenerservices显示当前详细的网络监听进程的服务处理状态$lsnrctlreload重新启动$lsnrctlstop关闭ORACLE网络监听进程$lsnrctlstart启动ORACLE网络监听进程124、ORACLE的内存结构(SGA)数据库启动时内存分配的情况ORACLE8.1.X版本SGA=((db_block_buffers*blocksize)+(shared_pool_size+large_pool_size+java_pool_size+log_buffers)+1MB理论上SGA可占OS系统物理内存的1/2——1/3，我们可以根据需求调整,我推荐SGA=0.4*(OSRAM)shared_pooldatabase_buffer_cacheredo_logbuffer134、Instance和Transaction是什么Instance=SGA+backgroundprocess实例=内存分配+一组后台进程启动Transaction交易事务(一组修改动作的集合)Eg:1、insertDDL(数据定义语句)delete例如：create,alter,drop,conmit等commit每两个DDL语句间是一个transaction2、updateDML(数据控制语句)rollback例如：Insert,Delete,Update145、ORACLE的整体体系结构PMONLCKnRECORECOsharedPoolDatabaseBufferCacheRedoLogBufferSGADataDataDataDataDataDataDataDataDataDataDatafilesRedoLogfilesParameterFileControlfilesServerUserDBWRLGWRCKPTARCHOfflinestorage156、分析一个SQL语句是怎样在Orcle内部工作的A、用户发出SQL请求，打开游标B、对SQL语句进行语法分析，找到执行计划，数据字典等信息存入SGA中共享池内C、从数据文件中把相关数据块读入数据缓冲区D、做相应操作，若做修改，先加上行级锁，经确认后把改过前后记录内容存入重做日志缓冲区内E、返回结果给用户，关闭游标备注：SQL语句大小写敏感的，同样的一个语句，若大小写不同，ORACLE需分析执行两次，每句后必以“；”结束16二、启动和关闭数据库1、命令$svrmgrl唤醒SVRMGRL数据库管理SVRMGRLconnectinternal;以系统管理员身份登录。SVRMGRLstartup启动instance连上数据库打开数据库$svrmgrlSVRMGRLconnectinternal;SVRMGRLshutdown关闭数据库时有几个参数shutdownimmediate;回退所有Transaction,关闭DBaltersystemswitchlogfile;做日志文件切换,把内存中修改过的数据块存进物理文件shutdownabort;强行关闭DB172、启动和关闭Oracle数据库过程图shutdownopen开机关机读控制文件未连上数据库仅启动instancesnomountmount连上数据库，但未打开读参数文件18三、管理数据库的用户1、查看当前数据库的用户信息SQLselectusername,default_tablespace,temporary_tablespacefromdba_users;查看在线用户信息SQLselectcount(*)currentusernumber,usernamecurrentusernamefromv$sessiongroupbyusername;用户查看自己的缺省表空间SQLselectusername,default_tablespacefromuser_users;192、创建新用户SQLcreateuserusernameidentifiedbypassworddefaulttablespacetablespace_nametemporarytablespacetempquotaunlimitedontablespace_namequota0konsystem[quota0konother_tablespace_name……];给用户赋权限SQLgrantconnect,resourcetousername;查看当前用户的权限角色SQLselect*fromuser_role_privs;查看当前用户的系统权限和表级权限SQLselect*fromuser_sys_privs;SQLselect*fromuser_tab_privs;203、常用的角色及其权限CONNECT8privs连上Oracle,做最基本操作RESOURCE8privs具有程序开发最的权限DBA114privs数据库管理员所有权限EXP_FULL_DATABASE5privs数据库整个备份输出的权限IMP_FULL_DATABASE64privs数据库整个备份输入的权限查看角色明细的系统权限SQLselect*fromrole_sys_privs;214、改变老用户可以改变老用户的密码,缺省表空间,临时表空间,空间限额.SQLalteruserusernameidentifiedbypassworddefaulttablespacetablespace_nametemporarytablespacetempquotaunlimitedontablespace_namequota0konsystem[quota0konother_tablespace_name……];撤销用户的角色或权限SQLrevokerole_nameorpriv_namefromusername;注意事项撤消用户的角色dba时,同时撤消了用户unlimitedtablespace的系统权限,切记要再次赋予resource角色给此用户SQLgrantresourcetousername;225、删除老用户如果用户下没有任何数据对象SQLdropuserusername;如果用户下有数据对象SQLdropuserusernamecascade;注意事项如果用户下有含clob,blob字段的表,应该先删除这些表后,才能用cascade选项完全删除.23四、数据库的存储结构1、Oracle数据存储单位A、Block数据块：2,4,8,16K最小的I-O单位伴随Database产生而产生，不可变B、Extent一组连续的数据块：是用户所能分配存储的最小单位C、Segment段：有共同结构的一个或几个区域(Extent)数据对象以段的形式存在(Table,Index,等)D、Tablespace表空间：一组物理数据的逻辑组合，(象逻辑的数据仓库)E、File文件：属于某个表空间的物理文件F、Database数据库：一组表空间所构成的逻辑的可共享的数据。242、数据块(block)的结构253、数据块的存储参数Table的默认值Pctfree10Pctfree,Pctused是互相消涨的,其和不能超过100Pctused40Inittrans1在单一块中最初活动的交易事务数Maxtrans255在单一块中最大交易事务数常见的几种分配方案大量Update操作Pcrfree20Pctused40大量Insert,Select,少量UpdatePctfree5Pctused60体积大的表,Select较多Pctfree5Pctused90264、表空间、表、索引、分区、快照、快照日志的存储参数默认值最小值Initial5个数据块2个数据块Next5个数据块1个数据块Minextents11Maxextents根据数据块大小而定1Pctincrease500推荐使用的参数规则,使用大小一致的,增长率较低存储分配initial=