Oracle数据库应用教程--管理表空间

第4章 管理表空间4本章要点：熟练掌握创建表空间的方法。掌握如何设置表空间的状态。了解如何重命名表空间。掌握表空间中数据文件的管理。了解临时表空间。了解大文件表空间。了解非标准数据块表空间。理解撤消表空间的作用。掌握创建与管理撤消表空间的方法。了解与表空间和数据文件相关的数据字典。24.1 基本表空间4在创建数据库时，Oracle会自动地创建一系列表空间，例如system表空间。用户可以使用这些表空间进行数据操作。4但是，在实际应用中，如果所有用户都使用系统自动创建的这几个表空间，将会严重影响I/O性能。34.1.1 创建表空间4创建表空间需要使用CREATE TABLESPACE语句。其基本语法如下：CREATE TEMPORARY|UNDO TABLESPACE tablespace_name DATAFILE|TEMPFILE file_name SIZE size K|M REUSE AUTOEXTEND OFF|ON NEXT number K|M MAXSIZE UNLIMITED|number K|M ,.MININUM EXTENT number K|M BLOCKSIZE number K ONLINE|OFFLINE LOGGING|NOLOGGING FORCE LOGGING DEFAULT STORAGE storage COMPRESS|NOCOMPRESS PERMANENT|TEMPORARY EXTENT MANAGEMENT DICTIONARY|LOCAL AUTOALLOCATE|UNIFORM SIZE number K|M SEGMENT SPACE MANAGEMENT AUTO|MANUAL;44.1.1 创建表空间4 语法说明如下。(1)TEMPORARY|UNDO(2)tablespace_name(3)DATAFILE|TEMPFILE file_name(4)SIZE size(5)REUSE(6)AUTOEXTEND OFF|ON(7)NEXT number (8)MAXSIZE UNLIMITED|number(9)MININUM EXTENT number(10)BLOCKSIZE number(11)ONLINE|OFFLINE(12)LOGGING|NOLOGGING(13)FORCE LOGGING(14)DEFAULT STORAGE storage(15)COMPRESS|NOCOMPRESS(16)PERMANENT|TEMPORARY(17)EXTENT MANAGEMENT DICTIONARY|LOCAL(18)AUTOALLOCATE|UNIFORM SIZE number(19)SEGMENT SPACE MANAGEMENT AUTO|MANUAL54.1.2 表空间状态属性41在线(ONLINE)语句形式如下：|ALTER TABLESPACE tablespace_name ONLINE;42离线(OFFLINE)语句形式如下：|ALTER TABLESPACE tablespace_name OFFLINE parameter;43只读(READ ONLY)语句形式如下：|ALTER TABLESPACE tablespace_name READ ONLY;44读写(READ WRITE)语句形式如下：|ALTER TABLESPACE tablespace_name READ WRITE;64.1.3 重命名表空间4重命名表空间的语法如下：ALTER TABLESPACE tablespace_name RENAME TO new_tablespace_name;4【例【例4.4】修改myspace表空间的名称为myspace2，语句如下：ALTER TABLESPACE myspace RENAME TO myspace2;74.1.4 修改表空间中数据文件的大小4【例【例4.5】通过数据字典dba_free_space查看myspace表空间的空闲空间信息，如下：SQL SELECT tablespace_name,bytes,blocks 2 FROM dba_free_space 3 WHERE tablespace_name=MYSPACE;TABLESPACE_NAME BYTES BLOCKS-MYSPACE 20905984 25524其中，bytes字段以字节的形式表示表空间的空闲空间大小；blocks字段则以数据块数目的形式表示表空间空闲空间的大小。84.1.4 修改表空间中数据文件的大小4【例【例4.6】通过数据字典dba_data_files查看myspace表空间的数据文件信息，如下：SQL COLUMN file_name FORMAT A35;SQL COLUMN tablespace_name FORMAT A15;SQL SELECT tablespace_name,file_name,bytes 2 FROM dba_data_files 3 WHERE tablespace_name=MYSPACE;TABLESPACE_NAME FILE_NAME BYTES -MYSPACE E:APPADMINISTRATORORADATAORCL20971520 MYSPACE.DBF4其中，file_name字段表示数据文件的名称与路径；bytes字段表示数据文件的大小。94.1.4 修改表空间中数据文件的大小4【例【例4.7】修改myspace表空间对应的数据文件的大小，如下：SQL ALTER DATABASE 2 DATAFILE E:APPADMINISTRATORORADATAORCLMYSPACE.DBF 3 RESIZE 40M;数据库已更改。104.1.5 增加表空间的数据文件4增加新的数据文件需要使用ALTER TABLESPACE语句，其语法如下：ALTER TABLESPACE tablespace_nameADD DATAFILE file_name SIZE number K|M AUTOEXTEND OFF|ON NEXT number K|M MAXSIZE UNLIMITED|number K|M ,.;114.1.5 增加表空间的数据文件4【例【例4.8】为myspace表空间增加两个新的数据文件，如下：SQL ALTER TABLESPACE myspace 2 ADD DATAFILE 3 E:appAdministratororadataorclmyspace02.dbf 4 SIZE 10M 5 AUTOEXTEND ON NEXT 5M MAXSIZE 40M,6 E:appAdministratororadataorclmyspace03.dbf 7 SIZE 10M 8 AUTOEXTEND ON NEXT 5M MAXSIZE 40M;表空间已更改。4上述语句为myspace表空间在E:appAdministratororadataorcl目录下增加了两个数据文件，名称分别为myspace02.dbf和myspace03.dbf。124.1.6 删除表空间的数据文件4删除表空间的数据文件的语法如下：ALTER TABLESPACE tablespace_nameDROP DATAFILE file_name;4【例【例4.9】删除myspace表空间数据文件E:appAdministratororadataorclmyspace03.dbf，如下：SQL ALTER TABLESPACE myspace 2 DROP DATAFILE E:appAdministratororadataorclmyspace03.dbf;表空间已更改。134.1.7 修改表空间中数据文件的自动扩展性4在创建表空间时，可以设置数据文件的自动扩展性。在为表空间增加新的数据文件时，也可以设置新数据文件的自动扩展性。而对于已创建的表空间中的已有数据文件，则可以使用ALTER DATABASE语句修改其自动扩展性。语法如下：ALTER DATABASE DATAFILE file_nameAUTOEXTEND OFF|ON NEXT number K|M MAXSIZE UNLIMITED|number K|M 144.1.7 修改表空间中数据文件的自动扩展性4【例【例4.10】修改myspace表空间中数据文件的自动扩展性，如下：SQL ALTER DATABASE 2 DATAFILE E:appAdministratororadataorclmyspace02.dbf 3 AUTOEXTEND OFF;数据库已更改。SQL ALTER DATABASE 2 DATAFILE E:appAdministratororadataorclmyspace02.dbf 3 AUTOEXTEND ON 4 NEXT 5M MAXSIZE 40M;数据库已更改。4 上述两条SQL语句中，第一条语句用于关闭myspace表空间的myspace02.dbf文件的自动扩展性，第二条语句用于再次为myspace02.dbf文件设置自动扩展性。154.1.8 修改表空间中数据文件的状态4数据文件的状态主要有3种：ONLINE、OFFLINE和OFFLINE DROP。4设置数据文件状态的语法如下：ALTER DATABASE DATAFILE file_name ONLINE|OFFLINE|OFFLINE DROP4其中，ONLINE表示数据文件可以使用；OFFLINE表示数据文件不可使用，用于数据库运行在归档模式下的情况；OFFLINE DROP与OFFLINE一样用于设置数据文件不可用，但它用于数据库运行在非归档模式下的情况。164.1.9 移动表空间中的数据文件4【例【例4.12】移动myspace表空间中数据文件myspace02.dbf的步骤如下。(1)修改myspace表空间的状态为OFFLINE，如下：|SQL ALTER TABLESPACE myspace OFFLINE;|表空间已更改。表空间已更改。(2)将磁盘中的myspace02.dbf文件移动到新的目录中(3)使用ALTER TABLESPACE语句，将myspace表空间中myspace02.dbf文件的原名称和路径修改为新名称和路径(4)修改myspace表空间的状态为ONLINE，如下：|SQL ALTER TABLESPACE myspace ONLINE;|表空间已更改。表空间已更改。检查文件是否移动成功，也就是检查myspace表空间的数据文件中是否包含了新的数据文件。174.1.10 删除表空间4删除表空间需要使用DROP TABLESPACE语句，其语法如下：DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES 4语法说明如下。(1)INCLUDING CONTENTS(2)AND DATAFILES4【例【例4.13】删除表空间myspace，并同时删除该表空间中的所有数据库对象，以及操作系统中与之相对应的数据文件，如下：SQL DROP TABLESPACE myspace 2 INCLUDING CONTENTS AND DATAFILES;表空间已删除。184.2 临时表空间4临时表空间是一个磁盘空间，主要用于存储用户在执行ORDER BY等语句进行排序或汇总时产生的临时数据，它是所有用户公用的。4默认情况下，所有用户都使用temp作为临时表空间。但是也允许使用其他表空间作为临时表空间，这需要在创建用户时进行指定。194.2.1 创建临时表空间4创建临时表空间时需要使用TEMPORARY关键字，并且与临时表空间对应的是临时文件，由TEMPFILE关键字指定，而数据文件由DATAFILE关键字指定。4【例【例4.14】创建一个临时表空间mytemp，如下：SQL CREATE TEMPORARY TABLESPACE mytemp 2 TEMPFILE F:oraclefilemytemp.dbf 3 SIZE 10M 4 AUTOEXTEND ON NEXT 2M MAXSIZE 20M;表空间已创建。204.2.1 创建临时表空间4【例【例4.15】通过数据字典v$tempfile，查看临时表空间mytemp的临时文件信息，如下：SQL COLUMN file_name FORMAT A40;SQL COLUMN tablespace_name FORMAT A15;SQL SELECT tablespace_name,file_name,bytes 2 FROM dba_temp_files 3 WHERE tablespace_name=MYTEMP;TABLESPACE_NAME FILE_NAME BYTES-MYTEMP F:ORACLEFILEMYTEMP.DBF 10485760214.2.2 创建与管理临时表空间组41创建临时表空间组临时表空间组不需要特别创建，只需要在创建临时表空间时，使用TABLESPACE GROUP语句为其指定一个组即可。42查看临时表空间组信息如果要查询一个临时表空间组中的临时表空间信息，可以使用数据字典dba_tablespace_ groups。43移动临时表空间可以使用ALTER TABLESPACE语句，将临时表空间从一个组移动到另一个组中，实际上也就是修改临时表空间所在的组。目标组同样可以是已存在的，也可以是不存在的。44删除临时表空间组一个临时表空间组中至少需要存在一个临时表空间，当组中的所有临时表空间都被删除或移动到其他组中后，该组就被自动删除了。224.3 大文件表空间4大文件表空间是Oracle 10g引进的一个新表空间类型，主要用于解决存储文件大小不够的问题。4创建大文件表空间需要使用BIGFILE关键字，而且只能为其指定一个数据文件或临时文件。4【例【例4.19】创建一个大文件表空间mybigspace，如下：SQL CREATE BIGFILE TABLESPACE mybigspace 2 DATAFILE F:oraclefilemybigspace.dbf 3 SIZE 10M;表空间已创建。234.3 大文件表空间4【例【例4.20】通过数据字典dba_tablespaces查看当前数据库中的表空间的类型，如下：SQL SELECT tablespace_name,bigfile 2 FROM dba_tablespaces;TABLESPACE_NAME BIG-SYSTEM NO SYSAUX NO UNDOTBS1 NO TEMP NO USERS NO MYTEMP NO MYBIGSPACE YES 已选择7行。4 如果bigfile字段值为NO，则表示对应的表空间的类型不是BIGFILE，即为SMALLFILE；如果bigfile字段值为YES，则表示对应的表空间的类型为BIGFILE。244.4 非标准数据块表空间4非标准数据块表空间是指其数据块大小不基于标准数据块大小的表空间。4使用SHOW PARAMETER语句可以查看db_block_size参数的信息，如下：SQL SHOW PARAMETER db_block_size;NAME TYPE VALUE-db_block_size integer 8192254.4 非标准数据块表空间4【例【例4.21】创建一个非标准数据块表空间nonstandard，其数据块大小设置为16KB。步骤如下。(1)使用SHOW PARAMWTER语句查看数据缓冲区参数db_16k_cache_size的值(2)使用ALTER SYSTEM语句修改参数db_16k_cache_size的值(3)创建表空间nonstandard，指定其数据块大小为16KB 264.5 设置默认表空间4Oracle允许使用非users表空间作为默认的永久性表空间，使用非temp表空间作为默认临时表空间。设置默认表空间需要使用ALTER DATABASE语句，语法如下：ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tablespace_name;4如果使用TEMPORARY关键字，则表示设置默认临时表空间；如果不使用该关键字，则表示设置默认永久性表空间。274.5 设置默认表空间4【例【例4.22】将myspace表空间设置为默认的永久性表空间，将mytemp表空间设置为默认临时表空间，如下：SQL ALTER DATABASE DEFAULT TABLESPACE myspace;数据库已更改。SQL ALTER DATABASE DEFAULT TEMPORARY TABLESPACE mytemp;数据库已更改。查询数据字典database_properties，检查默认表空间是否设置成功，如下：SQL SELECT property_name,property_value,description 2 FROM database_properties 3 WHERE property_name 4 IN(DEFAULT_PERMANENT_TABLESPACE,DEFAULT_TEMP_TABLESPACE);PROPERTY_NAME PROPERTY_VALUE DESCRIPTION-DEFAULT_TEMP_TABLESPACE MYTEMP Name of default temporary tablespace DEFAULT_PERMANENT_TABLESPACE MYSPACE Name of default permanent tablespace284.6 撤消表空间4用户对数据库中的数据进行修改后，Oracle将会把修改前的数据存储到撤消表空间中，如果用户需要对数据进行恢复，就会使用到撤消表空间中存储的撤消数据。4本节将介绍如何创建与管理撤消表空间。对于具体的数据撤消操作将在本书Oracle闪回技术章节中介绍。294.6.1 管理撤消表空间的方式4 Oracle 11g支持两种管理撤消表空间的方式：回退段撤消管理(Rollback Segments Undo，RSU)和自动撤消管理(System Managed Undo，SMU)。4 1自动撤消管理 如果选择使用自动撤消管理方式，则应将参数undo_management的值设置为AUTO，并且需要在数据库中创建一个撤消表空间。默认情况下，Oracle系统在安装时会自动创建一个撤消表空间undotbs1。系统当前所使用的撤消表空间由参数undo_tablespace决定。使用SHOW PARAMETER undo语句可以查看当前数据库的撤消表空间的设置，如下：|SQL SHOW PARAMETER undo;|NAME TYPE VALUE|-|undo_management string AUTO|undo_retention integer 900|undo_tablespace string UNDOTBS1304.6.1 管理撤消表空间的方式42回退段撤消管理如果选择使用回退段撤消管理方式，则应将参数undo_management的值设置为MANUAL，并且需要设置下列参数。|rollback_segments：设置数据库所使用的回退：设置数据库所使用的回退段名称。段名称。|transactions：设置系统中的事务总数。：设置系统中的事务总数。|transactions_per_rollback_segment：指定：指定回退段可以服务的事务个数。回退段可以服务的事务个数。|max_rollback_segments：设置回退段的最大：设置回退段的最大个数。个数。314.6.2 创建与管理撤消表空间41创建撤消表空间创建撤消表空间需要使用CREATE UNDO TABLESPACE语句，与创建普通表空间类似，但也有其特定的限制，说明如下：|撤消表空间只能使用本地化管理表空间类型，即撤消表空间只能使用本地化管理表空间类型，即EXTENT MANAGEMENT子句只能指定子句只能指定LOCAL(默认值默认值)。|撤消表空间的盘区管理方式只能使用撤消表空间的盘区管理方式只能使用AUTOALLOCATE(默默认值认值)，即由，即由Oracle系统自动分配盘区大小。系统自动分配盘区大小。|撤消表空间的段的管理方式只能为手动管理方式，即撤消表空间的段的管理方式只能为手动管理方式，即SEGMENT SPACE MANAGEMENT只能指定只能指定MANUAL。如果是创建普通表空间，则此选项默认为如果是创建普通表空间，则此选项默认为AUTO，而如果是，而如果是创建撤消表空间，则此选项默认为创建撤消表空间，则此选项默认为MANUAL。324.6.2 创建与管理撤消表空间42修改撤消表空间的数据文件由于撤消表空间主要由Oracle系统自动管理，所以对撤消表空间的数据文件的修改也主要限于以下几种形式：为撤消表空间添加新的数据文件。移动撤消表空间的数据文件。设置撤消表空间的数据文件的状态为ONINE或OFFLINE。334.6.2 创建与管理撤消表空间43切换撤消表空间一个数据库中可以有多个撤消表空间，但数据库一次只能使用一个撤消表空间。默认情况下，数据库使用的是系统自动创建的undotbs1撤消表空间。如果要将数据库使用的撤消表空间切换成其他表空间，修改参数undo_tablespace的值即可，这需要使用ALTER SYSTEM语句。切换撤消表空间后，数据库中新事务的撤消数据将保存在新的撤消表空间中。344.6.2 创建与管理撤消表空间44修改撤消记录的保留时间在Oracle中，撤消表空间中的撤消记录的保留时间由参数undo_retention决定，默认为900秒，900秒后，撤消记录将从撤消表空间中清除，这样可以防止撤消表空间的迅速膨胀。45删除撤消表空间删除撤消表空间同样需要使用DROP TABLESPACE语句，但删除的前提是该撤消表空间此时没有被数据库使用。如果需要删除正在被使用的撤消表空间，则应该先进行撤消表空间的切换操作。354.7 与表空间和数据文件相关的数据字典4常用的相关数据字典如表4-2所示。名 称说 明dba_data_files记录数据库实例中所有数据文件及表空间的信息v$datafile记录数据库实例中数据文件被使用情况的统计信息v$datafile_header记录数据文件使用中的头部信息dba_tablespaces记录数据库所有表空间的信息dba_free_space记录表空间中空闲空间的信息dba_extents记录段的扩展信息dba_temp_files记录临时表空间及其临时文件的信息dba_tablespace_groups记录临时表空间及其成员的信息v$tempfile记录数据库实例中临时文件被使用情况的统计信息v$undostat记录撤消表空间的统计信息，用于对撤消表空间进行监视和调整；管理员可以使用此数据字典估算当前负载下所需的撤消表空间的大小。而Oracle也正是使用此数据字典来实现对撤消表空间的自动管理v$rollstat记录撤消表空间中所有撤消段的信息v$transaction记录所有事务所使用的撤消段信息dba_undo_extents记录撤消表空间中每个盘区所对应的事务提交时间364.8 实验指导为图书管理系统创建表空间4实验指导4-1：为图书管理系统创建表空间1永久性表空间|首先为图书管理系统创建永久性表空间首先为图书管理系统创建永久性表空间bookspace(后续关后续关于图书管理系统的数据信息都将存储在该表空间中于图书管理系统的数据信息都将存储在该表空间中)，如下：，如下：v参见教材参见教材P852临时表空间|再为图书管理系统创建存储临时数据的临时表空间再为图书管理系统创建存储临时数据的临时表空间booktemp，如下：，如下：v参见教材参见教材P853撤消表空间|最后为图书管理系统创建存储撤消数据的撤消表空间最后为图书管理系统创建存储撤消数据的撤消表空间bookundo，如下：，如下：v参见教材参见教材P85374.9 习 题4参见教材P8538演讲完毕，谢谢观看！