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

tolywang發表於2008-02-20

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 了,下面稍微寫了一下步驟

[@more@]

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

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/35489/viewspace-999742/,如需轉載,請註明出處,否則將追究法律責任。

相關文章