获取表空间输出
获取表空间输出
提问于 2017-02-16 02:22:19
我们的oracle Enterprise manager数据库版本为12.1.0.2。在下面的查询中，我试图获得所有的表空间总大小、used_size、free_size。这个查询给了我一个错误。通过这个查询，我想显示以下内容: 1)目标名称、表空间名称、total_space、free_space、used_space ( GB )。2)我想要不带逗号的列别名写文件afiedt.buf
1 select * from
2 ( select target_name,KEY_VALUE NAME
3 ,decode(column_label,'Tablespace Allocated Space (MB)' ,total_space
4 ,'Tablespace Free Space (MB)',free_space,'Tablespace Us
ed Space (MB)',used_space,column_label) as column_label
5 ,value
6 from sysman.mgmt$metric_current
7 where COLUMN_LABEL IN('Tablespace Allocated Space (MB)','Tablespace Used
Space (MB)','Tablespace Free Space (MB)')
8 and target_type = 'rac_database'
9 )
10 PIVOT(
11 MAX(VALUE)
12* FOR COLUMN_LABEL IN( 'total_space ','used_space','free_space'))
SQL> /
,'Tablespace Free Space (MB)',free_space,'Tablespace Used Sp
ace (MB)',used_space,column_label) as column_label
*
ERROR at line 4:
ORA-00904: "USED_SPACE": invalid identifier
SQL>回答 2
Stack Overflow用户
发布于 2017-02-16 02:29:08
我认为你想要：
select * from
( select target_name,KEY_VALUE NAME
,decode(column_label,'Tablespace Allocated Space (MB)' ,'total_space'
,'Tablespace Free Space (MB)','free_space'
,'Tablespace Used Space (MB)','used_space'
,column_label) as column_label
,value
from sysman.mgmt$metric_current
where COLUMN_LABEL IN('Tablespace Allocated Space (MB)','Tablespace Used Space (MB)','Tablespace Free Space (MB)')
and target_type = 'rac_database'
)
PIVOT(
MAX(VALUE)
FOR COLUMN_LABEL IN( 'total_space','used_space','free_space'))尽管在我的数据库(11g)上，我没有看到标签"Tablespace Free Space (MB)“
注意，如果您想要为表空间提供空闲空间，可以使用dba_free_space：
select tablespace_name, sum(bytes)/1024/1024 as mb_free
from dba_free_space
group by tablespace_name;Stack Overflow用户
发布于 2017-02-16 02:41:45
我认为你可以比@tbone的答案更简单：
select *
from (
select column_label, key_value as tablespace_name, value
from sysman.mgmt$metric_current
where column_label in ('Tablespace Allocated Space (MB)',
'Tablespace Used Space (MB)', 'Tablespace Free Space (MB)')
and target_type = 'rac_database'
)
pivot (
max(value)
for column_label in ('Tablespace Allocated Space (MB)' as total_space,
'Tablespace Used Space (MB)' as used_space, 'Tablespace Free Space (MB)' as free_space)
)
/它(在11gR2中)得到如下输出：
TABLESPACE_NAME TOTAL_SPACE USED_SPACE FREE_SPACE
------------------------------ -------------------- -------------------- --------------------
SYSAUX 1770 1630.25
SYSTEM 730 727.125
TEMP 2009 0
UNDOTBS1 3469 20.5
USERS 832.25 54.0625
...在这个版本中，mgmt$metric_current表没有空闲空间的条目；我目前不能检查是否在12c中添加了空闲空间，但当然可以从其他两个中计算出来。
如果您想要以GB而不是MD为单位的值，只需在子查询中除以1024，也许可以四舍五入到合理的位数：
select *
from (
select column_label, key_value as tablespace_name,
round(to_number(value)/1024, 2) as value
from sysman.mgmt$metric_current
where column_label in ('Tablespace Allocated Space (MB)',
'Tablespace Used Space (MB)', 'Tablespace Free Space (MB)')
and target_type = 'rac_database'
)
pivot (
max(value)
for column_label in ('Tablespace Allocated Space (MB)' as total_space,
'Tablespace Used Space (MB)' as used_space,
'Tablespace Free Space (MB)' as free_space)
)
/
TABLESPACE_NAME TOTAL_SPACE USED_SPACE FREE_SPACE
------------------------------ -------------------- -------------------- --------------------
UNDOTBS1 3.39 .02
TEMP 1.96 0
SYSAUX 1.73 1.59
USERS .81 .05
SYSTEM .71 .71
...正如@tbone还建议的那样，还有其他视图可以给你提供这些信息，但至少在我的数据库中，这要慢得多：
select dfs.tablespace_name,
(select round(sum(ddf.bytes)/power(1024, 3), 2) from dba_data_files ddf
where ddf.tablespace_name = dfs.tablespace_name) as total_space,
(select round(sum(ds.bytes)/power(1024, 3), 2) from dba_segments ds
where ds.tablespace_name = dfs.tablespace_name) as used_space,
round(sum(dfs.bytes)/power(1024, 3), 2) as free_space
from dba_free_space dfs
group by dfs.tablespace_name
/
TABLESPACE_NAME TOTAL_SPACE USED_SPACE FREE_SPACE
------------------------------ -------------------- -------------------- --------------------
SYSAUX 1.73 1.59 .13
UNDOTBS1 3.39 .08 3.31
USERS .81 .05 .76
SYSTEM .71 .71 0
.....。从关于sysman拥有您正在查看的表的评论来看，当您连接到EM时，您可能看不到DBA视图。
如果要对结果进行进一步计算，如获取已用空间百分比，则可以将透视结果放入内联视图或CTE中：
with cte as (
select *
from (
select column_label, key_value as tablespace_name,
to_number(value)/1024 as value
from sysman.mgmt$metric_current
where column_label in ('Tablespace Allocated Space (MB)',
'Tablespace Used Space (MB)', 'Tablespace Free Space (MB)')
and target_type = 'rac_database'
)
pivot (
max(value)
for column_label in ('Tablespace Allocated Space (MB)' as total_space,
'Tablespace Used Space (MB)' as used_space,
'Tablespace Free Space (MB)' as free_space)
)
)
select tablespace_name, round(total_space, 2) as total_space,
round(used_space, 2) as used_space, round(free_space, 2) as free_space,
round(100 * used_space / total_space, 2) as pct_used
from cte;在11g中，这也让您可以轻松地根据总空间和已用空间计算可用空间：
with cte as (
select *
from (
select column_label, key_value as tablespace_name,
to_number(value)/1024 as value
from sysman.mgmt$metric_current
where column_label in ('Tablespace Allocated Space (MB)',
'Tablespace Used Space (MB)')
and target_type = 'rac_database'
)
pivot (
max(value)
for column_label in ('Tablespace Allocated Space (MB)' as total_space,
'Tablespace Used Space (MB)' as used_space)
)
)
select tablespace_name, round(total_space, 2) as total_space,
round(used_space, 2) as used_space,
round(total_space - used_space, 2) as free_space,
round(100 * used_space / total_space, 2) as pct_used
from cte;
TABLESPACE_NAME TOTAL_SPACE USED_SPACE FREE_SPACE PCT_USED
------------------------------ -------------------- -------------------- -------------------- ----------
UNDOTBS1 3.39 .02 3.37 .59
TEMP 1.96 0 1.96 0
SYSAUX 1.73 1.59 .14 92.1
USERS .81 .05 .76 6.5
SYSTEM .71 .71 0 99.61
...页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/42257238
复制相关文章
相似问题
腾讯云开发者
