ITPub博客

首页 > Linux操作系统 > Linux操作系统 > How to release space from database( in other words: resize datafile ) (zt)

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

原创 Linux操作系统 作者:tolywang 时间:2006-10-05 00:00:00 0 删除 编辑

http://rollingpig.itpub.net/post/81/47356

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/35489/viewspace-84454/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle , MySQL, SAP IQ, SAP HANA, PostgreSQL, Tableau 技术讨论,希望在这里一起分享知识,讨论技术,畅谈人生 。

注册时间:2007-12-10

  • 博文量
    5595
  • 访问量
    13383885