oracle 如何缩小oracle中的临时表空间？

How to shrink temp tablespace in oracle?

提问by P Sharma

How can we shrink temp tablespace in oracle? And why it is increasing so much like upto 25 GB since there is only one schema in the database for the application and data table space size is 2 GB and index table space size is 1 GB used.

我们如何缩小oracle中的临时表空间？以及为什么它会增加到 25 GB，因为数据库中只有一个模式用于应用程序，数据表空间大小为 2 GB，索引表空间大小为 1 GB。

回答by Philip Schlump

Oh My Goodness! Look at the size of my temporary table space! Or... how to shrink temporary tablespaces in Oracle.

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

Yes I ran a query to see how big my temporary tablespace is:

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

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

The first question you have to ask is why the temporary tablespace is so large. You may know the answer to this off the top of your head. It may be due to a large query that you just run with a sort that was a mistake (I have done that more than once.) It may be due to some other exceptional circumstance. If that is the case then all you need to do to clean up is to shrink the temporary tablespace and move on in life.

您要问的第一个问题是为什么临时表空间如此之大。您可能很快就知道了这个问题的答案。这可能是由于您刚刚使用错误的排序运行了一个大查询（我已经不止一次这样做了）。这可能是由于其他一些特殊情况。如果是这种情况，那么您需要做的就是缩小临时表空间并继续生活。

But what if you don't know? Before you decide to shrink you may need to do some investigation into the causes of the large tablespace. If this happens on a regular basis then it is possible that your database just needs that much space.

但如果你不知道呢？在决定缩小之前，您可能需要对大表空间的原因进行一些调查。如果这种情况经常发生，那么您的数据库可能只需要那么多空间。

The dynamic performance view

动态性能视图

V$TEMPSEG_USAGE

can be very useful in determining the cause.

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

Maybe you just don't care about the cause and you just need to shrink it. This is your third day on the job. The data in the database is only 200MiB if data and the temporary tablespace is 13GiB - Just shrink it and move on. If it grows again then we will look into the cause. In the mean time I am out of space on that disk volume and I just need the space back.

也许你只是不在乎原因，你只需要缩小它。这是你上班的第三天。如果数据和临时表空间为 13GiB，则数据库中的数据仅为 200MiB - 只需将其缩小并继续。如果它再次增长，那么我们将调查原因。与此同时，我的磁盘卷空间不足，我只需要空间。

Let's take a look at shrinking it. It will depend a little on what version of Oracle you are running and how the temporary tablespace was set up.
Oracle will do it's best to keep you from making any horrendous mistakes so we will just try the commands and if they don't work we will shrink in a new way.

让我们来看看缩小它。这将在一定程度上取决于您运行的 Oracle 版本以及临时表空间的设置方式。
Oracle 将尽最大努力防止您犯任何可怕的错误，因此我们只会尝试这些命令，如果它们不起作用，我们将以新的方式缩小。

First let's try to shrink the datafile. If we can do that then we get back the space and we can worry about why it grew tomorrow.

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

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

Depending on the error message you may want to try this with different sizes that are smaller than the current site of the file. I have had limited success with this. Oracle will only shrink the file if the temporary tablespace is at the head of the file and if it is smaller than the size you specify. Some old Oracle documentation (they corrected this) said that you could issue the command and the error message would tell you what size you could shrink to. By the time I started working as a DBA this was not true. You just had to guess and re-run the command a bunch of times and see if it worked.

根据错误消息，您可能想要尝试使用小于文件当前站点的不同大小。我在这方面取得了有限的成功。如果临时表空间位于文件的头部并且小于您指定的大小，Oracle 只会收缩文件。一些旧的 Oracle 文档（他们更正了这一点）说您可以发出命令，错误消息会告诉您可以缩小到什么大小。当我开始担任 DBA 时，情况并非如此。你只需要猜测并重新运行命令多次，看看它是否有效。

Alright. That didn't work. How about this.

好吧。那没有用。这个怎么样。

SQL> alter tablespace YOUR_TEMP_TABLESPACE_NAME shrink space keep 256M;

If you are in 11g (Maybee in 10g too) this is it! If it works you may want to go back to the previous command and give it some more tries.

如果你是 11 克（也许也是 10 克），就是这样！如果它有效，您可能想要返回上一个命令并再试几次。

But what if that fails. If the temporary tablespace is the default temporary that was set up when the database was installed then you may need to do a lot more work. At this point I usually re-evaluate if I really need that space back. After all disk space only costs $X.XX a GiB. Usually I don't want to make changes like this during production hours. That means working at 2AM AGAIN! (Not that I really object to working at 2AM - it is just that... Well I like to sleep too. And my wife likes to have me at home at 2AM... not roaming the downtown streets at 4AM trying to remember where I parked my car 3 hours earlier. I have heard of that "telecommuting" thing. I just worry that I will get half way through and then my internet connectivity will fail - then I have to rush downtown to fix it all before folks show up in the morning to use the database.)

但是如果失败了怎么办。如果临时表空间是安装数据库时设置的默认临时表空间，那么您可能需要做更多的工作。在这一点上，我通常会重新评估我是否真的需要那个空间。毕竟磁盘空间只需花费 X.XX 美元一个 GiB。通常我不想在生产时间内进行这样的更改。这意味着再次在凌晨 2 点工作！（并不是说我真的反对在凌晨 2 点工作——只是……我也喜欢睡觉。我妻子喜欢凌晨 2 点让我在家……而不是凌晨 4 点在市中心的街道上闲逛，试图记住在哪里3小时前我把车停好。我听说过“远程办公”的事情。

Ok... Back to the serious stuff... If the temporary tablespace you want to shrink is your default temporary tablespace, you will have to first create a new temporary tablespace, set it as the default temporary tablespace then drop your old default temporary tablespace and recreate it. Afterwords drop the second temporary table created.

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

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.

Hopefully one of these things will help!

希望其中一件事会有所帮助！

回答by APC

The options for managing tablespaces have got a lot better over the versions starting with 8i. This is especially true if you are using the appropriate types of file for a temporary tablespace (i.e. locally managed tempfiles).

管理表空间的选项比从 8i 开始的版本好很多。如果您为临时表空间使用适当类型的文件（即本地管理的临时文件），则尤其如此。

So, it could be as simple as this command, which will shrink your tablespace to 128 meg...

所以，它可以像这个命令一样简单，它将你的表空间缩小到 128 兆......

alter tablespace <your_temp_ts> shrink space keep 128M;

The Oracle online documentation is pretty good. Find out more.

Oracle 在线文档非常好。 了解更多。

edit

编辑

It would appear the OP has an earlier version of the database. With earlier versions we have to resize individual datafiles. So, first of all, find the file names. One or other of these queries should do it...

看起来 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>'
/ 

Then use that path in this command:

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

alter database datafile '/full/file/path/temp01.dbf'  resize 128m
/

回答by Marius Burz

You should have written what version of Oracle you use. You most likely use something else than Oracle 11g, that's why you can't shrink a temp tablespace.

您应该写下您使用的 Oracle 版本。您最有可能使用 Oracle 11g 以外的其他东西，这就是您无法缩小临时表空间的原因。

Alternatives:

备择方案：

1) alter database tempfile '[your_file]' resize 128M;which will probably fail
2) Drop and recreate the tablespace. If the temporary tablespace you want to shrink is your default temporary tablespace, you may have to first create a new temporary tablespace, set it as the default temporary tablespace then drop your old default temporary tablespace and recreate it. Afterwards drop the second temporary table created. 3) For Oracle 9i and higher you could just drop the tempfile(s) and add a new one(s)

1)alter database tempfile '[your_file]' resize 128M;这可能会失败
2) 删除并重新创建表空间。如果您要收缩的临时表空间是您的默认临时表空间，您可能必须首先创建一个新的临时表空间，将其设置为默认临时表空间，然后删除旧的默认临时表空间并重新创建它。然后删除创建的第二个临时表。3) 对于 Oracle 9i 及更高版本，您可以删除临时文件并添加一个新文件

Everything is described herein great detail.

一切都在这里非常详细地描述。

---

---

See this link: http://databaseguide.blogspot.com/2008/06/resizing-temporary-tablespace.html
It was already linked, but maybe you missed it, so here it is again.

请参阅此链接：http: //databaseguide.blogspot.com/2008/06/resizing-temporary-tablespace.html
已链接，但您可能错过了，所以又来了。

回答by Rosel

Temporary tablespaces are used for database sorting and joining operations and for storing global temporary tables. It may grow in size over a period of time and thus either we need to recreate temporary tablespace or shrink it to release the unused space.

临时表空间用于数据库排序和连接操作以及用于存储全局临时表。它的大小可能会在一段时间内增长，因此我们需要重新创建临时表空间或缩小它以释放未使用的空间。

Steps to shrink TEMP Tablespace

缩小 TEMP 表空间的步骤

回答by David Aldridge

It will be increasing because you have a need for temporary storage space, possibly due to a cartesian product or a large sort operation.

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

The dynamic performance view V$TEMPSEG_USAGEwill help diagnose the cause.

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

回答by demas

alter database datafile  'C:\ORA_SERVER\ORADATA\AXAPTA\AX_DATA.ORA' resize 40M;

If it doesn't help:

如果它没有帮助：

• Create new tablespace
• Switch to new temporary tablespace
• Wait until old tablespace will not be used
• Delete old tablespace

• 创建新表空间
• 切换到新的临时表空间
• 等到旧表空间不会被使用
• 删除旧表空间

回答by leinad31

I don't bother with dropping the alternate temp in case i need to reclaim storage again in the future...

如果我将来需要再次回收存储，我不会费心降低备用温度...

1. from temp group set default to stand-alone temp
2. wait awhile, then resize members of temp group
3. set default back to temp group
4. wait awhile, resize stand alone temp. there's no rush to do the last step

1. 从临时组设置默认为独立临时
2. 稍等片刻，然后调整临时组成员的大小
3. 将默认设置回临时组
4. 稍等片刻，调整独立温度。不急于做最后一步