大家好!
我们公司有台LINUX的服务器,它上面装了oracle,我知道oracle的管理员帐号,请问我怎么通过命令行的形式查看表空间的大小呢?
我们公司有台LINUX的服务器,它上面装了oracle,我知道oracle的管理员帐号,请问我怎么通过命令行的形式查看表空间的大小呢?
解决方案 »
- oracle 传值的 sql语句怎么写?????
- 请教DBMS_METADATA.GET_DDL报错 error stack
- 初学者,请教一个oracle问题
- oracle通过透明网关访问sql2005数据库查询没有结果
- 两个select怎么合并?
- 求教一条SQL语句
- oracle10g客户端如何才能连上oracle8i呢?
- sos:delphi7调用oralce9i存储过程,storedproc控件提示:operation not applicable?
- ems不能够启动!!
- 有没有日期函数是来计算两个日起相差几个月的?
- 在VB中怎么运用DTS包导出Oracle中内容生成txt文件?
- 向大家求解,来者有分
结合 v$tablespace可以看表空间的文件结构和大小
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;
col ts_name form a20 head 'Tablespace'
col pieces form 9990 head 'Pcs'
col ts_size form 999,990 head 'SizeMb'
col largestpc form 999,990 head 'LrgMB'
col totalfree form 999,990 head 'FreeMb'
col pct_free form 990 head '%Free'
col whatsused form 999,990 head 'Used'
col pct_used form 990 head '%Used'
col problem head 'Prob??'
--spool /tmp/tbs_size.logselect q2.other_tname ts_name, pieces, ts_size ts_size,
nvl(largest_chunk,0) largestpc, nvl(total_free,0) totalfree,
nvl(round((total_free/ts_size)*100,2),0) pct_free,
ts_size-total_free whatsused,
nvl(100-round((total_free/ts_size)*100,2),100) pct_used,
decode(nvl(100-round((total_free/ts_size)*100,0),100),
85,'+',86,'+',87,'+',88,'+',89,'++',90,'++',91,'++',
92,'++',93,'++',94,'+++',95,'+++',96,'+++',97,'++++',
98,'+++++',99,'+++++',100,'+++++','') problem
from (select dfs.tablespace_name,count(*) pieces,
round(max(dfs.bytes)/1024/1024,2) largest_chunk,
round(sum(dfs.bytes)/1024/1024,2) total_free
from dba_free_space dfs group by tablespace_name) q1,
(select tablespace_name other_tname,
round(sum(ddf2.bytes)/1024/1024,2) ts_size
from dba_data_files ddf2 group by tablespace_name) q2
where q2.other_tname = q1.tablespace_name(+)
order by nvl(100-round((total_free/ts_size)*100,0),100) desc;
SELECT a.tablespace_name "表空间名",total 表空间大小,free 表空间剩余大小,
(total-free) 表空间使用大小,
ROUND((total-free)/total,4)*100 "使用率 %"
FROM (SELECT tablespace_name,SUM(bytes) free FROM DBA_FREE_SPACE
GROUP BY tablespace_name ) a,
(SELECT tablespace_name,SUM(bytes) total FROM DBA_DATA_FILES
GROUP BY tablespace_name) b
WHERE a.tablespace_name=b.tablespace_name