v$dba_temp_files
v$sort_segment
v$tempseg_usage=v$sort_usage
V$TEMP_SPACE_HEADER
上面这几个视图是我总结的在遇到Oracle临时空间问题时能用到的数据字典信息。
1,先看v$dba_temp_files
这个视图显示了数据库中临时文件的具体信息。要注意的有4列
SQL> desc dba_temp_files;
Name Type Nullable Default Comments
--------------- ------------- -------- ------- ---------------------------------------------------
FILE_NAME VARCHAR2(513) Y Name of the database temp file
FILE_ID NUMBER Y ID of the database temp file
TABLESPACE_NAME VARCHAR2(30) Name of the tablespace to which the file belongs
BYTES NUMBER Y Size of the file in bytes
BLOCKS NUMBER Y Size of the file in ORACLE blocks
STATUS VARCHAR2(7) Y File status: "AVAILABLE"
RELATIVE_FNO NUMBER Y Tablespace-relative file number
AUTOEXTENSIBLE VARCHAR2(3) Y Autoextensible indicator: "YES" or "NO"
MAXBYTES NUMBER Y Maximum size of the file in bytes
MAXBLOCKS NUMBER Y Maximum size of the file in ORACLE blocks
INCREMENT_BY NUMBER Y Default increment for autoextension
USER_BYTES NUMBER Y Size of the useful portion of file in bytes
USER_BLOCKS NUMBER Y Size of the useful portion of file in ORACLE blocks
这四列中， BYTES , BLOCKS 显示的是临时文件有多少BYTE大小，包含多少个数据块。而USER_BYTES,USER_BLOCKS是可用的BYTE和数据块个数。因此，我们可以知道临时文件中有一部分是被系统占用的，大概可以理解成文件头信息，在我的db中，这一部分大小是128个block，如下图所示，3243776-3243648=128. 4194302-4194176=126
SQL> select file_name, blocks , user_blocks from dba_temp_files;
FILE_NAME BLOCKS USER_BLOCKS
------------------------------------------------------------------ --------- -----------------
+TCSF_DG/tcsf2/tempfile/temp.293.720318237 3243776 3243648
+TCSF_DG/tcsf2/tempfile/tcsf_temp.305.720321581 4194302 4194176
2,再看v$sort_segment视图
这个视图从字面翻译虽然叫做排序段，但实际上不仅仅是排序，只要是消耗了临时表空间的操作，比如创建临时表等，都会在这个视图中显示。但如果一个操作没有占用临时表空间（比如开销很小的排序，在内存中完成，或者一个临时表在创建时通过tablespace子句指定到了一个永久表空间中),那么这个视图就不会被更新。
但有一个问题是，我们指定正常的segment是一个segment用来承载一个对象如表或者index，再或者一个对象的分区，如分区表的分区，但我们的sort_segment似乎不是这样的，一个sort_segment可以供多个对象或者说操作使用。
如图，一个sort_segment, 有两个extent被占用，同时有两个user在使用。因为这个测试是我自己进行的，所以我清楚的知道，两个user 一个在创建临时表，另一个在创建一个大表的索引。这根本就是两件不相干的事，但用的是同一个sort_segment.
不过这个问题不重要，因为我们通过这个视图主要知道多少个用户在使用临时表空间，已经使用了多少这就够了。
SQL> select tablespace_name ,TOTAL_BLOCKS,USED_EXTENTS,USED_BLOCKS ,CURRENT_USERS from v$sort_segment;
TABLESPACE_NAME TOTAL_BLOCKS USED_EXTENTS USED_BLOCKS
------------------------------- ------------ ------------ -----------
CURRENT_USERS
-------------
TEMP 3240832 0 0
0
TCSF_TEMP 2217216 0 0
0
3,再看v$tempseg_usage
这个视图可以详细的显示那些用户在使用临时空间，使用了多少，其session的id，SQL的ID等。但有一个很困惑的问题是其中的SQL_ID列显示的信息不准确。根据我的试验，它显示的应该是目标会话的当前SQL的SQL_ID，而非消耗临时表空间的SQL_ID。比如说你在一个会话中发出了一条SQL消耗了很大的临时空间，然后你又发出了一条不相干的SQL，这时如果我select * from v$tempseg_usage，查到的SQL_ID对应的SQL是第二条，也就是最新的那条。
另外一条要注意的是，这个视图与 v$sort_usage是一样的，只不过后者改了个名字而已。大概oracle是这么考虑的，如果叫sort_usage会引起歧义，会让人以为这个视图针对的是排序的开销，但实际上不仅仅是排序，只要是对临时表空间的消耗都会体现在这个视图中。
4,V$TEMP_SPACE_HEADER（Oracle10g）
SQL> desc V$TEMP_SPACE_HEADER
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME NOT NULL VARCHAR2(30)
FILE_ID NUMBER
BYTES_USED NUMBER
BLOCKS_USED NUMBER
BYTES_FREE NUMBER
BLOCKS_FREE NUMBER
RELATIVE_FNO NUMBER
该视图要注意的是BLOCKS_USED 和 BLOCKS_FREE。前面两个比较简单，tablespace_name不用说了，而file_id 体现的是文件编号。如果把前面dba_temp_files 中blocks的值与BLOCKS_USED+ BLOCKS_FREE比较，刚好相等，这就是这个临时文件总块数。
监控临时表空间
一,查看临时表空间的使用情况(会重用,只有不够时才又重新请求分配)V$TEMP_SPACE_HEADER
SQL> select * from V$TEMP_SPACE_HEADER;
TABLESPACE_NAME FILE_ID BYTES_USED BLOCKS_USED BYTES_FREE BLOCKS_FREE RELATIVE_FNO
---------------------- ---------- --------------- ---------------- ------------- ---------------- ----------------
TEMP 1 2.6573E+10 3243776 0 0 1
TCSF_TEMP 2 3.4287E+10 4185470 72351744 8832 1
SQL> select
TABLESPACE_NAME,file_id,(bytes_used+bytes_free)/1024/1024 "size_total(mb)",
bytes_used/1024/1024 "size_allocated(mb)",bytes_free/1024/1024 "size_unallocated(mb)",blocks_used,blocks_free,relative_fno
from V$TEMP_SPACE_HEADER;
TABLESPACE_NAME FILE_ID size_total(mb) size_allocated(mb) size_unallocated(mb) BLOCKS_USED BLOCKS_FREE RELATIVE_FNO
-------------------- ---------- -------------- ------------------ -------------------- ----------- ----------- ------------
TEMP 1 25342 25342 0 3243776 0 1
TCSF_TEMP 2 32767.9844 32698.9844 69 4185470 8832 1
SQL> select file_name, blocks , user_blocks from dba_temp_files;
FILE_NAME BLOCKS USER_BLOCKS
------------------------------------------------------------------ --------- -----------------
+TCSF_DG/tcsf2/tempfile/temp.293.720318237 3243776 3243648
+TCSF_DG/tcsf2/tempfile/tcsf_temp.305.720321581 4194302 4194176
二,查看正在使用的sort_segment,V$SORT_SEGMENT
SQL> select tablespace_name,segment_block,used_blocks,free_blocks,current_users,total_blokcs from v$sort_segment;
TABLESPACE_NAME SEGMENT_BLOCK USED_BLOCKS FREE_BLOCKS CURRENT_USERS TOTAL_BLOCKS
--------------------------- ------------------- ----------------- ---------------- ------------------- -----------------
TEMP 0 0 3240832 0 3240832
TCSF_TEMP 0 0 2217216 0 2217216
对于tablespace_size，我们理解为是临时表空间的总大小。验证一下。
通过dba_temp_files视图，我们知道了temp表空间temp文件的数据块个数：3243776，tcsf_temp表空间temp文件的数据块个数：4194302. 那么总的大小是数据块个数之和乘以数据块的大小（本系统中是8192）。 3243776 * 8192 = 26573012992。 验证成功。
对于ALLOCATED_SPACE我们理解为分配出去的空间。
验证一下。
v$sort_segment中查到total_block 为3240832. 而我们知道temp文件每个文件都被系统占用了128个block（从dba_temp_file 视图的 blocks ， user_blocks对比可以看出）。那么被占用的总的数据块个数应该是128 加上sort_segment中的3240832. 128 + 3240832=3240960。 这是数据块的个数，如果要得到BYTE为单位的大小还要乘以8192（数据块尺寸）。不过要注意的很重要的一点是，这里虽然是allocate的空间但并非是不可用的。其中包含了一部分仍然可用的。比如sort_segment这个视图中没分配的extent。
三,查看哪个用户的哪个语句在使用sort_segment,V$SORT_USAGE=v$tempseg_usage
SQL> create global temporary table test(id number,name char(2)) on commit delete rows;
Table created.
SQL> insert into test values(1,'2');
1 row created.
SQL> select * from v$tempseg_usage;
USERNAME USER SESSION_ADDR
------------------------------ ------------------------------ ----------------
SESSION_NUM SQLADDR SQLHASH SQL_ID
----------- ---------------- ---------- -------------
TABLESPACE CONTENTS SEGTYPE SEGFILE# SEGBLK#
------------------------------- --------- --------- ---------- ----------
EXTENTS BLOCKS SEGRFNO#
---------- ---------- ----------
SYS SYS 070000010F9F0520
39564 070000010D68BE30 2802432354 0f8vysqmhmfb2
TEMP TEMPORARY DATA 201 3234313
1 128 1
SQL> select a.username, a.SESSION_NUM,b.sql_text, a.TABLESPACE
from v$tempseg_usage a, v$sqlarea b
where a.SQLHASH = b.hash_value;
USERNAME SESSION_NUM
------------------------------ -----------
SQL_TEXT
--------------------------------------------------------------------------------
TABLESPACE
-------------------------------
SYS 39564
select * from v$tempseg_usage
TEMP
可以看出找出的sql_text并不是确切使用temprary tablespace的sql语句
方法一、【实验】RESIZE方法解决临时表空间过大问题
1.数据库版本信息
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production
2.查看临时表空间信息
SQL> col file_name for a40
SQL> col tablespace_name for a10
SQL> select tablespace_name,file_name,bytes/1024/1024 M from dba_temp_files;
TABLESPACE FILE_NAME M
-------------------------------------------------- -------
TEMP +TCSF_DG/tcsf2/tempfile/temp.293.720318237 25342
TCSF_TEMP +TCSF_DG/tcsf2/tempfile/tcsf_temp.305.720321581 32767.9844
3.查看临时表空间数据文件占用操作系统的空间
p550a:/home/oracle$export ORACLE_SID=+ASM1
p550a:/home/oracle$asmcmd
ASMCMD> ls -l +TCSF_DG/tcsf2/tempfile/temp.293.720318237
Type Redund Striped Time Sys Name
TEMPFILE UNPROT COARSE APR 12 03:00:00 Y temp.293.720318237
ASMCMD> ls -l +TCSF_DG/tcsf2/tempfile/tcsf_temp.305.720321581
Type Redund Striped Time Sys Name
TEMPFILE UNPROT COARSE APR 12 03:00:00 Y tcsf_temp.305.720321581
4.关键的一部就在这里！RESIZE it！这一步未必修改成功
SQL> alter database tempfile '+TCSF_DG/tcsf2/tempfile/temp.293.720318237' resize 1024m;
Database altered.
5.确认已经修改成功
SQL> select tablespace_name,file_name,bytes/1024/1024 M from dba_temp_files;
TABLESPACE FILE_NAME M
-------------------------------------------------- -------
TEMP +TCSF_DG/tcsf2/tempfile/temp.293.720318237 1024
TCSF_TEMP +TCSF_DG/tcsf2/tempfile/tcsf_temp.305.720321581 32767.9844
p550a:/home/oracle$export ORACLE_SID=+ASM1
p550a:/home/oracle$asmcmd
ASMCMD> ls -l +TCSF_DG/tcsf2/tempfile/temp.293.720318237
Type Redund Striped Time Sys Name
TEMPFILE UNPROT COARSE APR 12 03:00:00 Y temp.293.720318237
ASMCMD> ls -l +TCSF_DG/tcsf2/tempfile/tcsf_temp.305.720321581
Type Redund Striped Time Sys Name
TEMPFILE UNPROT COARSE APR 12 03:00:00 Y tcsf_temp.305.720321581
6.OK，修改成功。
另外还有一种修改临时表空间大小的方法，详见《【实验】重建临时表空间解决临时表空间过大问题》
方法二、【实验】重建临时表空间解决临时表空间过大问题
今天在测试库中创建大表索引后临时表空间自动扩展到了10G，我采用了重建临时表空间的方式处理了一下，记录如下：
1.创建中转临时表空间
create temporary tablespace temp1 tempfile '/oracle/oradata/secooler/temp02.dbf' size 512m reuse autoextend on next 1m maxsize unlimited;
2.改变缺省临时表空间为刚刚创建的新临时表空间temp1
alter database default temporary tablespace temp1;
验证用户的临时表空间为temp1
Select username,temporary_tablespace from dba_users;
3.删除原临时表空间
drop tablespace temp including contents and datafiles;
4.重建临时表空间
create temporary tablespace temp tempfile '/oracle/oradata/secooler/temp01.dbf' size 512m reuse autoextend on next 1m maxsize unlimited;
5.重置缺省临时表空间为新建的temp表空间
alter database default temporary tablespace temp;
验证用户的临时表空间为temp
Select username,temporary_tablespace from dba_users;
6.删除中转用临时表空间
drop tablespace temp1 including contents and datafiles;
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14359/viewspace-731942/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/14359/viewspace-731942/
1220
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
