/**PageBeginHtml Block Begin **/ /***自定义返回顶部小火箭***/ /*生成博客目录的JS 开始*/ /*生成博客目录的JS 结束*/

管理表空间和数据文件

 

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

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

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

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

 

 

 

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

 

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

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

image

 

 

 

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

  1 SYS@orcl> col tablespace_name for a10;
  2 SYS@orcl> col file_name for a50;
  3 SYS@orcl> col bytes for 999,999,999
  4 SYS@orcl> select tablespace_name ,file_name,bytes from dba_data_files order by tablespace_name;
  5 
  6 TABLESPACE FILE_NAME                                                 BYTES
  7 ---------- -------------------------------------------------- ------------
  8 EXAMPLE    /u01/app/oracle/oradata/orcl/example01.dbf          362,414,080
  9 SYSAUX     /u01/app/oracle/oradata/orcl/sysaux01.dbf           786,432,000
 10 SYSTEM     /u01/app/oracle/oradata/orcl/system01.dbf           765,460,480
 11 UNDOTBS1   /u01/app/oracle/oradata/orcl/undotbs01.dbf          256,901,120
 12 USERS      /u01/app/oracle/oradata/orcl/users01.dbf              5,242,880
 13 
 14 SYS@orcl>

 

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

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

 

 

二：oracle 11g 默认的表空间

 

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

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

image

 

      1: system 表空间

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

 

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

  1 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 表空间的相关信息

  1 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

image

 

三：创建表空间

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

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

 

1：创建表空间的语法

创建表空间的语法如下：

 



语法中关键字


    

  • 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：通过本地化管理方式创建表空间




image


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


1: 创建前：


image


2: 创建脚本：




  1 
  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: 创建后：


image


 


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


 




  1 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>




 


创建后的：


image


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


 




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




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


 


1：手动段空间管理方式


image


 


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




  1 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 




 


image


 


2：采用段空间管理方式


image


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




  1 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 




image


image


 




4：创建非标准块表空间




image


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




  1 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：建立大文件 表空间




image


 


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




  1 
  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 




image


image




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




image


 




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




 




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




image




1：设置默认表空间




image


 


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


1：创建 临时表空间 temp_1




  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 设置为默认的临时表空间




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




 


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




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




 




2：更改表空间的状态




image


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




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




 


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




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




 




3：重命名表空间




image


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




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




image


 




4：删除表空间




image


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


 




  1 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：维护表空间中的数据文件




image


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


image


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




  1 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：从表空间中删除数据文件


image


 


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




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




 


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


image


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




  1 
  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 文件是否为自动扩展




  1 
  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 




 




五：管理撤销表空间




image




1：撤销表空间的作用




1： 使读写一致


imageimage


2：可以回滚事务


image


3：事务恢复


image


4：闪回操作


image


 


 




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




imageimage


 




  1 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，




  1 
  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 表空间


image


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




  1 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  表空间


image


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




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




image


 


4：删除 undo 表空间


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


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


 


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




  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  表空间的信息


image




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




 


image




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




image




  1 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 




image


image


image


 


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




  1 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>




image


 


image


 


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


 




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




 


image


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




  1 
  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




image


 


 


 


 




六：管理临时表空间




image




1：临时表空间概述




image


 




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




image


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




  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 并设置为系统默认的临时表空间。




  1 
  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>




image


 


 




3：关于临时表空间组




image


 


1：创建 临时表空间组


image




  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：转移临时表空间到另外一个组


image




  1 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>




image




  1 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>




image


 


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


image




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




 


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


 


image




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




 


5：删除临时表空间组


image




  1 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：增加临时表空间数据文件




  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：删除临时表空间的数据文件




  1 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>




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


另外一种删除方式：




  1 
  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：修改 临时表空间数据文件的大小




  1 
  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：将：临时表空间数据文件 脱机 和在线 之间切换




  1 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：设置临时表空间数据文件 自动扩展：




  1 
  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：移动/重命名  临时表空间数据文件




  1 
  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：删除临时表空间




  1 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错误




  1 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>




 


 


 


 


 


 


 




 


 


 


 


 


 


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







            

            
posted @ 
2018-03-09 11:58 
一品堂.技术学习笔记 
阅读(772) 
评论(0) 
编辑 
收藏 
举报