oracle临时表空间的管理

最新推荐文章于 2024-05-17 14:26:01 发布

清霄

最新推荐文章于 2024-05-17 14:26:01 发布

阅读量854

点赞数

分类专栏： Oracle

本文链接：https://blog.csdn.net/cwg_1992/article/details/10355797

版权

Oracle 专栏收录该内容

57 篇文章 0 订阅

订阅专栏

临时表空间的作用

索引create或rebuild；Order by 或 group by； Distinct 操作;Union 或 intersect 或 minus；Sort-merge joins；analyze的时候临时存储数据

oracle临时时表空间主要用来做查询和存放一些缓冲区数据。临时表空间消耗的主要原因是需要对查询的中间结果进行排序。
重启数据库可以释放临时表空间，如果不能重启实例，而一直保持问题sql语句的执行，temp表空间会一直增长。直到耗尽硬盘空间。
网上有人猜测在磁盘空间的分配上，oracle使用的是贪心算法，如果上次磁盘空间消耗达到1GB，那么临时表空间就是1GB。
也就是说当前临时表空间文件的大小是历史上使用临时表空间最大的大小。

注意：在视图dba_temp_files和v_$tempfile中查看临时表空间情况

查看临时表空间

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

TABLESPACE_NAME                           FILE_NAME                                                                        file_size(M)                    AUTOEXTEN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TEMP                                                        /oracle/oradata/SEM/temp02.dbf    391    NO

SQL> select status,enabled, name, bytes/1024/1024 file_size from v_$tempfile;          --用sys用户查看

STATUS                ENABLED                  NAME                      FILE_SIZE
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ONLINE                READ WRITE                    /oracle/oradata/SEM/temp02.dbf                         391

增大和减小临时表空间

SQL> alter database tempfile '/oracle/oradata/SEM/temp02.dbf' resize 400M; --临时表空间增大

数据库已更改。

SQL> select status,enabled, name, bytes/1024/1024 file_size from v_$tempfile;

STATUS ENABLED NAME FILE_SIZE
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ONLINE READ WRITE /oracle/oradata/SEM/temp02.dbf 400

SQL> alter database tempfile '/oracle/oradata/SEM/temp02.dbf' resize 40M; --临时表空间减小

数据库已更改。

设置临时表空间的自动增加

SQL> alter database tempfile '/oracle/oradata/SEM/temp02.dbf' autoextend on next 5m maxsize unlimited; --每次自动增长5M

数据库已更改。

向临时表空间添加数据文件

SQL> alter tablespace TEMP add tempfile '/oracle/oradata/SEM/temp01.dbf';

表空间已更改。

TEMP /oracle/oradata/SEM/temp02.dbf 40 YES --自动增长

TEMP /oracle/oradata/SEM/temp01.dbf 396 NO --没起用自动增长

SQL> select status,enabled, name, bytes/1024/1024 file_size from v_$tempfile;

STATUS ENABLED NAME FILE_SIZE

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

ONLINE READ WRITE /oracle/oradata/SEM/temp02.dbf 40

ONLINE READ WRITE /oracle/oradata/SEM/temp01.dbf 396

创建临时表空间

SQL> create temporary tablespace TEMP01 tempfile '/tmp/test.dbf' size 100M;

表空间已创建。

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

TEMP /oracle/oradata/SEM/temp02.dbf 40 YES

TEMP /oracle/oradata/SEM/temp01.dbf 396 NO

TEMP01 /tmp/test.dbf 100 NO

修改默认临时表空间

alter database default temporary tablespace TEMP01;

所有用户的默认临时表空间查看

select username,temporary_tablespace,default_tablespace from dba_users;

删除临时表空间

alter database tempfile '/tmp/test.dbf' drop;

彻底删除临时表空间

drop tablespace TEMP01 including contents and datafiles cascade constraints;

查看临时表空间使用情况

--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

select sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text
    from v$sort_usage sort, v$session sess, v$sql sql
   where sort.SESSION_ADDR = sess.SADDR
     and sql.ADDRESS = sess.SQL_ADDRESS
   order by blocks desc;

创建临时表空间组

create temporary tablespace tempgroup1 tempfile '/tmp/test2.dbf' size 2M tablespace group group1;
create temporary tablespace tempgroup2 tempfile '/tmp/test3.dbf' size 2M tablespace group group2;

查询临时表空间

select * from dba_tablespace_groups;

将表空间从一个临时表空间组移动到另外一个临时表空间组

alter tablespace temgroup1 tablespace group GROUP2 ;

把临时表空间组指定给用户
alter user scott temporary tablespace GROUP2;

在数据库级设置临时表空间
--alter database <db_name> default temporary tablespace GROUP2;
alter database orcl default temporary tablespace GROUP2;

删除临时表空间组 (删除组成临时表空间组的所有临时表空间)
    drop tablespace tempgroup1 including contents and datafiles;
    select * from dba_tablespace_groups;
    GROUP_NAME                     TABLESPACE_NAME
    ------------------------------ ------------------------------
    GROUP2                         TEMPTS2

    drop tablespace tempgroup2 including contents and datafiles;
    select * from dba_tablespace_groups;
    GROUP_NAME                     TABLESPACE_NAME

对临时表空间进行shrink（11g新增的功能）
将temp表空间收缩为20M
alter tablespace temp02 shrink space keep 20M;
自动将表空间的临时文件缩小到最小可能的大小
ALTER TABLESPACE temp SHRINK TEMPFILE ’/u02/oracle/data/lmtemp02.dbf’;

######################################

本文整理自网络

作者：john

转载请注明出处