这两天把临时表空间的知识点总结了一下。记下来分享一下。 首先从数据字典视图说起 v$dba_temp_files v$sort_segment v$tempseg_usage dba_temp_free_space 上面这几个视图是我总结的在遇到Oracle临时空间问题时能用到的数据字典信息。 先看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,如下图所示,22784-22656=128. SQL> select file_name, blocks , user_blocks from dba_temp_files; FILE_NAME BLOCKS USER_BLOCKS -------------------------------------------------------------------------------- -------------------- ----------- /u01/app/oracle/oradata/sales/temp01.dbf 22784 22656 /u01/app/oracle/oradata/sales/temp02.dbf 6400 6272 再看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 22656 2 256 2 再看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会引起歧义,会让人以为这个视图针对的是排序的开销,但实际上不仅仅是排序,只要是对临时表空间的消耗都会体现在这个视图中。 dba_temp_free_space SQL> desc dba_temp_free_space Name Null? Type ----------------------------------------- -------- ---------------------------- TABLESPACE_NAME NOT NULL VARCHAR2(30) TABLESPACE_SIZE NUMBER ALLOCATED_SPACE NUMBER FREE_SPACE NUMBER 该视图要注意的是ALLOCATED_SPACE 和 FREE_SPACE。前面两个比较简单,tablespace_name不用说了,而 tablespace_size 体现的是整个表空间的大小。如果把前面dba_temp_files 中BYTES列的值加起来,就是这个tablespace_size的值。 至于ALLOCATED_SPACE则要注意了,他体现的是分配出去的临时空间,包括分配出去但仍然可用的以及分配出去但不可用的。我们通过下面四个语句来理解这个视图。 SQL> select * from dba_temp_free_space; TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE ------------------------------ -------------------------------------- ---------------------- ---------- TEMP 239075328 187695104 234881024 SQL> select file_name, blocks , user_blocks from dba_temp_files; FILE_NAME BLOCKS USER_BLOCKS -------------------------------------------------------------------------------- -------------------- ----------- /u01/app/oracle/oradata/sales/temp01.dbf 22784 22656 /u01/app/oracle/oradata/sales/temp02.dbf 6400 6272 SQL> select TOTAL_BLOCKS,USED_EXTENTS,USED_BLOCKS ,CURRENT_USERS from v$sort_segment; TOTAL_BLOCKS USED_EXTENTS USED_BLOCKS CURRENT_USERS ----------------------- ---------------------- ----------- --------------------- 22656 2 256 2 对于tablespace_name没什么好说的。 对于tablespace_size,我们理解为是临时表空间的总大小。验证一下。 通过dba_temp_files视图,我们知道了每个temp文件的数据块个数,22784和6400. 那么总的大小是数据块个数之和乘以数据块的大小(本系统中是8192)。 (22784 + 6400) * 8192 = 239075328。 验证成功。 对于ALLOCATED_SPACE我们理解为分配出去的空间。 验证一下。 v$sort_segment中查到total_block 为22656. 而我们知道temp文件每个文件都被系统占用了128个block(从dba_temp_file 视图的 block , user_block对比可以看出)。那么被占用的总的数据块个数应该是128 *2 (两个临时文件所以乘以2) 加上sort_segment中的22656. 128*2 + 22656=22912。 这是数据块的个数,如果要得到BYTE为单位的大小还要乘以8192(数据块尺寸), 结果正好为ALLOCATED_SPACE。验证成功。不过要注意的很重要的一点是,这里虽然是allocate的空间但并非是不可用的。其中包含了一部分仍然可用的。比如sort_segment这个视图中没分配的extent。 对于FREE_SPACE,也许你会认为直接用 total size 减去 allocate的space就可以了。但如果这样,那么这列存在的意义就不大了。实际上这一列显示的是 临时空间中可用的空间。可用的意思是如果有排序操作或者说临时表操作需要,我们仍然可以提供这么大的空间。这一部分不是 total 与 allocate的差,实际上它应该是 total 减去 allocate 再加上allocate中可用的那部分。 验证一下 ,total 减去 allocate 为239075328 - 187695104 = 51380224。而 allocate中可用部分的大小我们可以从v$sort_segment视图中看出。可以看到仅仅使用了256个block。那么剩余的空间应该是v$sort_segment的总大小减去256 ,为22656-256 = 22400. 这里只是数据块的个数,要得到大小还要乘以8192 得 183500800。用这个数字加上之前的51380224正好为 234881024。 |
临时表空间的空间使用情况查询
精选 转载文章标签 临时表空间 v$dba_temp_fi 文章分类 Oracle 数据库
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
oracle 临时表空间使用情况
临时表空间
临时 表空间 -
oracle查询临时表空间使用情况
oracle查询临时表空间使用情况
oracle 查询 临时 -
Oracle查询表空间使用情况
如何查询分析oracle表空间
数据库 查询分析 -
查看临时表空间使用情况,如何扩展表空间 temp
查看临时表空间使用情况,如何扩展表空间 temp
使用情况 表空间 查看临时