oracle临时表空间

最新推荐文章于 2024-07-28 08:01:34 发布
最新推荐文章于 2024-07-28 08:01:34 发布
阅读量162 收藏
点赞数
文章标签： 数据库 运维

ORALCE 临时表空间

一 临时表空间概念

临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象,当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。


二 临时表空间信息

1 查看系统默认临时表空间(database_properties)

SELECT * FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

 

2 查看用户临时表空间(dba_users)

SELECT USERNAME, default_tablespace,TEMPORARY_TABLESPACE FROM DBA_USERS where username='CHEN';


3 查看临时表空间详细信息(v$tempfile,dba_temp_files)


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;



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;


select a.tablespace_name,

       a.file_name,

       a.status,

       b.status as bstatus,

       a.autoextensible,

       a.increment_by,

       a.bytes / 1024 / 1024 / 1024 || 'G' as file_size,

       decode(a.maxbytes,

              0,

              a.bytes / 1024 / 1024 / 1024 || 'G',

              maxbytes / 1024 / 1024 / 1024 || 'G') as max_size,

       a.USER_BYTES / 1024 / 1024 / 1024 || 'G' AS "USEFUL_SIZE"

  from dba_temp_files a, v$tempfile b

 where a.file_name = b.name;


其中使用decode函数的原因：

当临时表空间不是自动增长时，maxbytes=bytes

当临时表空间是自动增长时,maxbytes=maxbytes


三 管理临时表空间

1创建临时表空间

create temporary tablespace chen_temp tempfile '/data/oradata/cts/chen_temp01.dbf' size 100M autoextend off;


2增加数据文件

当临时表空间太小时，就需要扩展临时表空间（添加数据文件、增大数据文件、设置文件自动扩展）；有时候需要将临时数据文件分布到不同的磁盘分区中，提升IO性能，也需要通过删除、增加临时表空间数据文件。

方法一：

alter tablespace chen_temp add tempfile '/data/oradata/cts/chen_temp02.dbf' size 50m autoextend on next 10M maxsize 100M;

方法二：

alter tablespace chen_temp add tempfile '/data/oradata/cts/chen_temp03.dbf' size 50M autoextend off;

 

3 调整文件大小

如下例子，需要将临时数据文件从50M大小调整为60M

alter database tempfile '/data/oradata/cts/chen_temp03.dbf' resize 60M;


4 设置文件自动扩展,取消自动增长

alter database tempfile '/data/oradata/cts/chen_temp03.dbf' autoextend on next 10M maxsize unlimited;


alter database tempfile '/data/oradata/cts/chen_temp03.dbf' autoextend off;


5收缩临时表空间

排序等操作使用的临时段，使用完成后会被标记为空闲，表示可以重用，占用的空间不会立即释放，有时候临时表空间会变得非常大，此时可以通过收缩临时表空间来释放没有使用的空间。收缩临时表空间是ORACLE 11g新增的功能。

alter tablespace chen_temp shrink space keep 200M;

alter tablespace chen_temp shrink tempfile '/data/oradata/cts/chen_temp03.dbf';


6切换临时表空间

1 切换系统默认临时表空间

select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

alter database default temporary tablespace chen_temp;

2 切换用户默认来临时表空间

select username,temporary_tablespace from dba_users where username='CHEN';

alter user chen temporary tablespace chen_temp;


7移动重命名文件

例如，我需要将/data/oradata/cts/chen_temp03.dbf这个文件移动并重命名为/data/oradata/cts/chen/chen_temp003.dbf


1: 将临时表空间的临时文件脱机

alter database tempfile '/data/oradata/cts/chen_temp03.dbf' offline;


2：移动或重命名相关的临时文件

mv /data/oradata/cts/chen_temp03.dbf /data/oradata/cts/chen/chen_temp003.dbf


3: 使用脚本ALTER DATABASE RENAME FILE

alter database rename file '/data/oradata/cts/chen_temp03.dbf' to '/data/oradata/cts/chen/chen_temp003.dbf';


4: 将临时表空间的临时文件联机

alter database tempfile '/data/oradata/cts/chen/chen_temp003.dbf' online;


8监控临时表空间

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;


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 SYS.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;


9删除临时表空间及数据文件


(1)删除数据文件

方法1：

alter tablespace chen_temp drop tempfile '/data/oradata/cts/chen_temp01.dbf';

注意：这种删除临时表空间的写法会将对应的物理文件删除。


方法2：

alter database tempfile '/data/oradata/cts/chen_temp02.dbf' drop including datafiles;

注意：删除临时表空间的临时数据文件时，需要指定INCLUDING DATAFILES 选项才能真正删除物理文件，否则需要手工删除物理文件。


(2)删除临时表空间

drop tablespace chen_temp including contents and datafiles cascade constraints;

注意：

1 执行上面语句很长时间没没有结束，查询SELECT * FROM v$sort_usage where tablespace='CHEN_TEMP';

发现有大量的会话在使用这个空间，所有无法删除。

解决办法：重启数据库alter system checkpoint;

Shutdown immediate

Startup

在查询SELECT * FROM v$sort_usage where tablespace='CHEN_TEMP';已经没有会话；

再次执行drop tablespace chen_temp including contents and datafiles cascade constraints;成功。

2   Drop table cascade constraints 是不影响到存储于object里的row data,只是删除了相应的约束条件。

3   不能删除当前用户的默认表空间，否则会报ORA-12906错误。

如果需要删除某一个默认的临时表空间，则必须先创建一个临时表空间，然后指定新创建的表空间为默认表空间，然后删除原来的临时表空间。


四 临时表空间组


Oracle 10g之前，同一用户的多个会话只可以使用同一个临时表空间，因为在给定的时间只有一个临时表空间默认给用户，为了解决这个潜在的瓶颈，Oracle支持临时表空间组即包含多个临时表空间的集合。临时表空间组逻辑上就相当于一个临时表空间。

临进表空间组是ORACLE 10g引入的一个新特性，它是一个逻辑概念，不需要显示的创建和删除。只要把一个临时表空间分配到一个组中，临时表空间组就自动创建，所有的临时表空间从临时表空间组中移除就自动删除。

一个临时表空间组必须由至少一个临时表空间组成，并且无明确的最大数量限制。

 

create temporary tablespace chen_temp1 tempfile '/data/oradata/cts/chen_temp01.dbf' size 10M;


create temporary tablespace chen_temp2 tempfile '/data/oradata/cts/chen_temp02.dbf' size 10M;


create temporary tablespace chen_temp3 tempfile '/data/oradata/cts/chen_temp03.dbf' size 10M;


添加chen_temp1，chen_temp2，chen_temp3到临时表空间组tempgrp中


alter tablespace chen_temp1 tablespace group tempgrp;

alter tablespace chen_temp2 tablespace group tempgrp;

alter tablespace chen_temp3 tablespace group tempgrp;


启用临时表空间组

alter database default temporary tablespace tempgrp;

select * from dba_tablespace_groups;


此时数据库所有用户的默认临时表空间为tempgrp

SELECT USERNAME, default_tablespace,TEMPORARY_TABLESPACE FROM DBA_USERS where username='CHEN';


也可以指定已经创建好的临时表空间的临时表空间组

alter tablespace chen_temp1 tablespace group tempgrp; 


删除临时表空间组
必须先删除成员

alter tablespace chen_temp1 tablespace group ''; 

alter tablespace chen_temp2 tablespace group '';

select * from dba_tablespace_groups; 

当表空间组是数据库默认表空间时，最后一个成员删除报错：ORA-10919：Defualt temporary tablespace group must be have at least one tablespace
此时再删除最后一个成员，临时表空间组自动消失
alter tablespace chen_temp3 tablespace group '';
select * from dba_tablespace_groups;
 
删除临时表空间

SELECT * FROM v$sort_usage where tablespace='CHEN_TEMP1';

SELECT * FROM v$sort_usage where tablespace='CHEN_TEMP2';

SELECT * FROM v$sort_usage where tablespace='CHEN_TEMP3';

drop tablespace chen_temp1 including contents and datafiles cascade constraints;

drop tablespace chen_temp2 including contents and datafiles cascade constraints; 

drop tablespace chen_temp3 including contents and datafiles cascade constraints;
 
 



 

 

 

 

参考：

http://www.cnblogs.com/david-zhang-index/archive/2012/04/11/2442726.html

http://www.cnblogs.com/kerrycode/p/4006840.html

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29785807/viewspace-1579312/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29785807/viewspace-1579312/

ckth47350
关注
安装oracle临时表空间不释放,关于临时表空间无法释放的问题
weixin_33364018的博客
04-13 2409
今天有人提起9i中临时表空间的空间释放问题。简单记录下。正常来说，在完成Select语句、create index等一些使用TEMP表空间的排序操作后，Oracle是会自动释放掉临时段a的。但有些有侯我们则会遇到临时段没有被释放，TEMP表空间几乎满的状况，甚至是我们重启了数据库仍没有解决问题。一般当系统重启后，此表空间才会自动释放。可以查看v$sort_usage视图看看是哪个SQL语句导致te...
oracle临时表空间迁移,oracle 数据库迁移
weixin_36155936的博客
04-05 538
PL/SQL Release 11.2.0.4.0 - Production导出版本，数据库地址10.36.40.53PL/SQL Release 11.2.0.4.0 - Production导入版本，数据库地址10.36.40.7410.36.40.53 需要迁移的数据库地址1. 创建目录--创建目录cd /home/oraclemkdir bak201705112. 添加权限chmod ...
oracle-临时表空间
12-23
oracle-临时表空间详细介绍oracle-临时表空间详细介绍oracle-临时表空间详细介绍oracle-临时表空间详细介绍
Oracle（28）什么是临时表空间（Temporary Tablespace）？
最新发布
qq_43012298的博客
07-28 492
临时表空间（Temporary Tablespace）是Oracle数据库中用于存储临时数据的特殊表空间。它在执行排序、哈希连接、全表扫描等操作时起到关键作用。通过合理管理临时表空间，可以提高数据库操作的效率和性能。创建和管理临时表空间涉及创建临时文件、分配临时表空间给用户以及监控其使用情况。理解和管理临时表空间对于确保数据库系统的高效运行至关重要。
Oracle 临时表空间
weixin_34008933的博客
04-08 123
一、临时表空间主要使用在以下几种情况： 1、order by or group by (disc sort占主要部分)； 2、索引的创建和重创建； 3、distinct操作； 4、union & intersect & minus sort-merge joins； 5、Analyze 操作； 6、有些异常也会引起TEMP的暴涨。 Oracle临时表空间暴涨的现象经过...
Oracle数据库 临时表空间
专注于数据库技术分享，包含但不限于Oracle，MySQL，PostgreSQL，ElasticSearch及国产数据库等
03-12 986
Oracle临时表空间是用于存储临时数据和临时对象的特殊表空间。它主要用于支持数据库的排序、分组和连接等操作，以及临时表的创建和操作。临时表空间的管理是数据库管理中的重要任务，管理员需要定期监控、维护和优化临时表空间，以确保数据库的稳定运行和性能优化。通过正确管理和维护临时表空间，可以提高数据库的性能、可用性和安全性，从而更好地满足业务需求和用户需求。
Oracle 临时表空间管理（Temporary Tablespace）
Vincent的博客
03-17 3881
Oracle临时表空间（Temporary Tablespace）主要用来存储数据库运行中产生的临时对象，例如SQL排序结果集，临时表等，这些对象的生存周期只有会话。本文总结了Oralce中涉及临时表空间的管理和优化操作。
Oracle临时表空间不足和批处理缓慢问题探讨.pdf
10-09
Oracle 临时表空间不足和批处理缓慢问题探讨 本文探讨了 Oracle 临时表空间不足和批处理缓慢问题的原因和解决方法。通过分析和测试，发现了问题的根源在于应用逻辑方面，具体来说是 SQL 语句的编写问题。通过编写...
Oracle临时表空间满的解决步骤
04-08
解决 Oracle 临时表空间满的问题需要通过查看当前数据库的默认临时表空间、查看当前临时表空间的大小、创建新的临时表空间、将新建的临时表空间设置为数据库的默认临时表空间、删除原来的临时表空间、创建新的临时表...
Oracle临时表空间处理.txt
02-21
### Oracle 临时表空间管理详解 #### 一、概述 Oracle 数据库中的临时表空间主要用于存储临时对象，如排序操作、表连接等过程中产生的临时数据。这些数据在事务结束后会被自动清除，不会占用永久存储空间。正确管理...
『ORACLE』 对临时表空间相关操作（11g）
OFF-LINE
05-22 348
1、查看临时表空间 （dba_temp_files视图）（v_$tempfile视图） select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files; select status,enabled, name, bytes/1024/1024 file_size from v
22.Oracle中的临时表空间
qq_46645079的博客
11-30 1901
用来存放用户的临时数据，临时数据就是在需要时被覆盖，关闭数据库后自动删除，其中不能存放永久性数据。例如：当用户对很多数据行进行排序时，排序在PGA中进行。但是如果排序的数据过多，导致内存不足时，oracle会把要排序的数据分成多份，每次只取一份放在PGA中进行排序，其他的部分都放到临时表空间中，当PGA里的部分排序完成后，把排序好的部分交换到临时表空间中，同时再从临时表空间里取一份没有排序的数据到PGA中进行排序，这样直到所有数据排序完成为止。临时表空间组。
ORACLE临时表空间总结
Finder_Way
06-28 3082
临时表空间概念 临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象,当ORACLE里需要用到SORT的时候，并且当PGA中sort_area_size大小不够时，将会把数据放入临时表空间里进行排序。像数据库中一些操作： CREATE INDEX、 ANALYZE、SELECT DISTINCT、ORDER BY、GROUP BY、 UNION ALL、 INTERSECT、MINUS、SORT-MERGE JOINS、HASH JOIN等都可能会用到临时表空间。当操作完成后，.
oracle 临时 表空间,Oracle临时表空间
weixin_39668527的博客
04-03 558
Oracle临时表空间通过Oracle表空间的讲解，我们了解到表空间是Oracle数据库存储数据和对象的逻辑容器，那临时表空间呢？Oracle临时表空间主要是存储数据库的排序操作、临时表、中间排序结果等临时对象。例如，我们进行大数量级的排序操作时，当数据库内存不够时，就会写入临时表空间，当操作完成后，临时表空间就会自动清空释放。Oracle经常使用到临时表空间的操作有：create index(创...
Oracle临时表空间
02-22 1145
oracle的临时表空间 Oracle临时表空间为何暴涨？         昨天在做测试的时候发现一个非常奇怪的问题：在程序的查询模块中做查询的时候，开始速度很快，但是过了一段时间以后速度就变慢，最后干脆就报错，不工作了。在排错的过程中，发现Oracle临时表空间暴涨，达到了几十个GB，在Oracle中对Session进行跟踪，发现磁盘空间还在不停的消耗，几乎是每隔5s，临时表
Oracle创建表空间和用户、临时表空间
tcz_666的博客
09-22 2388
创建表空间，表空间是数据库中最大的逻辑单位，一个 Oracle 数据库至少包含一个表空间，就是名为SYSTEM的系统表空间。每个表空间是由一个或多个数据文件组成的，一个数据文件只能与一个表空间相关联。表空间的大小等于构成该表空间的所有数据文件大小之和。创建临时表空间，当排序不能在分配的空间中完成时，就会使用磁盘排序的方式，即在Oracle实例中的临时表空间中进行。创建用户语法。默认包含如下表空间（从v$tablespace中查看）：SYSTEM、SYSAUX、TEMP、UNDOTBS1、USERS
oracle设置临时表空间,oracle操作临时表空间
weixin_35745051的博客
04-03 3899
1、查看临时表空间 (dba_temp_files视图)(v_$tempfile视图)selectt ablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;selectstatus,enabled, name, bytes/1024/1024 file_size from v_$...
oracle临时表空间地址,Oracle中临时表空间
weixin_42538470的博客
04-03 254
临时表空间作为排序操作使用。当用户的sql语句使用了order by，group by子句时,Oracle服务器就需要对所选取的数据进行排序。这时如果排序的数据量很大，那么，此时内存的排序区(PGA中)就可能装不下,因此，Oracle服务器就要把一些中间的排序结果写到磁盘上，即临时表空间中。当用户的sql语句中经常有大规模的排序而内存的排序区不够时，使用临时表空间就可以改进数据库的效率。临时表空间...
Oracle临时表空间管理指南
"Oracle临时表空间处理涉及到对数据库中用于存储临时数据的区域进行管理，包括查看、增加、删除和修改其大小。本文件提供了详细的操作步骤，适用于Oracle数据库管理员进行日常维护工作。" 在Oracle数据库中，临时表...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值