怎么回收合并表空间的空余空间？

oldwain

最初由 kerlion 发布
[B]没有什么关系，只是感觉不爽，而且想讨论讨论有没有可行的办法。

有一个，可能比较麻烦。
export full database

recreate a new database with same structure with old

import full datadabase

I have not enough time, who can try it??! [/B]

要重整system 表空间, 可能只有这唯一一个办法.

kerlion

re:stevenzhaoyi
thank you, I find a new job, but I dont satisty with it ,especial the work place.

I tried to move tables to itself tablespace, but is seems to produce more fragments.

kerlion

[php]
set serveroutput on size 20000
set linesize 80

declare
  cursor c_map(p_file_id number) is
  (select file_id,block_id,blocks,'X' used
   from dba_extents where file_id=p_file_id
   union
   select file_id,block_id,blocks,'=' used
   from dba_free_space where file_id=p_file_id
   ) order by 1,2,3;
cursor c_file is
select file_name,file_id,blocks/400  bsize from dba_data_files;
j number :=1;
xsize number;
begin
   for r_file in c_file
   loop
       dbms_output.put_line('Map of '||r_file.file_name);
       for r_map in c_map(r_file.file_id)
       loop
           for i  in 1..r_map.blocks/r_file.bsize
           loop
            dbms_output.put(R_MAP.USED);
            if j>=80 then
        j :=1;dbms_output.new_line;
            else
               j := j+1;
            end if;
           end loop;
       end loop;
       dbms_output.new_line;j:=1;
   end loop;
end;
/
[/php]
我的数据库显示为这样：
,X表示使用，=表示空域：

Map of
/usr/oracle/oradata/PROD/system01.dbf
========================X=========XX===X
=====X====XXXXXXXXXXXXXXXXXXX===========
========================================
=============================X==========
========================================
==========XXX================X==========
=====================
Map of
/usr/oracle/oradata/PROD/tools01.dbf
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXX==========
Map of
/usr/oracle/oradata/PROD/rbs01.dbf
X===XXXXXXXXX=XXX=======X=======X====XX=
X===XXXXX=======X=======XX====XXX=======
X=====XXX==XXXXXXXXXXXXXX===XXXXXXXXXXXX
X=======XXXXXXXXX===XXXXXXXXXXXXX=======
XXXX====XX======XXX====XXXXXXXXXXX======
========X===XXX==XXXX==XXXXX=XXXXXXXX==X
===============XXXXXXXXXXX=XXX==========
=XX=XXXX=XXXXX=XXXX======XXX===========X
XXXXXXXXXXX=====XXXXXXXXXXXXXXXXXXXXXXXX
XXXX====X=XXX=========XX=====XX
Map of
/usr/oracle/oradata/PROD/temp01.dbf
========================================
========================================
========================================
========================================
========================================
========================================
========================================
========================================
========================================
======
Map of
/usr/oracle/oradata/PROD/users01.dbf
========================================
========================================
========================================
========================================
========================================
========================================
========================================
========================================
========================================
==================================

kerlion

XXXXXXXXXXXXXXXXXX==X==XXXXXXXXXXXXXX=XX
XXXX=XXXXXXXXXXXXXXXX=XXXXXXXXXXXXXXXXXX
XXXXXXXX===XXXXXXXXXXXXXX===XXXXXXXXXXXX
XXXXX===XXXXXXXX===XXXXXXXXXXXX===XXX===
XXXXXXXXXXXXXXXXXXXXXXX=========XXXXXXXX
X===XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX===XX
XXXXXX===XXXXXX===XXXXXXXXX===XXXXXXXXXX
XXX===XXX===XXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXX===XXXXXX===XXXXX
XXXXXXX====XXX===XXXXXXXX===XXXXXXX===XX
XXXXXXXX===XXXX===XXXXXXXXXXXXX===XXXXXX
XXXXXXXXXXXXXXX=========XXXXXXXXXXXX===X
XXXXXXXXXXXXXXXXXXXXXXXX===XXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXX===XXXXXX===XXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXX===XXXXXXXXXXXXXX===XXX===
XXXX===XXXXXXXXXXXXXXX===XXXX===XXXXXXXX
XXXXX===XXXXXXXXXXXX==X===XXXXXXXXXXXXXX
XXXXXXX===XXXXXXXXXXXXXXXXXXXXXXXXX=====
=XXXXXXXX===XXXXXXXXXXXXXXXXXXXXXXXXX===
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXX==XXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX=X===
========================================
========================================
=====X=XXXXXXXXXXXXXXX==X===============
========================================
========================================
======XXXXXXXXXXXXXXXXXXX==========XXX==
====X===================================
========================================
===============XXX============XXX=======
=====================================XXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXX============X
XXX====XXXXXXXXXXX===XX==XXXXXXXXXXXXXX=
=====XXXXXXXXXXXXXXXXXXXXX==========X===
=XXX==========XXXXXXXXXXXXXXXXXXXXXXXXXX
==XXX====XX==XXX==XXXXXXXX==============
=========================XXX============
=======XXXXXXXXXXXXXX===X==============X
XXX=========XXXXXXX======X==========XXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX====X=
=========XXXXX====XX====XXXXXXX=X==X=XXX
XXXXXXXXX====X====X==XX=XXXXX=XXXX======
===XXXXXXX====XX======XXXXXXXX====XXXX==
==XXXXXXXXXXXXXXXXX=====================
==XXXXXXXXXXXXXXXXXXXXXXXXXXXX==========
======================XXXXXXX===========
=====XXX=XXXXXXXXXXXX=======XXX=======XX
XXXXX============XX===XXXXXX======XX====
============XXXXXXXXXX==================
XXXXXX========================XXX=======
=======XXXXXXXXXXXXXX=============XXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX==========
=====================================XXX
========================================
=======================================X
XXXXXXXXXX===XXXXXXXX===X===============
========================================
========================================
========================================
========================================
========================================
========================================
========================================
========================================
========================================
========================================
========================================
========================================
========================================
========================================
========================================
========================================
========================================
========================================
========================================
========================================
========================================
========================================
========================================
======================================X=
============XXXXXXXXXXXXX======X========
========================================
========================================
========================================
==================XXX==========XXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX=====X
XXXXXXXXXXX=============================
====XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
X=======================================
======================================XX
XXX========================X============
========================================
==========XXX=======X==========X========
=======================================X
========================================
========================================
========================================
========================================
========================================
===================X====================
========================================
=======================================X
=====================XXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXX================================
========================================
========================================
========================================
========================================
========================================
========================================
========================================
===================================XXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX====
========================================
======XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXX=================
==================XXXXX=======XXXXXXXXX=
=========================XXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXX===XXXXXXXX=XXXXXXXX===XX=====
========================================
========================================
========================================
=========================XXX====XXXXXXXX
XXXXXXXX==XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXX=================X======
========X===============================
========================================
========================================
========================================
==========XXXXX=========================
============X===========================
========================================
========================================
========================================
================================XXX=====
========================================
========================================Courier New[/FONT] 10[/SIZE]

oldwain

非常棒的一段script!

帮你格式化一下. 好让大家都学习学习.

加入精华区.