管理表空间和数据文件

前言：管理表空间和数据文件

  在 Oraclｅ数据库中，表空间中的数据存在于磁盘的数据文件中，所以对表空间的管理操作与对数据文件的管理操作密切相关。通过使用表空间，可以有效的部署不同类型的数据，加强数据管理，从而提高数据库的运行性能。

通过阅读本章节内容，可以：

• 理解表空间和数据文件的关系
• 掌握如何创建表空间
• 掌握如何维护表空间和数据文件
• 了解如何管理撤销表空间
• 掌握如何管理临时表空间

一：表空间与数据文件的关系

       在Oracle 数据库中，表空间与数据文件之间的关系非常密切，这二者 相互依存。也就是说，创建表空间时必须创建数据文件，增加数据文件时也必须指定表空间。

       Oracle磁盘空间管理中的最高逻辑层是表空间（tablespace）,它的下一层是段（segment）,并且一个段只能驻留在一个表空间中。段的下一层是盘区。一个或者多个盘区 (extent）可以组成一个段，并且每个盘区只能驻留在一个数据文件中。如果一个段跨越多个数据文件，它就只能由多个驻留在不同数据文件中的盘区构成。盘区的下一层就是数据块，它也是磁盘空间管理中逻辑划分的最底层，一组连续的数据块可以组成一个盘区。

示例1：在sysetm 模式下：从 dba_data_files 数据字典中查询表空间及其包含的数据文件

SYS@orcl> col tablespace_name for a10;  2 SYS@orcl> col file_name for a50;
SYS@orcl> col bytes for 999,999,999
SYS@orcl> select tablespace_name ,file_name,bytes from dba_data_files order by tablespace_name;

TABLESPACE FILE_NAME                                                 BYTES
---------- -------------------------------------------------- ------------
EXAMPLE    /u01/app/oracle/oradata/orcl/example01.dbf          362,414,080
SYSAUX     /u01/app/oracle/oradata/orcl/sysaux01.dbf           786,432,000
SYSTEM     /u01/app/oracle/oradata/orcl/system01.dbf           765,460,480
UNDOTBS1   /u01/app/oracle/oradata/orcl/undotbs01.dbf          256,901,120
USERS      /u01/app/oracle/oradata/orcl/users01.dbf              5,242,880

SYS@orcl>

     从查询所列的结果来看，一个数据库包括多个表空间，比如system 表空间、users表空间。而每一个表空间又包含一个或者多个数据文件，比如：

users 表包括一个数据文件 users01.dbf ；表空间可以看成是oracle 数据库的逻辑结构，而数据文件可以看成是 Oracle数据库的物理结构。            

二：oracle 11g 默认的表空间

   默认表空间是指在创建Oracle数据库时，系统自动创建的表空间，这些表空间通常用于存放Oracle系统内部数据和提供样列所需的逻辑空间。

oracle默认的表空间及其声明如表：

      1: system 表空间

  Oracle 数据库的每个版本都使用system 表空间存放内部数据和数据字典。system表空间主要存放sys用户的各个对象和其他用户的少量对象。用户可以从DBA_SEGMENTS 数据字典中查询到某个表空间所存放的数据对象及其类型(如：索引、表、簇等）和拥有者。

1: 例如： 查询 users 表空间内存放的数据对象及其类型和拥有者。

SYS@orcl> col owner for a10;  2 SYS@orcl> col segment_name for a30;  3 SYS@orcl> col segment_type for a20;  4 SYS@orcl> select segment_type,segment_name,owner from dba_segments where tablespace_name ='USERS';  5   6 SEGMENT_TYPE         SEGMENT_NAME                   OWNER  7 -------------------- ------------------------------ ----------  8 TABLE                DDL_OPER_LOG                   SCOTT  9 TABLE                DEPT                           SCOTT 10 TABLE                DEPT_LOG                       SCOTT 11 TABLE                EMP                            SCOTT 12 TABLE                GOODS                          SCOTT 13 INDEX                PK_DEPT                        SCOTT 14 INDEX                PK_EMP                         SCOTT 15 TABLE                SALGRADE                       SCOTT 16 INDEX                SYS_C0011673                   SCOTT 17 TABLE                JOBS_TEMP                      HR 18 INDEX                SYS_C0011441                   HR 19  20 SEGMENT_TYPE         SEGMENT_NAME                   OWNER 21 -------------------- ------------------------------ ---------- 22 NESTED TABLE         ACTION_TABLE                   OE 23 INDEX                ACTION_TABLE_MEMBERS           OE 24 TABLE                CATEGORIES_TAB                 OE 25 LOBSEGMENT           EXTRADATA886_L                 OE 26 NESTED TABLE         LINEITEM_TABLE                 OE 27 INDEX                LINEITEM_TABLE_MEMBERS         OE 28 LOBSEGMENT           NAMESPACES887_L                OE 29 NESTED TABLE         PRODUCT_REF_LIST_NESTEDTAB     OE 30 TABLE                PURCHASEORDER                  OE 31 NESTED TABLE         SUBCATEGORY_REF_LIST_NESTEDTAB OE 32 INDEX                SYS_C0011250                   OE 33  34 SEGMENT_TYPE         SEGMENT_NAME                   OWNER 35 -------------------- ------------------------------ ---------- 36 INDEX                SYS_C0011251                   OE 37 INDEX                SYS_C0011254                   OE 38 INDEX                SYS_C0011255                   OE 39 INDEX                SYS_C0011256                   OE 40 INDEX                SYS_C0011257                   OE 41 INDEX                SYS_C0011258                   OE 42 INDEX                SYS_FK0000076744N00007$        OE 43 INDEX                SYS_FK0000076744N00009$        OE 44 LOBINDEX             SYS_IL0000076694C00004$$       OE 45 LOBINDEX             SYS_IL0000076694C00005$$       OE 46 LOBINDEX             SYS_IL0000076694C00008$$       OE 47  48 SEGMENT_TYPE         SEGMENT_NAME                   OWNER 49 -------------------- ------------------------------ ---------- 50 LOBINDEX             SYS_IL0000076694C00012$$       OE 51 LOBINDEX             SYS_IL0000076694C00017$$       OE 52 LOBINDEX             SYS_IL0000076694C00026$$       OE 53 LOBINDEX             SYS_IL0000076694C00033$$       OE 54 LOBINDEX             SYS_IL0000076695C00004$$       OE 55 LOBINDEX             SYS_IL0000076699C00005$$       OE 56 LOBINDEX             SYS_IL0000076699C00010$$       OE 57 LOBSEGMENT           SYS_LOB0000076695C00004$$      OE 58 LOBSEGMENT           SYS_LOB0000076699C00005$$      OE 59 LOBSEGMENT           SYS_LOB0000076699C00010$$      OE 60 LOBSEGMENT           SYS_XDBPD$881_L                OE 61  62 SEGMENT_TYPE         SEGMENT_NAME                   OWNER 63 -------------------- ------------------------------ ---------- 64 LOBSEGMENT           SYS_XDBPD$882_L                OE 65 LOBSEGMENT           SYS_XDBPD$883_L                OE 66 LOBSEGMENT           SYS_XDBPD$884_L                OE 67 LOBSEGMENT           SYS_XDBPD$885_L                OE 68  69 48 rows selected. 70  71 SYS@orcl>

   从运行结果可以看出，users表空间存放了scott用户的表和索引，以及OE 用户的大对象索引、索引等数据对象。

  2： sysaux 表空间

        system 表空间主要用于存放Oracle系统内部的数据字典，而sysaux表空间充当system的辅助表空间，主要用于储存数据字典以外的其他数据对象，它在一定程度上降低 了 system表空间的负荷。

示例1：下面通过dba_segments 数据字典来查询sysaux 表空间的相关信息

SYS@orcl>  select owner as "用户",count(segment_name) as "对象数量" from dba_segments where tablespace_name='SYSAUX' GROUP BY OWNER;  2   3 用户                             对象数量  4 ------------------------------ ----------  5 MDSYS                                 572  6 CTXSYS                                 59  7 OLAPSYS                               141  8 SYSTEM                                153  9 EXFSYS                                 58 10 APEX_030200                           467 11 DBSNMP                                 12 12 ORDSYS                                  7 13 SYSMAN                                746 14 XDB                                  1372 15 ORDDATA                               160 16  17 ????                             ???????? 18 ------------------------------ ---------- 19 SYS                                  1169 20 WMSYS                                  56 21  22 13 rows selected. 23  24 SYS@orcl> 25 

三：创建表空间

    为了简化表空间的管理并提供系统性能，Oracle建议将不同类型的数据对象存放到不同的表空间中。因此，在创建数据库后，数据库管理员还应该根据具体应用的情况，建立不同类型的表空间。例如：建立专门用于存放表数据的表空间、建立专门用于存放索引或簇数的表空间等，因此创建表空间的工作就显示十分重要，在创建表空间时必须考虑以下几点：

• 是创建小文件表空间，还是大文件表空间(默认为：小文件表空间)
• 是使用局部盘区管理方式，还是使用传统的目录盘区管理方式(默认为局部盘区管理)
• 是手动管理段空间，还是自动管理段空间(默认为自动)
• 是否用于临时段或撤销段的特殊表空间

1：创建表空间的语法

创建表空间的语法如下：

CREATE[SMALLFILE/BIGFILE] TABLESPACE  tablespace_name  2 DATAFILE '/path/filename' SIZE num[k/m] REUSE  3 [,'/path/filename' SIZE num[k/m] REUSE]  4 [...]  5 [AUTOEXTEN [ON/OFF] NEXT NUM[K/M] ]  6 [MAXSIZE num[K/M] | UNLIMITED ]  7 [MININUM EXTENT num[K/M] ]  8 [DEFAULT   STORAGE storage ]  9 [ONLINE | OFFLINE ] 10 [LOGGING | NOLOGGING ] 11 [PERMANENT | TEMPORARY ] 12 [ EXTENT  MANAGEMENT DICTIONARY | LOCAL [ AUTOALLOCATE | UNIFORM SIZE num [ K/M ] ]  ] 13 

语法中关键字

• SMALLFILE | BIGFILE : 表示创建的是小文件表空间 还是 大文件表空间
• REUSE:  表示 若该文件存在，则清除该文件再重新建立该文件，若该文件不存在，则创建该文件。
• AUTOEXEND[ ON | OFF ]:  表示数据文件为自动扩展(ON) 或者 非自动扩展(OFF)，如果是自动扩展，则需要设置 next 的值。
• MAXSIZE:  表示当数据文件自动扩展时，允许数据文件扩展的最大长度字节数，如果指定 UNLIMITED 关键字，则不需要指定字节长度。
• MINIMUN EXTENT: 指定最小的长度，由操作系统和数据库的块决定
• ONLINE | OFFLINE: 创建表空间时可以指定为在线或者离线
• PERMANENT | TEMPORARY :  指定创建的表空间是 永久表空间或临时表空间，默认为永久性表空间。
• LOGGING | NOLOGGING : 指定该表空间内的表在加载数据时是否产生日志，默认为产生日志(LOGGING)。即使设置为NOLOGGING，但在进行 INSERT、UPDATE 和 DELETE 操作时，Oracle 仍会将操作信息记录到Redo Log Buffer 中。
• EXTENT MANAGEMENT DICTIONARY | LOCAL：指定表空间的扩展方式是使用数据字典管理还是本地化管理，默认为本地化管理。Oracle不推荐使用数据字典表空间。
• AUTOALLOCATE | UNIFORM SIZE : 如果采用本地化管理表空间，在表空间扩展时，指定每次盘区扩展的大小是由系统自动指定还是按照等同大小进行。若是按照等同大小进行，则默认每次扩展的大小为1MB.
• DEFAULT STORAGE: 指定以后要创建的表、索引及簇的储存参数值，这些参数将影响以后表等的储存参数值。

语法中的参数

tablespace_name:该参数表示要创建的表空间的名称

‘/path/filename’:该参数表述数据文件的路径与名字

2：通过本地化管理方式创建表空间

示例1：通过本地化管理方式(Local）创建一个大小为10M 的表空间，其扩展大小为256kb

1: 创建前：

2: 创建脚本：

  2 SYS@orcl> create tablespace tbs_test1 datafile '/u01/app/oracle/oradata/orcl/datafile1.dbf' size 10m extent management local uniform size 256k;  3   4 Tablespace created.  5   6 SYS@orcl>

3: 创建后：

示例2：通过本地化管理方式(local) 创建一个大小为 10M的表空间，其扩展大小为 自动管理，

SYS@orcl> create tablespace tbs_test2 datafile '/u01/app/oracle/oradata/orcl/datafile2.dbf' size 10m extent management local autoallocate;  2   3 Tablespace created.  4   5 SYS@orcl>

创建后的：

        在上面的2个例子中，由于创建的都是本地化管理方式的表空间所有都是使用 extent management local 子句。当创建扩展大小等同的表空间时，使用 uniform 关键字，并指定每次扩展时的大小，当创建扩展大小为自动管理时，使用 autoallocate 关键字，并且不需要指定扩展时的大小。

3：通过段空间管理方式创建表空间

         段空间管理方式时建立在本地化空间管理方式基础之上的，即：只有本地化管理方式的表空间，才能进一步在其基础上建立段空间管理方式。它使用“SEGMENT SPACE MANAGEMENT MANUAL / LOCAL”语句，段空间管理又可以分为手动段和自动段两种空间管理方式。

1：手动段空间管理方式

示例1：通过本地化管理方式 local 创建一个表空间，其扩展大小为自动管理，其段空间管理方式为手动

SYS@orcl> create tablespace tbs_test3 datafile '/u01/app/oracle/oradata/orcl/datafile3.dbf' size 20m extent management local autoallocate segment space management manual;  2   3 Tablespace created.  4   5 SYS@orcl>  6   7 

2：采用段空间管理方式

示例2：通过本地化管理方式 local  创建1个大小为20M 的表空间，其扩展大小为自动管理，其段空间管理方式为自动，

SYS@orcl> create tablespace tbs_test4 datafile '/u01/app/oracle/oradata/orcl/datafile4.dbf' size 20m   extent management local autoallocate segment space management auto;  2   3 Tablespace created.  4   5 SYS@orcl>  6 

4：创建非标准块表空间

示例1：创建一个非标准块的表空间，块的大小为标准块的2倍

SYS@orcl> show parameter db_16k_cache_size  2   3 NAME                                 TYPE        VALUE  4 ------------------------------------ ----------- ------------------------------  5 db_16k_cache_size                    big integer 0  6 SYS@orcl> alter system set db_16k_cache_size=16M scope=both;  7   8 System altered.  9  10 SYS@orcl> show parameter db_16k_cache_size 11  12 NAME                                 TYPE        VALUE 13 ------------------------------------ ----------- ------------------------------ 14 db_16k_cache_size                    big integer 16M 15 SYS@orcl> create tablespace tbs_test5 datafile '/u01/app/oracle/oradata/orcl/datafile5.dbf' size 60M reuse autoextend on next 4m maxsize unlimited blocksize 16k extent management local autoallocate segment space management auto; 16  17 Tablespace created. 18  19 SYS@orcl> 20 

5：建立大文件 表空间

  示例：创建一个大文件表空间，指定一个数据文件，并且数据文件的大小为2GB

  2 SYS@orcl> create bigfile tablespace tbs_big_1 datafile '/u01/app/oracle/oradata/orcl/datafilebig_1.dfb' size 2G;  3   4 Tablespace created.  5   6 SYS@orcl>  7 

SYS@orcl> alter tablespace tbs_big_1 resize 1g;  2   3 Tablespace altered.  4   5 SYS@orcl>

SYS@orcl> alter database datafile '/u01/app/oracle/oradata/orcl/datafile3.dbf' resize 100M;  2   3 Database altered.  4   5 SYS@orcl>

四：维护表空间与数据文件

1：设置默认表空间

示例：将临时表空间 temp_1 设置为默认的临时表空间

1：创建 临时表空间 temp_1

SYS@orcl> create TEMPORARY tablespace temp_1 TEMPFILE '/u01/app/oracle/oradata/orcl/temp_1.dbf' size 50m autoextend on next 50m maxsize 2048m extent management local;  2   3 Tablespace created.  4   5 SYS@orcl>

2:将临时表空间temp_1 设置为默认的临时表空间

  2 SYS@orcl> alter database default temporary tablespace temp_1;  3   4 Database altered.  5   6 SYS@orcl>

示例2：将表空间 tbs_example 设置为默认的永久表空间

  2 SYS@orcl> alter database default tablespace TBS_TEST1;  3   4 Database altered.  5   6 SYS@orcl>

2：更改表空间的状态

示例：修改tbs_test3 表空间为只读状态

SYS@orcl> alter tablespace TBS_TEST3 read only;  2   3 Tablespace altered.  4   5 SYS@orcl>

示例：修改 tbs_test3 表空间状态为 可读可写状态；

  2 SYS@orcl> alter tablespace TBS_TEST3  read write;  3   4 Tablespace altered.  5   6 SYS@orcl>

3：重命名表空间

示例：将 tbs_test3 表空间重新命名为： tbs_test_3

SYS@orcl> alter tablespace tbs_test3 rename to tbs_test_3;  2   3 Tablespace altered.  4   5 SYS@orcl>

4：删除表空间

示例：删除表空间 tbs_test2及其包含的所有内容

SYS@orcl> drop tablespace TBS_TEST2 including contents cascade constraints;  2   3 Tablespace dropped.  4   5 SYS@orcl> ho rm -f /u01/app/oracle/oradata/orcl/datafile2.dbf  6   7 SYS@orcl>

在上面的代码中，不但删除了表空间 tbs_test2 ,而且删除了表空间中的数据(including contents) 和完整性约束（cascade constraints）.调用了 linux 删除 文件的命令 删除了 表空间的数据文件。

5：维护表空间中的数据文件

1：向 表空间中添加数据文件

示例：向 users 表空间在中添加1个新的数据文件 users02.dbf ,该文件支持自动扩展，扩展能力为每次扩展5MB,并且该文件的最大空间不受限制。

SYS@orcl> alter  tablespace users add datafile '/u01/app/oracle/oradata/orcl/users02.dbf' size 10m autoextend on next 5m maxsize unlimited;  2   3 Tablespace altered.  4   5 SYS@orcl>

2：从表空间中删除数据文件

示例：删除 users  表空间中的 users02.dbf 数据文件。

  2 SYS@orcl> alter tablespace users drop datafile '/u01/app/oracle/oradata/orcl/users02.dbf' ;  3   4 Tablespace altered.  5   6 SYS@orcl>

3：对数据文件的自动扩展设置

示例：实现查询 tbs_test3表空间 中的数据文件是否为自动扩展，如果不是，则修改为自动扩展 扩展量为10吗并且最大扩展空间不受限制。

  2 SYS@orcl> col file_name for a50;  3 SYS@orcl> select  file_name ,autoextensible from dba_data_files where tablespace_name = 'TBS_TEST3';  4   5 no rows selected  6   7 SYS@orcl> select  file_name ,autoextensible from dba_data_files where tablespace_name = 'TBS_TEST_3'  8   2  ;  9  10 FILE_NAME                                          AUT 11 -------------------------------------------------- --- 12 /u01/app/oracle/oradata/orcl/datafile3.dbf         NO 13  14 SYS@orcl> alter database datafile '/u01/app/oracle/oradata/orcl/datafile3.dbf' autoextend on next 10m maxsize unlimited; 15  16 Database altered. 17  18 SYS@orcl> 19 

从上面运行结果可以看出， datafile3.dbf 数据文件 不自动扩展（autoextsible 属性值为 no ） 然后使用 alter database 语句修改数据文件为自动扩展。接下来再通过查询 dba_data_file 数据字典来查看 datafile3。dbf 文件是否为自动扩展

  2 SYS@orcl> select  file_name ,autoextensible from dba_data_files where tablespace_name = 'TBS_TEST_3';  3   4 FILE_NAME                                          AUT  5 -------------------------------------------------- ---  6 /u01/app/oracle/oradata/orcl/datafile3.dbf         YES  7   8 SYS@orcl>  9 

五：管理撤销表空间

1：撤销表空间的作用

1： 使读写一致

2：可以回滚事务

3：事务恢复

4：闪回操作

2：撤销表空间的初始化参数

SYS@orcl> show parameter undo_tablespace;  2   3 NAME                                 TYPE        VALUE  4 ------------------------------------ ----------- ------------------------------  5 undo_tablespace                      string      UNDOTBS1  6   7 SYS@orcl> show parameter undo_management;  8   9 NAME                                 TYPE        VALUE 10 ------------------------------------ ----------- ------------------------------ 11 undo_management                      string      AUTO 12 SYS@orcl> show parameter undo_retention 13  14 NAME                                 TYPE        VALUE 15 ------------------------------------ ----------- ------------------------------ 16 undo_retention                       integer     900 17 SYS@orcl> 18 

3：撤销表空间的基本操作

1： 创建 undo 表空间

创建 UNDO 表空间 需要使用 create undo tablespace 语句。

例如：创建一个撤销undo 表空间，并指定数据文件大小为 100M，

  2 SYS@orcl>  create undo tablespace undo_tbs_1 datafile '/u01/app/oracle/oradata/orcl/undotbs_1.dbf' size 100m;  3   4 Tablespace created.  5   6 SYS@orcl>

在创建 表空间 需要注意以下两个方面：

• undo 表空间对应的数据文件大小通常由DML 操作可能产生的最大数据量来确定，通常该数据文件的大小至少应为1GB。
• 由于 undo表空间只用于存放撤销数据，所以不要在undo表空间建立任何数据对象（如：表、索引）

2：修改 undo 表空间

示例：向表空间 undo_tbs_1 中添加1个新的数据文件，指定该文件大小为2GB

SYS@orcl> alter tablespace undo_tbs_1 add datafile '/u01/app/oracle/oradata/orcl/undotbs_add.dbf' size 2G;  2   3 Tablespace altered.  4   5 SYS@orcl>

3：切换 undo  表空间

示例：把当前系统默认的 undo 表空间切换到自定义 撤销表空间 undo_tbs_1

  2 SYS@orcl> alter system set undo_tablespace = undo_tbs_1;  3   4 System altered.  5   6 SYS@orcl>

4：删除 undo 表空间

      如果确认不再使用某个自定义的 undo 表空间，数据库管理员就可以将其删除掉，删除掉 undo 表空间 与删除普通的永久表空间一样，都使用 drop tablespace 语句。

      当需要注意的是：当前例程正在使用 undo　表空间是不能被删除的，如果确定要删除当前例程正在使用的　ｕｎｄｏ　表空间，管理员应首先切换　ｕｎｄｏ　表空间，然后在删除且换掉的　ｕｎｄｏ　表空间。

示例：把当前例程　的　ｕｎｄｏ　表空间　从　‘UNDO_TBS-1’切换到 “undotbs1”；然后在删除“undo_tbs-1” 表空间

SYS@orcl> alter system set undo_tablespace = undotbs1;  2   3 System altered.  4   5 SYS@orcl> drop tablespace undo_tbs_1;  6   7 Tablespace dropped.  8   9 SYS@orcl>

5：查询 undo  表空间的信息

SYS@orcl> show parameter undo_tablespace;  2   3 NAME                                 TYPE        VALUE  4 ------------------------------------ ----------- ------------------------------  5 undo_tablespace                      string      UNDOTBS1  6 SYS@orcl>

  2 SYS@orcl> select tablespace_name from dba_tablespaces where contents='UNDO';  3   4 TABLESPACE_NAME  5 ------------------------------  6 UNDOTBS1  7   8 SYS@orcl>

SYS@orcl> select to_char(begin_time,'yyyy-MM-dd hh24:mi:ss') as "开始时间",to_char(end_time,'yyyy-MM-dd hh24:mi:ss') as "结束时间", undoblks as "回退块数"  from v$undostat order by begin_time;  2   3 开始时间            结束时间              回退块数  4 ------------------- ------------------- ----------  5 2018-03-07 23:23:03 2018-03-08 20:33:03          0  6 2018-03-08 20:33:03 2018-03-08 20:43:03         86  7 2018-03-08 20:43:03 2018-03-08 20:53:03          9  8 2018-03-08 20:53:03 2018-03-08 21:03:03        183  9 2018-03-08 21:03:03 2018-03-08 21:13:03         12 10 2018-03-08 21:13:03 2018-03-08 21:23:03       3057 11 2018-03-08 21:23:03 2018-03-08 21:33:03         14 12 2018-03-08 21:33:03 2018-03-08 21:43:03         14 13 2018-03-08 21:43:03 2018-03-08 21:53:03         17 14 2018-03-08 21:53:03 2018-03-08 22:03:03       1810 15 2018-03-08 22:03:03 2018-03-08 22:13:03         11 16  17 开始时间            结束时间              回退块数 18 ------------------- ------------------- ---------- 19 2018-03-08 22:13:03 2018-03-08 22:23:03         21 20 2018-03-08 22:23:03 2018-03-08 22:33:03         15 21 2018-03-08 22:33:03 2018-03-08 22:43:03         17 22 2018-03-08 22:43:03 2018-03-08 22:53:03         14 23 2018-03-08 22:53:03 2018-03-08 23:03:03         97 24 2018-03-08 23:03:03 2018-03-08 23:13:03         16 25 2018-03-08 23:13:03 2018-03-08 23:23:03         14 26 2018-03-08 23:23:03 2018-03-08 23:33:03         14 27 2018-03-08 23:33:03 2018-03-08 23:43:03         16 28 2018-03-08 23:43:03 2018-03-08 23:53:03         18 29 2018-03-08 23:53:03 2018-03-09 00:03:03        104 30 

示例：通过动态性能视图监视 特定的 undo 段的信息，包括 段名称、活动事务个数 和段中扩展个数信息

SYS@orcl> select rn.name,rs.xacts,rs.writes,rs.extents from v$rollname rn,v$rollstat rs where rn.usn=rs.usn;  2   3 NAME                                XACTS     WRITES    EXTENTS  4 ------------------------------ ---------- ---------- ----------  5 SYSTEM                                  0      42740          6  6 _SYSSMU1_3138885392$                    0   25562196          4  7 _SYSSMU2_4228238222$                    0   22379888          5  8 _SYSSMU3_2210742642$                    0   23811022          3  9 _SYSSMU4_1455318006$                    0   40462774         17 10 _SYSSMU5_3787622316$                    0   28625936          4 11 _SYSSMU6_2460248069$                    0   29402838          4 12 _SYSSMU7_1924883037$                    0   32887308         12 13 _SYSSMU8_1909280886$                    0   28905808          4 14 _SYSSMU9_3593450615$                    0   27273210         18 15 _SYSSMU10_2490256178$                   0   25188958          4 16  17 11 rows selected. 18  19 SYS@orcl>

示例：通过查询动态性能视图 v$transaction 来显示 事务的名称和状态

SYS@orcl> select name ,status from v$transaction;  2   3 no rows selected  4   5 SYS@orcl>

示例：在数据字典 dba_undo_extents 中 ，查询指定段的信息，包括段编号、段的大小和段的状态 等

  2 SYS@orcl> select segment_name ,extent_id,bytes,status from  dba_undo_extents where segment_name LIKE '%SYSS%';  3   4 SEGMENT_NAME                    EXTENT_ID      BYTES STATUS  5 ------------------------------ ---------- ---------- ---------  6 _SYSSMU1_3138885392$                    0      65536 EXPIRED  7 _SYSSMU1_3138885392$                    1      65536 EXPIRED  8 _SYSSMU1_3138885392$                    2    1048576 UNEXPIRED  9 _SYSSMU1_3138885392$                    3    1048576 EXPIRED 10 _SYSSMU2_4228238222$                    0      65536 EXPIRED 11 _SYSSMU2_4228238222$                    1      65536 EXPIRED 12 _SYSSMU2_4228238222$                    2    1048576 EXPIRED 13 _SYSSMU2_4228238222$                    3    1048576 UNEXPIRED 14 _SYSSMU2_4228238222$                    4    1048576 EXPIRED 15 _SYSSMU3_2210742642$                    0      65536 EXPIRED 16 _SYSSMU3_2210742642$                    1      65536 EXPIRED 17  18 SEGMENT_NAME                    EXTENT_ID      BYTES STATUS 19 ------------------------------ ---------- ---------- --------- 20 _SYSSMU3_2210742642$                    2    1048576 UNEXPIRED 21 _SYSSMU4_1455318006$                    0      65536 EXPIRED 22 _SYSSMU4_1455318006$                    1      65536 EXPIRED 23 _SYSSMU4_1455318006$                    2      65536 EXPIRED 24 _SYSSMU4_1455318006$                    3      65536 EXPIRED 25 _SYSSMU4_1455318006$                    4      65536 EXPIRED 26 _SYSSMU4_1455318006$                    5      65536 EXPIRED 27 _SYSSMU4_1455318006$                    6      65536 EXPIRED 28 _SYSSMU4_1455318006$                    7      65536 EXPIRED 29 _SYSSMU4_1455318006$                    8      65536 EXPIRED 30 _SYSSMU4_1455318006$                    9      65536 EXPIRED

六：管理临时表空间

1：临时表空间概述

2：查询、创建临时表空间

1:查询现有的临时表空间

SYS@orcl> col file_name for a40;  2 SYS@orcl> col tablespace_name for a10;  3 SYS@orcl> select file_name ,bytes,tablespace_name from dba_temp_files;  4   5 FILE_NAME                                     BYTES TABLESPACE  6 ---------------------------------------- ---------- ----------  7 /u01/app/oracle/oradata/orcl/temp.dbf      52428800 TEMP  8 /u01/app/oracle/oradata/orcl/temp_1.dbf    52428800 TEMP_1  9 /u01/app/oracle/oradata/orcl/temp_2.dbf  3221225472 TEMP_1 10 /home/oracle/oracle_system_files_back/da 2147483648 TEMP_1 11 tafiles_bak/datafiles_bak_20180311/temp_ 12 6.dbf 13  14 /u01/app/oracle/oradata/orcl/tbs_example   52428800 TBS_EXAMPL 15  .dbf                                               E 16  17  18 SYS@orcl>

2：创建一个新的临时表空间 temp_01 并设置为系统默认的临时表空间。

  2 SYS@orcl> create temporary tablespace temp_01 tempfile '/u01/app/oracle/oradata/orcl/temp_01.dbf' size 300m;  3   4 Tablespace created.  5   6 SYS@orcl> alter database default temporary tablespace temp_01;  7   8 Database altered.  9  10 SYS@orcl>

3：关于临时表空间组

1：创建 临时表空间组

SYS@orcl> create temporary tablespace tp1 tempfile '/u01/app/oracle/oradata/orcl/tp1.dbf' size 100m tablespace group group1;  2   3 Tablespace created.  4   5 SYS@orcl> create temporary tablespace tp2 tempfile '/u01/app/oracle/oradata/orcl/tp2.dbf' size 100m tablespace group group1;  6   7 Tablespace created.  8   9 SYS@orcl>

2：转移临时表空间到另外一个组

SYS@orcl> alter tablespace temp tablespace group group1;  2   3 Tablespace altered.  4   5 SYS@orcl> alter tablespace temp_1 tablespace group group1;  6   7 Tablespace altered.  8   9 SYS@orcl> create temporary tablespace tp3 tempfile '/u01/app/oracle/oradata/orcl/tp3.dbf' size 10m tablespace group group3; 10  11 Tablespace created. 12  13  14  15  16 SYS@orcl> alter tablespace tp1 tablespace group group3; 17  18 Tablespace altered. 19  20 SYS@orcl>

SYS@orcl> select  * from dba_tablespace_groups where group_name like '%GROUP%' ORDER BY GROUP_NAME ASC;  2   3 GROUP_NAME                     TABLESPACE  4 ------------------------------ ----------  5 GROUP1                         TP2  6 GROUP1                         TEMP_1  7 GROUP1                         TEMP  8 GROUP3                         TP3  9 GROUP3                         TP1 10  11 SYS@orcl>

3： 把临时表空间组分配给指定的用户使用

SYS@orcl> alter user hr temporary tablespace group3;  2   3 User altered.  4   5 SYS@orcl>

4：设置默认的临时表空间组

SYS@orcl> alter database orcl default temporary tablespace group1;  2   3 Database altered.  4   5 SYS@orcl>

5：删除临时表空间组

SYS@orcl> select  * from dba_tablespace_groups where group_name like '%GROUP%' ORDER BY GROUP_NAME ASC;  2   3 GROUP_NAME                     TABLESPACE  4 ------------------------------ ----------  5 GROUP1                         TP2  6 GROUP1                         TEMP_1  7 GROUP1                         TEMP  8 GROUP3                         TP3  9 GROUP3                         TP1 10  11 SYS@orcl> alter tablespace tp1 tablespace group group1; 12  13 Tablespace altered. 14  15 SYS@orcl> drop tablespace tp3 including contents and datafiles; 16  17 Tablespace dropped. 18  19 SYS@orcl> select  * from dba_tablespace_groups where group_name like '%GROUP%' ORDER BY GROUP_NAME ASC; 20  21 GROUP_NAME                     TABLESPACE 22 ------------------------------ ---------- 23 GROUP1                         TEMP 24 GROUP1                         TP2 25 GROUP1                         TP1 26 GROUP1                         TEMP_1 27  28 SYS@orcl>

扩展内容：

1：增加临时表空间数据文件

SYS@orcl> alter tablespace temp_1 add tempfile '/u01/app/oracle/oradata/orcl/temp_2.dbf' size 2G autoextend off;  2   3 Tablespace altered.  4   5 SYS@orcl>

2：删除临时表空间的数据文件

SYS@orcl> alter tablespace temp_1 add tempfile '/u01/app/oracle/oradata/orcl/temp_3.dbf' size 2G autoextend off;  2   3 Tablespace altered.  4   5 SYS@orcl> alter tablespace temp_1 drop tempfile '/u01/app/oracle/oradata/orcl/temp_3.dbf' ;  6   7 Tablespace altered.  8   9 SYS@orcl>

注意：这种删除临时表空间的写法会将对应的物理文件删除。

另外一种删除方式：

  2 SYS@orcl> alter tablespace temp_1 add tempfile '/u01/app/oracle/oradata/orcl/temp_4.dbf' size 2G autoextend off;  3   4 Tablespace altered.  5   6   7   8 SYS@orcl> alter database tempfile '/u01/app/oracle/oradata/orcl/temp_4.dbf' drop including datafiles;  9  10 Database altered. 11  12 SYS@orcl>

注意：删除临时表空间的临时数据文件时，不需要指定INCLUDING DATAFILES 选项也会真正删除物理文件，否则需要手工删除物理文件。

3：修改 临时表空间数据文件的大小

  2 SYS@orcl> alter database tempfile '/u01/app/oracle/oradata/orcl/temp_2.dbf' resize 3g;  3   4 Database altered.  5   6 SYS@orcl>

4：将：临时表空间数据文件 脱机 和在线 之间切换

SYS@orcl> alter database tempfile '/u01/app/oracle/oradata/orcl/temp_2.dbf' offline;  2   3 Database altered.  4   5 SYS@orcl> alter database tempfile '/u01/app/oracle/oradata/orcl/temp_2.dbf' online;  6   7 Database altered.  8   9 SYS@orcl>

默认临时表空间并不能脱机,否则会报错，如下所示

SQL> ALTER TABLESPACE TEMP OFFLINE;

ALTER TABLESPACE TEMP OFFLINE

*

ERROR at line 1:

ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

5：设置临时表空间数据文件 自动扩展：

  2 SYS@orcl> alter tablespace temp_1 add tempfile '/u01/app/oracle/oradata/orcl/temp_6.dbf' size 2G ;  3   4 Tablespace altered.  5   6 SYS@orcl> alter database tempfile '/u01/app/oracle/oradata/orcl/temp_6.dbf' autoextend on next 100m maxsize unlimited;  7   8 Database altered.  9  10 SYS@orcl>

6：移动/重命名  临时表空间数据文件

  2 SYS@orcl> alter database tempfile '/u01/app/oracle/oradata/orcl/temp_6.dbf' offline;  3   4 Database altered.  5   6 SYS@orcl> ho mv /u01/app/oracle/oradata/orcl/temp_6.dbf  /home/oracle/oracle_system_files_back/datafiles_bak/datafiles_bak_20180311/temp_6.dbf  7   8   9 SYS@orcl> alter database rename file '/u01/app/oracle/oradata/orcl/temp_6.dbf' to '/home/oracle/oracle_system_files_back/datafiles_bak/datafiles_bak_20180311/temp_6.dbf'; 10  11 Database altered. 12  13 SYS@orcl> alter database tempfile '/home/oracle/oracle_system_files_back/datafiles_bak/datafiles_bak_20180311/temp_6.dbf' online; 14  15 Database altered. 16  17 SYS@orcl>

7：删除临时表空间

SYS@orcl> create TEMPORARY tablespace temp_2 TEMPFILE '/u01/app/oracle/oradata/orcl/temp_2_1.dbf' size 50m autoextend on next 50m maxsize 2048m extent management local;  2   3 Tablespace created.  4   5   6 SYS@orcl> drop tablespace temp_2 including contents and datafiles cascade constraints;  7   8 Tablespace dropped.

注意：不能删除当前用户的默认表空间，否则会报ORA-12906错误

SYS@orcl> drop tablespace temp_1 including contents and  datafiles cascade constraints;  2 drop tablespace temp_1 including contents and  datafiles cascade constraints  3 *  4 ERROR at line 1:  5 ORA-12906: cannot drop default temporary tablespace  6   7   8 SYS@orcl>

———————————————————————————————————————————————————————————————————————————————————