Oracle Temporary tablespace

Temporary tablespace A temporary tablespace contains transient(临时) data that persists only for the duration of a session. No permanent schema objects can reside(驻留) in a temporary tablespace. The database stores temporary tablespace data in temp files. 临时表空间的作用： 1.用于存放临时数据 临时表：  会话级别：这个数据的生存周期是一个会话 事务级别 : 这个会话的生存周期是一个事物，用于中间数据的存储 2.用于排序 排序是在PGA（sort area区域用于排序）(当空间不足就会使用临时表空间) order by 索引(因为索引是有序的) ******************************************************************************************************* 查看当前的临时表空间？ SYS@orcl11g> select tablespace_name,contents from dba_tablespaces where contents='TEMPORARY'; TABLESPACE_NAME      CONTENTS ------------------------------ ---------------- TEMP                             TEMPORARY ******************************************************************************************************** 查看当前的临时文件： --oracle9i后，oracle将临时表空间所对应的临时数据文件与一般的数据文件分开 SYS@orcl11g> col file_name for a50 SYS@orcl11g> select file_id,file_name,tablespace_name,bytes,autoextensible from dba_temp_files; FILE_ID  FILE_NAME                             TABLESPACE_NAME  BYTES   AUT ---------- ----------------------------------------------------------  --------------------------- ------------- ----         1  /u01/app/oracle/oradata/orcl11g/temp01.dbf    TEMP                  30408704  YES 文件编号也是1，这是临时文件的编号，数据文件的编号也有一个1，两者之间各成体系； ********************************************************************************************************* 创建新的临时表空间： SYS@orcl11g> create temporary tablespace temp1  2   tempfile '/u01/app/oracle/oradata/orcl11g/temp1.dbf'  3  size 50m; SYS@orcl11g> create temporary tablespace temp2  2  tempfile '/u01/app/oracle/oradata/orcl11g/temp2.dbf'  3* size 50m; ********************************************************************************************************** 临时表空间 组的概念： SYS@orcl11g> alter tablespace temp1 tablespace group temp_grp; SYS@orcl11g> alter tablespace temp2 tablespace group temp_grp; ********************************************************************************************************** 查看表空间组的信息 SYS@orcl11g> select * from dba_tablespace_groups; GROUP_NAME                 TABLESPACE_NAME ------------------------------ ------------------------------ TEMP_GRP                       TEMP1 TEMP_GRP                       TEMP2 组的创建: 有两种方式： 一种是在创建时指定临时组> CREATE TEMPORARY TABLESPACE tbs_temp_02 TEMPFILE 'temp02.dbf' SIZE 5M AUTOEXTEND ON> TABLESPACE GROUP tbs_grp_01; 二创建完之后再指定 临时表空间的应用： 可以为oracle的每一个用户分配一个临时表空间，这样做较浪费； 可以使用临时表空间组，可减少资源的浪费，做法是将一个临时表空间组（有两个成员），分配给三个人使用； *********************************************************************************************************** 如何查看当前用户使用的临时表空间情况？ SYS@orcl11g> select username,default_tablespace,temporary_tablespace  2  from dba_users  3* where username in ('SCOTT','HR') USERNAME    DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ---------------    -------------------------------     ------------------------------ SCOTT                       USERS     TEMP HR                             USERS     TEMP SYS@orcl11g> select * from database_properties where rownum <4; --永久类型数据库的属性 PROPERTY_NAME                      PROPERTY_VALUE  DESCRIPTION ------------------------------------------------ --------------------------    -------------------------------------------------- DICT.BASE                             2      dictionary base tables version # DEFAULT_TEMP_TABLESPACE               TEMP    Name of default temporary tablespace DEFAULT_PERMANENT_TABLESPACE     USERS Name of default permanent tablespace DEFAULT_EDITION                     ORA$BASE    Name of the database default edition 如果你创建了一个用户，没有指定临时表空间，它就使用系统给分配的默认的临时表空间 ************************************************************************************************************************* 修改数据库的默认临时表空间属性： 如果将默认的临时表空间的修改成临时表空间组的话，所有用户使用的临时表空间全都变成临时表空间组了 SYS@orcl11g> alter database default temporary tablespace temp_grp; 修改用户的默认临时表空间： SYS@prod> alter  user demo1 temporary tablespace temp_grp; ************************************************************************************************************************* 验证临时表空间组的使用： SYS@orcl11g> create user u1 identified by u1; SYS@orcl11g> create user u2 identified by u2; SYS@orcl11g> select username,default_tablespace,temporary_tablespace from dba_users where username in ('U1','U2'); USERNAME           DEFAULT_TABLESPACE   TEMPORARY_TABLESPACE ----------------------- ------------------------------   ------------------------------ U1                             USERS    TEMP_GRP U2                             USERS    TEMP_GRP SYS@orcl11g> grant connect,resource to u1,u2; SYS@orcl11g> create table u1.obj as select * from dba_objects; SYS@orcl11g> create table u2.obj as select * from dba_objects; SYS@orcl11g> insert into u1.obj select * from u1.obj; ... 将两个表的数据量达到大约40万行，相对于临时表空间50m来说； U1> select * from obj order by 1,2,3,4,5,6,7,8,9,10,11,12,13; U2> select * from obj order by 1,2,3,4,5,6,7,8,9,10,11,12,13; sys> select username,tablespace         from  v$sort_usage; --如果排序的时候没有使用到临时表空间，那么此视图无数据； ************************************************************************************************************************* 如何退出临时表空间组： SYS@orcl11g> alter database default temporary tablespace temp; SYS@orcl11g> alter tablespace temp1 tablespace group ''; SYS@orcl11g> alter tablespace temp2 tablespace group ''; 所有的组的成员退出组，组消失； 临时表空间的维护： 1.增加临时文件 SYS@orcl11g> alter tablespace temp2  add tempfile '/u01/app/oracle/oradata/orcl11g/temp202.dbf'  size 50m; 2.重置文件的size SYS@orcl11g> alter database tempfile  '/u01/app/oracle/oradata/orcl11g/temp202.dbf'  resize 55m; 3.自动扩展 SYS@orcl11g> alter database   tempfile '/u01/app/oracle/oradata/orcl11g/temp202.dbf'   autoextend on next 5m maxsize 200m; 4.删除临时文件 SYS@orcl11g> alter tablespace temp2  drop tempfile '/u01/app/oracle/oradata/orcl11g/temp2.dbf'; 5.删除临时表空间 SYS@orcl11g> drop tablespace temp2;