How to release space from database( in other words: resize datafile ) (zt)

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

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