logo标签列表

如何在Oracle中缩小临时表空间？

oracle
39

我们如何在Oracle中缩小临时表空间？为什么它会增长得如此之多，达到25 GB，而应用程序中只有一个模式，在数据表空间大小为2 GB和索引表空间大小为1 GB的情况下使用。

你看过我的回答和链接到的页面吗？我想知道你还需要什么。 - Marius Burz
7个回答
118

哦，天哪！看看我的临时表空间有多大！或者说...如何缩小Oracle中的临时表空间。

是的，我运行了一个查询来查看我的临时表空间有多大：

SQL> SELECT tablespace_name, file_name, bytes
2  FROM dba_temp_files WHERE tablespace_name like 'TEMP%';

TABLESPACE_NAME   FILE_NAME                                 BYTES
----------------- -------------------------------- --------------
TEMP              /the/full/path/to/temp01.dbf     13,917,200,000

首先要问的问题是为什么临时表空间这么大。

你可能心中已经有了答案。可能是由于你运行了一个带有错误排序的大查询（我做过不止一次），也可能是由于其他异常情况。如果是这种情况，那么你只需要缩小临时表空间并继续生活即可。

但是如果你不知道答案呢？在决定缩小之前，你可能需要调查造成表空间增长的原因。如果这种情况经常发生，那么你的数据库可能确实需要这么多的空间。

动态性能视图

V$TEMPSEG_USAGE

在确定原因方面可能非常有用。

也许你并不关心原因，只需缩小它即可。这是你上班的第三天。数据库中的数据仅为200MiB，临时表空间为13GiB - 只需缩小它并继续进行。如果它再次增长，那么我们将研究原因。同时，我的磁盘卷已经没有空间了，我只需要回收空间。

让我们来看看如何缩小它。这将在某种程度上取决于您正在运行的Oracle版本以及临时表空间的设置方式。
Oracle会尽力避免您犯下任何可怕的错误，因此我们将尝试命令，如果不起作用，我们将通过其他方式来缩小。

首先，让我们尝试缩小数据文件。如果我们能做到这一点，那么我们就可以恢复空间，明天再担心它为什么增加。

SQL>
SQL> alter database tempfile '/the/full/path/to/temp01.dbf' resize 256M; 
alter database tempfile '/the/full/path/to/temp01.dbf' resize 256M
*   
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

根据错误信息，您可能需要尝试使用比当前文件大小更小的不同大小。我在这方面的成功有限。只有当临时表空间位于文件开头并且比您指定的大小要小时，Oracle才会缩小文件。一些旧的Oracle文档（已经更正）称您可以发出命令，错误消息将告诉您可以缩小到什么大小。但是在我担任数据库管理员的时候，这种说法已经不正确了。您只需猜测并多次重新运行该命令并查看是否有效。

好的，那样没用。那这个呢。

SQL> alter tablespace YOUR_TEMP_TABLESPACE_NAME shrink space keep 256M;
如果您在11g中（也许在10g中），这就是答案！如果它有效，您可能想回到之前的命令并尝试多次。但如果失败怎么办。如果临时表空间是安装数据库时设置的默认临时表空间，则可能需要进行更多的工作。此时，我通常会重新评估是否真的需要将该空间释放。毕竟，每个GiB只需要$X.XX的磁盘空间成本。通常情况下，我不想在生产时间更改这样的内容。这意味着再次在凌晨2点工作！（并不是我非常反对在凌晨2点工作-只是...嗯，我也喜欢睡觉。而且我的妻子喜欢在凌晨2点时有我在家...而不是在4点钟闲逛在市区街头试图记得3小时前停车的地方。我听说过“远程办公”的事情。我只担心我会走到一半，然后我的互联网连接就会失败-然后我必须在早上使用数据库之前赶到市中心修复所有问题。

好吧...回到严肃的事情上来...如果要缩小的临时表空间是您的默认临时表空间，您必须首先创建一个新的临时表空间，将其设置为默认的临时表空间，然后删除旧的默认临时表空间并重新创建它。之后再删除创建的第二个临时表。

SQL> CREATE TEMPORARY TABLESPACE temp2
2  TEMPFILE '/the/full/path/to/temp2_01.dbf' SIZE 5M REUSE
3  AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Database altered.

SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.


SQL> CREATE TEMPORARY TABLESPACE temp
2  TEMPFILE '/the/full/path/to/temp01.dbf' SIZE 256M REUSE
3  AUTOEXTEND ON NEXT 128M MAXSIZE unlimited
4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Database altered.

SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

希望这些东西中的一个能够帮到您！

只是一个提醒--SHRINK SPACE是在11g中引入的，不可用于10g。感谢您对所有选项进行了非常全面的撰写！ - michel-slm
1
互联网上的许多帖子和文章都建议非常复杂的程序。而这个是我找到的最简单的！ - Olivier Jacot-Descombes
1
非常感谢您提供这么清晰、精确的解释和解决方案。这是我在互联网上找到的最好的一个。 - John Verco
我同意，这个答案非常棒且非常有帮助！谢谢！再考虑一下，我有一个问题 -是否可能在临时表空间下创建第二个数据文件，然后将原始数据文件脱机，删除/重新创建它，然后将其重新上线？这只是一个小差别，但我想知道保留表空间是否会有所帮助或妨碍该过程？ - chopsuei3
第二种方法（使用temp2）可能会在“DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES”步骤上因为“enq: TS - contention”的原因而长时间挂起，如果有直接或间接使用它的会话（不一定是活动状态）。 - Tagar
6

从8i版本开始，管理表空间的选项变得更加优秀。如果您使用适合临时表空间的类型文件（即本地管理的临时文件），这尤其正确。

所以，它可能只需要这个命令，就能将您的表空间缩小到128兆...

alter tablespace <your_temp_ts> shrink space keep 128M;

Oracle在线文档非常好。 了解更多信息。 编辑
看来OP使用的是数据库的早期版本。在早期版本中，我们必须调整每个数据文件的大小。因此，首先找到文件名。以下任一查询都可以完成此操作...
select file_name from dba_data_files where tablespace_name = '<your_temp_ts>'
/

select file_name from dba_temp_files where tablespace_name = '<your_temp_ts>'
/

然后在此命令中使用该路径：

alter database datafile '/full/file/path/temp01.dbf'  resize 128m
/
SQL> alter tablespace temp shrink space keep 128M; 缩小表空间temp并保留128M空间。 * 第1行出现错误： ORA-02142：缺少或无效的ALTER TABLESPACE选项。 - P Sharma
2
您应该写下您使用的Oracle版本。您很可能使用的不是Oracle 11g，这就是为什么您无法缩小临时表空间的原因。
替代方案：
1）alter database tempfile '[your_file]' resize 128M; 这可能会失败 2）删除并重新创建表空间。如果您想要缩小的临时表空间是您的默认临时表空间，则可能需要先创建一个新的临时表空间，将其设置为默认临时表空间，然后删除旧的默认临时表空间并重新创建它。然后删除第二个创建的临时表。 3）对于Oracle 9i及更高版本，您可以直接删除tempfile并添加新的tempfile。
所有内容都在此处详细描述。
请查看此链接：http://databaseguide.blogspot.com/2008/06/resizing-temporary-tablespace.html
它已经被链接了，但是也许你错过了，所以我再发一遍。
1
临时表空间用于数据库排序和连接操作以及存储全局临时表。它可能会随着时间的推移而增大，因此我们需要重新创建临时表空间或缩小它以释放未使用的空间。 缩小TEMP表空间的步骤
1

由于需要临时存储空间，可能是由于笛卡尔积或大型排序操作，因此它将增加。

动态性能视图V$TEMPSEG_USAGE将有助于诊断原因。

另一个原因可能是全局临时表的大量使用。 - Gary Myers
是的，很好的观点。或者甚至是子查询因子子句结果集的实体化。 - David Aldridge
0
alter database datafile  'C:\ORA_SERVER\ORADATA\AXAPTA\AX_DATA.ORA' resize 40M;

如果这并没有帮助：
  • 创建新的表空间
  • 切换到新的临时表空间
  • 等待旧表空间不再被使用
  • 删除旧表空间
0

我不会费心去删除备用的临时文件，以防将来需要再次回收存储空间...

  1. 将临时组从默认设置更改为独立的临时文件
  2. 等待一段时间，然后调整临时组成员的大小
  3. 将默认设置恢复为临时组
  4. 等待一段时间，调整独立临时文件的大小。最后一步没有必要急着做。
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接