Oracle数据库应用与开发案例教程 - 万水书苑-出版资源网

(1)

表空间是 Oracle 数据库中最大的逻辑存储结构，一个 Oracle 数据库由一个或多个表空间组成，表空间与操作系统的数据文件相对应，每个表空间由一个或多个数据文件组成。Oracle 中的数据逻辑上存储于表空间中，而物理上则存储于属于表空间的数据文件中。本章将主要介绍 Oracle 的基本表空间、临时表空间、大文件表空间、非标准数据库表空间和撤销表空间等表空间的创建和管理。 l 表空间的作用及默认表空间 l 各种表空间和数据文件的创建 l 改变表空间和数据文件的名称、状态及可用性 l 数据文件的移动 l 扩展已有表空间 l 表空间和数据文件的删除 l 通过数据字典查看表空间和数据文件信息

3.1 表空间和数据文件概述

表空间是 Oracle 数据库中最大的逻辑存储结构，它与操作系统中的数据文件相对应，用于存储数据库中用户创建的所有内容。可以将表空间看作是数据库对象的容器，表空间的逻辑存储单位是段，数据库的所有对象、方案对象都被逻辑地保存在表空间中。表空间提供了一套有效组织数据的方法，它不仅对数据库的性能有重要的影响，而且对简化管理有明显的作用。一个表空间由一个或多个数据文件组成，数据文件是存储数据库所有逻辑结构数据的操作系统文件。 3.1.1 表空间的作用 Oracle 数据库把方案对象逻辑地存储在表空间中，同时把它们物理地存储在数据文件中，而数据文件又物理地分布在各个磁盘中。表空间具有以下作用：（1）控制数据库所占用的磁盘空间。（2）控制用户所占用的空间配额。（3）通过将不同类型数据部署到不同的位置，可以提高数据库的 I/O 性能，并且有利于

(2)

备份和恢复等管理操作。（4）可以将表空间设置成只读状态而保持大量的静态数据。 3.1.2 默认表空间对于 Oracle 中新建的数据库，系统自动添加了 6 个默认的表空间，分别是：（1）EXAMPLE 表空间：用于安装 Oracle 数据库使用的示例数据库。（2）SYSTEM 表空间：系统表空间，用于存放数据字典对象，包括表、视图、存储过程的定义等，默认的数据文件为“system01.dbf” 。

（3）SYSAUX 表空间：SYSAUX 表空间是在 Oracle 10g 中引入的，作为 SYSTEM 表空间的一个辅助表空间，其主要作用是为了减少 SYSTEM 表空间的负荷，默认的数据文件是 “sysaux01.dbf” ；这个表空间和 SYSTEM 表空间一样不能被删除、更名、传递或设置为只读。（4）TEMP 表空间：临时表空间，用于存储数据库运行过程中由排序和汇总等操作产生的临时数据信息，默认的数据文件是“temp01.dbf” 。（ 5 ） UNDOTBS1 表空间：撤销表空间，用于存储撤销信息，默认的数据文件为 “undotbs01.dbf” 。（6）USERS 表空间：用户表空间，存储数据库用户创建的数据库对象，默认的数据文件为“user01.dbf” 。 3.1.3 表空间的状态属性通过设置表空间的状态属性，可以对表空间的使用进行管理。表空间的状态属性主要有联机、读写、只读和脱机等四种状态，其中只读状态与读写状态属于联机状态的特殊情况。 1．联机状态（ONLINE）表空间通常处于联机状态，以便数据库用户访问其中的数据。 2．读写状态（READWRITE）读写状态是表空间的默认状态，当表空间处于读写状态时，用户可以对表空间进行正常的数据查询、更新和删除等各种操作。读写状态实际上为联机状态的一种特殊情况，只有当表空间处于只读状态下才能转换到读写状态。 3．只读状态（READONLY）当表空间处于只读状态时，任何用户都无法向表空间中写入数据，也无法修改表空间中已有的数据，用户只能以 SELECT 方式查询只读表空间中的数据。将表空间设置成只读状态可以避免数据库中的静态数据被修改。如果需要更新一个只读表空间，需要将该表空间转换到可读写状态，完成数据更新后再将表空间恢复到只读状态。 4．脱机状态（OFFLINE）当一个表空间处于脱机状态时， Oracle 不允许任何访问该表空间中数据的操作。当数据库管理员需要对表空间执行备份或恢复等维护操作时，可以将表空间设置为脱机状态；如果某个表空间暂时不允许用户访问，DBA 也可以将这个表空间设置为脱机状态。【例 31】通过数据字典 dba_tablespaces，查看当前数据库中表空间的状态。 SQL> SELECT TABLESPACE_NAME,STATUS 2 FROM dba_tablespaces;

(3)

TABLESPACE_NAME STATUS SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS ONLINE EXAMPLE ONLINE ZCGL_TBS ONLINE ZCGL_TEMP ONLINE ZCGL_UNDO ONLINE ZCGL_TBS1 ONLINE ZCGL_TEMP1 ONLINE ZCGL_UNDO1 ONLINE ZCGL_TBS_4K ONLINE ZCGL_BIGTBS ONLINE MYTS ONLINE 已选择 15 行。 3.1.4 数据文件数据文件是 Oracle 数据库中用来存储各种数据的地方，在创建表空间的同时将为表空间创建相应的数据文件。一个数据文件只能属于一个表空间，一个表空间可以有多个数据文件。在对数据文件进行管理时，数据库管理员可以修改数据文件的大小、名称、增长方式和存放位置，并能够删除数据文件。

3.2 创建表空间

在创建 Oracle 数据库时会自动创建 SYSTEM、SYSAUX 和 USERS 等表空间，用户可以使用这些表空间进行各种数据操作。但在实际应用中，如果使用系统创建的这些表空间会加重它们的负担，严重影响系统的 I/O 性能，因此 Oracle 建议根据实际需求来创建不同的非系统表空间，用来存储所有的用户对象和数据。创建表空间需要有 CREATE TABLESPACE 系统权限。在创建表空间时应该事先创建一个文件夹，用来放置新创建表空间的各个数据文件。当通过添加数据文件来创建一个新的表空间或修改一个表空间时，应该给出文件大小和带完整存取路径的文件名。在表空间的创建过程中，Oracle 会完成以下工作：（1）在数据字典和控制文件中记录下新创建的表空间。（2）在操作系统中按指定的位置和文件名创建指定大小的操作系统文件，作为该表空间对应的数据文件。（3）在预警文件中记录下创建表空间的信息。

(4)

3.2.1 创建表空间的一般命令 1．创建表空间命令的语法结构 CREATE [TEMPORARY|UNDO] TABLESPACE tablespace_name [DATAFILE|TEMPFILE file_spec1 [,file_spec2] ...SIZE size K | M [REUSE] [MININUM EXTENT integer K | M] [BLOCKSIZE integer k] [LOGGING|NOLOGGING] [FORCE LOGGING] [DEFAULT {data_segment_compression} storage_clause] [ONLINE|OFFLINE] [PERMANENT|TEMPORARY] [EXTENT MANAGEMENT DICTIONARY|LOCAL] [AUTOALLOCATE|UNIFORM SIZE number] [SEGMENT MANAGEMENT AUTO|MANUAL] 2．语法说明（1）TEMPORARY|UNDO 说明系统创建表空间的类型。TEMPORARY 表示创建一个临时表空间。UNDO 表示创建一个撤销表空间。创建表空间时，如果没有使用关键字 TEMPORARY 或 UNDO，表示创建永久性表空间。（2）tablespace_name 指定表空间的名称。（3）DATAFILE file_spec1 指定与表空间关联的数据文件。file_spec1 需要指定数据文件路径和文件名。如果要创建临时表空间，需要使用子句 TEMPFILE file_spec1。（4）SIZE size K | M [REUSE] 指定数据文件的大小。如果要创建的表空间的数据文件在指定的路径中已经存在，可以使用 REUSE 关键字将其删除并重新创建该数据文件。（5）MININUM EXTENT integer K | M 指出在表空间中盘区的最小值。（6）BLOCKSIZE integer k 如果在创建永久性表空间时不采用参数 db_block_size 所指定的数据块的大小，可以使用此子句设定一个数据块的大小。（7）LOGGING|NOLOGGING 这个子句声明这个表空间上所有的用户对象的日志属性（缺省是 LOGGING）。（8）FORCE LOGGING 使用这个子句指出表空间进入强制日志模式，这时表空间上对象的任何改变都将产生日志，并忽略 LOGGING|NOLOGGING 选项。在临时表空间和撤销表空间中不能使用这个选项。（9）DEFAULT storage_clause 声明缺省的存储子句。

(5)

（10）ONLINE|OFFLINE 将表空间的状态设置为联机状态（ONLINE）或脱机状态（OFFLINE）。ONLINE 是缺省值，表示表空间创建后立即可以使用；OFFLINE 表示不可以使用。（11）PERMANENT|TEMPORARY 指定表空间中数据对象的保存形式，PERMANENT 表示永久存放，TEMPORARY 表示临时存放。（12）EXTENT MANAGEMENT DICTIONARY|LOCAL 指定表空间的管理方式。如果希望本地管理表空间，声明 LOCAL 选项，这是默认选项，本地管理表空间是通过位图进行管理的；如果希望以数据字典的形式管理表空间，声明 DICTIONARY 选项。（13）AUTOALLOCATE|UNIFORM SIZE number 指定表空间的盘区大小。AUTOALLOCATE 表示盘区大小由 Oracle 自动分配；UNIFORM SIZE number 表示表空间中所有盘区大小统一为 number。（14）SEGMENT MANAGEMENT AUTO|MANUAL 指定段空间的管理方式，自动或者手动，默认为 AUTO。 3.2.2 创建（永久）表空间如果在使用 CREATE TABLESPACE 语句创建表空间时，没有使用关键字 TEMPORARY 或 UNDO，或者使用了关键字 PERMANENT，则表示创建的表空间是永久保存数据库对象数据的永久表空间。 1．创建本地管理方式的永久表空间根据表空间对盘区的管理方式，表空间可以分为数据字典管理的表空间和本地管理的表空间。本地管理表空间使用位图的方法来管理表空间中的数据块，从而避免了使用 SQL 语句引起的系统性能下降，Oracle 建议在建立表空间时选择本地管理方式。从 Oracle 9i R2 后，系统创建的表空间在默认情况下都是本地管理表空间。在使用 CREATE TABLESPACE 语句创建表空间时，如果省略了 EXTENT MANAGEMENT 子句，或者显式地使用了 EXTENT MANAGEMENT LOCAL 子句，表示所创建的是本地管理方式的表空间。【例 32】创建永久表空间 ZCGL_TBS1，采用本地管理方式。 SQL> CREATE TABLESPACE ZCGL_TBS1 2 DATAFILE 'E:\ORACLE11G\ZCGL\ZCGL_TBS1_01.DBF' 3 SIZE 20M 4 EXTENT MANAGEMENT LOCAL; 表空间已创建。说明：如果在数据文件 DATAFILE 子句中没有指定文件路径，Oracle 会在默认的路径中创建这些数据文件，默认的路径取决于操作系统。如果在指定的路径中有同名的操作系统文件存在，则需要在数据文件子句中使用 REUSE 选项；如果数据库中已经存在同名的表空间，则必须先删除该表空间。 2．创建 UNIFORM 盘区分配方式的永久表空间如果在 EXTENT MANAGEMENT 子句中指定了 UNIFORM 关键字，则说明表空间中所有

(6)

的盘区都具有统一的大小。【例 33】创建永久表空间 ZCGL_TBS2，采用本地管理方式，表空间中所有分区大小都是 128KB。 SQL> CREATE TABLESPACE ZCGL_TBS2 2 DATAFILE 'E:\ORACLE11G\ZCGL\ZCGL_TBS2_01.DBF' 3 SIZE 20M 4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K; 表空间已创建。说明：如果在 UNIFORM 关键字后没有指定 SIZE 参数值，则 SIZE 参数值为 1MB。 3．创建 ALLOCATE 盘区分配方式的表空间

如果在 EXTENT MANAGEMENT 子句中指定了 AUTOALLOCATE 关键字，则说明盘区

大小由 Oracle 进行自动分配，不需要指定大小，盘区大小的指定方式默认是 AUTOALLOCATE。【例 34】创建一个 AUTOALLOCATE 方式的本地管理表空间。 SQL> CREATE TABLESPACE ZCGL_TBS3 2 DATAFILE 'E:\ORACLE11G\ZCGL\ZCGL_TBS3_01.DBF' 3 SIZE 20M 4 AUTOEXTEND ON NEXT 5M 5 MAXSIZE 100M 6 AUTOALLOCATE; 表空间已创建。说明：本题中表空间 ZCGL_TBS3 的初始大小为 20M，自动增长，每次增长 5M，最大可以达到 100M，采用本地 AUTOALLOCATE 方式管理表空间。 3.2.3 创建临时表空间临时表空间主要用来存储用户在执行 ORDER BY 等语句进行排序或汇总时产生的临时数据信息。通过使用临时表空间，Oracle 能够使带有排序等操作的 SQL 语句获得更高的执行效率。在数据库中创建用户时必须为用户指定一个临时表空间来存储该用户生成的所有临时表数据。

创建临时表空间时需要使用 CREATE TEMPORARY TABLESPACE 命令。如果在数据库运行过程中经常发生大量的并发排序，那么应该创建多个临时表空间来提高排序性能。【例 35】创建一个名 ZCGL_TEMP1 的临时表空间，大小为 20M，并使用 UNIFORM 选项指定盘区大小，统一为 128K。 SQL> CREATE TEMPORARY TABLESPACE ZCGL_TEMP1 2 TEMPFILE 'E:\ORACLE11G\ZCGL\ZCGL_TEMP1_01.dbf' 3 SIZE 20M 4 UNIFORM SIZE 128K; 表空间已创建。说明：临时表空间不使用数据文件，而使用临时文件，所以在创建临时表空间时，必须将表示数据文件的关键字 DATAFILE 改为表示临时文件的关键字 TEMPFILE。临时文件只能与临时表空间一起使用，不需要备份，也不会把数据修改记录到重做日志中。

(7)

3.2.4 创建撤销表空间 Oracle 使用撤销表空间来管理撤销数据。当用户对数据库中的数据进行 DML 操作时， Oracle 会将修改前的旧数据写入到撤销表空间中；当需要进行数据库恢复操作时，用户会根据撤销表空间中存储的这些撤销数据来对数据进行恢复，所以说撤销表空间用于确保数据的一致性。撤销表空间只能使用本地管理方式，在临时表空间、撤销表空间上都不能创建永久方案对象（表、索引、簇）。可以通过执行 CREATE UNDO TABLESPACE 选项来创建 UNDO 表空间。【例 36】创建名称为 ZCGL_UNDO1 的撤销表空间，该表空间的空间管理方式为本地管理，大小为 20M，盘区的大小由系统自动分配。 SQL> CREATE UNDO TABLESPACE ZCGL_UNDO1 2 DATAFILE 'E:\ORACLE11G\ZCGL\ZCGL_ UNDO1_01.dbf' 3 SIZE 20M; 表空间已创建。说明：创建表空间时，表空间盘区大小默认为 AUTOALLOCATE，所以如果在创建表空间的命令中省略了关键字 AUTOALLOCATE，那么盘区的大小就是由系统自动分配的方式。 3.2.5 创建非标准块表空间

Oracle 数据块是 Oracle 在数据文件上执行 I/O 操作的最小单位，其大小应该设置为操作系统物理块的整数倍。初始化参数 DB_BLOCK_SIZE 定义了标准数据块的大小，在创建数据库后就不能再修改该参数的值。当创建表空间时，如果不指定 BLOCKSIZE 选项，那么该表空间将采用由参数 DB_BLOCK_SIZE 决定的标准数据块大小。Oracle 允许用户创建非标准块表空间，在 CREATE TABLESPACE 命令中使用 BLOCKSIZE 选项来指定表空间数据块的大小。创建的非标准块表空间的数据块大小也应该是操作系统物理块的倍数。在建立非标准块表空间之前，必须为非标准块分配非标准数据高速缓冲区参数 db_nk_cache_size，并且数据高速缓存的尺寸可以动态修改。【例 37】为 4KB 数据块设置 10MB 的高速缓冲区，然后创建数据块大小为 4KB 的非标准数据块表空间。 ① 查看 db_block_size 参数的信息。 SQL> SHOW PARAMETER db_block_size;

NAME TYPE VALUE

db_block_size integer 8192 说明：如果 db_block_size 的参数值为 8K，就不能再设置 db_8k_cache_size 参数的值，否则会出现如下错误。 SQL> ALTER SYSTEM SET db_8k_cache_size=10M; ALTER SYSTEM SET db_8k_cache_size=10M * 第 1 行出现错误:

(8)

ORA32017: 更新 SPFILE 时失败 ORA00380: 无法指定 db_8k_cache_size, 因为 8K 是标准块大小 ② 为 4KB 数据块设置 10MB 的高速缓冲区参数 db_4k_cache_size。 SQL> ALTER SYSTEM SET db_4k_cache_size=10M; 系统已更改。说明：BLOCKSIZE 参数与 db_nk_cache_size 参数值的对应关系如下：如果 BLOCKSIZE 参数的值设置为 4K，就必须设置 db_4k_cache_size 参数的值；如果 BLOCKSIZE 参数的值设置为 2K，就必须设置 db_2k_cache_size 参数的值。 ③ 为非标准块分配了非标准数据高速缓存后，就可以创建非标准块表空间了。 SQL> CREATE TABLESPACE ZCGL_TBS_4K 2 DATAFILE 'E:\ORACLE11G\ZCGL\ZCGL_ TBS_4K.dbf' 3 SIZE 2M 4 BLOCKSIZE 4K; 表空间已创建。 3.2.6 创建大文件表空间从 Oracle 10g 开始，引入了大文件表空间，用于解决不够存储文件大小的问题。这种表空间只能包括一个数据文件或临时文件，其对应的文件可以包含 4G 个数据块。如果数据块大小为 8KB，大文件表空间的数据文件最大可以达到 32TB；如果块的大小是 32KB，那么大文件表空间的数据文件最大可以达到 128TB。因此能够显著提高 Oracle 数据库的存储能力。【例 38】创建名称为 ZCGL_BIGTBS 的大文件表空间，其大小为 20MB。 SQL> CREATE BIGFILE TABLESPACE ZCGL_BIGTBS 2 DATAFILE 'E:\ORACLE11G\ZCGL\ ZCGL_BIGTBS.dbf' 3 SIZE 20M; 表空间已创建。

3.3 维护表空间和数据文件

对数据库管理员而言，需要经常维护表空间。各种维护表空间的操作包括重命名表空间和数据文件，改变表空间和数据文件的状态，设置默认表空间，扩展表空间，删除表空间及数据文件，以及查看表空间和数据文件的信息等。用户可以使用 ALTER TABLESPACE 命令完成维护表空间和数据文件的各种操作，但该用户必须拥有 ALTER TABLESPACE 或 ALTER DATABASE 系统权限。

3.3.1 重命名表空间和数据文件 1．重命名表空间

通过使用 ALTER TABLESPACE 的 RENAME 选项，就可以修改表空间的名称。需要注意的是，SYSTEM 表空间和 SYSAUX 表空间的名称不能被修改，如果表空间或其中的任何数据

(9)

文件处于 OFFLINE 状态，该表空间的名称也不能被改变。重命名表空间的一般语法格式为：

ALTER TABLESPACE tablespace_name RENAME TO tablespace_new_name;

说明：tablespace_name 为重命名前表空间名称，tablespace_new_name 为新的表空间名称。【例 39】将表空间 ZCGL_TBS3 改名为 ZCGL_TBS3NEW。 SQL> ALTER TABLESPACE ZCGL_TBS3 RENAME TO ZCGL_TBS3NEW; 表空间已更改。说明：虽然表空间的名称被修改了，但表空间对应的数据文件、数据文件的位置和名称都没有变化，所有的 SQL 语句仍能正常运行。 2．重命名数据文件当创建数据文件后，可以改变数据文件的名称。改变数据文件的名称的具体步骤如下： ① 使表空间处于 OFFLINE 状态。 SQL> ALTER TABLESPACE ZCGL_TBS1 OFFLINE NORMAL; 表空间已更改。 ② 用操作系统命令重命名数据文件。 SQL> HOST RENAME E:\ORACLE11G\ZCGL\ZCGL_TBS1_02.dbf ZCGL_TBS1_03.dbf 说明：HOST 表示需要在 SQL Plus 中执行操作系统命令 RENAME。 ③ 使用带 RENAME DATAFILE 子句的 ALTER TABLESPACE 语句改变数据文件名称。 SQL> ALTER TABLESPACE ZCGL_TBS1 2 RENAME DATAFILE 'E:\ORACLE11G\ZCGL\ZCGL_TBS1_02.dbf' 3 TO 4 'E:\ORACLE11G\ZCGL\ZCGL_TBS1_03.dbf'; 表空间已更改。 ④ 将表空间重新设置为联机状态。 SQL> ALTER TABLESPACE ZCGL_TBS1 ONLINE; 表空间已更改。 3.3.2 改变表空间和数据文件状态表空间主要有联机、读写、只读和脱机等四种状态，因此修改表空间的状态包括使表空间只读，使表空间可读写，使表空间脱机或联机。 1．设置表空间为只读状态如果表空间只用于存放静态数据，或者该表空间需要被迁移到其他数据库时，应该将表空间的状态修改为只读，可以通过在 ALTER TABLESPACE 语句中使用 READ ONLY 子句来完成这一操作。将表空间设置为只读状态时，该表空间必须为 ONLINE，并且该表空间不能包含任何撤销段。系统表空间 SYSTEM 和 SYSAUX 不能设置为只读状态。

【例 310】将表空间 ZCGL_TBS1 设置为只读状态。

(10)

表空间已更改。

说明：当表空间设置为只读状态时，就不能执行 INSERT 操作向其中添加数据了，但仍然可以执行 DROP 操作，删除该表空间上的对象。

2．设置表空间为可读写

若想将表空间恢复为读写状态时，需要在 ALTER TABLESPACE 语句中使用 READ WRITE 子句。【例 311】将表空间 ZCGL_TBS1 转变为 READ WRITE 状态，使表空间可读写。 SQL> ALTER TABLESPACE ZCGL_TBS1 READ WRITE; 表空间已更改。 3．改变表空间可用性当创建表空间时，表空间及其所有数据文件都处于 ONLINE 状态，此时表空间是可以被访问的。当表空间或数据文件处于 OFFLINE 状态时，表空间及其数据文件就不可以被访问了。（1）将表空间设置为脱机 OFFLINE 状态下列情况需要将表空间设置为脱机状态：需要对表空间进行备份或恢复等维护操作；某个表空间暂时不允许用户访问；需要移动特定表空间的数据文件，防止其中的数据文件被修改以确保数据文件的一致性。需要注意的是，SYSTEM 和 SYSAUX 表空间不能被脱机。【例 312】将表空间 ZCGL_TBS1 转变为 OFFLINE 状态，使其脱机。 SQL> ALTER TABLESPACE ZCGL_TBS1 OFFLINE; 表空间已更改。说明：当表空间处于 OFFLINE 状态时，该表空间将无法访问。（2）使表空间联机完成了表空间的维护操作后，应该将表空间设置为 ONLINE 状态，这样该表空间就可以被访问了。【例 313】将表空间 ZCGL_TBS1 转变为 ONLINE 状态。 SQL> ALTER TABLESPACE ZCGL_TBS1 ONLINE; 表空间已更改。 4．改变数据文件可用性修改数据文件可用性的一般语法格式如下： ALTER DATABASE DATAFILE file_name ONLINE | OFFLINE | OFFLINE DROP 说明：数据文件的状态有三种，ONLINE 表示数据文件可以使用；OFFLINE 表示当数据库运行在存档模式下时，数据文件不可以使用；OFFLIEN DROP 表示当数据库运行在非存档模式下时，数据文件不可以使用。【例 314】将表空间 ZCGL_TBS1 中的数据文件 ZCGL_TBS1_01.dbf 设置为脱机状态 OFFLINE。 ① 如果要将数据文件设置为脱机状态，需要将数据库启动到 MOUNT 状态下，设置数据库运行在存档模式下。

(11)

SQL> SHUTDOWN IMMEDIATE 数据库已经关闭。已经卸载数据库。 ORACLE 例程已经关闭。 SQL> STARTUP MOUNT; ORACLE 例程已经启动。 Total System Global Area 535662592 bytes Fixed Size 1375792 bytes Variable Size 276824528 bytes Database Buffers 251658240 bytes Redo Buffers 5804032 bytes 数据库装载完毕。 SQL> ALTER DATABASE ARCHIVELOG; 数据库已更改。 ② 使用 ALTER DATABASE 命令将数据文件 ZCGL_TBS1_01.dbf 设置为脱机状态。 SQL> ALTER DATABASE 2 DATAFILE 'E:\ORACLE11G\ZCGL\ZCGL_TBS1_01.dbf' 3 OFFLINE; 数据库已更改。说明：将数据文件设置为脱机状态时，不会影响到表空间的状态。相反，将表空间设置为脱机状态时，属于该表空间的数据文件将会全部处于脱机状态。 3.3.3 设置默认表空间在 Oracle 中，对于像 SCOTT 这样的普通用户来说，其初始默认表空间为 USERS，默认临时表空间为 TEMP；而对 SYSTEM 用户来说，其初始默认表空间为 SYSTEM，默认临时表空间为 TEMP。在创建新用户时，如果不为其指定默认表空间，系统会将上述初始的默认表空间作为这个用户的默认表空间，这将导致 TEMP、USERS 或 SYSTEM 等表空间迅速被用户数据占满，严重影响系统 IO 性能。使用 ALTER DATABASE DEFAULT TABLESPACE 命令可以设置数据库的默认表空间；使用 ALTER DATABASE DEFAULT TEMPORARY TABLESPACE 语句可以改变数据库的默认临时表空间。【例 315】查看数据字典 database_properties，查看当前用户使用的永久表空间与默认表空间。 SQL> COLUMN property_value FORMAT A15 SQL> COLUMN description FORMAT A25 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 TEMP Name of default

(12)

temporary tablespace DEFAULT_PERMANENT_TABLESPACE USERS Name of default

permanent tablespace 【例 316】设置数据库的默认表空间为 ZCGL_TBS1。 SQL> ALTER DATABASE DEFAULT TABLESPACE ZCGL_TBS1; 数据库已更改。【例 317】设置数据库的默认临时表空间为 ZCGL_TEMP1。 SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE ZCGL_TEMP1; 数据库已更改。 3.3.4 扩展表空间数据文件的大小实际上代表了该数据文件在磁盘上的可用空间。表空间的大小实际上就是其对应的数据文件大小的和。如果表空间中所有数据文件都已经被写满，那么向该表空间上的表中插入数据时，会显示错误信息。这种情况下必须扩展表空间来增加更多的存储空间。通常扩展表空间的方法有添加新的数据文件、改变数据文件的大小以及允许数据文件自动扩展等。 1．添加新的数据文件添加新的数据文件的一般语法格式为： ALTER TABLESPACE tablespace_name ADD DATAFILE 'datafilepath' SIZE nM; 说明：tablespace_name 为表空间名称，datafilepath 为数据文件路径，n 为数据文件大小，单位为 M。【例 318】为表空间 ZCGL_TBS1 增加一个 5MB 的数据文件 ZCGL_TBS1_02.dbf。 SQL> ALTER TABLESPACE ZCGL_TBS1 2 ADD DATAFILE 'E:\ORACLE11G\ZCGL\ ZCGL_TBS1_02.dbf' 3 SIZE 5M; 表空间已更改。 2．改变数据文件的大小修改数据文件的大小需要使用 ALTER DATABASE 命令，其语法格式如下所示： ALTER DATABASE tablespace_name DATAFILE filename RESIZE nM; 说明：tablespace_name 为表空间名称，filename 为要修改的数据文件的名称，n 为数据文件的大小，单位为 M。【例 319】将数据文件 ZCGL_TBS1_01.dbf 扩展为 100M。 ① 通过数据字典 DBA_DATA_FILES 查看表空间 ZCGL_TBS1 中的数据文件信息。 SQL> SELECT FILE_NAME,TABLESPACE_NAME 2 FROM DBA_DATA_FILES 3 WHERE TABLESPACE_NAME='ZCGL_TBS1';

(13)

FILE_NAME TABLESPACE_NAME E:\ORACLE11G\ZCGL\ZCGL_TBS1_01.DBF ZCGL_TBS1 ② 通过 ALTER DATABASE…RESIZE 命令将数据文件 ZCGL_TBS1_01.dbf 扩展为 100M。 SQL> ALTER DATABASE 2 DATAFILE 'E:\ORACLE11G\ZCGL\ZCGL_TBS1_01.dbf' 3 RESIZE 100M; 数据库已更改。说明：可以利用 RESIZE 子句来缩小数据文件的大小，但必须保证缩小后的数据文件足够容纳其中现有的数据，否则会有错误提示。 3．允许数据文件自动扩展在为表空间指定数据文件时，如果没有使用 AUTOEXTEND ON 选项，那么该数据文件将不允许自动扩展。为了使数据文件可以自动扩展，就必须指定 AUTOEXTEND ON 选项。当指定了 AUTOEXTEND ON 选项后，在表空间填满时，数据文件将自动扩展，从而扩展了表空间的存储空间。设置数据文件为自动扩展的一般语法格式为： ALTER DATABASE DATAFILE 'datafilepath' AUTOEXTEND ON NEXT mM MAXSIZE maxM; 说明：datafilepath 为数据文件路径，NEXT 语句指定数据文件每次增长的大小 mM。 MAXSIZE 表示允许数据文件增长的最大限度 maxM。【例 320】将数据文件 ZCGL_TBS1_01.dbf 设置为自动扩展。 SQL> ALTER DATABASE 2 DATAFILE 'E:\ORACLE11G\ZCGL\ZCGL_TBS1_01.dbf' 3 AUTOEXTEND ON NEXT 2M MAXSIZE 30M; 数据库已更改。说明：执行上述命令后，当该数据文件被填满时会自动扩展，每次增长的大小为 2MB，最大尺寸可达到 30MB。【例 321】取消数据文件 ZCGL_TBS1_01 的自动扩展性。 SQL> ALTER DATABASE 2 DATAFILE 'E:\ORACLE11G\ZCGL\ZCGL_TBS1_01.dbf' 3 AUTOEXTEND OFF; 数据库已更改。 3.3.5 删除表空间和数据文件 1．删除表空间当表空间中的所有数据都不再需要时，或者当表空间因损坏而无法恢复时，可以将表空间删除，这要求用户具有 DROP TABLESPACE 系统权限。默认情况下，Oracle 在删除表空间时只是从数据字典和控制文件中删除表空间信息，而不会物理地删除操作系统中相应的数据文

(14)

件。删除表空间的一般语法格式为：

DROP TABLESPACE tablespace_name

INCLUDING CONTENTS | INCLUDING CONTENTS AND DATAFILES;

说明：tablespace_name 为要删除的表空间名称，INCLUDING CONTENTS 选项表示删除表空间的所有对象，INCLUDING CONTENTS AND DATAFILES 表示级联删除所有数据文件。【例 322】删除表空间 ZCGL_TBS2。 SQL> DROP TABLESPACE ZCGL_TBS2 INCLUDING CONTENTS; 表空间已删除。说明：如果要删除的表空间中有数据库对象，则必须使用 INCLUDING CONTENTS 选项。【例 323】在删除表空间 ZCGL_TBS3NEW 的同时删除它所对应的数据文件。 SQL> DROP TABLESPACE ZCGL_TBS3NEW INCLUDING CONTENTS AND DATAFILES; 表空间已删除。说明：删除表空间时，如果级联删除其所拥有的所有数据文件，此时需要显式地指定 INCLUDING CONTENTS AND DATAFILES。 2．删除数据文件从 Oracle 10g R2 开始，允许从表空间中删除数据文件，但是该数据文件中不能包含任何数据。当数据文件处于以下三种情况时是不能被删除的：（1）数据文件中存在数据。（2）数据文件是表空间中唯一的或第一个数据文件。（3）数据文件或数据文件所在的表空间处于只读状态。

从表空间中删除数据文件，需要使用带 DROP DATAFILE 子句的 ALTER TABLESPACE 命令来完成，其一般语法格式为： ALTER TABLESPACE tablespace_name DROP DATAFILE 'datafilepath'; 说明：tablespace_name 为要删除的数据文件所在的表空间名称，datafilepath 为数据文件路径。【例 324】删除表空间 ZCGL_TBS1 中的数据文件 ZCGL_TBS1_03.DBF。 SQL> ALTER TABLESPACE ZCGL_TBS1 2 DROP DATAFILE 'F:\ORACLE11G\ZCGL\ZCGL_TBS1_03.DBF'; 表空间已更改。

3.4 查看表空间和数据文件信息

1．查看表空间信息可以通过查询有关数据字典来查看表空间信息，如表 3.1 所示。

(15)

表 3.1 与表空间相关的数据字典视图表名注释 V$TABLESPACE 从控制文件中获取的表空间名称和编号 DBA_TABLESPACE 所有用户可访问的表空间信息 USER_TABLESPACE 用户可访问的表空间的信息 DBA_SEGMENTS 所有表空间中的段的描述信息 USER_SEGMENTS 用户可访问的表空间中的段的描述信息 DBA_EXTENTS 所有用户可访问的表空间中的数据盘区的信息 USER_EXTENTS 用户可访问的表空间中的数据盘区的信息 V$DATAFILE 所有数据文件的信息，包括所属表空间的名称和编号 V$TEMPFILE 所有临时文件的信息，包括所属表空间的名称和编号 DBA_DATA_FILES 所有数据文件及其所属的表空间的信息 DBA_TEMP_FILES 所有临时文件及其所属的临时表空间的信息 V$TEMP_EXTENT_POOL 本地管理的临时表空间的缓存信息，使用的临时表空间的状态信息 V$TEMP_EXTENT_MAP 本地管理的临时表空间中的所有盘区的信息 V$SORT_USER 用户使用的临时排序段的信息 V$SORT_SEGMENT 例程的每个排序段的信息 2．查看数据文件信息可以使用数据字典视图和动态性能视图来查看数据文件的信息，如表 3.2 所示。表 3.2 与数据文件相关的数据字典视图和动态性能视图表名注释 DBA_DATA_FILES 包含数据库中所有数据文件的基本信息 DBA_TEMP_FILES 包含数据库中所有临时数据文件信息 DBA_EXTENTS 包含所有表空间中已分配的区的描述信息，如区所属的数据文件的文件号等 USER_EXTENTS 包含当前用户所拥有的对象在所有表空间中已分配的区的描述信息 DBA_FREE_SPACE 包含表空间中空闲区的描述信息，如空闲区所属的数据文件的文件号等 USER_FREE_SPACE 包含可被当前用户访问的表空间中空闲区的描述信息 V$DATAFILE 包含从控制文件中获取的数据文件信息，主要是用于同步的信息 V$DATAFILE_HEADER 包含从数据文件头部获取的信息【例 325】通过 dba_tablespaces，查看当前数据库的表空间的类型，及其每个表空间的数据库大小。 SQL> SELECT tablespace_name,bigfile,block_size 2 FROM dba_tablespaces;

(16)

TABLESPACE_NAME BIG BLOCK_SIZE SYSTEM NO 8192 SYSAUX NO 8192 UNDOTBS1 NO 8192 TEMP NO 8192 USERS NO 8192 EXAMPLE NO 8192 MYTMP1 NO 8192 MYTMP2 NO 8192 MYTMP3 NO 8192 MYTEMPORARY NO 8192 ZCGL_TBS1 NO 8192 ZCGL_TEMP1 NO 8192 已选择 12 行。【例 326】通过 DBA_TEMP_FILES，查看临时表空间 ZCGL_TEMP1 的临时文件信息。 SQL> COLUMN file_name FORMAT A50; SQL> COLUMN tablespace_name FORMAT A15; SQL> SELECT tablespace_name,file_name,bytes 2 FROM dba_temp_files 3 WHERE tablespace_name='ZCGL_TEMP1';

TABLESPACE_NAME FILE_NAME BYTES ZCGL_TEMP1 D:\APP\ADMINISTRATOR\ORADATA\ZCGL\ 52428800 ZCGL_TEMP1_01.DBF

习题三

1．表空间有哪些作用？ 2．Oracle 数据库默认的表空间有哪些？ 3．表空间的管理类型可分为哪些？ 4．一个表空间具有哪些状态？哪个表空间不能切换为脱机状态？ 5．什么是 UNDO 表空间？ 6．如何创建非标准块表空间？ 7．如何设置默认表空间？ 8．哪些表空间的名称不能被修改？哪些表空间不能被设置为只读状态？ 9．扩展表空间通常具有哪几种方法？ 10．删除表空间时，如果要删除其所拥有的所有数据文件，该如何操作？ 11．与表空间相关的数据字典视图有哪些？ 12．如何在数据库中创建新的数据文件？ 13．如何将数据文件设置为自动扩展？

(17)

14．改变数据文件的大小有哪两种方式？ 15．如何改变数据文件的位置？试举例说明。 16．数据文件处于哪些情况下不能被删除？ 17．与数据文件相关的数据字典视图和动态性能视图有哪些？

实验三表空间和数据文件管理

一、实验目的 1．理解各种类型的表空间和数据文件的作用。 2．熟悉各种表空间和数据文件的创建和维护。二、实验内容 1．各种类型的表空间和数据文件的作用。 2．表空间的创建和维护。为当前数据库 myxkxt 创建下列表空间：永久性表空间 myxkxt_tbs，数据文件：myxkxt_tbs1.dbf；临时表空间 myxkxt_temp，数据文件：myxkxt_temp.dbf；撤销表空间 myxkxt_undo，数据文件：myxkxt_und.dbf。 3．数据文件的创建与维护。

为永久性表空间 myxkxt_tbs 添加新的数据文件 myxkxt_tbs2.dbf 和 myxkxt_tbs3.dbf，将 myxkxt_tbs3.dbf 设置为脱机状态；将临时表空间 myxkxt_temp 的数据文件 myxkxt_temp.dbf 的大小在原来的基础上增加 20M；撤销表空间 myxkxt_undo 的数据文件 myxkxt_und.dbf，重新命名为 myxkxt_undo.dbf。三、实验步骤 1．各种类型的表空间和数据文件的作用。 ① 对于新建的数据库，Oracle 系统自动添加的表空间、表空间的功能及其默认的数据文件如表 3.3 所示。表 3.3 系统自动添加的表空间、表空间的功能及其默认的数据文件表空间名称作用默认数据文件 SYSTEM 系统表空间存储系统的数据字典和系统的管理信息 system01.dbf SYSAUX 系统辅助表空间不存储用户信息，由系统自动维护 sysaux01.dbf TEMP 临时表空间存储数据库运行过程中产生的临时数据（排序和汇总操作时产生的临时数据） temp01.dbf USERS 用户表空间存储一般用户方案中的表和索引数据 users01.dbf UNDOTBS1 撤销表空间用于存储之前修改的旧数据 undotbs01.dbf

(18)

② 表空间下可以创建很多数据文件，可以将表空间中的数据文件放到不同的磁盘上。为了提高性能、便于管理，建议建立自己的表空间。 2．表空间的创建和维护。（1）为当前数据库 myxkxt 创建下列表空间： ① 永久性表空间 myxkxt_tbs，数据文件：myxkxt_tbs1.dbf； SQL> CREATE TABLESPACE myxkxt_tbs 2 DATAFILE 'D:\app\administrator\oradata\myxkxt\tbs\myxkxt_tbs1.dbf' 3 SIZE 100M 4 AUTOEXTEND ON NEXT 5M 5 MAXSIZE 200M; 表空间已创建。 ② 临时表空间 myxkxt_temp, 数据文件：myxkxt_temp.dbf； SQL> CREATE TEMPORARY TABLESPACE myxkxt_temp 2 TEMPFILE 'D:\app\administrator\oradata\myxkxt\tbs\myxkxt_temp.dbf' 3 SIZE 10M 4 AUTOEXTEND ON NEXT 2M 5 MAXSIZE 20M; 表空间已创建。 ③ 创建撤销表空间 myxkxt_undo，数据文件：myxkxt_und.dbf SQL> CREATE UNDO TABLESPACE myxkxt_undo 2 DATAFILE 'D:\app\administrator\oradata\myxkxt\tbs\myxkxt_und.dbf' 3 SIZE 50M 4 AUTOEXTEND ON NEXT 5M 5 MAXSIZE 100M; 表空间已创建。 ④ 通过数据字典 dba_tablespaces，查看当前数据库的所有表空间的名称、状态等信息。 SQL> SELECT tablespace_name,status, logging FROM dba_tablespaces;

TABLESPACE_NAME STATUS LOGGING SYSTEM ONLINE LOGGING SYSAUX ONLINE LOGGING UNDOTBS1 ONLINE LOGGING TEMP ONLINE NOLOGGING USERS ONLINE LOGGING MYXKXT_TBS ONLINE LOGGING MYXKXT_TEMP ONLINE NOLOGGING MYXKXT_UNDO ONLINE LOGGING

已选择 8 行。

（2）将临时表空间 myxkxt_tbs 的可用性设置为脱机，再修改为联机；

(19)

SQL> SELECT tablespace_name,status, logging FROM dba_tablespaces;

TABLESPACE_NAME STATUS LOGGING SYSTEM ONLINE LOGGING SYSAUX ONLINE LOGGING UNDOTBS1 ONLINE LOGGING TEMP ONLINE NOLOGGING USERS ONLINE LOGGING MYXKXT_TBS OFFLINE LOGGING MYXKXT_TEMP ONLINE NOLOGGING MYXKXT_UNDO ONLINE LOGGING

已选择 8 行。

SQL> ALTER TABLESPACE MYXKXT_TBS ONLINE;

3．数据文件的创建与维护。

（1）为永久性表空间 myxkxt_tbs 添加新的数据文件 myxkxt_tbs2.dbf 和 myxkxt_tbs3.dbf，将 myxkxt_tbs3.dbf 设置为脱机状态。 ① 为永久性表空间 myxkxt_tbs 添加新的数据文件 myxkxt_tbs2.dbf 和 myxkxt_tbs3.dbf。 SQL> ALTER TABLESPACE MYXKXT_TBS 2 ADD DATAFILE 3 'D:\app\administrator\oradata\myxkxt\tbs\myxkxt_tbs2.dbf' 4 SIZE 10M 5 AUTOEXTEND ON NEXT 5M MAXSIZE 50M, 6 'D:\app\administrator\oradata\myxkxt\tbs\myxkxt_tbs3.dbf' 7 SIZE 20M; 表空间已更改。 ② 将 myxkxt_tbs3.dbf 设置为脱机状态。 SQL> ALTER DATABASE 1 DATAFILE 'D:\app\administrator\oradata\myxkxt\tbs\myxkxt_tbs3.dbf' 2 OFFLINE ; 数据库已更改。（2）将临时表空间 myxkxt_undo 的数据文件 myxkxt_und.dbf 大小在原来的基础上增加 20M。 ① 查看所有表空间大小。 SQL> SELECT tablespace_name, sum(bytes)/1024/1024 FROM dba_data_files 2 group by tablespace_name;

(20)

TABLESPACE_NAME SUM(BYTES)/1024/1024 UNDOTBS1 70 SYSAUX 480 MYXKXT_TBS 110 MYXKXT_UNDO 50 USERS 5 SYSTEM 680 已选择 6 行。 ② 将数据文件 myxkxt_und.dbf 大小在原来的基础上增加 20M。 SQL> ALTER DATABASE 2 DATAFILE 'D:\app\administrator\oradata\myxkxt\tbs\myxkxt_und.dbf' 3 RESIZE 70M; 数据库已更改。

（3）将撤销表空间 myxkxt_undo 的数据文件 myxkxt_und.dbf 重新命名为 myxkxt_undo.dbf。 ① 查看表空间 myxkxt_undo 对应的数据文件的存储位置。 SQL> SELECT a.name,b.name FROM v$tablespace a, v$datafile b WHERE a.ts#=b.ts# 2 AND a.name='MYXKXT_UNDO'; NAME NAME MYXKXT_UNDO D:\APP\ADMINISTRATOR\ORADATA \MYXKXT\TBS\MYXKXT_UND.DBF ② 使表空间 myxkxt_undo 处于 OFFLINE 状态。 SQL> ALTER DATABASE 2 DATAFILE 'D:\app\administrator\oradata\myxkxt\tbs\myxkxt_und.dbf' 3 OFFLINE; 数据库已更改。 ③ 用操作系统命令重命名数据文件。 ④ 使用带 RENAME DATAFILE 子句的 ALTER TABLESPACE 语句改变数据文件名称。 SQL> ALTER TABLESPACE myxkxt_undo 2 RENAME DATAFILE 3 'D:\app\administrator\oradata\myxkxt\tbs\myxkxt_und.dbf' 4 TO 5 'D:\app\administrator\oradata\myxkxt\tbs\myxkxt_undo.dbf'; 表空间已更改。 ⑤ 将表空间重新设置为联机状态。 SQL> ALTER DATABASE 2 DATAFILE 3 'D:\app\administrator\oradata\myxkxt\tbs\myxkxt_undo.dbf' 4 ONLINE; ALTER DATABASE

(21)

* 第 1 行出现错误: ORA01113: 文件 6 需要介质恢复 ORA01110: 数据文件 6: 'D:\APP\ADMINISTRATOR\ORADATA\MYXKXT\TBS\MYXKXT_UNDO.DBF' 在表空间重新设置为联机状态时，出现上面所示需要介质恢复的提示。这种情况下需要对数据文件 D:\app\administrator\oradata\myxkxt\tbs\myxkxt_undo.dbf 进行介质恢复，介质恢复的步骤如下所示：（1）执行 SHUTDOWN IMMEDIATE 关闭数据库。 SQL> SHUTDOWN IMMEDIATE; 数据库已经关闭。已经卸载数据库。 ORACLE 例程已经关闭。（2）执行 startup mount 命令启动数据库。 SQL> startup mount; ORACLE 例程已经启动。 Total System Global Area 535662592 bytes Fixed Size 1375792 bytes Variable Size 192938448 bytes Database Buffers 335544320 bytes Redo Buffers 5804032 bytes 数据库装载完毕。（3）进行介质恢复。 SQL> RECOVER DATAFILE 'D:\app\administrator\oradata\myxkxt\tbs\myxkxt_undo.dbf'; 完成介质恢复。（4）打开数据库。 SQL> ALTER DATABASE OPEN; 数据库已更改。