AI 助理
备案控制台
开发者社区 数据库 文章 正文

Oracle临时表空间总结

2018-07-12 1311
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： Oracle临时表空间总结http://www.bieryun.com/3338.html 临时表空间概念 临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象,当ORACLE里需要用到SORT的时候，并且当PGA中sort_area_size大小不够时，将会把数据放入临时表空间里进行排序。


临时表空间概念

临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象,当ORACLE里需要用到SORT的时候，并且当PGA中sort_area_size大小不够时，将会把数据放入临时表空间里进行排序。像数据库中一些操作： CREATE INDEX、 ANALYZE、SELECT DISTINCT、ORDER BY、GROUP BY、 UNION ALL、 INTERSECT、MINUS、SORT-MERGE JOINS、HASH JOIN等都可能会用到临时表空间。当操作完成后，系统会自动清理临时表空间中的临时对象，自动释放临时段。这里的释放只是标记为空闲、可以重用，其实实质占用的磁盘空间并没有真正释放。这也是临时表空间有时会不断增大的原因。

临时表空间存储大规模排序操作(小规模排序操作会直接在RAM里完成，大规模排序才需要磁盘排序Disk Sort)和散列操作的中间结果.它跟永久表空间不同的地方在于它由临时数据文件（temporary files）组成的,而不是永久数据文件（datafiles）。临时表空间不会存储永久类型的对象，所以它不会也不需要备份。另外，对临时数据文件的操作不产生redo日志，不过会生成undo日志。

创建临时表空间或临时表空间添加临时数据文件时，即使临时数据文件很大，添加过程也相当快。这是因为ORACLE的临时数据文件是一类特殊的数据文件:稀疏文件（Sparse File）,当临时表空间文件创建时，它只会写入文件头部和最后块信息(only writes to the header and last block of the file)。它的空间是延后分配的.这就是你创建临时表空间或给临时表空间添加数据文件飞快的原因。

另外，临时表空间是NOLOGGING模式以及它不保存永久类型对象，因此即使数据库损毁，做Recovery也不需要恢复Temporary Tablespace。

临时表空间信息

 

查看实例的临时表空间

SQL1：

SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE
 
2 FROM DATABASE_PROPERTIES
 
3 WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
 
PROPERTY_NAME                    PROPERTY_VALUE
 
------------------------------ ----------------------------
 
DEFAULT_TEMP_TABLESPACE               TEMP
 

SQL2：

SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS;

 

查看临时表空间信息：

 

SET LINESIZE 1200
COL NAME FOR A60
SELECT FILE#                        AS FILE_NUMBER
    ,NAME                           AS NAME
    ,CREATION_TIME                  AS CREATION_TIME
    ,BLOCK_SIZE                     AS BLOCK_SIZE
    ,BYTES/1024/1024/1024           AS "FILE_SIZE(G)"
    ,CREATE_BYTES/1024/1024/1024    AS "INIT_SIZE(G)"
    ,STATUS                         AS STATUS
    ,ENABLED                        AS ENABLED
FROM V$TEMPFILE;

 

官方文档关于V$TEMPFILE的介绍如下

td {border:solid #9ec9ec; border-width:0px 1px 1px 0px; padding:4px 0px;} table {border:1px solid #9ec9ec; width:100%;border-width:1px 0px 0px 1px;text-align:left;font-size:12px}

Column Datatype Description
FILE# NUMBER Absolute file number
CREATION_CHANGE# NUMBER Creation System Change Number (SCN)
CREATION_TIME DATE Creation time
TS# NUMBER Tablespace number
RFILE# NUMBER Relative file number in the tablespace
STATUS VARCHAR2(7) Status of the file (OFFLINE|ONLINE)
ENABLED VARCHAR2(10) Enabled for read and/or write
BYTES NUMBER Size of the file in bytes (from the file header)
BLOCKS NUMBER Size of the file in blocks (from the file header)
CREATE_BYTES NUMBER Creation size of the file (in bytes)
BLOCK_SIZE NUMBER Block size for the file
NAME VARCHAR2(513) Name of the file 
SET LINESIZE 1200
    COL TABLESPACE_NAME FOR A30
   COL FILE_NAME FOR A60
SELECT TABLESPACE_NAME                 AS TABLESPACE_NAME
        ,FILE_NAME                     AS FILE_NAME
        ,BLOCKS                        AS BLOCKS
        ,STATUS                        AS STATUS
        ,AUTOEXTENSIBLE                AS AUTOEXTENSIBLE
        ,BYTES/1024/1024/1024          AS "FILE_SIZE(G)"
        ,DECODE(MAXBYTES, 0, BYTES/1024/1024/1024,
                          MAXBYTES/1024/1024/1024)
                                       AS "MAX_SIZE(G)"
        ,INCREMENT_BY                  AS "INCREMENT_BY"
        ,USER_BYTES/1024/1024/1024     AS "USEFUL_SIZE"
FROM DBA_TEMP_FILES;

DBA_TEMP_FILES describes all temporary files (tempfiles) in the database.

Column Datatype NULL Description
FILE_NAME VARCHAR2(513)   Name of the database temp file
FILE_ID NUMBER   File identifier number of the database temp file
TABLESPACE_NAME VARCHAR2(30) NOT NULL Name of the tablespace to which the file belongs
BYTES NUMBER   Size of the file (in bytes)
BLOCKS NUMBER   Size of the file (in Oracle blocks)
STATUS CHAR(9)  

File status:

·

· AVAILABLE
RELATIVE_FNO NUMBER   Tablespace-relative file number
AUTOEXTENSIBLE VARCHAR2(3)   Indicates whether the file is autoextensible (YES) or not (NO)
MAXBYTES NUMBER   maximum size of the file (in bytes)
MAXBLOCKS NUMBER   Maximum size of the file (in Oracle blocks)
INCREMENT_BY NUMBER   Default increment for autoextension
USER_BYTES NUMBER   Size of the useful portion of the file (in bytes)
USER_BLOCKS NUMBER   Size of the useful portion of the file (in Oracle blocks)

 

SQL> SELECT BYTES,BLOCKS,  USER_BYTES, USER_BLOCKS,
            BLOCKS -USER_BLOCKS AS SYSTEM_USED
     FROM DBA_TEMP_FILES;
 
     BYTES     BLOCKS USER_BYTES USER_BLOCKS SYSTEM_USED
---------- ---------- ---------- ----------- -----------
2147483648     262144 2146435072      262016         128
1073741824     131072 1072693248      130944         128
 209715200      25600  208666624       25472         128

 

这四列中， BYTES , BLOCKS 显示的是临时文件有多少BYTE大小，包含多少个数据块。而USER_BYTES,USER_BLOCKS是可用的BYTE和数据块个数。因此，我们可以知道临时文件中有一部分是被系统占用的，大概可以理解成文件头信息，这一部分大小是128个block，如下图所示:

clip_image001

 

管理临时表空间

 

创建临时表空间

下面是一个简单的创建临时表空间的例子，具体很多细节可以参考官方文档，这里省略,不做过多介绍。

http://docs.oracle.com/cd/B10501_01/server.920/a96540/statements_75a.htm

http://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces002.htm#ADMIN11366

 

CREATE TEMPORARY TABLESPACE TMP
 
TEMPFILE '/u01/gsp/oradata/TMP01.dbf'
 
SIZE 8G
 
AUTOEXTEND OFF;
 

 

增加数据文件

当临时表空间太小时，就需要扩展临时表空间（添加数据文件、增大数据文件、设置文件自动扩展）；有时候需要将临时数据文件分布到不同的磁盘分区中，提升IO性能，也需要通过删除、增加临时表空间数据文件。

 

SQL> ALTER TABLESPACE TEMP
  2  ADD TEMPFILE '/u04/gsp/oradata/temp02.dbf'
  3  SIZE 4G
  4  AUTOEXTEND ON
  5  NEXT 128M
  6  MAXSIZE 6G;
 
Tablespace altered.
 
 
SQL> ALTER  TABLESPACE TMP
ADD TEMPFILE '/u03/eps/oradata/temp02.dbf'
SIZE 64G
AUTOEXTEND OFF;
 
Tablespace altered.

 

删除数据文件

 

例如，我想删除临时表空间下的某个文件，那么我们有两种方式删除临时表空间的数据文件。

方法1：

SQL> ALTER TABLESPACE TEMP
 
     DROP TEMPFILE '/u01/app/oracle/oradata/GSP/temp02.dbf';
 
Tablespace altered.
 

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

方法2：

SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/GSP/temp02.dbf'
 
DROP INCLUDING DATAFILES;
 
Database altered.
 

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

 

调整文件大小

 

如下例子，需要将临时数据文件从1G大小调整为2G

SQL> ALTER DATABASE TEMPFILE
 
'/u01/app/oracle/oradata/GSP/temp02.dbf' RESIZE 2G;
 

 

文件脱机联机

 

SQL> ALTER DATABASE TEMPFILE
  2  '/u01/app/oracle/oradata/GSP/temp02.dbf' OFFLINE;
 
Database altered.
 
SQL> ALTER DATABASE TEMPFILE
  2  '/u01/app/oracle/oradata/GSP/temp02.dbf' ONLINE;
 
Database altered.

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

SQL> ALTER TABLESPACE TEMP OFFLINE;

ALTER TABLESPACE TEMP OFFLINE

*

ERROR at line 1:

ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

 

设置文件自动扩展

 

SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/GSP/temp03.dbf'
 
2 AUTOEXTEND ON
 
3 NEXT 100M
 
4 MAXSIZE UNLIMITED;
 

 

移动重命名文件

例如，我需要将/u01/app/oracle/oradata/GSP/temp4.dbf这个文件重命名为/u01/app/oracle/oradata/GSP/temp04.dbf

1: 将临时表空间的临时文件脱机

SQL> ALTER DATABASE TEMPFILE
  2   '/u01/app/oracle/oradata/GSP/temp4.dbf' OFFLINE;

2：移动或重命名相关的临时文件

mv /u01/app/oracle/oradata/GSP/temp4.dbf /u01/app/oracle/oradata/GSP/temp04.dbf'

3: 使用脚本ALTER DATABASE RENAME FILE

SQL> ALTER DATABASE RENAME FILE
  2  '/u01/app/oracle/oradata/GSP/temp4.dbf' TO
  3  '/u01/app/oracle/oradata/GSP/temp04.dbf';

4: 将临时表空间的临时文件联机

SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/GSP/temp04.dbf' ONLINE;
 
Database altered.

 

删除临时表空间

SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

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

SQL> DROP TABLESPACE TMP INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
 
DROP TABLESPACE TMP INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS
 
*
 
ERROR at line 1:
 
ORA-12906: cannot drop default temporary tablespace
 

如果需要删除某一个默认的临时表空间，则必须先创建一个临时表空间，然后指定新创建的表空间为默认表空间，然后删除原来的临时表空间

 

临时表空间组

临进表空间组:

临进表空间组是ORACLE 10g引入的一个新特性，它是一个逻辑概念，不需要显示的创建和删除。只要把一个临时表空间分配到一个组中，临时表空间组就自动创建，所有的临时表空间从临时表空间组中移除就自动删除。

一个临时表空间组必须由至少一个临时表空间组成，并且无明确的最大数量限制.

A temporary tablespace group contains at least one tablespace. There is no limit for a group to have a maximum number of tablespaces

如果删除一个临时表空间组的所有成员，该组也自动被删除。

临时表空间的名字不能与临时表空间组的名字相同。

It shares the namespace of tablespaces, thus its name cannot be the same as that of any tablespace.

可以在创建临时表空间是指定表空间组，即隐式创建。

SQL>CREATE TEMPORARY TABLESPACE TEMP2
    TEMPFILE '/u01/app/oracle/oradata/GSP/temp2_1.dbf' SIZE 200M
    TABLESPACE GROUP GRP_TEMP;

查看临时表空间组：

SQL> SELECT * FROM DBA_TABLESPACE_GROUPS;
 
GROUP_NAME                            TABLESPACE_NAME
------------------------------ ------------------------------
GRP_TEMP                                  TEMP2

也可以指定已经创建好的临时表空间的临时表空间组。

SQL> ALTER TABLESPACE  TEMP TABLESPACE GROUP GRP_TEMP;
 
    Tablespace altered.
 
SQL> select * from dba_tablespace_groups;
 
GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
GRP_TEMP                       TEMP
GRP_TEMP                       TEMP2

从组中移除：

SQL> ALTER TABLESPACE TEMP TABLESPACE GROUP '';

当为数据库指定临时表空间或为用户指定临时表空间时，可以使用临时表空间组的名称

ALTER USER DM TEMPORARY TABLESPACE GRP_TEMP;

 

切换临时表空间

 

1：查看旧临时表空间信息

SELECT * FROM V$TEMPFILE

SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS

2：创建中转的临时表空间

3：添加相应的数据文件

4：切换临时表空间。

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TMP;

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

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

6：如果有必要，重新指定用户临时表空间为新建的临时表空间

ALTER USER ODS TEMPORARY TABLESPACE TMP;
 
ALTER USER EDS TEMPORARY TABLESPACE TMP;
 
ALTER USER ETL TEMPORARY TABLESPACE TMP;
 
ALTER USER DM TEMPORARY TABLESPACE TMP;
 

 

收缩临时表空间

排序等操作使用的临时段，使用完成后会被标记为空闲，表示可以重用，占用的空间不会立即释放，有时候临时表空间会变得非常大，此时可以通过收缩临时表空间来释放没有使用的空间。收缩临时表空间是ORACLE 11g新增的功能。

SQL> ALTER TABLESPACE TEMP SHRINK SPACE KEEP 8G;
 
SQL> ALTER TABLESPACE TEMP SHRINK TEMPFILE '/u01/app/oracle/oradata/GSP/temp02.dbf'
 

 

监控临时表空间

查看临时表空间使用情况：

SELECT TU.TABLESPACE_NAME                                    AS "TABLESPACE_NAME",
       TT.TOTAL - TU.USED                                    AS "FREE(G)",
       TT.TOTAL                                              AS "TOTAL(G)",
       ROUND(NVL(TU.USED, 0) / TT.TOTAL * 100, 3)            AS "USED(%)",
       ROUND(NVL(TT.TOTAL - TU.USED, 0) * 100 / TT.TOTAL, 3) AS "FREE(%)"
FROM (SELECT TABLESPACE_NAME,
              SUM(BYTES_USED) / 1024 / 1024 / 1024 USED
       FROM GV_$TEMP_SPACE_HEADER
       GROUP BY TABLESPACE_NAME) TU ,
     (SELECT TABLESPACE_NAME,
              SUM(BYTES) / 1024 / 1024 / 1024 AS TOTAL
       FROM DBA_TEMP_FILES
       GROUP BY TABLESPACE_NAME) TT
WHERE TU.TABLESPACE_NAME = TT.TABLESPACE_NAME;

 

COL TEMP_FILE FOR A60;
SELECT ROUND((F.BYTES_FREE  + F.BYTES_USED)/1024/1024/1024, 2)                         AS "TOTAL(GB)",
       ROUND(((F.BYTES_FREE  + F.BYTES_USED) - NVL(P.BYTES_USED, 0))/1024/1024/1024,2) AS "FREE(GB)",
       D.FILE_NAME                                                                     AS "TEMP_FILE",
       ROUND(NVL(P.BYTES_USED, 0)/1024/1024/1024, 2)                                   AS "USED(GB)" ,
       ROUND((F.BYTES_USED + F.BYTES_FREE)/1024/1024/1024, 2)                          AS "TOTAL(GB)",
       ROUND(((F.BYTES_USED + F.BYTES_FREE) - NVL(P.BYTES_USED, 0))/1024/1024/1024, 2) AS "FREE(GB)" ,
       ROUND(NVL(P.BYTES_USED, 0)/1024/1024/1024, 2)                                   AS "USED(GB)"
FROM SYS.V_$TEMP_SPACE_HEADER F ,DBA_TEMP_FILES D ,SYS.V_$TEMP_EXTENT_POOL P
WHERE F.TABLESPACE_NAME(+) = D.TABLESPACE_NAME
  AND F.FILE_ID(+) = D.FILE_ID
  AND P.FILE_ID(+) = D.FILE_ID;

查看临时表空间对应的临时文件的使用情况

SELECT TABLESPACE_NAME         AS TABLESPACE_NAME    ,
    BYTES_USED/1024/1024/1024    AS TABLESAPCE_USED  ,
    BYTES_FREE/1024/1024/1024  AS TABLESAPCE_FREE
FROM V$TEMP_SPACE_HEADER
ORDER BY 1 DESC;

查找消耗临时表空间资源比较多的SQL语句

SELECT   se.username,
         se.sid,
         su.extents,
         su.blocks * to_number(rtrim(p.value)) as Space,
         tablespace,
         segtype,
         sql_text
FROM v$sort_usage su, v$parameter p, v$<span style="color: #0000ff">session</span> se, v$sql s
   WHERE p.name = 'db_block_size'
     AND su.session_addr = se.saddr
     AND s.hash_value = su.sqlhash
     AND s.address = su.sqladdr
ORDER BY se.username, se.sid;
文章标签：
关系型数据库
Oracle
数据库
SQL
存储
关键词：
Oracle数据库一键上云表空间
Oracle数据库一键上云空间
Oracle数据库一键上云临时表
Oracle数据库一键上云临时表空间
Oracle数据库一键上云总结
优惠码优惠
目录
相关文章
_长银_
|
6月前
|
存储 Oracle NoSQL
Oracle 表空间、数据文件、schema的关系
Oracle 表空间、数据文件、schema的关系
_长银_
185 2
冷色系456
|
SQL 监控 Oracle
Oracle创建和管理表空间
Oracle创建和管理表空间
冷色系456
94 1
度假的小鱼
|
Oracle 关系型数据库 数据库
9-4 Oracle管理表空间和数据文件
9-4 Oracle管理表空间和数据文件
度假的小鱼
119 0
花酒锄作田
|
3月前
|
Oracle 关系型数据库 数据库
[oracle]拆分多用户的公共表空间
[oracle]拆分多用户的公共表空间
花酒锄作田
37 1
以山向海
|
4月前
|
存储 监控 Oracle
关系型数据库Oracle空间不足
【7月更文挑战第15天】
以山向海
56 6
以山向海
|
4月前
|
存储 Oracle 关系型数据库
关系型数据库Oracle 空间不足
【7月更文挑战第16天】
以山向海
51 2
宁波阿成.
|
6月前
|
存储 数据库
发现oracle10gSYSAUX空间没有了进行处理
发现oracle10gSYSAUX空间没有了进行处理
宁波阿成.
39 0
熊猫Jay字节之旅
|
6月前
|
SQL Oracle 关系型数据库
Oracle 数据泵导出导入(映射表空间、Schema)
Oracle 数据泵导出导入(映射表空间、Schema)
熊猫Jay字节之旅
100 0
李振良_阿良
|
6月前
|
Oracle 关系型数据库 数据库
Oracle 11gR2学习之三（创建用户及表空间、修改字符集和Oracle开机启动）
Oracle 11gR2学习之三（创建用户及表空间、修改字符集和Oracle开机启动）
李振良_阿良
218 0
1941623231718325
|
6月前
|
存储 Oracle 关系型数据库
Oracle表空间：数据王国的疆域规划
【4月更文挑战第19天】Oracle中的表空间是逻辑存储结构，用于存放数据库对象的物理数据，是数据库性能优化和备份恢复的基础。表空间类型多样，如永久和临时表空间，需根据业务需求进行规划和管理。通过监控使用情况、利用自动扩展功能，可有效管理表空间，提高数据访问速度和可靠性。深入理解表空间有助于优化数据库存储和管理。
1941623231718325
50 0