请问我的oracle表空间怎么就一下子满了？

[verysorrow]

我有两台服务器，分别装了oracle，版本完全相同，主要数据都在其中一个tablespace上，数据量相当，其中一台（假设为A）tablespace是10G，显示目前只用了76％；另外一台（假设为B）15G，居然满了，用了99%了。
两台的数据文件如下：
A的四个数据文件如下：
SQL> select bytes,blocks,status,relative_fno,a.autoextensible,a.maxbytes,a.maxblocks,a.increment_by,a.user_bytes,a.user_blocks,a.online_status from dba_data_files a where a.tablespace_name='HLMIS';

BYTES BLOCKS STATUS RELATIVE_FNO AUTOEXTENSIBLE MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS
---------- ---------- --------- ------------ -------------- ---------- ---------- ------------ ---------- ----------- -------------
1073741824 131072 AVAILABLE 6 NO 0 0 0 1073676288 131064 ONLINE
1073741824 131072 AVAILABLE 7 NO 0 0 0 1073676288 131064 ONLINE
2147483648 262144 AVAILABLE 8 NO 0 0 0 2147418112 262136 ONLINE
6475481088 790464 AVAILABLE 10 YES 3435972198 4194302 1 6475415552 790456 ONLINE
B的5个数据文件如下：
SQL> select bytes,blocks,status,relative_fno,a.autoextensible,a.maxbytes,a.maxblocks,a.increment_by,a.user_bytes,a.user_blocks,a.online_status from dba_data_files a where a.tablespace_name='HLMIS';

BYTES BLOCKS STATUS RELATIVE_FNO AUTOEXTENSIBLE MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS
---------- ---------- --------- ------------ -------------- ---------- ---------- ------------ ---------- ----------- -------------
3221225472 393216 AVAILABLE 5 NO 0 0 0 3221159936 393208 ONLINE
3221225472 393216 AVAILABLE 6 NO 0 0 0 3221159936 393208 ONLINE
3221225472 393216 AVAILABLE 7 NO 0 0 0 3221159936 393208 ONLINE
3221225472 393216 AVAILABLE 8 NO 0 0 0 3221159936 393208 ONLINE
3221225472 393216 AVAILABLE 9 NO 0 0 0 3221159936 393208 ONLINE

[liuyi8903]

最好是看看哪些segments增长的比较快了。

[pobaby]

查查

查看当前用户每个表占用空间的大小：
    Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name

查看每个表空间占用空间的大小：
    Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group By Tablespace_Name

[yuzhenhuan01]

查查是哪个表占用的空间
针对表数据再找原因

[yuzhenhuan01]

是否发生大量行迁移，行链接之类的

[vs_net]

查看一下pctfree

[Dave]

是否有删除大量的数据？

把相关的表shrink 一下看看

Oracle 10g Shrink Table 详解
http://blog.csdn.net/tianlesoftware/archive/2009/11/03/4764254.aspx



------------------------------------------------------------------------------
Blog： http://blog.csdn.net/tianlesoftware
网上资源： http://tianlesoftware.download.csdn.net
相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
Q Q 群：62697716

[碧水幽幽泉]

查查表空间大小:
select a.tablespace_name "表空间名",
total "表空间大小(m)",
total - free "已使用空间(m)",
to_char(round((total - free) / total * 100, 2),'990.99') "使用比(%)",
free "空闲空间(m)",
max_bytes "最大块(m)"
from (select tablespace_name,
round(sum(bytes) / (1024 * 1024), 2) free,
round(max(bytes) / (1024 * 1024), 2) max_bytes
from dba_free_space
group by tablespace_name) a,
(select tablespace_name,
round(sum(bytes) / (1024 * 1024), 2) total
from dba_data_files
group by tablespace_name) b
where b.tablespace_name = a.tablespace_name
order by 4 desc;