Oracle-临时表空间(组)解读

简介: Oracle-临时表空间(组)解读

以前的整理


哪些情况下的操作会使用到临时表空间


首先我们要明确下哪些情况下的操作会使用到临时表空间:

  • 排序操作 比如select或dml(ddl)语句中包含order by之类;
  • create index
  • create pk constraint (其实这个跟create index类似,因为创建主键约束时默认会同时创建index)
  • enable constraint操作
  • create table语句


temp表空间的作用


temp表空间的作用,temp表空间主要是用作需要排序的操作。

  • 1.临时表空间 是用于在进行排序操作(如大型查询,创建索引和联合查询期间存储临时数据)每个用户都有一个临时表空间。
  • 2.对于大型操作频繁,(大型查询,大型分类查询,大型统计分析等),应指定单独的临时表空间,以方便管理。
  • 3.分配用户单独临时表空间,一般是针对 大型产品数据库,OLTP数据库,数据库仓库对于小型产品不需要单独制定临时表空间,使用默认临时表空间。


正常情况下,一个sql执行之后,返回结果后系统会自动收回分配给这个用户的空间,以便可以把此部分空间再分配给其他用户。


临时表空间信息


(查询用户需要具备dba权限)

 select * from dba_tablespaces  
         where tablespace_name = 'TEMP';
--自动扩展字段autoextendsible(yes/no)
select * from dba_temp_files;
select * from v$tempfile ;


20161117233544771.png


select tablespace_name,file_name,bytes/1024/1024 "file_size(M)",autoextensible from dba_temp_files;

Temporary Tablespacs 说明


临时表空间主要用途是在数据库进行排序运算、管理索引、访问视图等操作

时提供临时的运算空间,当运算完成之后系统会自动清理。


当 oracle 里需要用到sort 的时候, PGA 中 sort_area_size 大小不够时,将会把数据放入临时表空间里进行排序,同时如果有异常情况的话,也会被放入临时表空间。


正常来说,在完成 Select 语句、 create index 等一些使用 TEMP 表空间的排序操作后, Oracle 是会自动释放掉临时段的。


注意这里的释放,仅仅是将这些空间标记为空闲,并可重用,真正占用的磁盘空间并没有释放。 所以 Temp 表空间可能会越来越大。


排序是很耗资源的, Temp 表空间满了,关键是优化你的语句,尽量使排序减少才是上策.


Temp 表空间的操作


创建临时表空间


create temporary tablespace TEMP
tempfile '/oradata/cc/temp01.dbf' 
size 50m  
autoextend on  
next 50m maxsize 20480m  
extent management local;  --默认的是local ,可以不加,另外一种是dictionary(数据字典管理)


You can use ALTER TABLESPACE to add a tempfile, take a tempfile offline, or bring a tempfile online, as illustrated in the following examples:


SQL>ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/cc/temp02.dbf' SIZE 18M REUSE;
SQL>ALTER TABLESPACE TEMPFILE TEMPFILE OFFLINE;
SQL>ALTER TABLESPACE TEMPFILE TEMPFILE ONLINE;

不可以将 Temp 表空间 offline,但是可以将 tempfile offline。 V$TEMPFILE 显示了 tempfile 的状态。


The ALTER DATABASE statement can be used to alter tempfiles.

SQL>ALTER DATABASE TEMPFILE '/oradata/cc/temp02.dbf' OFFLINE;
SQL>ALTER DATABASE TEMPFILE '/oradata/cc/temp02.dbf' ONLINE;

改变临时表空间大小

alter database tempfile '/oradata/cc/temp01.dbf' resize 1024M;


扩展临时表空间

方法一、增大临时文件大小:

SQL> alter database tempfile ‘/oradata/cc/temp01.dbf’ resize 100m;


方法二、将临时数据文件设为自动扩展:


 SQL> alter database tempfile ‘/oradata/cc/temp01.dbf’ autoextend on next 5m maxsize unlimited;


方法三、向临时表空间中添加数据文件:

SQL> alter tablespace temp add tempfile ‘/oradata/cc/temp02.dbf’ size 100m;


Temp 表空间过大的处理方法


11g的shrink方法更加简单快捷,如果是11g的话,建议使用shrink.


替换 Temp 表空间


查看目前 Temp 表空间的信息


SQL> select name from v$tempfile;
NAME
--------------------------------------------------
/oradata/cc/temp01.dbf
SQL> select username,temporary_tablespace from dba_users;
USERNAME             TEMPORARY_TABLESPACE
------------------------ ------------------------------
SYS                       TEMP
SYSTEM                    TEMP
UCC                       TEMP
CC                        TEMP
.........


关于用户这块是要特别注意的,如果我们将默认的 Temp 表空间指向其他的

名称,那么这些用户的信息就会失效。


所以,我们替换时,

要么创建一个临时的Temp 表空间中转一下,这样切换之后,我们的 temp 空间名称不变,


要么改变名称,同时更新相关用户的 default temp 表空间。


这里用中转的方法来测试.


创建中转临时表空间


Temp 表空间必须是 uniform 的, undo 必须是 autoallocate 的。默认情况 下 uniform 是 1M。

创建 SQL

SQL>CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE
'/oradata/cc/temp02.dbf' SIZE 10M AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

修改 Temp2 为默认临时表空间

SQL>alter database default temporary tablespace temp2;


删除原来临时表空间

SQL>drop tablespace temp including contents and datafiles;

重新创建临时表空间


SQL>CREATE TEMPORARY TABLESPACE TEMP TEMPFILE
'/oradata/cc/temp02.dbf' SIZE 10M AUTOEXTEND OFF 
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;


重置缺省临时表空间为新建的 temp 表空间

SQL>alter database default temporary tablespace temp;


删除中转用临时表空间

SQL>drop tablespace temp2 including contents and datafiles;


如果有必要,重新指定用户表空间为重建的临时表空间

SQL>alter user dave temporary tablespace temp;


对临时表空间进行shrink


11g中针对临时表空间过大的问题推出了SHRINK方法,使用这种方法可以非常便捷的自动化完成缩小临时表空间或临时文件的目的。

SQL> select * from dba_temp_free_space;
TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
--------- --------- --------------- ----------
TEMP          1073741824       248512512 1069547520


官方说明dba_temp_free_space视图是11g中新增加的视图,使用这个视图可以很方便的得到临时表空间的使用情况。


当排序操作完成, 占用的空间并没有释放,仅仅是将它标记为空闲,并可重用,可以使用 shrink 来释放没有使用的空间。


shrink 是一个 online 的操作,不影响其他的查询.


使用临时表空间的SHRINK方法缩小临时表空间的大小


–将temp表空间收缩为20M

SQL>alter tablespace temp shrink space keep 20M;


或者

SQL> alter tablespace temp shrink space;
Tablespace altered.


操作之前,查询下大小,可以方便的比较出效果。

select * from dba_temp_free_space;


收缩表空间中具体的临时文件


SHRINK同样可以作用到具体的临时文件


SQL> select file#,name,bytes/1024/1024 MB from v$tempfile;
     FILE#             NAME                    MB
---------- ----------------------       ----------
     1         /oradata/cc/temp01.dbf           1024
SQL> alter tablespace temp shrink tempfile '/oradata/cc/temp01.dbf' keep 100m;
Tablespace altered
SQL> select file#,name,bytes/1024/1024 MB from v$tempfile;
     FILE#      NAME                   MB                                                                
---------- -------------------- --------------
         1   /oradata/cc/temp01.dbf    100.992187                              


20161119000723490.png


或者

SQL>ALTER TABLESPACE temp SHRINK TEMPFILE
'/oradata/cc/temp01.dbf ';--不指定大小,自动将表空间的临时文件缩小到最小可能的大小


更改系统的默认临时表空间


查询默认临时表空间

select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';


20161118001816226.png


修改默认临时表空间

alter database default temporary tablespace temp02;


所有用户的默认临时表空间都将切换为新的临时表空间:

select username,temporary_tablespace,default_tablespace from dba_users;


更改某一用户的临时表空间:

alter user scott temporary tablespace temp02;


删除临时表空间


删除临时表空间的一个数据文件:


alter database tempfile '/oradata/cc/temp01.dbf' drop;


删除临时表空间(彻底删除):


drop tablespace temp including contents and datafiles cascade constraints;


查看临时表空间的使用情况


GV_$TEMP_SPACE_HEADER视图必须在sys用户下才能查询 ,拥有DBA权限的用户也不行,必须sys用户

GV_$TEMP_SPACE_HEADER视图记录了临时表空间的使用大小与未使用的大小

dba_temp_files视图的bytes字段记录的是临时表空间的总大小

 SELECT temp_used.tablespace_name,
            total - used as "Free",
            total as "Total",
            round(nvl(total - used, 0) * 100 / total, 3) "Free percent"
       FROM (SELECT tablespace_name, SUM(bytes_used) / 1024 / 1024 used
               FROM GV_$TEMP_SPACE_HEADER
              GROUP BY tablespace_name) temp_used,
            (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total
               FROM dba_temp_files
              GROUP BY tablespace_name) temp_total
      WHERE temp_used.tablespace_name = temp_total.tablespace_name


SQL> conn sys/system as sysdba
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 
Connected as sys@cc AS SYSDBA
SQL>   SELECT temp_used.tablespace_name,
  2              total - used as "Free",
  3              total as "Total",
  4              round(nvl(total - used, 0) * 100 / total, 3) "Free percent"
  5         FROM (SELECT tablespace_name, SUM(bytes_used) / 1024 / 1024 used
  6                 FROM GV_$TEMP_SPACE_HEADER
  7                GROUP BY tablespace_name) temp_used,
  8              (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total
  9                 FROM dba_temp_files
 10                GROUP BY tablespace_name) temp_total
 11        WHERE temp_used.tablespace_name = temp_total.tablespace_name
 12  ;
TABLESPACE_NAME       Free      Total     Free percent
------------------------------ ---------- ---------- 
TEMP                   787       1024       76.855

tempfile 数据文件重命名的步骤:


( 1)将 tempfile offline

( 2)在操作系统上重命名 tempfile

( 3)使用 alter database rename file 更新控制文件


临时表空间组


概述


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

临时表空间组逻辑上就相当于一个临时表空间。


操作

SQL>create temporary tablespace temp1 tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' size 10M;
SQL>create temporary tablespace temp2 tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' size 10M;
SQL>create temporary tablespace temp3 tempfile '/u01/app/oracle/oradata/orcl/temp03.dbf' size 10M;
SQL>select name from v$tempfile;
NAME
----------------------------------------------------
/u01/app/oracle/oradata/orcl/temp01.dbf
/u01/app/oracle/oradata/orcl/temp02.dbf
/u01/app/oracle/oradata/orcl/temp01.dbf
SQL>select tablespace_name from dba_tablespaces where contents='TEMPORARY';
TABLESPACE_NAME
-------------------------------------------------------------
TEMP1
TEMP2
TEMP3

添加temp1,temp2,temp3到临时表空间组tempgrp中

SQL>alter tablespace temp1 tablespace group tempgrp;
SQL>alter tablespace temp2 tablespace group tempgrp;
SQL>alter tablespace temp3 tablespace group tempgrp;


启用临时表空间组

SQL>alter database default temporary tablespace tempgrp;
SQL>select * from dba_tablespace_groups;
GROUP_NAME                     TABLESPACE_NAME
---------------------------------------------------------
TEMPGRP                 TEMP1
TEMPGRP                 TEMP2
TEMPGRP                 TEMP3

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

SQL>select username,defualt_tablespace,temporary_tablespace from dba_user where username='SCOTT';
USERNAME        DEFAULT_TABLESPACE     TEMPORARY_TABLESPACE
-------------------------------------------------------
SCOTT            USERS                 TEMPGRP


删除临时表空间组

1.必须先删除成员

SQL>alter tablespace temp1 tablespace group '';(表示删除temp1)


SQL>select * from dba_tablespace_groups;
GROUP_NAME                    TABLESPACE_NAME
----------------------------------------------------------
TEMPGRP                 TEMP2
TEMPGRP                 TEMP3

同理将temp2,temp3删除


当表空间组是数据库默认表空间时,最后一个成员删除报错:ORA-10919:Defualt temporary tablespace group must be have at least one tablespace


SQL>alter database default temporary tablespace temp;

此时再删除最后一个成员,临时表空间组自动消失


SQL>select * from dba_tablespace_groups;
no rows selected

删除temp1表空间及数据文件

SQL>drop temporary tablespace temp1 including contents and datafiles;


相关文章
|
7月前
|
存储 Oracle NoSQL
Oracle 表空间、数据文件、schema的关系
Oracle 表空间、数据文件、schema的关系
198 2
|
SQL 监控 Oracle
Oracle创建和管理表空间
Oracle创建和管理表空间
102 1
|
Oracle 关系型数据库 数据库
9-4 Oracle管理表空间和数据文件
9-4 Oracle管理表空间和数据文件
126 0
|
4月前
|
Oracle 关系型数据库 数据库
[oracle]拆分多用户的公共表空间
[oracle]拆分多用户的公共表空间
|
5月前
|
存储 监控 Oracle
关系型数据库Oracle空间不足
【7月更文挑战第15天】
66 6
|
5月前
|
存储 Oracle 关系型数据库
关系型数据库Oracle 空间不足
【7月更文挑战第16天】
67 2
|
7月前
|
存储 数据库
发现oracle10gSYSAUX空间没有了进行处理
发现oracle10gSYSAUX空间没有了进行处理
48 0
|
7月前
|
SQL Oracle 关系型数据库
Oracle 数据泵导出导入(映射表空间、Schema)
Oracle 数据泵导出导入(映射表空间、Schema)
|
7月前
|
Oracle 关系型数据库 数据库
Oracle 11gR2学习之三(创建用户及表空间、修改字符集和Oracle开机启动)
Oracle 11gR2学习之三(创建用户及表空间、修改字符集和Oracle开机启动)
|
7月前
|
存储 Oracle 关系型数据库
Oracle表空间:数据王国的疆域规划
【4月更文挑战第19天】Oracle中的表空间是逻辑存储结构,用于存放数据库对象的物理数据,是数据库性能优化和备份恢复的基础。表空间类型多样,如永久和临时表空间,需根据业务需求进行规划和管理。通过监控使用情况、利用自动扩展功能,可有效管理表空间,提高数据访问速度和可靠性。深入理解表空间有助于优化数据库存储和管理。