Oracle数据库 加入小组

60个成员 56个话题 创建时间:2021-01-15

【运维脚本】查看表空间占用率

发表于2021-04-16 2160次查看

分享一个查询表空间占用率的脚步,可用于12c以后的多租户,支持RAC,是EM13c中使用,值得收藏!

 

SET LINES 200
COL name FORMAT a20
SELECT pdb.name,
       ts.tablespace_name,
       NVL(t.bytes/1024/1024,0) "allocated_space(M)",
       NVL(DECODE(un.bytes,null,DECODE(ts.contents,'TEMPORARY',
DECODE(ts.extent_management,'LOCAL',u.bytes,t.bytes - NVL(u.bytes, 0)),
t.bytes - NVL(u.bytes, 0)), un.bytes)/1024/1024,0) "used_space(M)"
  FROM
       cdb_tablespaces ts, v$containers pdb,
      (select con_id, tablespace_name, sum(bytes) bytes
       from cdb_free_space group by con_id,tablespace_name
     UNION ALL
        SELECT con_id, tablespace_name, NVL(SUM(bytes_used), 0)
          FROM gv$temp_extent_pool
        GROUP BY con_id, tablespace_name) u,
       (SELECT con_id, tablespace_name,
               SUM(NVL(bytes, 0)) bytes
          FROM cdb_data_files
      GROUP BY con_id, tablespace_name
     UNION ALL
        SELECT con_id, tablespace_name,
               SUM(NVL(bytes, 0)) bytes
          FROM cdb_temp_files
      GROUP BY con_id, tablespace_name) t,
       (SELECT ts.con_id, ts.tablespace_name,
NVL(um.used_space*ts.block_size, 0) bytes
          FROM cdb_tablespaces ts, cdb_tablespace_usage_metrics um
         WHERE ts.tablespace_name = um.tablespace_name(+)
           AND ts.con_id = um.con_id(+)
           AND ts.contents='UNDO') un
 WHERE ts.tablespace_name = t.tablespace_name(+)
   AND ts.tablespace_name = u.tablespace_name(+)
   AND ts.tablespace_name = un.tablespace_name(+)
   AND ts.con_id = pdb.con_id
   AND ts.con_id = u.con_id(+)
   AND ts.con_id = t.con_id(+)
   AND ts.con_id = un.con_id(+)
ORDER BY 1,2
;

 

tbspAllocation_cdb.sql.zip 预览 下载
发表回复
你还没有登录,请先 登录或 注册!