Oracle-临时表空间

Command not found

于 2024-06-25 08:57:52 发布

阅读量622

点赞数 21

分类专栏： Oracle-文件相关文章标签： oracle 数据库

本文链接：https://blog.csdn.net/weixin_44904114/article/details/139946072

版权

Oracle-临时表空间

常见占用临时表空间的几种情况

order by or group by (disc sort占主要部分)；

索引的创建和重创建；

distinct操作；

union & intersect & minus sort-merge joins；

analyze 操作；

有些异常也会引起TEMP的暴涨。

数据库临时表空间使用情况

set line 400 pagesize 500
col TABLESPACE_NAME for a15
col FILE_NAME for a40
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;

TABLESPACE FILE_NAME						  		BLOCKS STATUS  AUT FILE_SIZE(G) MAX_SIZE(G) INCREMENT_BY USEFUL_SIZE
---------- --------------------------------------- ---------- ------- --- ------------ ----------- ------------ -----------
TEMP	   +DATA/orcl/tempfile/temp.263.1045489063		    2560 ONLINE  YES	.01953125  31.9999847			80  .018554688
TEMP	   +DATA/orcl/tempfile/temp.270.1046028793		   25600 ONLINE  YES	 .1953125  31.9999847	     12800  .194335938

确认数据库临时表空间使用率

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 GV_$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;

TABLESPACE_NAME 		  FREE(G)   TOTAL(G)	USED(%)    FREE(%)
--------------------	 ---------- ---------- ---------- ----------
TEMP			       	.193359375  .21484375	     10 	90

set line 400
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;

TABLESPACE_NAME 	TOTAL_GB    FREE_GB     USE_GB	   USE
------------------ ----------- ----------- ----------- -------
TEMP				    96.388	  .394	    95.994  99.59%

确定数据库用户的默认临时表空间

set line 400 pagesize 500
select username,temporary_tablespace from dba_users where account_status ='OPEN';

USERNAME		       			TEMPORARY_TABLESPACE
------------------------------ --------------------
SYSTEM			       			TEMP
SYS			       				TEMP
TEST1			       			TEMP

确定数据库的默认临时表空间

set line 400 pagesize 500
col PROPERTY_NAME for a30
col PROPERTY_VALUE for a20
col DESCRIPTION for a50
select * from database_properties where property_name ='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME		       		PROPERTY_VALUE	    DESCRIPTION
------------------------------ -------------------- ------------------------------------
DEFAULT_TEMP_TABLESPACE        		TEMP		    Name of default tempora