Oracle存储空间管理及应用方案

1、Oracle存储空间管理及应用摘要：本文详细介绍了Oracle存储架构及其存储体系的管理和应用，包括表空间、回滚段、临时表等，还对其在管理和应用时常见的错误进行了分析探讨主题词：Oracle技术 表空间 数据文件 回滚段 临时表 错误引言数据库空间的有效使用和维护不仅是数据库管理的重要工作，也是大多数开发人员所关心的内容，它直接关系到数据库性能的发挥。Oracle提供了不少方法用于数据空间的使用、监控和维护，同时也在各版本中陆续对这方面的功能进行了增强，目的在于简化这方面工作的复杂度，提高应用的运行效率。本文希望通过系统地介绍这方面的有关概念，让大家能更好地规划使用数据空间，正确使用Oracl

2、e提供的有关功能特性，提高应用的执行效率。Oracle数据库的存储体系及有关概念2.1 Oracle数据库的逻辑结构从应用者的角度来考察数据库的组成。自下向上，数据库的逻辑结构共有6层：2.2 Oracle数据库的存储结构数据库的存储结构指逻辑结构在物理上的实现，共有3层 其中：数据文件：用于存放所有的数据，以DBF为扩展名。日志文件：记录了对数据库进行的所有操作，以LOG为扩展名。控制文件：记录了数据库所有文件的控制信息，以CTL为扩展名。综上，Oracle数据库的数据存储空间在逻辑上分为多个表空间，每个表空间由系统中的一个或多个物理数据文件构成；Oracle存储数据的基本单位是块，其大小在

3、建库时由DB_BLOCK_SIZE参数确定，一个或多个连续的块构成一个区间（EXTENT），它作为数据对象存储的基本单位来使用。在Oracle中，每个基本数据对象使用的空间称为段（SEGMENT），段存放在唯一的表空间上，每个段实际上是一系列区片（更为准确地是数据块）的集合。每个简单数据对象对应一个段；对于分区对象如分区表、索引，则每个（子）分区对应一个段，由各个（子）分区共同构成一个完整的数据对象。名词解释：表空间（Tablespace）：为数据库提供使用空间的逻辑结构，其对应物理结构是数据文件，一个表空间可以包含多个数据文件。表空间可分为System表空间、非System表空间和回滚段表空

4、间，其中，System表空间是安装数据库时自动建立的，它包含数据库的全部数据字典，存储过程、包、函数和触发器的定义以及系统回滚段。除此之外，还能包含用户数据。段（Segment）:数据库一种逻辑结构，如表段，索引段，回滚段等，段存在于表空间中，并对应一定的存储空间，共有四种类型的段：表/数据（Table/Data）：保存表数据索引(Index)：保存索引数据回滚(Rollback)：保存回滚数据，用于回滚一次事务处理，以及保持读操作一致性临时(Temporary)：用于满足排序的需要，比如查询和索引创建中的ORDER BY从句区间（Extent）:段的存储可以分成一个或多个区间，每个区间占用一

5、定数量的数据块（block）块（Block）:数据库最小的存储单位，由Block参数（db_block_size）指定表空间及数据文件3.1 表空间简介通常，有以下几种类型的表空间（除数据表空间需用户自己创建外，其它6种表空间在数据库创建时均会默认建立1个）：3.1.1 系统表空间（SYSTEM）系统表空间是每个Oracle数据库都必须具备的部分，是安装数据库时自动建立的。它包含数据库的全部数据字典，存储过程、包、函数和触发器的定义以及系统回滚段等管理数据库自身所需的信息。一般来说，应该尽量避免在SYSTEM表中存储非SYSTEM用户的对象。因为这样会带来数据库维护和管理的很多问题。一旦SYS

6、TEM表空间损坏了，只能重新生成数据库。3.1.2 临时表空间（TEMP）临时表空间并不包含真正的数据，临时表空间中存储数据库中动态生成的对象，如排序操作或表连接时的临时数据等；临时表空间也是Oracle用于存储其所有临时表的所在。在一些非常繁忙的数据库中，可能会存在多个临时表空间。3.1.3 工具表空间（TOOLS）工具表空间用于保存数据库工具软件所需的数据库对象，如像Oracle Reports这样的工具软件在工作时就有自己的一组专用表。Oracle Reports把这些表存放在数据库中。大多数DBA都将支持工具运行所需的表存放在这个表空间中。3.1.4 用户表空间（USERS）用户表空间

7、用于存放用户的私有信息，用户的专用数据库对象。3.1.5 回滚表空间（RBS）Oracle数据库需要在一个地方保存恢复（Undo）信息。回滚表空间中存放数据库对象的回滚段，在出现失败和临时终止事务时，可以用回滚段使数据库对象退回到旧值。3.1.6 数据和索引表空间数据表空间用来存放用户的应用数据，索引表空间用来存放应用数据对象的索引。索引可以帮助Oracle迅速找到表中存放的数据。3.2 表空间的管理和使用3.2.1 创建表空间创建表空间的脚本示例如下：create tablespace ts_name datafile-创建名为ts-name的表空间 c:oracleoradatafile1

8、.dbf size 100m ,-表空间的文件1，大小为100Mc:oracleoradatafile2.dbf size 100m -表空间的文件2，大小为100Mminimum extent 550k -最小区间大小为550Klogging/nologging-记录日志/不记录日志default storage (initial 500k -初始大小为500Knext 500k -下一个区大小为500Kmaxextents 500 -最大区间数为500个pctinccease 0) -增量为0online/offline -联机/脱机permanent/temporary -永久/临时【巡

9、检实例】-创建数据表空间-数据文件e:oracleoradataqinxjgl_data01.dbf，初始尺寸1G，可自动扩展，最大为5G-存储属性：初始大小 10M,下一个大小10M,增量为0，最大数量为500create tablespace xjgl_data datafile e:oracleoradataqinxjgl_data01.dbf size 1000Mautoextend on next 500m maxsize 5000m default storage(initial 10M next 10M maxextents 500 pctincrease 0);-创建索引表空间

10、（最好跟数据表空间建在不同的磁盘上）-数据文件：e:oracleoradataqinxjgl_idx01.dbf，初始尺寸500G，可自动扩展，最大为2G-存储属性：初始大小 5M,下一个大小5M,增量为0，最大数量为400create tablespace xjgl_idx datafile e:oracleoradataqinxjgl_idx01.dbf size 500Mautoextend on next 250m maxsize 2000m default storage(initial 5M next 5M maxextents 400 pctincrease 0);-创建LOB段

11、表空间-数据文件：e:oracleoradataqinxjgl_idx01.dbf，初始尺寸500G，可自动扩展，最大为1G-存储属性：初始大小 5M,下一个大小5M,增量为0，最大数量为100create tablespace xjgl_data_lobs datafile e:oracleoradataqinxjgl_data_lobs01.dbf size 500Mautoextend on next 250m maxsize 1000mdefault storage(initial 10M next 10M maxextents 100 pctincrease 0);3.2.2 改变表

12、空间尺寸为表空间增加新的数据文件 其脚本如下所示:alter tablespace xjgl_data add datafile e:oracleoradataqinxjgl_data02.dbf size 200m 调整数据文件尺寸其脚本如下所示:alter database datafile e:oracleoradataqinxjgl_data01.dbf resize 400m允许（或禁止）数据文件的自动扩展 其脚本如下所示alter database datafile e:oracleoradataqinxjgl_data02.dbf autoextend on next 10m m

13、axsize 500m; 改变数据文件的可用性 其脚本如下所示alter database datafile e:oracleoradataqinxjgl_data02.dbf onlineoffline以上调整通过Oracle DBA Studio进行也可调整数据文件尺寸时，如果是减小，不能减小到小于已用到空间数量3.2.3 改变表空间状态和存储设置表空间联机 其脚本如下所示:ALTER TABLESPACE xjgl_data ONLINE表空间脱机 其脚本如下所示:ALTER TABLESPACE xjgl_data OFFLINE NORMALTEMPORARY/ IMMEDIATE/

14、 FOR RECOVER设置表空间为只读ALTER TABLESPACE xjgl_data READ ONLY修改表空间的存储设置ALTER TABLESPACE xjgl_data DEFAULT STORAGE ()整理空间碎片可通过先将某表空间用export导出，再用import将其导入实现删除表空间DROP TABLESPACE “xjgl_data” including contents;注： 表空间非空时，加上including contents关键字才可将其删除删除表空间并不是删除正在使用的数据文件，必须使用操作系统删除数据数据文件3.2.4表空间迁移可通过下列步骤实现（只适用

15、于非系统表空间。不能用于回滚段、临时段的表空间）置表空间状态为脱机alter tablespace xjgl_data offline;使用操作系统命令来移动文件$move e:oracleoradataqinxjgl_data01.dbf e:使用alter tablespace来更改数据库中的文件名alter database rename file e:oracleoradataqinxjgl_data01.dbf to e:xjgl_data01.dbf;重新置表空间状态为联机alter tablespace xjgl_data online;3.3 管理数据文件数据文件是构成表空间和

16、数据库的物理文件，存放在操作系统中。数据文件管理使用时应注意以下几点：初始化参数DB_FILES（默认为1024）设置一个实例可以建立的最大数据文件数。当数据文件的个数达到初始化参数DB_FILES的值时，必须修改DB_FILES的值并重新启动数据库使修改生效。归档模式下数据文件可以单独OFFLINE，而非归档模式数据文件不能正常OFFLINE。数据文件的重命名和移植操作有两种方法实现。对于单个非SYSTEM表空间的数据文件的转移或重命名可以在数据库打开的情况下完成，对于多个表空间或SYSTEM表空间的数据文件的转移或重命名，只能在数据库的MOUNT模式下进行。Oracle没有办法删除已经加载

17、到数据库的数据文件。只能通过删除表空间的方式将表空间和它包含的全部数据文件删除，不能只删除一个单独的数据文件。ALTER DATABASE DATAFILE OFFLINE DROP语句只能使数据文件不再使用，并没有从数据库上真正删除掉数据文件。初始化参数DB_BLOCK_CHECKSUM设置Oracle是否对BLOCK进行校验。不管是否设置这个值，SYSTEM表空间都会进行校验。如果设置为TRUE，那么DBWn进程会对每个写回磁盘的BLOCK进行校验计算，结果存放到BLOCK的头部。Oracle下次读取该块时，会根据这个校验结果验证是否存在磁盘错误3.4 应用开发时的若干注意事项在多磁盘系统

18、，为提高数据库的性能，数据表空间和索引表空间应分别存储，回滚段（RBS）表空间和数据表空间分别存储，系统表（SYSTEM）空间与数据库中其它表空间分别存储。创建表及其它对象时，要为其分配一个表空间，如果未指定表空间，则使用当前系统用户确省的表空间。一般可以按如下步骤进行用create user 命令建立用户并给其分配默认表空间和临时表空间，如create user XJGL_SA identified by xjgl default tablespace xjgl_data temporary tablespace temp;给用户授权grant connect,resource to XJG

19、L_SA;更改以新建的用户（XJGL_SA）登陆connect xjgl_sa/xjgl;创建表及其它对象这样建表时若不指定表空间，对象将会默认创建到xjgl_data表空间上若表中存在大型对象LOB数据字段段，为提高数据库检索性能，一般都需要为LOB数据指定专用的表空间，并指定nocache 参数（表示不这些LOB数据将不会存贮在内存中，以便在查询时获得最快的访问速度）在建立索引时，如果不指定相应的索引表空间名，那么，该索引就会建立在数据表空间中。这是程序员经常忽略的一个问题。应该在建索引时，明确的指明相应的索引表空间(使用using index tablespace子句)。如CREATE

20、TABLE TEST (NAME VARCHAR2(30) PRIMARY KEY USING INDEX TABLESPACE xjgl_idx PCTFREE 0 STORAGE ( )AGE NUMBER);创建触发器、存储过程、函数和包时都不能为其指定存储空间，它们都会被存储到系统（SYSTEM）表空间中回滚段及临时表的使用4.1 回滚段4.1.1 回滚段概述回滚段用于存放数据修改之前的值（包括数据修改之前的位置和值）。回滚段头部包含正在使用的该回滚段事务的信息。一个事务只能使用一个回滚段来存放它的回滚信息，而一个回滚段可以存放多个事务的回滚信息。4.1.2 回滚段的作用事务回滚：当事

21、务修改表中数据的时候，该数据修改前的值（即前影像）会存放在回滚段中，当用户回滚事务（ROLLBACK）时，ORACLE将会利用回滚段中的数据前影像来将修改的数据恢复到原来的值。事务恢复：当事务正在处理的时候，例程失败，回滚段的信息保存在重做日志文件中，ORACLE将在下次打开数据库时利用回滚来恢复未提交的数据。读一致性：当一个会话正在修改数据时，其他的会话将看不到该会话未提交的修改。而且，当一个语句正在执行时，该语句将看不到从该语句开始执行后的未提交的修改（语句级读一致性）。当一个长时间的查询正在执行时，若其他会话改变了该查询要查询的某个数据块，ORACLE将利用回滚段的数据前影像来构造一个读

22、一致性视图。4.1.3 回滚段的种类系统回滚段：当数据库创建后，将自动创建一个系统回滚段，该回滚段只用于存放系统表空间中对象的前影像。非系统回滚段：拥有多个表空间的数据库至少应该有一个非系统回滚段，用于存放非系统表空间中对象的数据前影像。非系统回滚段又分为私有回滚段和公有回滚段，私有回滚段应在参数文件的ROLLBACKSEGMENTS参数中列出，以便例程启动时自动使其在线（ONLINE）。公有回滚段在例程启动时自动在线。 DEFERED（延迟）回滚段：该回滚段在表空间离线（OFFLINE）时由系统自动创建，当表空间再次在线（ONLINE）时由系统自动删除，用于存放表空间离线时产生的回滚信息。4

23、.1.4 回滚段的使用分配回滚段：当事务开始时，ORACLE将为该事务分配回滚段，并将拥有最少事务的回滚段分配给该事务。事务可以用以下语句申请指定的回滚段： SET TRANSTRACTION USE ROLLBACK SEGMENT rollback_segment事务将以顺序，循环的方式使用回滚段的区（EXTENTS），当当前区用满后移到下一个区。几个事务可以写在回滚段的同一个区，但每个回滚段的块只能包含一个事务的信息。回滚段的扩展（EXTEND）：当当前回滚段区的所有块用完而事务还需要更多的回滚空间时，回滚段的指针将移到下一个区。当最后一个区用完，指针将移到第一个区的前面。回滚段指针移到

24、下一个区的前提是下一个区没有活动的事务，同时指针不能跨区。当下一个区正在使用时，事务将为回滚段分配一个新的区，这种分配称为回滚段的扩展。回滚段将一直扩展到该回滚段区的个数到达回滚段的参数MAXEXTENTS的值时为止回滚段的回收: OPTIMAL(最佳大小)参数指明回滚段空闲时收缩到的位置，指明回滚段的OPTIMAL参数可以减少回滚段空间的浪费。4.1.5 回滚段常用操作创建回滚段CREATE PUBLIC ROLLBACK SEGMENT rollback_segmentTABLESPACE tablespaceSTORAGE (INITIAL integerK|M NEXT integer

25、K|MMINEXTENTS integerMAXTENTS integer|UNLIMITEDOPTIMAL integerK|M|NULL) 注：回滚段可以在创建时指明PRIVATE或PUBLIC，一旦创建将不能修改。MINEXTENTS 必须大于等于2PCTINCREASE必须是0OPTIMAL如果要指定，必须大于等于回滚段的初始大小（由MINEXTENTS指定）建议：一般情况下，INITIAL=NEXT设置OPTIMAL参数来节约空间的使用不要设置MAXEXTENTS为UNLIMITED回滚段应创建在一个特定的回滚段表空间内使回滚段在线当回滚段创建后，回滚段是离线的，不能被数据库使用，为

26、了使回滚段被事务利用，必须将回滚段在线。可以用以下命令使回滚段在线：ALTER ROLLBACK SEGMENT rollback_segment ONLINE;为了使回滚段在数据库启动时自动在线，可以在数据库的参数文件中列出回滚段的名字。例如在参数文件中加入以下一行ROLLBACK_SEGMENT=(rbs01,rbs02)修改回滚段的存储参数可以使用ALTER ROLLBACK SEGMENT命令修改回滚段的存储参数（包括OPTIMAL，MAXEXTENTS）。ALTER ROLLBACK SEGMENT rollback_segmentSTORAGE (NEXT integerK|MMI

27、NEXTENTS integerMAXEXTENTS integer|UNLIMITEDOPTIMAL integerK|M|NULL) 回收回滚段的空间如果指定了回滚段的OPTIMAL参数，ORACLE将自动回收回滚段到OPTIMAL指定的位置。用户也可以手动回收回滚段的空间。语法：ALTER ROLLBACK SEGMENT rollback_segment SHRINK TO integer K|M;如果不指明TO integer的数值，ORACLE将试图回收到OPTIMAL的位置。使回滚段离线为了达到以下两个目的需要回滚段离线： 阻止新的事务使用该回滚段； 该回滚段必须删除。ALTER

28、 ROLLBACK SEGMENT rollback_segment OFFLINE;如果有事务正在使用该回滚段，运行该命令后，回滚段的状态将是PENDING OFFLINE。事务结束后，状态将改为OFFLINE，可以通过V$ROLLSTAT查询回滚段的状态。删除回滚段当回滚段不再需要或要重建以改变INITIAL，NEXT或MINEXTENTS参数时，可以将其删除。DROP ROLLBACK SEGMENT rollback_segment;查询回滚段的信息所用数据字典：DBA_ROLLBACK_SEGS可以查询的信息：回滚段的标识(SEGMENT_ID)、名称(SEGMENT_NAME)、所

29、在表空间(TABLESPACE_NAME)、类型(OWNER)、状态(STATUS)。如：SQLSELECT segment_name,tablespace_name,owner,status FROM dba_rollback_segs;回滚段的统计信息所用数据字典：V$ROLLNAME,V$ROLLSTAT如：SQLSELECT ,s.extents,s.rssize,s.optsize,s.hwmsize,s.xacts,s.status FROM v$rollname n,v$rollstat s WHERE n.usn=s.usn;查看回滚段的当前活动事务所用数据字典：V$SESSI

30、ON,V$TRANSACTION。如SQLSELECT s.username,t.xidusn,t.ubafil,t.ubablk,t.used_ublkFROM v$session s,v$transaction tWHERE s.saddr=t.ses_addr;回滚段的数量规划对于OLTP系统，存在大量的小事务处理，一般建议：数量多的小回滚段；每四个事务一个回滚段；每个回滚段不要超过十个事务。对于批处理，一般建议：数量少的大回滚段；每个事务一个回滚段。4.2 临时表的使用4.2.1 临时表的基本概念Oracle临时表用来保存事务或会话期间的中间结果。在临时表中保存的数据只有对当前会话是可

31、见的，任何会话都不能看到其他会话的数据，即使在当前会话提交数据以后也是不可见的。一个会话从来不阻塞另一个会话使用临时表。即使锁定临时表，一个会话也不会阻塞其他会话使用临时表。临时表将从用户临时表空间中分配空间，或者如果从有定义权的程序中访问，将使用程序所有者的临时表空间。全局临时表实际上只是表本身的模板，不包含数据。创建临时表的行为不包括存储空间的分配，也不包括INITIAL的分配。因此，在运行时当一个会话首先将数据放到临时表中时，这时将创建这个会话的临时段。由于每个会话获取自己的临时段，每个用户可能在不同的表空间中为临时表分配空间。如USER1的default临时表空间为TEMP1，他的临时

32、表将从TEMP1中分配空间，USER2的default临时表空间为TEMP2，他的临时表将从TEMP2中分配空间。临时表在每个数据库中只需创建一次，不必在每个存储过程中创建。临时表总是存在的，除非手动的删除它。临时表作为对象存在数据字典中，并且总是保持为空，直到有会话在其中放入数据。Oracle允许创建基于临时表的视图和存储过程。4.2.2 临时表的创建在oracle中，应用程序需要的临时表应该在程序安装时创建，而不是在程序运行时创建。有两种类型的临时表，会话特有的临时表和事务特有的临时表。创建会话特有的临时表CREATE GLOBAL TEMPORARY ( ) ON COMMIT PRES

33、ERVE ROWS创建事务特有的临时表CREATE GLOBAL TEMPORARY ( ) ON COMMIT DELETE ROWS；ON COMMIT PRESERVE ROWS 说明临时表是会话指定，当中断会话时截断表。ON COMMIT DELETE ROWS 说明临时表是事务指定，每次提交后截断表4.2.3 临时表的特性和性能临时表只在当前连接内有效临时表不建立索引，所以如果数据量比较大或进行多次查询时，不推荐使用在仅仅查询数据的时候建议用游标: open cursor for sql clause;有关存储管理的常见Oracle错误分析5.1 ORA-01552：没有可用的非系统

34、回滚段5.1.1错误现象ORA-01552 cannot use system rollback segment for non-system tablespace5.1.2原因分析可能有以下几种原因：除了系统回滚段, 未创建其它回滚段 只创建了PRIVATE回滚段, 但INITsid.ORA的ROLLBACK_SEGMENTS中未列出这些回滚段 创建了PUBLIC回滚段, 但这些回滚段都处于OFFLINE状态5.1.3解决方式根据以上原因相应解决。5.2 ORA-01555：快照太老5.2.1错误现象ORA_01555 snapshot too old: rollback segment n

35、umber string with name string too small5.2.2原因分析产生该错误，可能有以下几种原因：回滚段太少/太小数据库中有太多的事务修改数据未提交, 就发生已提交事务曾使用的空间被重用, 从而造成一个延续时间长的查询所请求的数据已经不在回滚段中. 回滚段被破坏由于回滚段被破坏, 造成事务无法将修改前的内容(read-consistent snapshot) 放入回滚段FETCH ACROSS COMMIT当一个进程打开一个CURSOR, 然后循环执行FETCH, UPDATE, COMMIT, 如果更新的表与FETCH的是同一个表, 就很可能发生ORA-0155

36、5错误.不适当的OPTIMAL（最佳大小）参数太小的OPTIMAL参数会使回滚段很快被SHRINK（收缩）, 造成后续读取操作访问时, 先前的内容已丢失DB_BLOCK_BUFFER太小如果读一致性所请求的块的先前内容在缓冲区中, 那么就不用去访问回滚段. 而如果缓冲区太小, 使得先前版本的内容在CACHE中的可能性变小, 从而必须频繁的访问回滚段来获取先前的内容, 这将大大增大ORA-01555发生的可能.5.2.3解决方式根据以上原因相应解决。对1) 创建更多的回滚段, 为回滚段设置较大的EXTENT以及较大的MINEXTENTS对2) 将被破坏的回滚段OFFLINE, 删除重建对3) 使

37、用大的回滚段（只能减少该错误发生的可能, 不能完全避免）减少提交频率（只能减少该错误发生的可能, 不能完全避免）建立一个临时表, 存放要更新的表的查询列(如主键及相关的条件列), 从临时表FETCH, 更新原来的表.捕获ORA-01555错误, 关闭并重新打开CURSOR, 继续执行循环对4)仔细设计OPTIMAL参数, 不要让回滚段过于频繁的EXTEND/SHRINK对5)尽可能的增大 DB_BLOCK_BUFFER值5.3 ORA-01578：Oracle数据块被破坏 5.3.1错误现象ORA-01578:Oracle data block corrupted(file # num,blo

38、ck # num)5.3.2原因分析当ORACLE访问一个数据块时，由于1、硬件的I/O错误；2、操作系统的I/O错误或缓冲问题；3、内存或页问题；4、ORACLE试图访问一个未被格式化的系统块；5、数据文件部分溢出等上述几种情况的一种，都有可能报ORA-01578的错误。5.3.3解决方式由于ORACLE只有在访问到有问题的数据文件时才会报错，所以报错的时间有可能会比实际出错的时间要晚，如果ORA-01578出错信息提示数据坏块指向的是用户自己的数据文件，则用以下方法来解决： 如果通过下面的SQL语句查出的坏块出现有索引上，则只需重建索引即可 SQLSelect owner,segment_

39、name,segment_type from dba_extents where file_id= and between block_id and block_id+blocks-1;-(和分别是ORA-01578报出的坏块出现的文件号和块号) 如果坏块出现在表上，先用以下语句分析是否为永久性坏块（建议多执行一两次，有助于鉴别数据坏块是永久性的（硬盘上的物理坏块）还是随机性的（内存或硬件错误引起）： SQLAnalyze table validate structure cascade; 执行该命令后，可能会出现以下的结果： ORA-01578：与原先错误信息有相同的参数，为永久性的物理或逻

40、辑坏块；与原先错误信息有不同的参数，可能与内存，page space和I/O设备有关。 如果用户有此表的最新备份，那么最好是用此备份来恢复此表，或者使用event 10231来取出坏块以外的数据： 先关闭数据库 编辑init.ora文件，加入： event=”10231 trace name context forever,level 10” .startup restrict 创建一个临时表：SQLcreate table errortemp as select * from error;(error是坏表的表名) 把event从init.ora文件中删掉并重起数据库 rename坏表，把临

41、时表rename成坏表的表名 创建表上的INDEX等5.4 ORA-01628：区达到回滚段最大值5.4.1错误现象ORA-01628:max # of extents num reached for rollback segment num 5.4.2原因分析这种错误通常为一个回滚段和一个表空间已经达到MAXEXTENTS参数设置的极限。5.4.3解决方式使用SQL命令ALTER TABLESPACESTORAGE（MAXEXTENTS XXXX）来增加 MAXEXTENTS，其中“XXXX”值必须大于错误信息中所指的数值，也可以重新创建较大的范围尺寸，使用带有选项COMPRESS=Y的Ex

42、port工具导出表，如果表空间有可用空间，先给表做一个备份，用alter tablespace tablespace_name更改其名字，然后再装载表回数据库。 查看其错误出现的地方，如果出现在回滚段或索引上，那么必须将其删除并重建，如果出现在临时表空间，修改临时表空间的存储字段，便可解决这个问题。 5.5 ORA-01650：回滚段不能扩展5.5.1错误现象ORA-01650:unable to extend rollback segment NAME by NUM in tablespace NAME5.5.2原因分析该错误为回滚段表空间不足引起的，这也是ORACLE DBA最常见ORAC

43、LE错误信息当用户在做一个非常庞大的数据操作导致现有回滚段的不足，使可分配用的回滚段表空间已满，无法再进行分配，就会出现上述的错误。5.5.3解决方式使用“ALTER TABLESPACE tablespace_name ADD DATAFILE filename SIZE size_of_file”命令增加表空间，根据具体的情况可以增加一个或多个表空间文件。当然这与还与你主机上的裸盘设备有关，如果你主机的裸盘设备已经没有多余的使用空间，建议不要轻意的增加回滚段表空间的大小，可使用下列的语句先查询一下剩余的tablespace空间有多少：Select user_name,sql_text fr

44、om V$open_cursor where user_name=;如果多余的空间比较多，就可以适当追加一个大的回滚段给表空间使用，从而避免上述的错误。你也可以用以下语句来检测一下rollback segment的竞争状况：Select class,count from V$waitstat where calss in(system undo header,system undo block,undo header,undo block);和 Select sum(value) from V$sysstat where name in (db_block_gets,consistents g

45、ets); 如果任何一个class in count/sum(value)大于1%，就应该考虑增加rollback segment。 5.6 ORA-01652：临时段不能扩展5.6.1错误现象ORA-01652:unable to extend temp segment by num in tablespace name 5.6.2原因分析ORACLE临时段表空间不足，因为ORACLE总是尽量分配连续空间，一但没有足够的可分配空间或者分配不连续就会出现上述的现象。5.6.3解决方式由于ORACLE将表空间作为逻辑结构单元，而表空间的物理结构是数据文件，数据文件在磁盘上物理地创建，表空间的所有

46、对象也存在于磁盘上，为了给表空间增加空间，就必须增加数据文件。先查看一下指定表空间的可用空间，使用视图SYS.DBA_FREE_SPACE，视图中每条记录代表可用空间的碎片大小： SQLSelect file_id,block_id,blocks,bytes from sys.dba_free_space where tablespace_name=; 返回的信息可初步确定可用空间的最大块，看一下它是否小于错误信息中提到的尺寸，再查看一下缺省的表空间参数： SQLSELECT INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,PCT_INCREASE FROM SY

47、S.DBA_TABLESPACES WHERE TABLESPACE_NAME=name; 通过下面的SQL命令修改临时段表空间的缺省存储值： SQLALTER TABLESPACE name DEFAULT STORAGE (INITIAL XXX NEXT YYY); 适当增大缺省值的大小有可能解决出现的错误问题，也可以通过修改用户的临时表空间大小来解决这个问题： SQLALTER USER username TEMPORARY TABLESPACE new_tablespace_name; 使用ALTER TABLESPACE命令，一但完成，所增加的空间就可使用，无需退出数据库或使表空间

48、脱机，但要注意，一旦添加了数据文件，就不能再删除它，若要删除，就要删除表空间。 附录资料：不需要的可以自行删除年数据库-oracle-学习之路数据库基础数据库主键生成Select Substr(To_char(dbms_random.value), 2, 22) | getUUID.NextvalFrom dual ;数据库空间查询-表空间在哪些空间下select df.tablespace_name 表空间名,totalspace 总空间M,freespace 剩余空间M,round(1-freespace/totalspace)*100,2) 使用率%from(select tablesp

49、ace_name,round(sum(bytes)/1024/1024) totalspacefrom dba_data_filesgroup by tablespace_name) df,(select tablespace_name,round(sum(bytes)/1024/1024) freespacefrom dba_free_spacegroup by tablespace_name) fswhere df.tablespace_name=fs.tablespace_name;-查用户下所用空间SELECT owner, tablespace_name, ROUND (SUM (B

50、YTES) / 1024 / 1024, 2) USED(M) FROM dba_segmentsGROUP BY owner, tablespace_nameORDER BY SUM (BYTES) DESC;-查用户下所有表所占空间select OWNER, t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) mmmfrom dba_segments twhere t.owner = JIANGSUand t.segment_type=TABLEgroup by OWNER, t.segment_name, t.segmen

51、t_typeorder by mmm desc;oracle 之删除重复数据select a.rowid,a.* from 表名 a where a.rowid != (select max(b.rowid) from 表名 b where a.字段1 = b.字段1 and a.字段2 = b.字段2 )删除delete from 表名 a where a.rowid != (select max(b.rowid) from 表名 b where a.字段1 = b.字段1 and a.字段2 = b.字段2 )oracle 之查询数据第一条记录select * from tab rownu

52、m sqlplus /nologSQL*Plus: Release .0 Production on Fri Jan 20 02:29:37 2006Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.SQL connect /as sysdbaConnected to an idle instance.SQL startupORACLE instance started.Total System Global Area 135352820 bytesFixed Size 455156 bytesVariable

53、Size 109051904 bytesDatabase Buffers 25165824 bytesRedo Buffers 679936 bytesDatabase mounted.2)关闭数据库： HYPERLINK mailto:oraclesuse92 oraclesuse92: sqlplus /nologSQL*Plus: Release .0 Production on Fri Jan 20 02:29:37 2006Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.SQL connect /as

54、 sysdbaConnected to an idle instance.SQL shutdwon abort;3)启动监听器 HYPERLINK mailto:oraclesuse92 oraclesuse92: lsnrctl start4)停止监听器 HYPERLINK mailto:oraclesuse92 oraclesuse92: lsnrctl stop5)查看监听器状态 HYPERLINK mailto:oraclesuse92 oraclesuse92: lsnrctl status或lsnrctl命令进入监听程序后再用statusoracle 之备份工作背景：Oracle

55、10g 服务器，Oracle 10g 客户端，windowsXP 操作平台要求：Oracle 数据库服务器对数据库 ytcn 每天自动备份一次。解决方案：利用任务计划、批处理文件和 Oracle 的 exp 导出功能，根据日期自动生成 Oracle 备份文件。详细步骤： 1） 创建批处理文件 ytcn.batytcn.bat中详细内容如下：echo offecho 正在备份银通网 Oracle 数据库，请稍等exp userid=ytcn/ytcnytcn as sysdba file=e:/bak/ytcn/oracle/ytcn/ytcn%date:0,4%date:5,2%date:8,

56、2%.dmp log=e:/bak/ytcn/oracle/ytcn/ytcn%date:0,4%date:5,2%date:8,2%.log full=yecho 任务完成！其中红色部分是根据需要进行变动的地方，例如作者的项目名“银通网”，数据库 ytcn 用户名 ytcn，密码 ytcn，要在目录 e:/bak/ytcn/oracle/ytcn 下生成形如 ytcn20090711.dmp 和 ytcn20090711.log 的备份和日志文件，全表导出。另外：%date%的值在不同的系统、语言版本下可能是不一样的，控制面板里面区域选项的设定也会改变%date%的值。请先在命令行中测试 e

57、cho %date% 的返回值。%date:4,10% 是返回日期函数，后的第一个参数是要截取的起始位置（从0开始），第二个参数是要截取的长度，如没有则是截取到最后，参数可酌情修改。 如需要准确的时间做为文件名，请用%time%函数，参数同上。 2） 添加一个任务计划 ytcn开始 所有程序 附件 系统工具 任务计划 添加任务计划 下一步 在 浏览 中查找刚刚写好的 ytcn.bat 文件 任务名输入ytcn,执行这个任务选择每天,下一步 起始时间下午12:00,起始日期2009-7-11,下一步 输入用户名及密码,用户名要求是管理员权限用户名,下一步 完成点击完成之后，会在任务计划栏目下新增

58、一个名为ytcn的任务计划，表明已经配置完毕。备注：有时点击完成 之后,系统警告已创建新任务，但可能不能运行，因为无法设置账户信息。指定的错误是：Ox80041315：任务计划程序服务没有运行这是因为电脑的任务计划程序服务没有启动起来。开始 所有程序 管理工具 服务，找到Task Scheduler服务，发现启动类型为已禁用,右键单击更改为自动，并把它启动起来，然后重新添加一次任务计划 ytcn 就可以了。oracle之命中率查询Buffer cache由数据块组成。1. Buffer cache的工作原理LRU列表：MRU 。 LRU.（全表扫描FTS放在LRU端。）缓冲区块的状态：Free

59、、Pinned、Clean、Dirty.Dirty List或Write List（写列表）。数据库写进程DBW0将缓冲区高速缓存中的数据写到数据文件中。2. 测量Buffer cache的性能测量Buffer cache的命中率：SQL select 1 - (physical.value - direct.value - lobs.value) / logical.value) Buffer Cache Hit Ratio from V$SYSSTAT physical, V$SYSSTAT direct, V$SYSSTAT lobs, V$SYSSTAT logical where =

60、 physical reads And = physical reads direct and = physical reads direct (lob) And = session logical reads;“Buffer Cache Hit Ratio”的值要 90%.使用STATSPACK来监视Buffer cache.使用REPORT.TXT来监视Buffer cache.非命中率指标：Free Buffer Inspected、Free Buffer Waits、Buffer Busy Waits.（V$sysstat）使用Performance Manager（数据库例程）来监视