如何在oracle中缩小临时表空间? [英] How to shrink temp tablespace in oracle?

问题描述

如何在oracle中缩小临时表空间?以及为什么它之所以增加到多达25 GB的原因，是因为数据库中只有一个适用于应用程序的架构，并且数据表空间大小为2 GB，索引表空间大小为1 GB.

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中缩小临时表空间.

Oh My Goodness! Look at the size of my temporary table space! Or... how to shrink temporary tablespaces in 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.

动态效果视图

V$TEMPSEG_USAGE

对于确定原因非常有用.

can be very useful in determining the cause.

也许您只是不关心原因，而只需要缩小原因即可. 这是您上班的第三天.数据库中的数据仅为200MiB 如果数据和临时表空间是13GiB-只需收缩并继续. 如果它再次增长，那么我们将调查原因.同时我是 该磁盘卷上的空间不足，我只需要退回空间.

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.

让我们来看看缩小它.这将取决于什么版本 您正在运行的Oracle数据库以及如何设置临时表空间.
甲骨文将尽力防止您犯任何可怕的错误 因此，我们将尝试使用命令，如果它们不起作用，我们将缩小 以一种新的方式.

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.

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

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

根据错误消息，您可能需要尝试使用不同的大小来尝试 小于文件的当前站点.我有限制 成功的.只有临时表空间，Oracle才会收缩文件 位于文件的开头，如果小于文件大小， 指定.一些旧的Oracle文档(他们已对此进行了更正)说: 您可以发出命令，错误消息将告诉您什么 您可以缩小到的大小.当我开始担任DBA时，这已经是 不对.您只需要猜测并重新运行该命令很多次 看看是否有效.

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.

好的.那没用.这样吧.

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

SQL> alter tablespace YOUR_TEMP_TABLESPACE_NAME shrink space keep 256M;

如果您使用的是11克(Maybee也使用的是10克)就可以了！如果可行，您可能想要 返回上一个命令并进行更多尝试.

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.

但是如果失败了该怎么办.如果临时表空间是默认临时表 在安装数据库时设置的设置，那么您可能需要执行 还有更多工作.在这一点上，我通常会重新评估我是否真的需要 退格.毕竟，所有磁盘空间的价格仅为每GiB $ X.XX.通常我不想要 在生产时间内进行这样的更改.这意味着在凌晨2点工作 再次！ (不是我真的反对 到凌晨2点工作-就是...我也喜欢睡觉.还有我老婆 喜欢在凌晨2点让我在家...不在凌晨4点漫游市区街道 记得三个小时前我把车停在哪里.我听说过这种远程办公" 事物.我只是担心会无法顺利进行，然后才可以连接互联网 将会失败-然后我必须赶紧去市区解决所有问题，然后人们才能出现在 早上使用数据库.)

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

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

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!

这篇关于如何在oracle中缩小临时表空间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！