Oracle中表空间SGA相关.doc

Oracle中表空间,SGA相关目录Oracle中表空间(tablespace)1创建表空间:2删除表空间及文件:2如何将表，索引移动到表空间?2查询表空间信息?2如何查看各个表空间使用情况？2如何监控表空间的I/O 比例？3如何知道表在表空间中的存储情况?3如何知道索引在表空间中的存储情况?3表空间不够的错误处理4事务要求的回滚段空间不够4Oracle 系统全局区SGA5数据库的键取用问题6Oracle中表空间(tablespace) 表空间是Oracle内部定义的一个概念,是为了统一Oracle物理和逻辑上的结构而专门建立的,从物理上来说,一个表空间是由具体的一个或多个磁盘上数据文件构成的(至少1对1,可以1对多),从逻辑上来说一个表空间是由具体的一个或多个用户模式下的表,索引等等里面的数据所构成的。 因此从用户的角度来看是不应该看到磁盘上物理的数据文件的,其从小到大的视角是这样的关系: 字段值 - 记录值 - 表数据 - 用户 - 表空间 - Oracle 而从一个Oracle DBA的视角来看应是这样的关系: 数据文件 - 表空间 - Oracle 综合一下,也就是说在Oracle中表不可能单独存在,一定隶属于某一个用户,而某一用户的数据必定存在于某个表空间中。 当然,上面我是用最通俗的语言介绍的。实际上来说中间还存在很多技术术语和概念,比如说SEGMENTS,EXTENTS,BLOCK等等。 Oracle表空间，底层以data_file文件形式存放，可以定义一个数据库能打开多少个数据文件，一个表空间可以有1到多个数据文件，一个数据文件只能隶属于一个表空间。建表时：1. 指定表空间；2. 指定扩展大小3. 普通表只能建在一个表空间，分区表可以建在多个表空间上。创建表空间:SQLcreate tablespace histdb datafile D:oracleproduct10.2.0oradataorclhistdb.dbf size 200m autoextend on next 10m maxsize unlimited;SQLalter database datafile D:oracleproduct10.2.0oradataorclhistdb.dbf autoextend on;l DATAFILE: 表空间数据文件存放路径。l SIZE: 起初设置为200M。l UNIFORM: 指定区尺寸为128k,如不指定，区尺寸默认为64k。l 空间名称histdb 与 数据文件名称 histdb.dbf 不要求相同,可随意命名。l AUTOEXTEND ON/OFF 表示启动/停止自动扩展表空间。l alter database datafile D:oracleproduct10.2.0oradataorclhistdb.dbf resize 500m; /手动修改数据文件大小为500M。删除表空间及文件:delete只能删除数据，还占着表空间，用truncate命令，但truncate没有条件。用DROP可删除表空间及数据文件。DROP TABLESPACE histdb INCLUDING CONTENTS AND DATAFILES;如何将表，索引移动到表空间?ALTER TABLE TABLE_NAME MOVE TABLESPACE_NAME;如何将索引移动表空间?ALTER INDEX INDEX_NAME REBUILD TABLESPACE TABLESPACE_NAME;查询表空间信息?SELECT * FROM DBA_DATA_FILES;如何查看各个表空间使用情况？方法一：select dbf.tablespace_name,dbf.totalspace 总量(M),dbf.totalblocks as 总块数,dfs.freespace 剩余总量(M),dfs.freeblocks 剩余块数,(dfs.freespace / dbf.totalspace) * 100 空闲比例 from (select t.tablespace_name,sum(t.bytes) / 1024 / 1024 totalspace,sum(t.blocks) totalblocksfrom dba_data_files tgroup by t.tablespace_name) dbf,(select tt.tablespace_name,sum(tt.bytes) / 1024 / 1024 freespace,sum(tt.blocks) freeblocksfrom dba_free_space ttgroup by tt.tablespace_name) dfswhere trim(dbf.tablespace_name) = trim(dfs.tablespace_name)如何监控表空间的I/O 比例？select B.tablespace_name name,B.file_name file,A.phyrds pyr,A.phyblkrd pbr,A.phywrts pyw, A.phyblkwrt pbwfrom v$filestat A, dba_data_files Bwhere A.file# = B.file_idorder by B.tablespace_name;如何知道表在表空间中的存储情况?select segment_name,sum(bytes),count(*) ext_quanfrom dba_extentswhere tablespace_name=&tablespace_name and segment_type=TABLEgroup by tablespace_name,segment_name;如何知道索引在表空间中的存储情况?select segment_name,count(*)from dba_extents where segment_type=INDEX and owner=&ownergroup by segment_name;表空间不够的错误处理当发现有的表空间不够的错误时，处理如下：1). 找出该表空间对应的数据文件及路径select * from dba_data_files twhere t.tablespace_name = ARD2). 增大数据文件alter database datafile 全路径的数据文件名称 resize *M3). 增加数据文件alter tablespace 表空间名称add datafile 全路径的数据文件名称 *M注解：表空间尽量让free百分比保持在10%以上，如果低于10%就增加datafile或者resizedatafile，一般数据文件不要超过2G事务要求的回滚段空间不够事务要求的回滚段空间不够，表现为表空间用满（ORA-01560错误），回滚段扩展到达参数MAXEXTENTS的值（ORA-01628）的解决办法.向回滚段表空间添加文件或使已有的文件变大；增加MAXEXTENTS的值。数据字典表空间信息：数据字典描述DBA_TABLESPACES所有表空间的描述.V$TABLESPACE控制文件中表空间的信息V$TEMP_EXTENT_MAP显示所有临时表空间的每一个单元的状态信息.V$TEMP_SPACE_HEADER显示每一个临时表空间中每一个文件的聚集信息,涉及每个空间首部中当前使用多少空间和有多少自由空间.USER_TABLESPACES可存取的表空间的描述.USER_TS_QUOTAS用户的表空间限额.DBA_FREE_SPACE列出所有表空间中的空闲分区.DBA_FREE_SPACE_COALESCED包含表空间中合并空间的统计数据DBA_TS_QUOTAS所有用户的表空间的限额.DBA_UNDO_EXTENTS在撤消表空间的每个范围的提交时间.USER_FREE_SPACE用户可存取表空间中的空闲范围.TS_PITR_CHECK提供可能禁止表空间及时点恢复继续的依赖或约束信息TS_PITR_OBJECTS_TO_BE_DROPPED列出作为执行表空间及时点恢复的结果而丢失的所有对象.Oracle 系统全局区SGAOracle系统全局区SGA是Oracle 系统用于存放系统信息的一块存储区域，用户进程和Oracle后台进程都可以使用SGA.在SGA中含有许多组件（不同的部分）。 数据高速缓冲区（Data Buffer Cache） 在数据高速缓冲区中存放着Oracle系统最近使用过的数据块（即用户的高速缓冲区），当把数据写入数据库时，它以数据块为单位进行读写，当数据高速缓冲区填满时，则系统自动去掉一些不常被用访问的数据。如果用户要查的数据不在数据高速缓冲区时，Oracle自动从磁盘中去读取。数据高速缓冲区包括三个类型的区： 1）脏的区（Dirty Buffers）：包含有已经改变过并需要写回数据文件的数据块。 2）自由区（Free Buffers）：没有包含任何数据并可以再写入的区，Oracle可以从数据文件读数据块该区。 3）保留区（Pinned Buffers）：此区包含有正在处理的或者明确保留用作将来用的区。数据库的键取用问题主键 (PRIMARY KEY) 唯一标识表中的所有行的一个列或一组列。主键不允许空值。不能存在具有相同的主键值的两个行，因此主键值总是唯一标识单个行。表中可以有不止一个键唯一标 识行，每个键都称作候选键。只有一个候选键可以选作表的主键，所有其它候选键称作备用键。尽管表不要求具有主键，但定义主键是很好的做法。 在规范化的表中，每行中的所有数据值都完全依赖于主键。 主键的更新问题主键是可以被更新的，但在应用中注注意：关于业务主键和逻辑主键的取舍 关于这个问题网上已经有很多的讨论，现在综合这些讨论在加上自己众多建模及数据仓库工作中的经验给出以下分析及取舍建议，供各位同行参考：一、业务的东西，是每一个做软件的最薄弱的，并且是最有可能受到客户影响的，也是最会引起问题的。 比如身份证，如果有系统的表用此做主键，其他众多表以此为外键，当身份证从15位升到18位时，整个数系统的重构将是一个非常困难的工作。一个系统在维护的成本远大于开发的成本，所以要充分考虑客户业务变更的需求,用户今天说不会变，而明天可能就变了,即使用户已经对需求确认签字。这些都是不可预知的。二、业务主键在存在主从关系时候，更新时不方便(这样你必须要检查从表，再处理主表)。三、业务主键是复合型时，CRUD操作时不方便(比如要定位一条记录时必须传入复合的每个字段,四、使用业务主键，基于源数据的质量问题，往往存在业务主键重复,对于源数据可控及数据量小的操作,业务主键重复还容易控制，而对于某些高度耦合的系统来说，后果是不堪设想的。五、数据仓库的表中的冗余字段不是很少而是大量的,增加逻辑主键并不是冗余的根源。建议设计的基本原则（具体情况可能要具体分析）：一、对于业务数据，最好采用逻辑主键;二、对于业务复合主键有多个字段(3?)，需要采用逻辑主键;三、对于基础数据，基于多方面考虑，是可以采用业务主键的。这类表初始化以后数据不会经常发生改变。四、取消业务主键后，在查询经常会用到的相关的业务字段建立INDEX，可以提高查询效率;五、使用逻辑主键,表的业务数据唯一性由程序来检查控制，使业务数据重复这类脏数据控制在业务允许的范围;六、业务数据的重复这类脏数据也可以通过分析结果数据得到;七、业务