我有两台服务器,分别装了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
两台的数据文件如下:
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
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
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;