ORACLE临时表空间的总结分析
ORACLE临时表空间的总结分析，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。
临时表空间概念
临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象,当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>SELECTPROPERTY_NAME,PROPERTY_VALUE
2FROMDATABASE_PROPERTIES
3WHEREPROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAMEPROPERTY_VALUE
----------------------------------------------------------
DEFAULT_TEMP_TABLESPACETEMP
SQL2：
SELECTUSERNAME,TEMPORARY_TABLESPACEFROMDBA_USERS;
查看临时表空间信息：
SETLINESIZE1200
COLNAMEFORA60
SELECTFILE#ASFILE_NUMBER
,NAMEASNAME
,CREATION_TIMEASCREATION_TIME
,BLOCK_SIZEASBLOCK_SIZE
,BYTES/1024/1024/1024AS"FILE_SIZE(G)"
,CREATE_BYTES/1024/1024/1024AS"INIT_SIZE(G)"
,STATUSASSTATUS
,ENABLEDASENABLED
FROMV$TEMPFILE;
官方文档关于V$TEMPFILE的介绍如下
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
SETLINESIZE1200
COLTABLESPACE_NAMEFORA30
COLFILE_NAMEFORA60
SELECTTABLESPACE_NAMEASTABLESPACE_NAME
,FILE_NAMEASFILE_NAME
,BLOCKSASBLOCKS
,STATUSASSTATUS
,AUTOEXTENSIBLEASAUTOEXTENSIBLE
,BYTES/1024/1024/1024AS"FILE_SIZE(G)"
,DECODE(MAXBYTES,0,BYTES/1024/1024/1024,
MAXBYTES/1024/1024/1024)
AS"MAX_SIZE(G)"
,INCREMENT_BYAS"INCREMENT_BY"
,USER_BYTES/1024/1024/1024AS"USEFUL_SIZE"
FROMDBA_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>SELECTBYTES,BLOCKS,USER_BYTES,USER_BLOCKS,
BLOCKS-USER_BLOCKSASSYSTEM_USED
FROMDBA_TEMP_FILES;
BYTESBLOCKSUSER_BYTESUSER_BLOCKSSYSTEM_USED
----------------------------------------------------
21474836482621442146435072262016128
10737418241310721072693248130944128
2097152002560020866662425472128
这四列中， BYTES , BLOCKS 显示的是临时文件有多少BYTE大小，包含多少个数据块。而USER_BYTES,USER_BLOCKS是可用的BYTE和数据块个数。因此，我们可以知道临时文件中有一部分是被系统占用的，大概可以理解成文件头信息，这一部分大小是128个block，如下图所示:
管理临时表空间
创建临时表空间
下面是一个简单的创建临时表空间的例子，具体很多细节可以参考官方文档，这里省略,不做过多介绍。
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
CREATETEMPORARYTABLESPACETMP
TEMPFILE'/u01/gsp/oradata/TMP01.dbf'
SIZE8G
AUTOEXTENDOFF;
增加数据文件
当临时表空间太小时，就需要扩展临时表空间（添加数据文件、增大数据文件、设置文件自动扩展）；有时候需要将临时数据文件分布到不同的磁盘分区中，提升IO性能，也需要通过删除、增加临时表空间数据文件。
SQL>ALTERTABLESPACETEMP
2ADDTEMPFILE'/u04/gsp/oradata/temp02.dbf'
3SIZE4G
4AUTOEXTENDON
5NEXT128M
6MAXSIZE6G;
Tablespacealtered.
SQL>ALTERTABLESPACETMP
ADDTEMPFILE'/u03/eps/oradata/temp02.dbf'
SIZE64G
AUTOEXTENDOFF;
Tablespacealtered.
删除数据文件
例如，我想删除临时表空间下的某个文件，那么我们有两种方式删除临时表空间的数据文件。
方法1：
SQL>ALTERTABLESPACETEMP
DROPTEMPFILE'/u01/app/oracle/oradata/GSP/temp02.dbf';
Tablespacealtered.
注意：这种删除临时表空间的写法会将对应的物理文件删除。
方法2：
SQL>ALTERDATABASETEMPFILE'/u01/app/oracle/oradata/GSP/temp02.dbf'
DROPINCLUDINGDATAFILES;
Databasealtered.
注意：删除临时表空间的临时数据文件时，不需要指定INCLUDING DATAFILES 选项也会真正删除物理文件，否则需要手工删除物理文件。
调整文件大小
如下例子，需要将临时数据文件从1G大小调整为2G
SQL>ALTERDATABASETEMPFILE
'/u01/app/oracle/oradata/GSP/temp02.dbf'RESIZE2G;
文件脱机联机
SQL>ALTERDATABASETEMPFILE
2'/u01/app/oracle/oradata/GSP/temp02.dbf'OFFLINE;
Databasealtered.
SQL>ALTERDATABASETEMPFILE
2'/u01/app/oracle/oradata/GSP/temp02.dbf'ONLINE;
Databasealtered.
默认临时表空间并不能脱机,否则会报错，如下所示
SQL> ALTER TABLESPACE TEMP OFFLINE;
ALTER TABLESPACE TEMP OFFLINE
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
设置文件自动扩展
SQL>ALTERDATABASETEMPFILE'/u01/app/oracle/oradata/GSP/temp03.dbf'
2AUTOEXTENDON
3NEXT100M
4MAXSIZEUNLIMITED;
移动重命名文件
例如，我需要将/u01/app/oracle/oradata/GSP/temp4.dbf这个文件重命名为/u01/app/oracle/oradata/GSP/temp04.dbf
1: 将临时表空间的临时文件脱机
SQL>ALTERDATABASETEMPFILE
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>ALTERDATABASERENAMEFILE
2'/u01/app/oracle/oradata/GSP/temp4.dbf'TO
3'/u01/app/oracle/oradata/GSP/temp04.dbf';
4: 将临时表空间的临时文件联机
SQL>ALTERDATABASETEMPFILE'/u01/app/oracle/oradata/GSP/temp04.dbf'ONLINE;
Databasealtered.
删除临时表空间
SQL>DROPTABLESPACETEMPINCLUDINGCONTENTSANDDATAFILESCASCADECONSTRAINTS;
注意：不能删除当前用户的默认表空间，否则会报ORA-12906错误
SQL>DROPTABLESPACETMPINCLUDINGCONTENTSANDDATAFILESCASCADECONSTRAINTS;
DROPTABLESPACETMPINCLUDINGCONTENTSANDDATAFILESCASCADECONSTRAINTS
*
ERRORatline1:
ORA-12906:cannotdropdefaulttemporarytablespace
如果需要删除某一个默认的临时表空间，则必须先创建一个临时表空间，然后指定新创建的表空间为默认表空间，然后删除原来的临时表空间
临时表空间组
临进表空间组:
临进表空间组是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>CREATETEMPORARYTABLESPACETEMP2
TEMPFILE'/u01/app/oracle/oradata/GSP/temp2_1.dbf'SIZE200M
TABLESPACEGROUPGRP_TEMP;
查看临时表空间组：
SQL>SELECT*FROMDBA_TABLESPACE_GROUPS;
GROUP_NAMETABLESPACE_NAME
------------------------------------------------------------
GRP_TEMPTEMP2
也可以指定已经创建好的临时表空间的临时表空间组。
SQL>ALTERTABLESPACETEMPTABLESPACEGROUPGRP_TEMP;
Tablespacealtered.
SQL>select*fromdba_tablespace_groups;
GROUP_NAMETABLESPACE_NAME
------------------------------------------------------------
GRP_TEMPTEMP
GRP_TEMPTEMP2
从组中移除：
SQL>ALTERTABLESPACETEMPTABLESPACEGROUP'';
当为数据库指定临时表空间或为用户指定临时表空间时，可以使用临时表空间组的名称
ALTERUSERDMTEMPORARYTABLESPACEGRP_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：如果有必要，重新指定用户临时表空间为新建的临时表空间
ALTERUSERODSTEMPORARYTABLESPACETMP;
ALTERUSEREDSTEMPORARYTABLESPACETMP;
ALTERUSERETLTEMPORARYTABLESPACETMP;
ALTERUSERDMTEMPORARYTABLESPACETMP;
收缩临时表空间
排序等操作使用的临时段，使用完成后会被标记为空闲，表示可以重用，占用的空间不会立即释放，有时候临时表空间会变得非常大，此时可以通过收缩临时表空间来释放没有使用的空间。收缩临时表空间是ORACLE 11g新增的功能。
SQL>ALTERTABLESPACETEMPSHRINKSPACEKEEP8G;
SQL>ALTERTABLESPACETEMPSHRINKTEMPFILE'/u01/app/oracle/oradata/GSP/temp02.dbf'
监控临时表空间
查看临时表空间使用情况：
SELECTTU.TABLESPACE_NAMEAS"TABLESPACE_NAME",
TT.TOTAL-TU.USEDAS"FREE(G)",
TT.TOTALAS"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(SELECTTABLESPACE_NAME,
SUM(BYTES_USED)/1024/1024/1024USED
FROMGV_$TEMP_SPACE_HEADER
GROUPBYTABLESPACE_NAME)TU,
(SELECTTABLESPACE_NAME,
SUM(BYTES)/1024/1024/1024ASTOTAL
FROMDBA_TEMP_FILES
GROUPBYTABLESPACE_NAME)TT
WHERETU.TABLESPACE_NAME=TT.TABLESPACE_NAME;
COLTEMP_FILEFORA60;
SELECTROUND((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_NAMEAS"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)"
FROMSYS.V_$TEMP_SPACE_HEADERF,DBA_TEMP_FILESD,SYS.V_$TEMP_EXTENT_POOLP
WHEREF.TABLESPACE_NAME(+)=D.TABLESPACE_NAME
ANDF.FILE_ID(+)=D.FILE_ID
ANDP.FILE_ID(+)=D.FILE_ID;
查看临时表空间对应的临时文件的使用情况
SELECTTABLESPACE_NAMEASTABLESPACE_NAME,
BYTES_USED/1024/1024/1024ASTABLESAPCE_USED,
BYTES_FREE/1024/1024/1024ASTABLESAPCE_FREE
FROMV$TEMP_SPACE_HEADER
ORDERBY1DESC;
查找消耗临时表空间资源比较多的SQL语句
SELECTse.username,
se.sid,
su.extents,
su.blocks*to_number(rtrim(p.value))asSpace,
tablespace,
segtype,
sql_text
FROMv$sort_usagesu,v$parameterp,v$sessionse,v$sqls
WHEREp.name='db_block_size'
ANDsu.session_addr=se.saddr
ANDs.hash_value=su.sqlhash
ANDs.address=su.sqladdr
ORDERBYse.username,se.sid;
看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注亿速云行业资讯频道，感谢您对亿速云的支持。
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
