度量快速开发平台-专业、快速的软件定制快开平台

标题: 当前用户默认表空间使用情况及单张表使用情况 [打印本页]

作者: 张兴康 时间: 2020-6-8 17:14
标题: 当前用户默认表空间使用情况及单张表使用情况
当前用户默认表空间使用情况
select tablespacename,
   sum(totalContent),
   sum(usecontent),
   sum(sparecontent),
   avg(sparepercent)
  from (SELECT b.file_id as id,
            b.tablespace_name as tablespacename,
            b.bytes as totalContent,
            (b.bytes - sum(nvl(a.bytes, 0))) as usecontent,
            sum(nvl(a.bytes, 0)) as sparecontent,
            sum(nvl(a.bytes, 0)) / (b.bytes) * 100 as sparepercent
      FROM dba_free_space a, dba_data_files b
      WHERE a.file_id = b.file_id
         and b.tablespace_name =
            (select default_tablespace
               from dba_users
               where username = user)
      group by b.tablespace_name, b.file_name, b.file_id, b.bytes)
GROUP BY tablespacename

执行效果：