oracle 11g在线收回临默认临时表空间

最新推荐文章于 2024-09-06 09:54:05 发布

lovehewenyu

最新推荐文章于 2024-09-06 09:54:05 发布

阅读量839

点赞数

CC 4.0 BY-SA版权

分类专栏： oracle 基础知识

本文链接：https://blog.youkuaiyun.com/dbadoudou/article/details/18043729

oracle 基础知识专栏收录该内容

27 篇文章

订阅专栏

本文详细介绍了如何在Oracle 11g中在线回收默认临时表空间，包括查看数据库版本、检查默认临时表空间、检查临时表空间当前空闲情况、查看当前临时文件大小、使用命令释放不使用的临时表空间等步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

oracle 11g在线收回临默认临时表空间

--数据库版本
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

--查看数据库默认临时表空间
COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30
COLUMN description FORMAT A50
select *
from database_properties
where property_name like 'DEFAULT%TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE                 DESCRIPTION
------------------------------ ------------------------------ --------------------------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP2                          Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE   USERS                          Name of default permanent tablespace

--检查临时表空间当前空闲情况（收回前查看临时表空间的空间情况）
set line 150
col TABLESPACE_NAME for a30
col TABLESPACE_SIZE for 99999999999999
col ALLOCATED_SPACE for 99999999999999
col FREE_SPACE for      99999999999999
SELECT TABLESPACE_NAME,
       TABLESPACE_SIZE/1024/1024/1024 as "TABLESPACE_SIZE(G)",
       ALLOCATED_SPACE/1024/1024/1024 as "ALLOCATED_SPACE (G)",
       FREE_SPACE/1024/1024/1024       as "FREE_SPACE(G)"
from DBA_TEMP_FREE_SPACE;
TABLESPACE_NAME                TABLESPACE_SIZE(G) ALLOCATED_SPACE (G) FREE_SPACE(G)
------------------------------ ------------------ ------------------- -------------
TEMP2                                  31.9999847          31.9999847    31.9980469

--查看当前临时文件大小
col name for a50
SELECT file#, name, bytes/1024/1024/1024 as "TEMPFILE(G)" FROM v$tempfile ORDER BY name;
SQL> SELECT file#, name, bytes/1024/1024/1024 as "TEMPFILE(G)" FROM v$tempfile;

FILE# NAME TEMPFILE(G)
---------- -------------------------------------------------- -----------
1 /oradata/app/oracle/oradata/img/temp2.dbf 31.9999847

--oracle 11g 使用命令释放不使用的临时表空间（仅locally managed temporary tablespaces可用：Doc ID 452697.1）
--语法如下：
--ALTER TABLESPACE tablespace SHRINK SPACE | TEMPFILE tempfile_name [KEEP size];

--ALTER TABLESPACE tablespace SHRINK SPACE [KEEP size];
SQL> alter tablespace temp2 shrink space keep 30G;

Tablespace altered.

SQL> SELECT file#, name, bytes/1024/1024/1024 as "TEMPFILE(G)" FROM v$tempfile;

FILE# NAME TEMPFILE(G)
---------- -------------------------------------------------- -----------
1 /oradata/app/oracle/oradata/img/temp2.dbf 30.0009766

--ALTER TEMPFILE tablespace SHRINK tempfile tempfile_name [KEEP size];
SQL> alter tablespace temp2 shrink tempfile '/oradata/app/oracle/oradata/img/temp2.dbf' keep 29G;

Tablespace altered.

SQL> SELECT file#, name, bytes/1024/1024/1024 as "TEMPFILE(G)" FROM v$tempfile;

FILE# NAME TEMPFILE(G)
---------- -------------------------------------------------- -----------
1 /oradata/app/oracle/oradata/img/temp2.dbf 29.0009689

总结：
  11g的在线回收默认临时表空间功能很强大（仅locally managed temporary tablespaces可用：Doc ID 452697.1）
  11g以前回收默认临时表空间：(How to Shrink the Datafile of Temporary Tablespace (Doc ID 273276.1))
      1.建立新的小的默认临时表空间temp1
      2.alter tablespace命令使temp1成为默认临时表空间
      3.删除老的默认临时表空间temp