怎样查看oracle表空间还剩多少空间?
解决方案 »
- oracle 调用远程存储过程 并写入数据?DB LINK可以实现吗?可以调用远程数据库存储过程并且向远程数据库写入数据吗?麻烦贴下sql脚本demo解释下、谢
- oracle distinct 字段的问题求解
- oracle中有无'在1个字符串中查找非数字'的函数
- 查询效率慢
- oracle 中number 类型对应pgsql 中什么类型
- 请问这个错误如何解决?ORA-01406:读取的列值被截断
- 请问如何删除 控制面板->管理工具->服务中的那些项啊?
- 有什么好的办法能够把一个用户及数据从一个表空间移动到另一个表空间?
- 有一个数据库,其中有2个表到达300万,这样的数据库服务器(软硬)怎么配置最好??
- 关天developer 2000
- 求助,什么叫迫切左外连接,迫切内连接。另,oracle中sql语句默认的连接方式。
- 求CNOUG论坛(Oracle.com.cn)邀请码一个,谢谢!
SELECT DBName.name,
sysdate,
Total.name "Tablespace Name",
num_db_files,
Free_space,
((100 * Free_space) / total_space) "%Free",
(total_space - Free_space) Used_space,
((100 * (total_space - free_space)) / total_space) "%Used",
total_space
FROM (select tablespace_name, sum(bytes / 1024) Free_Space
from sys.dba_free_space
group by tablespace_name) Free,
(select b.name, count(*) num_db_files, sum(bytes / 1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name) Total,
(select name from sys.v_$database) DBName
WHERE Free.Tablespace_name = Total.name
and DBName.name = DBName.name;
SELECT c.tablespace_name tablespace_name, ROUND(a.bytes/1048576,2) total_size,ROUND((a.bytes-b.bytes)/1048576,2) used_size,
ROUND(b.bytes/1048576,2) free_size,
ROUND(b.bytes/a.bytes * 100,2)||'%' free_pct FROM
(SELECT tablespace_name,SUM(a.bytes) bytes
FROM sys.DBA_DATA_FILES a
GROUP BY tablespace_name) a,
(SELECT a.tablespace_name,
NVL(SUM(b.bytes),0) bytes
FROM sys.DBA_DATA_FILES a,
sys.DBA_FREE_SPACE b
WHERE a.tablespace_name = b.tablespace_name (+)
AND a.file_id = b.file_id (+)
GROUP BY a.tablespace_name) b,
sys.DBA_TABLESPACES c
WHERE a.tablespace_name = b.tablespace_name(+)
AND a.tablespace_name = c.tablespace_name
ORDER BY ROUND(b.bytes/1048576,2);
SELECT c.tablespace_name tablespace_name, ROUND(a.bytes/1048576,2) total_size,ROUND((a.bytes-b.bytes)/1048576,2) used_size,
ROUND(b.bytes/1048576,2) free_size,
ROUND(b.bytes/a.bytes * 100,2)||'%' free_pct FROM
(SELECT tablespace_name,SUM(a.bytes) bytes
FROM sys.DBA_DATA_FILES a
GROUP BY tablespace_name) a,
(SELECT a.tablespace_name,
NVL(SUM(b.bytes),0) bytes
FROM sys.DBA_DATA_FILES a,
sys.DBA_FREE_SPACE b
WHERE a.tablespace_name = b.tablespace_name (+)
AND a.file_id = b.file_id (+)
GROUP BY a.tablespace_name) b,
sys.DBA_TABLESPACES c
WHERE a.tablespace_name = b.tablespace_name(+)
AND a.tablespace_name = c.tablespace_name
ORDER BY ROUND(b.bytes/1048576,2);
[/code]