How to release space from database

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