第7章 表空间管理

Oracle11g+ASP.NET数据库系统开发案例教程Oracle的表空间表空间管理“电子题库系统”表空间的创建表空间管理

第7章7.1

Oracle的表空间

表空间是Oracle数据库中最大的逻辑组织单位，Oracle数据库通过表空间来组织数据库中的数据。一个数据库逻辑上由一个或多个表空间组成，一个表空间物理上是由一个或多个数据文件组成。在任何一个时刻，一个数据文件只能属于一个表空间，一个表空间只能属于一个数据库，反之则不成立。表空间的类型系统表空间：随数据库的创建而创建，主要存放Oracle系统内部表、数据字典等用于系统管理的数据，包括SYSTEM表空间和SYSAUX表空间。系统表空间不能删除，不能改名，不能修改成只读状态。临时表空间：保存SQL语句在执行过程中所产生的临时数据（如查询操作中的记录排序、分组汇总等）。此类表空间通常不能存放用户数据，由系统自动管理，其中的数据不需要永久保存，属于“临时”性质。7.1

Oracle的表空间表空间的类型回退表空间：又称撤消表空间、回滚表空间，是在进行事务回滚操作时使用的表空间，存储数据撤消操作所涉及的数据及相关信息。此类表空间通常不能存放用户数据，由系统自动管理。永久表空间：专门针对用户的具体应用而创建的数据表空间，用户方案对象（如表、索引等）中的数据就存放在此。表

空

间类

型说

明SYSTEM系统表空间主要存放系统内部表、数据库的数据字典

SYSAUX系统表空间辅助系统表空间，存放一些附加数据库组件对象TEMP临时表空间系统默认临时表空间，不推荐使用一个非常大的临时表空间UNDOTBS1回退表空间系统默认的回退表空间，在任意给定时间内也只能有一个活动的回滚表空间USERS永久表空间拟提供给所有用户公用的数据表空间，在创建用户时，通常需要另设默认数据表空间Oracle安装时自动创建的表空间7.1

Oracle的表空间表空间的状态“读-写”状态读写状态（READWRITE）：默认情况下，表空间可以进行读写操作。只读状态（READONLY）：无法写入或修改。可以把不需要修改的数据放入只读表空间，避免修改。“脱机-联机”状态脱机状态（OFFLINE）：暂时不让用户访问该表空间。联机状态（ONLINE）：恢复用户对该表空间的访问。7.1

Oracle的表空间表空间的文件类型小文件类型（SMALLFILE）：有些操作系统不支持大数据文件（如Windows中的FAT32格式硬盘分区，单个数据文件最大不能超过4GB），针对这种情况，我们在创建一个大容量的表空间（如容量大小为10GB）时，需要为这个表空间创建5个2GB的小数据文件。大文件类型（BIGFILE）：对于超大数据量的应用，需要创建超大的表空间（如20GB），如果采用小文件类型，势必需要许多个数据文件才能满足需求，多个数据文件的管理效率低下，如果操作系统支持大数据文件（如磁盘陈列的容量可以达到ＴＢ级），这时就应该采用一个20GB的数据文件。提示大文件类型的表空间只能有一个数据文件，而且该文件的容量大小必须采取自动增长的管理方式，以满足用户动态对表空间进行扩容的需要。7.1

Oracle的表空间表空间的管理方式字典管理方式（DictionaryManagedTablespace）:表空间使用数据字典来管理存储空间的分配，当进行区的分配与回收时，Oracle将对数据字典中的相关基础表进行更新，同时会产生回退信息和重做信息。该管理方式是传统的、老式的管理方式。本地管理方式（LocallyManagedTablespace）:区的分配与管理信息都存储在表空间的数据文件中，而与数据字典无关。表空间在每个数据文件中维护一个“位图”结构，用于记录表空间中的所有区的分配情况，因此区在分配与回收时，Oracle将对数据文件中的位图进行更新，不会产生回退信息或重做信息。Oracle11g推荐使用本地管理方式，其是默认的管理方式。7.1

Oracle的表空间表空间的管理准则不要把用户数据和对象存放在系统表空间。回滚数据与用户数据分开存放。不同应用程序中的数据分开存放，以免一个表空间脱机影响多个应用。不同表空间的数据文件存放在不同的磁盘上，减少I/O竞争。为特殊用途专门设置一个表空间。7.1

Oracle的表空间规划表空间的大小

在具体创建表空间之前，需要对具体方案在近期内（如１年）可预期的数据量进行估算，通过计算来规划表空间的大小。对于数据表空间，其估算方式如下：确定表的大小：最大行长×行数×（1+空闲百分比）×预留百分比。确定数据表空间的大小：应该把属于一个应用程序、相互关联的表放入一个表空间，这些表的空间之和，可以作为数据表空间大小的估值。7.1

Oracle的表空间对于索引表空间，其估算方式如下：确定索引的大小：单个索引项的大小×行数×（1+空闲百分比）×预留百分比确定索引表空间的大小：将本方案中的所有索引的空间之和，作为索引表空间大小的估值。对于用户专用临时表空间不需要设置得太大，通常以MB级为参考量。7.1

Oracle的表空间与表空间相关的视图名

称说

明V$TABLESPACE所有表空间的名称和数量DBA_TABLESPACES所有表空间的描述信息USER_TABLESPACES所有用户可访问表空间的描述信息DBA_FREE_SPACE所有表空间中的空闲区间信息USER_FREE_SPACE所有用户表空间中的空闲区间信息V$DATAFILE所有数据文件信息V$TEMPFILE所有临时文件信息DBA_DATA_FILES所有属于表空间的数据文件信息DBA_TEMP_FILES所有属于临时表空间的临时文件信息7.2表空间管理永久表空间的创建CREATE[SMALLFILE|BIGFILE][PERMANENT]

TABLESPACEtablespace_nameDATAFILEfull_filenameSIZEinteger[K∣M∣G∣T]

[AUTOEXTEND{OFF∣ON[NEXTinteger[K∣M∣G∣T]][MAXSIZE{UMLIMITED∣integer[K∣M∣G∣T]}]}][，…][ONLINE∣OFFLINE]；

语法说明：PERMANENT：表示创建永久表空间，可以省略，系统默认为创建永久表空间。tablespace_name：表空间名，要符合Oracle命名规则。SMALLFILE|BIGFILE：数据文件类型，默认为小文件类型（SMALLFILE）。full_filename：数据文件的详细描述，包括盘符、路径、文件名，要确保路径是已经存在的，如果没有指定路径，则会将数据库文件保存到系统安装时默认的数据文件保存目录下。7.2表空间管理SIZEinteger[K∣M∣G∣T]：指定数据文件的初始大小，integer需要用一个整数替代，[K∣M∣G∣T]指定存储单位，分别表示KB、MB、GB、TB。如果没有指定大小，默认为100MB。AUTOEXTEND{OFF∣ON……：是否允许数据文件自动增长，如果选择ON，允许数据文件自动增长，此时需要指定下次增长的数量及最大数量。NEXTinteger[K∣M∣G∣T]：指定数据文件自动增长的步长。MAXSIZE{UMLIMITED∣integer[K∣M∣G∣T]：指定数据文件自动增长的最大容量，UMLIMITED表示无穷大，integer[K∣M∣G∣T]指定最大值。[，…]：表示其他数据文件，如果指定了BIGFILE选项，则数据文件只能1个，不能重复。ONLINE∣OFFLINE：指定表空间状态，如果没有指定，默认是联机状态。7.2表空间管理full_filename：数据文件的详细描述，包括盘符、路径、文件名，要确保路径是已经存在的，如果没有指定路径，则会将数据库文件保存到系统安装时默认的数据文件保存目录下。SIZEinteger[K∣M∣G∣T]：指定数据文件的初始大小，integer需要用一个整数替代，[K∣M∣G∣T]指定存储单位，分别表示KB、MB、GB、TB。如果没有指定大小，默认为100MB。AUTOEXTEND{OFF∣ON……：是否允许数据文件自动增长，如果选择ON，允许数据文件自动增长，此时需要指定下次增长的数量及最大数量。NEXTinteger[K∣M∣G∣T]：指定数据文件自动增长的步长。MAXSIZE{UMLIMITED∣integer[K∣M∣G∣T]：指定数据文件自动增长的最大容量，UMLIMITED表示无穷大，integer[K∣M∣G∣T]指定最大值。[，…]：表示其他数据文件，如果指定了BIGFILE选项，则数据文件只能1个，不能重复。ONLINE∣OFFLINE：指定表空间状态，如果没有指定，默认是联机状态。7.2表空间管理【例7.1】创建表空间test01_data，数据文件大小为10MB，不指定路径，数据文件保存到系统默认的目录下。CREATETABLESPACEtest01_dataDATAFILE'test01_data01.dbf'SIZE10M；【例7.2】创建表空间test02_data，数据文件大小为10MB，数据文件保存到指定路径目录下。CREATETABLESPACEtest02_dataDATAFILE'd:\database\test02_data01.dbf'SIZE10M；【例7.3】创建表空间test03_data，数据文件大小为10MB，数据文件保存到指定路径目录下，允许文件自动扩展，扩展的步长为4MB，最大不超过32MB。CREATETABLESPACEtest03_dataDATAFILE'd:\database\test03_data01.dbf'SIZE10MAUTOEXTENDONNEXT4MMAXSIZE32M；7.2表空间管理【例7.4】创建表空间test04_data分别指定2个数据文件，每个数据文件大小为10MB，数据文件保存到指定路径目录下，允许文件自动扩展，扩展的步长为4MB，最大不超过32MB。CREATETABLESPACEtest04_dataDATAFILE'd:\database\test04_data01.dbf'SIZE10MAUTOEXTENDONNEXT4MMAXSIZE32M，

'd:\database\test04_data02.dbf'SIZE10MAUTOEXTENDONNEXT4MMAXSIZE32M；【例7.5】创建表空间test05_data，数据文件大小为10MB，数据文件保存到指定路径目录下，允许文件自动扩展，扩展的步长为4MB，最大不限，表空间状态为脱机状态。CREATETABLESPACEtest05_dataDATAFILE'd:\database\test05_data01.dbf'SIZE10MAUTOEXTENDONNEXT4MMAXSIZEUNLIMITEDOFFLINE；【例7.6】创建表空间test06_data，采用大文件类型，数据文件大小为16MB，数据文件保存到指定路径目录下。CREATEBIGFILETABLESPACEtest06_dataDATAFILE'd:\database\test06_data01.dbf'SIZE16M；临时表空间的创建CREATE[SMALLFILE|BIGFILE]TEMPORARY

TABLESPACEtablespace_nameTEMPFILEfull_filenameSIZEinteger[K∣M∣G∣T][AUTOEXTEND{OFF∣ON[NEXTinteger[K∣M∣G∣T]][MAXSIZE{UMLIMITED∣integer[K∣M∣G∣T]}]}][，…]；语法说明：TEMPORARY：创建临时表空间时一定要加上此关键词。TEMPFILE：指定临时表空间文件，不是数据文件。警告临时表空间不能使用[ONLINE∣OFFLINE]选项。下面举例说明临时表空间的创建。7.2表空间管理7.2表空间管理【例７.７】创建表空间test01_temp，临时文件大小为10MB，数据文件保存到指定路径目录下。CREATETEMPORARYTABLESPACEtest01_tempTEMPFILE'd:\database\test01_temp01.dbf'SIZE10M；回退表空间的创建CREATE[SMALLFILE|BIGFILE]UNDO

TABLESPACEtablespace_nameDATAFILEfull_filenameSIZEinteger[K∣M∣G∣T][AUTOEXTEND{OFF∣ON[NEXTinteger[K∣M∣G∣T]][MAXSIZE{UMLIMITED∣integer[K∣M∣G∣T]}]}][，…]RETENTION{GUARANTEE|NOGUARANTEE}[ONLINE∣OFFLINE]；语法说明：UNDO：创建回退表空间时一定要加上此关键词。RETENTION{GUARANTEE|NOGUARANTEE}：Oracle回退表空间中存放用户进行数据更新操作时的旧值，当事务正常提交后，该旧值还会保留一段时间，保留的期限由参数undo_retention（该参数通常在init.ora或者SPFILE文件中）来设定，该参数的值通常为10800秒（3小时），在此保留期间并不能保证提交后的信息一定不被覆写，当回退段空间不够时，还是会覆盖已保留的旧值，如果希望不被覆盖的话，就需要设置RETENTIONGUARANTEE选项。7.2表空间管理提示设置该选项会带来风险。如果在undo_retention设置的期限内，有大量的数据更新操作，而回退表空间大小又设置得过小且设置了AUTOEXTENDOFF选项，则会因为空间不足而报错。7.2表空间管理【例7.8】创建表空间test01_undo，临时文件大小为10MB，数据文件保存到指定路径目录下。CREATEUNDOTABLESPACEtest01_undoDATAFILE'd:\database\test01_undo01.dbf'SIZE10M；提示同一时刻数据库例程只能使用一个回退表空间，Oracle默认的回退表空间是UNDOTBS1，如果要切换到用户自己定义的回退表空间则需要运行如下语句，修改undo_tablespace参数的值。但通常我们不会专门自己来定义回退表空间，如果UNDOTBS1表空间不够使用，则可以采取增加数据文件的方式来扩充此表空间，详见下面章节。ALTERSYSTEMSETundo_tablespace=test01_undo；7.2表空间管理表空间的修改Oracle11g表空间的修改主要应用在以下两方面。

表空间大小的调整当数据表空间快用尽，已不够使用时，需要扩充表空间；当创建的表空间过大，数据量较少时，会造成硬盘空间浪费，需要缩小表空间大小。表空间大小的调整主要通过以下3种方式进行。表空间状态的调整表空间状态的调整主要包括修改数据文件的联机或脱机状态、表空间的联机或脱机状态、表空间的只读或读写状态。7.2表空间管理表空间大小的调整主要通过以下3种方式进行：对于小文件类型的表空间，可以增加、删除新数据文件来调整表空间大小。增加新数据文件的命令语法如下所示：7.2表空间管理ALTERTABLESPACEtablespace_nameADD{DATAFILE|TEMPFILE}full_filenameSIZEinteger[K∣M∣G∣T][AUTOEXTEND{OFF∣ON[NEXTinteger[K∣M∣G∣T]][MAXSIZE{UMLIMITED∣integer[K∣M∣G∣T]}]}][，…]；语法说明：DATAFILE|TEMPFILE：DATAFILE表示为永久表空间、回退表空间增加数据文件，TEMPFILE表示为临时表空间增加临时文件。7.2表空间管理【例7.9】修改表空间test02_data，为其增加一个数据文件，文件大小为20MB，数据文件保存到指定路径目录下。ALTERTABLESPACEtest02_dataADDDATAFILE'd:\database\test02_data02.dbf'SIZE20M；【例7.10】修改表空间test01_temp，为其增加一个数据文件，文件大小为20MB，数据文件保存到指定路径目录下，允许其自动增长，增长步长为10MB，最大不限。ALTERTABLESPACEtest01_tempADDTEMPFILE'd:\database\test01_temp02.dbf'SIZE20MAUTOEXTENDONNEXT10MMAXSIZEUNLIMITED；删除原有数据文件的命令语法如下所示：7.2表空间管理ALTERTABLESPACEtablespace_nameDROP{DATAFILE|TEMPFILE}full_filename；提示：如果表空间只有一个数据（临时）文件则不能删除。【例7.11】删除表空间test02_data的数据文件d:\database\test02_data02.dbf。ALTERTABLESPACEtest02_dataDROPDATAFILE'd:\database\test02_data02.dbf'；【例7.12】删除临时表空间test01_temp的数据文件d:\database\test01_temp02.dbf。ALTERTABLESPACEtest01_tempDROPTEMPFILE'd:\database\test01_temp02.dbf'；通过调整原有数据文件的大小来达到表空间大小调整的目的。对于大文件类型的表空间，可以直接修改表空间原有数据文件的大小以达到扩大或缩小表空间的目的，命令语法如下所示：7.2表空间管理ALTERTABLESPACEtablespace_nameRESIZEinteger[K∣M∣G∣T]；语法说明：此方式只适合于大文件类型的表空间，因为其只能有一个数据文件，所以在语法中不需要列出数据文件的名称。【例7.13】修改大文件类型表空间test06_data，将其数据文件大小调整为32MB。ALTERTABLESPACEtest06_dataRESIZE32M；对于小文件类型的表空间，可以通过修改数据库的方式来修改数据文件的大小。命令语法如下所示：7.2表空间管理语法说明：此方式也适合于大文件类型的表空间。【例7.14】调整小文件类型表空间test02_data的数据文件d:\database\test02_data01.dbf的大小为16MB。ALTERDATABASEDATAFILE'd:\database\test02_data01.dbf'RESIZE16M；

【例7.15】调整临时表表空间test01_temp的数据文件d:\database\test01_temp01.dbf的大小为16MB。ALTERDATABASETEMPFILE'd:\database\test01_temp01.dbf'RESIZE16M；ALTERDATABASE{DATAFILE|TEMPFILE}full_filenameSIZEinteger[K∣M∣G∣T]；可以将数据文件设置为自动扩展，以达到扩充表空间的目的。对于大文件类型的表空间，可以允许数据文件自动扩展，以达到扩展表空间的目的，命令语法如下所示：7.2表空间管理ALTERTABLESPACEtablespace_nameAUTOEXTEND{OFF∣ON[NEXTinteger[K∣M∣G∣T]][MAXSIZE{UMLIMITED∣integer[K∣M∣G∣T]}]}语法说明：此方式只适合于大文件类型的表空间，因为其只能有一个数据文件，所以在语法中不需要列出数据文件的名称。【例7.16】修改大文件类型表空间test06_data，将其数据文件调整为自动扩展。ALTERTABLESPACEtest06_dataAUTOEXTENDON；对于小文件类型的表空间，可以通过修改数据库的方式来允许数据文件的自动增长，命令语法如下所示：7.2表空间管理ALTERDATABASE{DATAFILE|TEMPFILE}full_filenameAUTOEXTEND{OFF∣ON[NEXTinteger[K∣M∣G∣T]][MAXSIZE{UMLIMITED∣integer[K∣M∣G∣T]}]}语法说明：此方式也适合于大文件类型的表空间。【例7.17】调整小文件类型表空间test02_data的数据文件d:\database\test02_data01.dbf，允许其可以自动增长，增长步长为8MB，最大值不限。ALTERDATABASEDATAFILE'd:\database\test02_data01.dbf'AUTOEXTENDONNEXT8MMAXSIZEUNLIMITED；表空间状态的调整修改数据文件的联机或脱机状态，命令语法如下所示：7.2表空间管理ALTERTABLESPACEtablespace_name{DATAFILE|TEMPFILE}{ONLINE|OFFLINE}语法说明：此命令会将所有数据文件全部修改成联机或脱机状态，但不会影响表空间本身的状态。只有Oracle处于重做日志归档模式下才能正常让数据文件转换成脱机模式【例7.18】修改表空间test02_data，让所有数据库文件脱机。ALTERTABLESPACEtest02_dataDATAFILEOFFLINE；修改表空间的联机或脱机状态，命令语法如下所示：7.2表空间管理ALTERTABLESPACEtablespace_nameONLINE|OFFLINE语法说明：修改表空间的联机或脱机状态会同步修改数据文件的联机或脱机状态。【例7.19】修改表空间test02_data，让其脱机。ALTERTABLESPACEtest02_dataOFFLINE；修改表空间的只读或读写状态，命令语法如下所示：7.2表空间管理ALTERTABLESPACEtablespace_name{READONLY|READWRITE}语法说明：用户不能修改系统表空间的状态，即不能修改SYSTEM、SYSAUX表空间的状态。【例7.20】修改表空间test02_data，让其只读。ALTERTABLESPACEtest02_dataREADONLY；表空间的查询通过SELECT语句查询DBA_TABLESPACES表来了解数据库中的表空间详情，此表的字段较多，重点关注TABLESPACE_NAME、STATUS、CONTENTS这3个字段。7.2表空间管理【例7.21】查询本章例子中创建的所有表空间信息，只列出表空间名、表空间状态、表空间类别。SELECTTABLESPACE_NAME，STATUS，CONTENTSFROMdba_tablespacesWHERETABLESPACE_NAMELIKE'TEST%'ORDERBYtablespace_name；表空间的查询可以通过查询DBA_DATA_FILES表来了解表空间中的数据文件详情。7.2表空间管理【例7.22】查询本章例子中创建的所有表空间的数据文件信息，只列出表空间名、数据文件状态、完整的数据文件标识、数据文件长度（单位为字节）、是否允许自动扩展、最大限制长度（单位为字节）、自动增长步长（单位为块）、可利用空间等信息。SELECTTABLESPACE_NAME，STATUS，FILE_NAME，BYTES/(1024*1024)asSizes，AUTOEXTENSIBLE，MAXBYTES/(1024*1024)asMaxs，INCREMENT_BY，USER_BYTES/(1024*1024)asUsefulFROMdba_data_filesWHERETABLESPACE_NAMELIKE'TEST%'；表空间的删除如果用户具有DROPTABLESPACE的权限就可以删除表空间，通常只限于删除用户自己定义的表空间，系统自动创建的表空间不要轻易去删除。7.2表空间管理DROPTABLESPACEtablespace[INCLUDINGCONTENTS[ANDDATAFILES][CASCADECONSTRAINTS]]；语法说明：INCLUDINGCONTENTS：删除表空间的同时将表空间的内容一并删除，如果表空间包含内容，此选项必须选择，否则会报告一个错误信息，并不能删除表空间。ANDDATAFILES：删除表空间的同时将数据文件一并删除。CASCADECONSTRAINTS：如果被删除表空间中的表与其他表空间中的表存在主外引用则需要使用此选项，将参照完整性约束删除，否则Oracle会报告一个错误，删除失败。7.2表空间管理【例7.24】将本章创建的表空间全部删除。DROPTABLESPACEtest01_dataINCLUDINGCONTENTSANDDATAFILES；DROPTABLESPACEtest02_dataINCLUDINGCONTENTSANDDATAFILES；DROPTABLESPACEtest03_dataINCLUDINGCONTENTSANDDATAFILES；DROPTABLESPACEtest04_dataINCLUDINGCONTENTSANDDATAFILES；DROPTABLESPACEtest05_dataINCLUDINGCONTENTSANDDATAFILES；DROPTABLESPACEtest06_dataINCLUDINGCONTENTSANDDATAFILES；DROPTABLESPACEtest01_tempINCLUDINGCONTENTSANDDATAFILES；DROPTABLESPACEtest