oracle user表空间使用率,oracle11g 查询临时表空间的使用率和正在使用临时表空间的用户...

今天开发反映说临时表空间不够要求添加临时表空间，添加完成(添加了30G的临时表空间)又报临时表空间的不足，开发又要求是添加，有添加了10G，监控临时表空间的使用情况，又撑满了，又添加了30G,最后临时表空间又撑满了，最后批处理还是没有执行，SQL语句的执行是一次性全部执行完成，最后的解决办法是开发使用游标，一万条数据一提交或者定量提交，下午监控临时表空间的使用情况发现使用率 74.91%。不禁有些疑问到底是那些用户在使用临时表空间，使用临时表空间的大小有多大，

在做一些什么操作，SQL语句是什么。

首先要说明一点的是表空间的使用惰性，如果你一下使用几十个G的临时表空间，如果SQL语句执行失败，临时表空间的释放需要时间，不会一下全部释放，这个时候我们检查临时表空间的使用率发现使用率已经是99%，如果你扩容临时表空间，扩容30G，开发又开始执行sql，你会发现临时表空间使用率蹭蹭的网上增最后又达到了99%，开发的sql又没有执行过去，还是报临时表空间不足，而临时表空间释放有需要时间，如果开发的非常着急需要执行sql。所以建议dba一次扩容临时表，扩容大点，

说一下今天处理问题的过程，

1.查询临时表空间的使用率：

select c.tablespace_name,

to_char(c.bytes/1024/1024/1024,'99,999.999') total_gb,

to_char( (c.bytes-d.bytes_used)/1024/1024/1024,'99,999.999') free_gb,

to_char(d.bytes_used/1024/1024/1024,'99,999.999') use_gb,

to_char(d.bytes_used*100/c.bytes,'99.99') || '%'use

from  (select tablespace_name,sum(bytes) bytes

from dba_temp_files GROUP by tablespace_name) c,

(select tablespace_name,sum(bytes_cached) bytes_used

from v$temp_extent_pool GROUP by tablespace_name) d

where c.tablespace_name = d.tablespace_name;

2.查询那些用户在使用

select a.username,

a.sql_id,

a.SEGTYPE,

b.BYTES_USED/1024/1024/1024||‘G’,

b.BYTES_FREE/1024/1024/1024  from   V$TEMPSEG_USAGE  a  join  V$TEMP_SPACE_HEADER b on   a.TABLESPACE=b.tablespace_name;

解释username  正在执行sql的用户名

sql_id    正在执行的sql的的sql_id

segtype   正在执行的SQL语句做的是什么操作

BYTES_USED 正在执行sql语句使用的临时表空间的大小

BYTES_FREE  剩余多少临时表空间

大家可以看到这个临时表空间的有6个数据文件，查询的结果显示按照每个临时的数据文件使用了多少，还剩余多少，又又有一个问题是第一用户DBSNMP用户已经把临时表空间占满了那么第二个用户还能使用这个六个数据文件的临时表空间么，个人理解是六个数据文件中有第一个用户在使用，也有第二个用户在使用。

查询实例中时候是否有大字段在使用临时表空间：

select *  from V$TEMPORARY_LOBS;

关于数据字典V$TEMP_SPACE_HEADER官方文档的解释：

V$TEMP_SPACE_HEADER 显示每个LOCALLY MANAGED临时表空间的每个文件的聚合信息，包括当前正在使用的空间量以及空间头中标识的空闲量。

Column

Datatype

Description

TABLESPACE_NAME

VARCHAR2(30)

Name of the temporary tablespace

FILE_ID

NUMBER

Absolute file number

BYTES_USED

NUMBER

How many bytes are in use

BLOCKS_USED

NUMBER

How many blocks are in use

BYTES_FREE

NUMBER

How many bytes are free

BLOCKS_FREE

NUMBER

How many blocks are free

RELATIVE_FNO

NUMBER

The relative file number for the file

关于V$TEMPSEG_USAGE的官方文档的解释：

V$TEMPSEG_USAGE 描述临时段使用情况。

柱

数据类型

描述

USERNAME

VARCHAR2(30)

请求临时空间的用户

USER

VARCHAR2(30)

此列已过时并维护以便向后兼容。

此列的值始终等于中的值

USERNAME

。

SESSION_ADDR

RAW(4 | 8)

会话地址

SESSION_NUM

NUMBER

会话序列号

SQLADDR

RAW(4 | 8)

SQL语句的地址

SQLHASH

NUMBER

SQL语句的哈希值

SQL_ID

VARCHAR2(13)

SQL语句的SQL标识符

TABLESPACE

VARCHAR2(31)

分配空间的表空间

CONTENTS

VARCHAR2(9)

指示表是否

TEMPORARY

或

PERMANENT

SEGTYPE

VARCHAR2(9)

排序类型的类型：

SORT

HASH

DATA

INDEX

LOB_DATA

LOB_INDEX

SEGFILE#

NUMBER

初始范围的文件号

SEGBLK#

NUMBER

初始范围的块号

EXTENTS

NUMBER

分配给排序的范围

BLOCKS

NUMBER

分配给排序的块中的范围

SEGRFNO#

NUMBER

初始范围的相对文件号

关于V$TEMP_EXTENT_POOL的官方文档中的解释

V$TEMP_EXTENT_POOL显示缓存并用于实例的临时空间的状态。请注意，临时空间缓存的加载是惰性的，并且实例可以处于休眠状态。

柱

数据类型

描述

TABLESPACE_NAME

VARCHAR2(30)

表空间的名称

FILE_ID

NUMBER

绝对文件号

EXTENTS_CACHED

NUMBER

已缓存的范围数

EXTENTS_USED

NUMBER

实际使用的范围数

BLOCKS_CACHED

NUMBER

缓存的块数

BLOCKS_USED

NUMBER

使用的块数

BYTES_CACHED

NUMBER

缓存的字节数

BYTES_USED

NUMBER

使用的字节数

RELATIVE_FNO

NUMBER

相对文件号

关于V$TEMPORARY_LOBS官方文档解释

V$TEMPORARY_LOBS 显示临时LOB。

柱

数据类型

描述

SID

NUMBER

会话ID

CACHE_LOBS

NUMBER

缓存临时LOB的数量

NOCACHE_LOBS

NUMBER

nocache临时LOB的数量

ABSTRACT_LOBS

NUMBER

抽象LOB的数量

如果书写过程或者对官方文档理解有什么错误欢迎大家留言。