我有两台服务器,分别装了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
解决方案 »
- 通过服务命连接oracle数据库,在配置网络服务名时点击测试一直没有反应
- oracle text 创建索引报 ora:04030错误!
- 求定时将ORACLE中一表数据导入另一表的存储过程
- ORACLE ora-03113 如何解决。(已经不能用sqlplus /nolog conn / as sysdba 登录了)
- 一字段存的是多个类别的ID的字符串,转化成Name字符串,在线等
- 求oracle 9i教程
- 請問如何建立方案??
- 请教:在存储过程中使用动态Sql语句的方法,up有分
- 如何读取导入的数据表名?
- like %%在PL/SQL怎么用?特别是like一个变量的时候
- Oracle 数据替换 怎么实现?
- 同为oracle 10G的字符集为Simplified Chinese_china zhs16gbk的库如何移殖入字符集为utf8的库中
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;