临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象,当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:
SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
ALTER TABLESPACE TEMP ADD TEMPFILE '/u04/gsp/oradata/temp02.dbf' SIZE 4G AUTOEXTEND ON NEXT 128M MAXSIZE 6G;
ALTER TABLESPACE TMP ADD TEMPFILE '/u03/eps/oradata/temp02.dbf' SIZE 64G AUTOEXTEND OFF;
3.删除数据文件
例如,我想删除临时表空间下的某个文件,那么我们有两种方式删除临时表空间的数据文件。
方法1:
ALTER TABLESPACE TEMP DROP TEMPFILE '/u01/app/oracle/oradata/GSP/temp02.dbf';
方法2:
ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/GSP/temp02.dbf' DROP INCLUDING DATAFILES;
注意:删除临时表空间的临时数据文件时,不需要指定INCLUDING DATAFILES 选项也会真正删除物理文件,否则需要手工删除物理文件。
4.调整文件大小
将临时数据文件从1G大小调整为2G
ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/GSP/temp02.dbf' RESIZE 2G;
5.文件脱机
ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/GSP/temp02.dbf' OFFLINE;
7. 移动重命名文件
例如,我需要将/u01/app/oracle/oradata/GSP/temp4.dbf这个文件重命名为/u01/app/oracle/oradata/GSP/temp04.dbf
1. 将临时表空间的临时文件脱机
ALTER DATABASE TEMPFILE '/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
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/GSP/temp4.dbf' TO '/u01/app/oracle/oradata/GSP/temp04.dbf';
4.将临时表空间的临时文件联机
ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/GSP/temp04.dbf' ONLINE;
8.删除临时表空间
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
注意: 不能删除当前用户的默认表空间,否则会报ORA-12906错。
如果需要删除某一个默认的临时表空间,则必须先创建一个临时表空间,然后指定新创建的表空间为默认表空间,然后删除原来的表空间。
临时表空间租
TEMPFILE '/u01/app/oracle/oradata/GSP/temp2_1.dbf' SIZE 200M
TABLESPACE GROUP GRP_TEMP;
查看临时表空间组:
临时表空间存储大规模排序操作(小规模排序操作会直接在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:
SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
SQL2:
SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS;
查看临时表空间信息
1. 查v$tempfile
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;
2. 查dba_temp_files
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;
3. 查temp表空间的使用情况
SELECT BYTES,BLOCKS, USER_BYTES, USER_BLOCKS, BLOCKS -USER_BLOCKS AS SYSTEM_USED FROM DBA_TEMP_FILES;
这四列中, BYTES , BLOCKS 显示的是临时文件有多少BYTE大小,包含多少个数据块。而USER_BYTES,USER_BLOCKS是可用的BYTE和数据块个数。因此,我们可以知道临时文件中有一部分是被系统占用的,大概可以理解成文件头信息,这一部分大小是128个block。
管理临时表空间
1.创建临时表空间
CREATE TEMPORARY TABLESPACE TMP
TEMPFILE '/u01/gsp/oradata/TMP01.dbf'
SIZE 8G
AUTOEXTEND OFF;
2.增加数据文件
当临时表空间太小时,就需要扩展临时表空间(添加数据文件、增大数据文件、设置文件自动扩展);有时候需要将临时数据文件分布到不同的磁盘分区中,提升IO性能,也需要通过删除、增加临时表空间数据文件。SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS;
1. 查v$tempfile
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;
2. 查dba_temp_files
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;
3. 查temp表空间的使用情况
SELECT BYTES,BLOCKS, USER_BYTES, USER_BLOCKS, BLOCKS -USER_BLOCKS AS SYSTEM_USED FROM DBA_TEMP_FILES;
这四列中, BYTES , BLOCKS 显示的是临时文件有多少BYTE大小,包含多少个数据块。而USER_BYTES,USER_BLOCKS是可用的BYTE和数据块个数。因此,我们可以知道临时文件中有一部分是被系统占用的,大概可以理解成文件头信息,这一部分大小是128个block。
管理临时表空间
1.创建临时表空间
CREATE TEMPORARY TABLESPACE TMP
TEMPFILE '/u01/gsp/oradata/TMP01.dbf'
SIZE 8G
AUTOEXTEND OFF;
2.增加数据文件
ALTER TABLESPACE TEMP ADD TEMPFILE '/u04/gsp/oradata/temp02.dbf' SIZE 4G AUTOEXTEND ON NEXT 128M MAXSIZE 6G;
ALTER TABLESPACE TMP ADD TEMPFILE '/u03/eps/oradata/temp02.dbf' SIZE 64G AUTOEXTEND OFF;
3.删除数据文件
例如,我想删除临时表空间下的某个文件,那么我们有两种方式删除临时表空间的数据文件。
方法1:
ALTER TABLESPACE TEMP DROP TEMPFILE '/u01/app/oracle/oradata/GSP/temp02.dbf';
方法2:
ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/GSP/temp02.dbf' DROP INCLUDING DATAFILES;
注意:删除临时表空间的临时数据文件时,不需要指定INCLUDING DATAFILES 选项也会真正删除物理文件,否则需要手工删除物理文件。
4.调整文件大小
将临时数据文件从1G大小调整为2G
ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/GSP/temp02.dbf' RESIZE 2G;
5.文件脱机
ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/GSP/temp02.dbf' OFFLINE;
ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/GSP/temp02.dbf' ONLINE;
默认临时表空间不能脱机,否则会报错。
6.设置文件自动扩展
ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/GSP/temp03.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
默认临时表空间不能脱机,否则会报错。
6.设置文件自动扩展
ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/GSP/temp03.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
7. 移动重命名文件
例如,我需要将/u01/app/oracle/oradata/GSP/temp4.dbf这个文件重命名为/u01/app/oracle/oradata/GSP/temp04.dbf
1. 将临时表空间的临时文件脱机
ALTER DATABASE TEMPFILE '/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
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/GSP/temp4.dbf' TO '/u01/app/oracle/oradata/GSP/temp04.dbf';
4.将临时表空间的临时文件联机
ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/GSP/temp04.dbf' ONLINE;
8.删除临时表空间
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
注意: 不能删除当前用户的默认表空间,否则会报ORA-12906错。
如果需要删除某一个默认的临时表空间,则必须先创建一个临时表空间,然后指定新创建的表空间为默认表空间,然后删除原来的表空间。
临时表空间租
临进表空间组:
临进表空间组是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.
可以在创建临时表空间是指定表空间组,即隐式创建。
CREATE TEMPORARY TABLESPACE TEMP2TEMPFILE '/u01/app/oracle/oradata/GSP/temp2_1.dbf' SIZE 200M
TABLESPACE GROUP GRP_TEMP;
查看临时表空间组:
SELECT * FROM DBA_TABLESPACE_GROUPS;
也可以指定已经创建好的临时表空间的临时表空间组。
ALTER TABLESPACE TEMP TABLESPACE GROUP GRP_TEMP;
select * from dba_tablespace_groups;从组中移除:
ALTER TABLESPACE TEMP TABLESPACE GROUP '';当为数据库指定临时表空间或为用户指定临时表空间时,可以使用临时表空间组的名称
ALTER USER DM TEMPORARY TABLESPACE GRP_TEMP;
监控临时表空间1.查看临时表空间使用情况:
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 V$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 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;
2. 查看临时表空间对应的文件的使用情况
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;
3. 查找消耗临时表空间资源比较多的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$session 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;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21374452/viewspace-2137181/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/21374452/viewspace-2137181/