问获取表空间输出
EN

Stack Overflow用户

提问于 2017-02-15 18:22:19

回答 2查看 713关注 0票数 0

我们的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>

oracle

语音识别特惠，低至14.9元！

提供业界非常具有性价比的语音识别服务，超高识别准确率，适用多场景

回答 2

Stack Overflow用户

发布于 2017-02-15 18: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;

票数 1

Stack Overflow用户

发布于 2017-02-15 18: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
...