在oracle 11g中收缩永久表空间 [英] shrink permanent tablespace in oracle 11g

问题描述

我需要在oracle 11g中缩小表空间.它不是临时表空间，我不能丢失该表空间中的数据.我的其他表空间之一空间不足，因此我不得不重新分配该表空间的剩余大小.收缩不适用于永久表空间.数据文件的当前大小为1150MB，我希望为256MB

I need to shrink a tablespace in oracle 11g. It is not a temporary table-space, I cant lose the data in that tablespace. One of my other tablespaces is out of space so I have to reallocate the remaining size of this tablespace. shrink does not work on a permanent tablespace. The current size of the datafile is 1150MB and I want it to be 256MB

推荐答案

您可以调整文件大小，从而:-

You can resize the file, thus:-

ALTER DATABASE DATAFILE '/your/path/your_file01.dbf' RESIZE 256M;

当然，如果您已经使用了256M以上的空间，则会收到错误消息

of course if you already used some space above 256M then you will get an error

ORA-03297: file contains used data beyond requested RESIZE value

然后，您可以使用此查询查看可以调整数据文件大小的最小大小:

Then you can use this query to see the smallest size you can resize the datafile:

SELECT  CEIL((NVL(e.hwm, 1) * 8192)/1024/1024) as "Mb"
FROM
    dba_data_files f
LEFT
JOIN    (SELECT file_id, max(block_id + blocks - 1) hwm FROM dba_extents GROUP BY file_id) e ON f.file_id = e.file_id
WHERE
    f.file_name = '/your/path/your_file01.dbf'
/

*如果您的表空间块大小不是8192，则请首先更改该值.还要注意，查询将需要很长时间才能运行-这是正常现象-或者，您也可以使用许多人都喜欢的反复试验技术，并一次调整一下它的大小，直到出现错误为止.

*If your tablespace block size is not 8192 then change that value first. Also note that the query will take a long time to run - this is normal - alternatively you can just use the trial and error technique favoured by many and resize it a bit at a time until it errors out.

这篇关于在oracle 11g中收缩永久表空间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！