管理表空间和数据文件（二）

只读表空间

使用以下命令将表空间设置为只读模式：

ALTER TABLESPACE userdata READ ONLY;

必须等到TABLESPACE所有的过程都commit；才能可以执行成功。

• 导致检查点 Causes a checkpoint
  意思是将内存中的数据（如缓冲区中的更改）写入磁盘的过程。它用于确保数据的一致性和持久性，减少系统崩溃时的数据丢失风险。检查点可以提高恢复性能，因为在恢复时，数据库可以从最近的检查点开始，而不必从头开始重做所有事务。
• 只能用于读操作的数据
• 支持从表空间中删除对象

ALTER TABLESPACE[TABLESPACE]READ ONLY命令将表空间设置为过渡只读模式。在这种过渡状态下，除了回滚先前修改过表空间块的现有事务外，表空间中不能再进行写操作。在提交或回滚所有现有事务之后，只读命令完成，表空间被置于只读模式。
可以从只读表空间中删除表和索引等项，因为这些命令只影响数据字典。这是可能的，因为DROP命令只更新数据字典，而不更新构成表空间的物理文件。对于本地管理的表空间，被删除的段被更改为临时段，以防止位图被更新。要使只读表空间可写，表空间中的所有数据文件必须处于在线状态。将表空间设置为只读会在表空间的数据文件上产生检查点。
将表空间设置为只读可以防止对表空间中的数据文件进行进一步的写操作。因此，数据文件可以驻留在只读介质上，例如cd-rom或WORM（writeonce）驱动器。
只读表空间消除了对数据库的大型静态部分执行备份的需要。

切换到sys用户，commit，然后使用ALTER TABLESPACE[TABLESPACE]READ ONLY生效。

表空间离线

• 不能用于数据访问
• 不能离线的表空间：
  • SYSTEM tablespace
  • 具有活动撤消段的表空间
  • 默认临时表空间
• 使表空间离线。

ALTER TABLESPACE userdata OFFLINE;

• 使表空间在线：

ALTER TABLESPACE userdata ONLINE;

• 表空间通常是在线的，因此数据库用户可以使用其中包含的数据。但是，数据库管理员可能会使表空间脱机，以便：
  • 使数据库的一部分不可用，同时允许对数据库的其余部分进行正常访问
  • 执行离线表空间备份（尽管表空间可以在在线和使用时进行备份）
  • 在数据库打开时恢复表空间或数据文件
  • 在数据库打开时移动数据文件
• 表空间离线状态
  当表空间离线时，Oracle不允许任何后续SQL语句引用该表空间中包含的对象。试图访问处于脱机状态的表空间中的对象的用户会收到一个错误。
  当表空间脱机或重新联机时，该事件将记录在数据字典和控制文件中。如果在关闭数据库时表空间处于离线状态，则在随后挂载和重新打开数据库时不会检查该表空间。

Oracle实例在遇到某些错误时会自动将表空间从在线状态切换为离线状态，例如当数据库写入进程（DBWn）多次尝试写入表空间的数据文件失败时。数据库管理员可以在数据库打开时将任何表空间（除了SYSTEM表空间或具有活动撤销段或临时段的表空间）设置为离线。当表空间被设置为离线时，Oracle服务器会将所有相关的数据文件也设置为离线。

使用以下命令可以将表空间设置为在线或离线：

ALTER TABLESPACE tablespace
{ ONLINE | OFFLINE [ NORMAL | TEMPORARY | IMMEDIATE | FOR RECOVER] }

• NORMAL：将表空间中所有数据文件的所有块从SGA中刷新出去。这是默认选项。使用NORMAL选项时，无需在将表空间重新上线之前进行介质恢复。
• TEMPORARY：对表空间中所有在线数据文件执行检查点，即使某些文件无法写入。任何离线文件可能需要介质恢复。
• IMMEDIATE：不确保表空间文件可用，并且不执行检查点。在将表空间重新上线之前，必须对其进行介质恢复。
• FOR RECOVER：将表空间设置为离线，以便进行表空间的时间点恢复。
  .
  .

更改存储设置 ：Changing Storage Settings

• 使用ALTER TABLESPACE命令修改存储设置：

ALTER TABLESPACE userdata MINIMUM EXTENT 2M;  # 这条语句将表空间userdata的最小扩展大小设置为2MB。即在该表空间中，任何新创建的段的最小扩展大小将为2MB。


ALTER TABLESPACE userdata   # 这条语句设置了表空间userdata的默认存储参数：
DEFAULT STORAGE (INITIAL 2M NEXT 2M  # INITIAL 2M：新段的初始大小为2MB。
MAXEXTENTS 999);             # NEXT 2M：每次扩展时，段的下一个扩展大小为2MB。
                             # MAXEXTENTS 999：段的最大扩展数为999。

• 不能更改本地管理的表空间的存储设置。

更改存储设置

使用ALTER TABLESPACE命令可以修改表空间的默认存储定义。以下是ALTER TABLESPACE命令的语法格式：

ALTER TABLESPACE tablespace
[MINIMUM EXTENT integer[K|M]
DEFAULT storage_clause ]

• tablespace：要修改的表空间的名称。
• MINIMUM EXTENT integer[K|M]：可选项，指定表空间中新段的最小扩展大小。integer表示整数值，K表示KB，M表示MB。
• DEFAULT storage_clause：可选项，指定表空间的默认存储参数，如INITIAL、NEXT、MINEXTENTS、MAXEXTENTS等。

调整表空间大小

表空间可以通过以下方式调整大小：

• 更改数据文件的大小：
  • 自动使用AUTOEXTEND
  • 手动使用ALTER DATABASE
• 使用ALTER TABLESPACE添加数据文件

具体操作

要扩展表空间，可以通过以下几种方法：

1. 在创建表空间时自动扩展数据文件：

   CREATE TABLESPACE userdata022 DATAFILE '/u01/oradata/userdata02.dbf' SIZE 5M AUTOEXTEND ON NEXT 2M MAXSIZE 200M;
   

   • 在创建表空间时，指定数据文件的初始大小为5MB，并设置为自动扩展，每次扩展2MB，最大扩展到200MB。

2. 在表空间创建后指定自动扩展：

   ALTER DATABASE DATAFILE '/u01/oradata/userdata02.dbf' AUTOEXTEND ON NEXT 2M;
   

   • 通过ALTER DATABASE命令为已存在的数据文件启用自动扩展。

3. 手动调整数据文件大小：

   ALTER DATABASE DATAFILE '/u01/oradata/userdata02.dbf' RESIZE 5M;
   

   • 使用ALTER DATABASE命令手动调整数据文件的大小，设置为5MB。

4. 向表空间添加数据文件：

   ALTER TABLESPACE userdata022 ADD DATAFILE '/u01/oradata/userdata03.dbf' SIZE 5M;
   

   • 向表空间userdata022添加一个新的数据文件，初始大小为5MB。

知识点总结：

• 自动扩展：在创建表空间时可以设置数据文件的自动扩展，以便在需要时自动增加大小。
• 手动调整：可以通过RESIZE命令手动调整数据文件的大小。
• 添加数据文件：可以向现有表空间添加新的数据文件，以增加存储容量。

查询数据文件剩余空间

这段SQL查询语句的目的是获取每个表空间的使用情况，包括已使用的字节数、最大的空闲块大小以及使用百分比。以下是查询语句的简要解释：

SELECT a.tablespace_name, a.bytes AS bytes_used, b.largest, ROUND(((a.bytes - b.bytes) / a.bytes) * 100, 2) AS percent_used
FROM
    (SELECT tablespace_name, SUM(bytes) AS bytes
     FROM dba_data_files
     GROUP BY tablespace_name) a,
    (SELECT tablespace_name, SUM(bytes) AS bytes, MAX(bytes) AS largest
     FROM dba_free_space
     GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
ORDER BY ((a.bytes - b.bytes) / a.bytes) DESC;

查询解释：

• 第一个子查询（子查询a）：从dba_data_files视图中获取每个表空间的总字节数，并按表空间名称分组。
• 第二个子查询（子查询b）：从dba_free_space视图中获取每个表空间的总字节数、最大的空闲块大小，并按表空间名称分组。
• 主查询：将两个子查询的结果连接起来，计算每个表空间的使用百分比，并按使用百分比降序排序。
• SELECT字段：
  • tablespace_name：表空间名称。
  • bytes_used：已使用的字节数。
  • largest：最大的空闲块大小。
  • percent_used：使用百分比，通过计算已使用字节数与总字节数的比例得出。

在您提供的SQL查询中，涉及到的主要表空间和方法如下：

1. 表空间

• dba_data_files：

  • 描述：这是一个数据字典视图，包含数据库中所有数据文件的信息。每个数据文件对应一个表空间，存储实际的数据。
  • 主要字段：
    • tablespace_name：表空间的名称。
    • bytes：数据文件的大小（以字节为单位）。

• dba_free_space：

  • 描述：这是一个数据字典视图，显示每个表空间中可用的空闲空间信息。它提供了表空间中未使用的空间的详细信息。
  • 主要字段：
    • tablespace_name：表空间的名称。
    • bytes：空闲空间的大小（以字节为单位）。
    • largest：最大的连续空闲空间块的大小。

2. 方法

• SUM()：

  • 描述：用于计算某个字段的总和。在查询中，SUM(bytes)用于计算每个表空间的总字节数和空闲字节数。

• MAX()：

  • 描述：用于获取某个字段的最大值。在查询中，MAX(bytes)用于获取每个表空间中最大的空闲块大小。

• GROUP BY：

  • 描述：用于将结果集按指定字段分组。在查询中，GROUP BY tablespace_name用于按表空间名称分组，以便计算每个表空间的总字节数和空闲字节数。

• ROUND()：

  • 描述：用于将数字四舍五入到指定的小数位数。在查询中，ROUND(((a.bytes - b.bytes) / a.bytes) * 100, 2)用于计算并格式化使用百分比。

• ORDER BY：

  • 描述：用于对结果集进行排序。在查询中，ORDER BY ((a.bytes - b.bytes) / a.bytes) DESC用于按使用百分比降序排列结果。

启用数据文件自动扩展功能

• 可以使用以下命令自动调整大小：
  • 创建数据库
  • 创建表空间
  • 改变表空间……添加数据文件
• 例子：

CREATE TABLESPACE user_data
DATAFILE
'/u01/oradata/userdata01.dbf' SIZE 200M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M;

• 查询DBA_DATA_FILES视图，确定是否启用了AUTOEXTEND

向表空间添加数据文件

• 通过添加额外的数据文件来增加分配给表空间的空间
• ADD DATAFILE子句用于添加数据文件
• 例子：

  ALTER TABLESPACE user_data 
  ADD DATAFILE '/u01/oradata/userdata03.dbf' 
  SIZE 200M;
  

移动数据文件的方法

• ALTER TABLESPACE
  • 表空间必须离线。
  • 目标数据文件必须存在。

ALTER TABLESPACE userdata RENAME
DATAFILE '/u01/oradata/userdata01.dbf'
TO '/u02/oradata/userdata01.dbf';

源文件名必须与存储在控制文件中的文件名匹配。重命名数据文件的步骤
1.使表空间离线。
2.使用操作系统命令移动或复制文件
3.执行ALTER TABLESPACE RENAME DATAFILE命令。
4.使表空间在线。
5·如果需要，可以使用操作系统命令删除该文件。

删除表空间

• 在以下情况下不能删除表空间：
  • 是SYSTEM表
  • 空间一有活动的段
• 包括CONTENTS删除片段。
• 包括内容和数据文件删除数据文件。
• CASCADE CONSTRAINTS删除所有引用完整性约束。

DROP TABLESPACE USERDATA
INCLUDING CONTENTS AS DATAFILES;

删除表空间的SQL命令

DROP TABLESPACE tablespace
[INCLUDING CONTENTS [AND DATAFILES] [CASCADE CONSTRAINTS]];

参数说明：

• tablespace: 要删除的表空间名称。
• INCLUDING CONTENTS: 删除表空间中的所有段。
• AND DATAFILES: 删除与表空间关联的操作系统文件。
• CASCADE CONSTRAINTS: 删除引用被删除表空间中主键和唯一键的外部表的完整性约束。

示例

DROP TABLESPACE my_tablespace INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;   # 这个命令将删除名为`my_tablespace`的表空间及其所有内容和数据文件，并删除所有相关的完整性约束。

• 如果没有include CONTENTS选项，则不能删除仍然包含数据的表空间。当表空间包含许多对象时，此选项可能会产生大量撤消操作。
• 删除表空间后，其数据将不再存在数据库中。
• 删除表空间时，仅删除关联数据库的控制文件中的文件指针。操作系统文件仍然存在，必须使用适当的操作系统命令显式删除，除非使用and DATAFILES子句或数据文件是OMF的。
• 即使将表空间切换为只读，它仍然可以被删除，以及其中的段。
• 建议在删除表空间之前将其脱机，以确保没有事务访问表空间中的任何段。

使用OMF管理表空间

• 定义DB_CREATE_FILE_DEST参数的方法如下：
  • 初始化参数文件
  • 使用ALTER SYSTEM命令动态设置

  ALTER SYSTEM SET 
  db_create_file_dest = '/u01/oradata/dba01';
  

• 创建表空间时：
  • 数据文件自动创建并位于 DB_CREATE_FILE_DEST
  • 默认大小为100mb
  • AUTOEXTEND设置为UNLIMITED

在使用Oracle Managed Files（OMF）来管理表空间时，可以通过指定一个初始化参数DB_CREATE_FILE_DEST来配置。不需要使用DATAFILE子句来指定数据文件。所有数据文件将自动创建，并它们的位置由DB_CREATE_FILE_DEST定义。数据文件的文件名会由Oracle服务器自动生成，例如(ora_tbs1_2ixfh90q.dbf)。

Oracle Managed Files（OMF）的特点和优势：

1. 自动文件管理：使用OMF，Oracle数据库会自动管理数据文件和日志文件的创建和命名，减少了手动管理的工作量。

2. 简化管理：无需手动指定数据文件的路径和名称，减少了配置和管理的复杂性。

3. 避免命名冲突：自动生成的文件名确保了文件之间不会发生命名冲突，提高了系统的稳定性和可靠性。

配置OMF的步骤：

1. 设置DB_CREATE_FILE_DEST参数：在Oracle数据库中设置DB_CREATE_FILE_DEST参数，指定数据文件的存储位置。

2. 创建表空间：创建表空间时，不需要指定数据文件的路径和名称，Oracle会自动根据DB_CREATE_FILE_DEST参数来创建数据文件。

3. 自动生成文件名：Oracle服务器会自动为每个数据文件生成唯一的文件名，避免了手动命名文件可能带来的错误。

4. 管理表空间：可以通过Oracle的管理工具或SQL命令来管理使用OMF创建的表空间，如扩展表空间、删除表空间等操作。

通过使用OMF，可以简化表空间的管理和配置过程，提高数据库管理的效率和可靠性。如果您有任何进一步的问题或需要更详细的解释，请随时告诉我！

OMF具体步骤

1. 创建一个OMF表空间

CREATE TABLESPACE my_omf_tablespace 
DATAFILE SIZE 100M;

在这个命令中，my_omf_tablespace是表空间的名称，DATAFILE SIZE 100M指定了数据文件的大小，Oracle会自动管理文件的位置和名称。

2. 向现有表空间添加一个OMF数据文件

ALTER TABLESPACE my_omf_tablespace 
ADD DATAFILE;

这个命令将向my_omf_tablespace表空间添加一个新的OMF数据文件，文件的位置和名称将由Oracle自动管理。

3. 动态更改默认文件位置

ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/new/file/location' SCOPE=BOTH;

这个命令将DB_CREATE_FILE_DEST参数的值更改为/new/file/location，影响后续创建的OMF数据文件的位置。

4. 删除表空间（包括删除操作系统文件）

DROP TABLESPACE my_omf_tablespace INCLUDING CONTENTS AND DATAFILES;

这个命令将删除my_omf_tablespace表空间及其所有内容和数据文件，Oracle会自动删除与该表空间相关的操作系统文件。

获取表空间信息

表空间和数据文件信息的获取方式如下：

• 表空间信息：
  • DBA_TABLESPACES
  • v$TABLESPACE
• 数据文件信息：
  • DBA_DATA_FILES
  • V$DATAFILE
• 临时文件信息：
  • DBA_TEMP_FILES
  • V$TEMPFILE