How to release space from database

这篇文章比较详细，包括回收到HWM，以及如何降低HWM后再继续回收。

How to release space from database
(Step 3-5 only applies to development/testing DB or in an offline production DB, because move table would destroy index.)

当然就是resize datafile 了，下面稍微写了一下步骤

How to release space from database
(Step 3-5 only applies to development/testing DB or in an offline production DB, because move table would destroy index.)

1. Find which tablespace have much free space.

Select sum(bytes)/1024/1024 , tablespace_name from dba_free_space

Group by tablespace_name order by 1 desc

SUM(BYTES)/1024/1024 TABLESPACE_NAME

721.0625 USERS

327.25 UNDOTBS1

2. For the tablespace that has much free space, try to resize it to the highest blocks.

SELECT 'alter database datafile '||d.file_id ||' resize '||

NVL(ROUND(MAX(e.block_id+e.blocks -1)* t.block_size /1024/1024+1),11)||'M;'

FROM dba_extents e, dba_data_files d , dba_tablespaces t

WHERE d.tablespace_name = t. tablespace_name

AND d.file_id = e.file_id(+)

AND t. tablespace_name = :tbsname

AND e. tablespace_name(+) = :tbsname

GROUP BY d.file_id , t.block_size

SCRIPT

alter database datafile 5 resize 237M;

alter database datafile 6 resize 41M;

3. Check if there is still much free space. If not, continue to the next tablespace, Else, continue to the next step.

4. Find the free space of the tablespace.

SELECT file_id , block_id, block_id+blocks , blocks

FROM dba_free_space

WHERE TABLEspace_name = :tbsname

ORDER BY 1, 2 DESC

FILE_ID
First_block
Last_block
Total Block

5
30217
30337
120

5
28937
29833
896

5
28553
28681
128

5
28185
28297
112

5
28129
28169
40

5
28081
28121
40

5
13129
28041
14912

5
7601
13097
5496

5
2657
7585
4928

5
2585
2625
40

5
137
2569
2432

6
5105
5249
144

6
5081
5097
16

6
5001
5073
72

6
4745
4849
104

6
3625
4705
1080

6
97
3593
3496

5. For each file, try to move the segment down the big free chunk.

a. Move tables above the point

b. Rebuild affected indexes

c. Rebuild indexes above the point

a. Move tables above the point

Select distinct ‘alter table ‘||owner||’.’||segment_name||’ move ;’

From dba_extents

Where file_id = :file_id

And segment_type = ‘TABLE’

And tablespace_name = :tbs_name

And block_id > :block_id --(for file_id 6, should be 3593)

b. Rebuild affected indexes

Select ‘alter index ‘||owner||’.’||index_name||’ rebuild;’

From all_indexes

where status = ‘UNUSED’

c. Rebuild indexes above the point

Select distinct ‘alter table ‘||owner||’.’||segment_name||’ move ;’

From dba_extents

Where file_id = :file_id

And segment_type = ‘INDEX’

And tablespace_name = :tbs_name

And block_id > :block_id --(for file_id 6, should be 3593)

6. Continue step 2