重庆思庄Oracle、Redhat认证学习论坛

标题: Oracle查询表空间的每日增长量 [打印本页]

作者: jiawang 时间: 2023-5-29 16:37
标题: Oracle查询表空间的每日增长量
10g和11g：
SELECT a.snap_id,
   c.tablespace_name ts_name,
   to_char(to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss'), 'yyyy-mm-dd hh24:mi') rtime,
   round(a.tablespace_size * c.block_size / 1024 / 1024, 2) ts_size_mb,
   round(a.tablespace_usedsize * c.block_size / 1024 / 1024, 2) ts_used_mb,
   round((a.tablespace_size - a.tablespace_usedsize) * c.block_size / 1024 / 1024,
         2) ts_free_mb,
   round(a.tablespace_usedsize / a.tablespace_size * 100, 2) pct_used
  FROM dba_hist_tbspc_space_usage a,
   (SELECT tablespace_id,
            substr(rtime, 1, 10) rtime,
            max(snap_id) snap_id
      FROM dba_hist_tbspc_space_usage nb
      group by tablespace_id, substr(rtime, 1, 10)) b,
      dba_tablespaces c,
      v$tablespace d
where a.snap_id = b.snap_id
and a.tablespace_id = b.tablespace_id
and a.tablespace_id=d.TS#
and d.NAME=c.tablespace_name
   and  to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss') >=sysdate-30
order by a.tablespace_id,to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss') desc;

12C:
SELECT a.snap_id,
   a.con_id,
   e.name pdbname,
   c.tablespace_name ts_name,
   to_char(to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss'), 'yyyy-mm-dd hh24:mi') rtime,
   round(a.tablespace_size * c.block_size / 1024 / 1024, 2) ts_size_mb,
   round(a.tablespace_usedsize * c.block_size / 1024 / 1024, 2) ts_used_mb,
   round((a.tablespace_size - a.tablespace_usedsize) * c.block_size / 1024 / 1024,
         2) ts_free_mb,
   round(a.tablespace_usedsize / a.tablespace_size * 100, 2) pct_used
  FROM cdb_hist_tbspc_space_usage a,
   (SELECT tablespace_id,
            nb.con_id,
            substr(rtime, 1, 10) rtime,
            max(snap_id) snap_id
      FROM dba_hist_tbspc_space_usage nb
      group by tablespace_id, nb.con_id,substr(rtime, 1, 10)) b,
      cdb_tablespaces c,
      v$tablespace d,
      V$CONTAINERS e
where a.snap_id = b.snap_id
and a.tablespace_id = b.tablespace_id
and a.con_id=b.con_id
and a.con_id=c.con_id
and a.con_id=d.con_id
and a.con_id=e.con_id
and a.tablespace_id=d.TS#
and d.NAME=c.tablespace_name
   and  to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss') >=sysdate-30
order by a.CON_ID,a.tablespace_id,to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss') desc;

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)