以下是一个不时出现在OTN数据库论坛上的主题示例（我没有提供链接，因为这只是许多类似问题中的一个示例）：
“我为3-4个schemas分配了一个用户表空间。由于我急需硬盘空间，我不得不删除其中一个schema（drop user）。现在这个表空间显示为70%已填充。我想减少分配给它的空间。”
直接的答案很简单——alter database datafile ‘xxx’ resize NNN;–但有两个问题。首先，如何为NNN找出一个合适的值，其次，当你指定NNN的值，但Oracle报错：“ORA-03297: file contains used data beyond requested RESIZE value”时，该怎么办。
这两个问题的答案可以通过视图dba_extents和dba_free_space来解决。如果表空间中有一些空闲空间，那么只有它在数据文件的末尾才能“回收”（这是我最近在一篇https://jonathanlewis.wordpress.com/2010/01/30/free-space/关于空闲空间的一般性评论中提到的一点）。dba_free_space将告诉您空闲空间在哪里，dba_extents将告诉您空闲空间之后的内容是什么。我在这个URL（https://jonathanlewis.wordpress.com/tablespace-hwm/）上发布了一个简单的脚本来演示这个原理。下面的文本是输出的一个示例，显示了针对某个文件的报告的最后几行，以及为演示目的创建的一个小表空间的第二个文件报告的行。
FILE_ID BLOCK_ID END_BLOCK OWNER SEGMENT_NAME SEGMENT_TYPE
------- ----------- ----------- ---------- --------------- ------------------
5 2,185 2,312 TEST_USER PARENT TABLE
2,313 2,440 TEST_USER PARENT TABLE
2,441 2,568 TEST_USER PARENT TABLE
2,569 2,696 TEST_USER PARENT TABLE
2,697 2,824 TEST_USER PARENT TABLE
2,825 2,952 TEST_USER PARENT TABLE
2,953 3,080 TEST_USER PARENT TABLE
3,081 3,208 TEST_USER PARENT TABLE
3,209 3,336 TEST_USER PARENT TABLE
3,337 13,448 free free
13,449 13,576 TEST_USER GGP_PK INDEX
13,577 13,704 TEST_USER GP_PK INDEX
13,705 13,832 TEST_USER P_PK INDEX
13,833 15,112 free free
15,113 15,240 TEST_USER NUMTEST TABLE
15,241 32,648 free free
6 9 136 TEST_USER GENERATOR TABLE
137 8,072 free free
如你所见，我可以将文件6一直压缩到块136，将文件5压缩到块15240。如果我移动表numtest，它将（可能）进入file 5中从3337块开始的大间隙（尽管在最新版本的Oracle中，它可能会移动到文件6 – 我的测试仅在10.2.0.3上），并且我将能够将文件缩小到块13832。然而，如果我还重建了ggp_pk、gp_pk和p_pk三个索引，它们也会（可能）进入一个大的间隙，我将能够将文件5缩小到接近块4000的某个地方。我将分两步演示，首先移动表，然后移动索引；每次更改后，我将再次显示报告的最后几行。
FILE_ID BLOCK_ID END_BLOCK OWNER SEGMENT_NAME SEGMENT_TYPE
------- ----------- ----------- ---------- --------------- ------------------
5 2,185 2,312 TEST_USER PARENT TABLE
2,313 2,440 TEST_USER PARENT TABLE
2,441 2,568 TEST_USER PARENT TABLE
2,569 2,696 TEST_USER PARENT TABLE
2,697 2,824 TEST_USER PARENT TABLE
2,825 2,952 TEST_USER PARENT TABLE
2,953 3,080 TEST_USER PARENT TABLE
3,081 3,208 TEST_USER PARENT TABLE
3,209 3,336 TEST_USER PARENT TABLE
3,337 13,448 free free
13,449 13,576 TEST_USER GGP_PK INDEX
13,577 13,704 TEST_USER GP_PK INDEX
13,705 13,832 TEST_USER P_PK INDEX
13,833 32,648 free free
6 9 136 TEST_USER GENERATOR TABLE
137 264 TEST_USER NUMTEST TABLE
265 8,072 free free
在第一步之后，表numtest现在位于文件6中–我的会填充文件5中的间隙的预测是错误的；Oracle在一段时间前更改了段分配的代码，以确保您不会结束于表空间中每个段的第一个extent所在的第一个文件上–但这个更改一定比我记忆中出现得稍早。（如果我想确保我的预测是正确的，我可以在移动之前将文件6大小调整到最小值，或者，当然了，我可以回去重新调整我的预测。）
FILE_ID BLOCK_ID END_BLOCK OWNER SEGMENT_NAME SEGMENT_TYPE
------- ----------- ----------- ---------- --------------- -----------------
5 2,185 2,312 TEST_USER PARENT TABLE
2,313 2,440 TEST_USER PARENT TABLE
2,441 2,568 TEST_USER PARENT TABLE
2,569 2,696 TEST_USER PARENT TABLE
2,697 2,824 TEST_USER PARENT TABLE
2,825 2,952 TEST_USER PARENT TABLE
2,953 3,080 TEST_USER PARENT TABLE
3,081 3,208 TEST_USER PARENT TABLE
3,209 3,336 TEST_USER PARENT TABLE
3,337 32,648 free free
6 9 136 TEST_USER GENERATOR TABLE
137 264 TEST_USER NUMTEST TABLE
265 392 TEST_USER P_PK INDEX
393 520 TEST_USER GP_PK INDEX
521 648 TEST_USER GGP_PK INDEX
649 8,072 free free
当我重建索引p_pk、gp_pk和ggp_pk（按顺序）时，它们都移到了文件6，这使我能够显著地减小文件5的大小。顺便说一句，这种类型的移动和重建在本地管理表空间（LMT）中工作得很好，因为重新创建的对象往往会从文件的开始去使用空闲空间。字典管理表空间（DMT）就不是这样，它使用在字典缓存（vrowcache）中dc_free_extents下缓存的任何空闲区，然后释放在sys.fet查询中先出现的正确大小的空闲区。
现在剩下的就是调整数据文件的大小。这个表空间使用的是8KB的块大小，因此检查每个文件中最后使用的块时，我将乘以8192得到数据文件的新大小。下面是从我脚本的最后输出中剪切粘贴的，它显示文件末尾不再有任何可用块：
SQL> alter database datafile 'C:\ORACLE\ORADATA\D10G\TEST_8K_B.DBF' resize 5308416;
Database altered.
SQL> alter database datafile 'C:\ORACLE\ORADATA\D10G\TEST_8K.DBF' resize 27328512;
Database altered.
FILE_ID BLOCK_ID END_BLOCK OWNER SEGMENT_NAME SEGMENT_TYPE
------- ----------- ----------- ---------- --------------- ------------------
5 2,185 2,312 TEST_USER PARENT TABLE
2,313 2,440 TEST_USER PARENT TABLE
2,441 2,568 TEST_USER PARENT TABLE
2,569 2,696 TEST_USER PARENT TABLE
2,697 2,824 TEST_USER PARENT TABLE
2,825 2,952 TEST_USER PARENT TABLE
2,953 3,080 TEST_USER PARENT TABLE
3,081 3,208 TEST_USER PARENT TABLE
3,209 3,336 TEST_USER PARENT TABLE
6 9 136 TEST_USER GENERATOR TABLE
137 264 TEST_USER NUMTEST TABLE
265 392 TEST_USER P_PK INDEX
393 520 TEST_USER GP_PK INDEX
521 648 TEST_USER GGP_PK INDEX
要调查的是：如果使用dbms_space_admin.tablespace_migrate_To_local（）过程将字典管理的表空间（DMT）转换为本地管理的表空间（LMT），而Oracle必须在接近文件末尾处创建空间管理位图，该怎么办。有没有办法将映射视为一个区段（它会显示为“SPACE HEADER”类型的段吗？），dbms_SPACE_admin.tablespace_relocate_bitmaps（）会像说的那样工作吗？
脚注：
这些说明不适用于UNDO表空间或临时表空间，并且通常不能将该技术应用于系统表空间，也不应将该技术应用于sysaux表空间，因为必须移动的对象可能对数据库的操作至关重要。
2012年1月28日更新：
如果删除一个对象并且启用了recyclebin，则可能会得到一个矛盾的结果。该空间在报表中显示为可用空间（在dba_free_space中查），但在手动清除recyclebin之前，您将无法收缩数据文件以使用该“不完全可用”空间。要指出你遇到此问题的一个事实是，你可能会看到报告中的两行（或多行）连续显示“free”-recyclebin中每个段的每个extent都显示为单独的“free” extent。
以下附原文链接和内容：
https://jonathanlewis.wordpress.com/2010/02/06/shrink-tablespace/
Shrink Tablespace
Filed under: fragmentation,Infrastructure,Oracle — Jonathan Lewis @ 5:31 pm GMT Feb 6,2010
Here’s an example of a theme that appears on the OTN database forum from time to time (I haven’t included a link to it because it’s just one example of many similar questions):
“I have a user tablespace allocated for 3-4 schemas. As I urgently needed space on hard disk I had to remove one of the schema(drop user). Now this tablespace is shown as 70% filled. I want to reduce allocated space to it.”
The immediate answer is simple – alter database datafile ‘xxx’ resize NNN; – except there are two problems with this solution. First, how do you work out a suitable value for NNN, and secondly what do you do when every value you try for NNN gives you Oracle error: “ORA-03297: file contains used data beyond requested RESIZE value”.
The answer to both problems can be resolved through views dba_extents and dba_free_space. If you have some free space in a tablespace it can only be “reclaimed” if it is at the end of the data files (a point I made quite recently in a more generic comment on free space). dba_free_space will tell you where the space is, dba_extents will tell you what’s in the file after the free space. I’ve published a simple script at this URL to demonstrate the principle. The following text is a sample from the output, showing the last few rows reported for one file, and the rows reported for the second file of a small tablespace I created for demonstration purposes.
FILE_ID BLOCK_ID END_BLOCK OWNER SEGMENT_NAME SEGMENT_TYPE
------- ----------- ----------- ---------- --------------- ------------------
5 2,185 2,312 TEST_USER PARENT TABLE
2,313 2,440 TEST_USER PARENT TABLE
2,441 2,568 TEST_USER PARENT TABLE
2,569 2,696 TEST_USER PARENT TABLE
2,697 2,824 TEST_USER PARENT TABLE
2,825 2,952 TEST_USER PARENT TABLE
2,953 3,080 TEST_USER PARENT TABLE
3,081 3,208 TEST_USER PARENT TABLE
3,209 3,336 TEST_USER PARENT TABLE
3,337 13,448 free free
13,449 13,576 TEST_USER GGP_PK INDEX
13,577 13,704 TEST_USER GP_PK INDEX
13,705 13,832 TEST_USER P_PK INDEX
13,833 15,112 free free
15,113 15,240 TEST_USER NUMTEST TABLE
15,241 32,648 free free
6 9 136 TEST_USER GENERATOR TABLE
137 8,072 free free
As you can see, I could shrink file six all the way down to block 136, and file five down to block 15,240. If I move table numtest it will (probably) go into the large gap starting in file five at block 3,337 (though in the latest versions of Oracle it might move to file 6 – my test is only on 10.2.0.3) and I will be able to shrink the file down to block 13,832. However, if I also rebuild the three indexes ggp_pk, gp_pk and p_pk, they too will (probably) move into the big gap, and I will be able to shrink file five right down to somewhere near block 4,000. I’ll demonstrate in two steps, first moving the table and then moving the indexes; and after each change I’ll show just the last few lines of the report again.
FILE_ID BLOCK_ID END_BLOCK OWNER SEGMENT_NAME SEGMENT_TYPE
------- ----------- ----------- ---------- --------------- ------------------
5 2,185 2,312 TEST_USER PARENT TABLE
2,313 2,440 TEST_USER PARENT TABLE
2,441 2,568 TEST_USER PARENT TABLE
2,569 2,696 TEST_USER PARENT TABLE
2,697 2,824 TEST_USER PARENT TABLE
2,825 2,952 TEST_USER PARENT TABLE
2,953 3,080 TEST_USER PARENT TABLE
3,081 3,208 TEST_USER PARENT TABLE
3,209 3,336 TEST_USER PARENT TABLE
3,337 13,448 free free
13,449 13,576 TEST_USER GGP_PK INDEX
13,577 13,704 TEST_USER GP_PK INDEX
13,705 13,832 TEST_USER P_PK INDEX
13,833 32,648 free free
6 9 136 TEST_USER GENERATOR TABLE
137 264 TEST_USER NUMTEST TABLE
265 8,072 free free
After the first step, table numtest is now in file six – my prediction of filling the gap in file five was wrong; Oracle changed the code for segment allocation some time ago to make sure that you don’t end up with the first extent of every segment in the first file of a tablespace – but that change must have appeared a little earlier than I remembered. (If I had wanted to make sure my prediction was correct I could have resized file six to its minimum before I did the move – or I could have gone back and retro-fitted my prediction, of course.)
FILE_ID BLOCK_ID END_BLOCK OWNER SEGMENT_NAME SEGMENT_TYPE
------- ----------- ----------- ---------- --------------- -----------------
5 2,185 2,312 TEST_USER PARENT TABLE
2,313 2,440 TEST_USER PARENT TABLE
2,441 2,568 TEST_USER PARENT TABLE
2,569 2,696 TEST_USER PARENT TABLE
2,697 2,824 TEST_USER PARENT TABLE
2,825 2,952 TEST_USER PARENT TABLE
2,953 3,080 TEST_USER PARENT TABLE
3,081 3,208 TEST_USER PARENT TABLE
3,209 3,336 TEST_USER PARENT TABLE
3,337 32,648 free free
6 9 136 TEST_USER GENERATOR TABLE
137 264 TEST_USER NUMTEST TABLE
265 392 TEST_USER P_PK INDEX
393 520 TEST_USER GP_PK INDEX
521 648 TEST_USER GGP_PK INDEX
649 8,072 free free
When I rebuilt the indexes p_pk, gp_pk and ggp_pk (in that order) they all moved to file six, allowing me to reduce the size of file five dramatically. In passing, this type of moving and rebuilding tends to work very well in locally managed tablespaces (LMTs) because an object that is recreated tends to use up the empty space from the start of the file. The same is not true of dictionary managed tablespaces (DMTs) which use up whichever free extents happen to be cached in the dictionary cache (v.
All that remains now is to resize the data files. This tablespace is using an 8KB block size, so checking the last used block in each file I’ll multiply by 8192 to get the new size of the data files. Here’s a quick cut-n-paste followed by one last listing from my script to show that there are no longer any free blocks at the end of the files:
SQL> alter database datafile 'C:\ORACLE\ORADATA\D10G\TEST_8K_B.DBF' resize 5308416;
Database altered.
SQL> alter database datafile 'C:\ORACLE\ORADATA\D10G\TEST_8K.DBF' resize 27328512;
Database altered.
FILE_ID BLOCK_ID END_BLOCK OWNER SEGMENT_NAME SEGMENT_TYPE
------- ----------- ----------- ---------- --------------- ------------------
5 2,185 2,312 TEST_USER PARENT TABLE
2,313 2,440 TEST_USER PARENT TABLE
2,441 2,568 TEST_USER PARENT TABLE
2,569 2,696 TEST_USER PARENT TABLE
2,697 2,824 TEST_USER PARENT TABLE
2,825 2,952 TEST_USER PARENT TABLE
2,953 3,080 TEST_USER PARENT TABLE
3,081 3,208 TEST_USER PARENT TABLE
3,209 3,336 TEST_USER PARENT TABLE
6 9 136 TEST_USER GENERATOR TABLE
137 264 TEST_USER NUMTEST TABLE
265 392 TEST_USER P_PK INDEX
393 520 TEST_USER GP_PK INDEX
521 648 TEST_USER GGP_PK INDEX
To be investigated: what if you’ve use the dbms_space_admin.tablespace_migrate_to_local() procedure to convert a dictionary manged tablespace (DMT) to a locally managed tablespace (LMT) and Oracle has had to create the space management bitmap near the end of the file. Is there a way to see the map as an extent (would it show up as segment of type “SPACE HEADER”), and does dbms_space_admin.tablespace_relocate_bitmaps() work as advertised ?
Footnote:
These notes don’t apply to the undo,or temporary tablespaces, and generally you can’t apply the technique to the system tablespace and shouldn’t apply the technique to the sysaux tablespace because the objects you have to move are likely to be critical to the operation of the database.
Update 28th Jan 2012:
If you drop an object and the recyclebin is enabled then you can get a contradictory result. The space appears as free space (in dba_free_space) in the report, but you won’t be able to shrink the data file to use that “not quite free” space until you purge the recyclebin manually. A pointer to the fact that you’re hitting this issue is that you may see two (or more) consecutive lines of the report showing “free” – each extent of each segment in the recyclebin shows up as a separate “free” extent.
