因为从dba_free_space 的视图创建过程看以知道,与回收站recyclebin有关,因为回收站保存的东西都是删除的东西,而dba_free_space视图,包括了回收站的对象。 楼主可以查看dba_free_space 视图的源码:create or replace view sys.dba_free_space as select ts.name, fi.file#, f.block#, f.length * ts.blocksize, f.length, f.file# from sys.ts$ ts, sys.fet$ f, sys.file$ fi where ts.ts# = f.ts# and f.ts# = fi.ts# and f.file# = fi.relfile# and ts.bitmapped = 0 union all select /*+ ordered use_nl(f) use_nl(fi) */ ts.name, fi.file#, f.ktfbfebno, f.ktfbfeblks * ts.blocksize, f.ktfbfeblks, f.ktfbfefno from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi where ts.ts# = f.ktfbfetsn and f.ktfbfetsn = fi.ts# and f.ktfbfefno = fi.relfile# and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0 union all select /*+ ordered use_nl(u) use_nl(fi) */ ts.name, fi.file#, u.ktfbuebno, u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefno from sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi where ts.ts# = rb.ts# and rb.ts# = fi.ts# and u.ktfbuefno = fi.relfile# and u.ktfbuesegtsn = rb.ts# and u.ktfbuesegfno = rb.file# and u.ktfbuesegbno = rb.block# and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0 union all select ts.name, fi.file#, u.block#, u.length * ts.blocksize, u.length, u.file# from sys.ts$ ts, sys.uet$ u, sys.file$ fi, sys.recyclebin$ rb where ts.ts# = u.ts# and u.ts# = fi.ts# and u.segfile# = fi.relfile# and u.ts# = rb.ts# and u.segfile# = rb.file# and u.segblock# = rb.block# and ts.bitmapped = 0; comment on column sys.DBA_FREE_SPACE.TABLESPACE_NAME is 'Name of the tablespace containing the extent'; comment on column sys.DBA_FREE_SPACE.FILE_ID is 'ID number of the file containing the extent'; comment on column sys.DBA_FREE_SPACE.BLOCK_ID is 'Starting block number of the extent'; comment on column sys.DBA_FREE_SPACE.BYTES is 'Size of the extent in bytes'; comment on column sys.DBA_FREE_SPACE.BLOCKS is 'Size of the extent in ORACLE blocks'; comment on column sys.DBA_FREE_SPACE.RELATIVE_FNO is 'Relative number of the file containing the extent'; 根据DBA_FREE_SPACE中的file_id和relative_fno可知道 如果两者不一致,说明回收站有很多对象,可用如下查询方式:select count(*) from dba_free_space where file_id<>RELATIVE_FNO; 这时,可以使用sys用户清空回收站 purge dba_recyclebin; 再检查是否DBA_FREE_SPACE视图中还有那么多记录。
我知道针对某些表清理碎片的方法。
用“shrink”可以清理表碎片,对表进行收缩,但这个命令是10G的命令,低版本的好象用不了。
命令使用:
alter table tb_manual shrink space;
前提是
1.该表必须启动row movement
alter table table_auto enable row movement;
2.该表表空间所在的段的管理方式是auto,而不是manualcreate tablespace ts_auto1 datafile '/d01/ts_auto1.dbf' size 100m
extent management local segment space management auto;
怎么去确定是 row migration造成的,还是chaining造成的呢?谢谢您的帮助,非常有用。
所以认为这些空闲的表空间可能是由于碎片造成的。没有其它的分析依据
楼主可以查看dba_free_space 视图的源码:create or replace view sys.dba_free_space as
select ts.name, fi.file#, f.block#,
f.length * ts.blocksize, f.length, f.file#
from sys.ts$ ts, sys.fet$ f, sys.file$ fi
where ts.ts# = f.ts#
and f.ts# = fi.ts#
and f.file# = fi.relfile#
and ts.bitmapped = 0
union all
select /*+ ordered use_nl(f) use_nl(fi) */
ts.name, fi.file#, f.ktfbfebno,
f.ktfbfeblks * ts.blocksize, f.ktfbfeblks, f.ktfbfefno
from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi
where ts.ts# = f.ktfbfetsn
and f.ktfbfetsn = fi.ts#
and f.ktfbfefno = fi.relfile#
and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
union all
select /*+ ordered use_nl(u) use_nl(fi) */
ts.name, fi.file#, u.ktfbuebno,
u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefno
from sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi
where ts.ts# = rb.ts#
and rb.ts# = fi.ts#
and u.ktfbuefno = fi.relfile#
and u.ktfbuesegtsn = rb.ts#
and u.ktfbuesegfno = rb.file#
and u.ktfbuesegbno = rb.block#
and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
union all
select ts.name, fi.file#, u.block#,
u.length * ts.blocksize, u.length, u.file#
from sys.ts$ ts, sys.uet$ u, sys.file$ fi, sys.recyclebin$ rb
where ts.ts# = u.ts#
and u.ts# = fi.ts#
and u.segfile# = fi.relfile#
and u.ts# = rb.ts#
and u.segfile# = rb.file#
and u.segblock# = rb.block#
and ts.bitmapped = 0;
comment on column sys.DBA_FREE_SPACE.TABLESPACE_NAME is 'Name of the tablespace containing the extent';
comment on column sys.DBA_FREE_SPACE.FILE_ID is 'ID number of the file containing the extent';
comment on column sys.DBA_FREE_SPACE.BLOCK_ID is 'Starting block number of the extent';
comment on column sys.DBA_FREE_SPACE.BYTES is 'Size of the extent in bytes';
comment on column sys.DBA_FREE_SPACE.BLOCKS is 'Size of the extent in ORACLE blocks';
comment on column sys.DBA_FREE_SPACE.RELATIVE_FNO is 'Relative number of the file containing the extent';
根据DBA_FREE_SPACE中的file_id和relative_fno可知道
如果两者不一致,说明回收站有很多对象,可用如下查询方式:select count(*) from dba_free_space where file_id<>RELATIVE_FNO;
这时,可以使用sys用户清空回收站
purge dba_recyclebin;
再检查是否DBA_FREE_SPACE视图中还有那么多记录。