TA的每日心情 | 开心 2023-8-9 11:05 |
---|
|
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
- AND d.tablespace_name='&TS_NAME'
- ORDER by to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss');
复制代码 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;
复制代码 查看数据库历史增长情况(不含undo和temp)
- WITH tmp
- AS (
- SELECT rtime
- ,sum(tablespace_usedsize_kb) tablespace_usedsize_kb
- ,sum(tablespace_size_kb) tablespace_size_kb
- FROM (
- SELECT rtime
- ,e.tablespace_id
- ,(e.tablespace_usedsize) * (f.block_size) / 1024 tablespace_usedsize_kb
- ,(e.tablespace_size) * (f.block_size) / 1024 tablespace_size_kb
- FROM dba_hist_tbspc_space_usage e
- ,dba_tablespaces f
- ,v$tablespace g
- WHERE e.tablespace_id = g.TS#
- AND f.tablespace_name = g.NAME
- AND f.contents NOT IN (
- 'TEMPORARY'
- ,'UNDO'
- )
- )
- GROUP BY rtime
- )
- SELECT tmp.rtime
- ,tablespace_usedsize_kb
- ,tablespace_size_kb
- ,(
- tablespace_usedsize_kb - LAG(tablespace_usedsize_kb, 1, NULL) OVER (
- ORDER BY tmp.rtime
- )
- ) AS DIFF_KB
- FROM tmp
- ,(
- SELECT max(rtime) rtime
- FROM tmp
- GROUP BY substr(rtime, 1, 10)
- ) t2
- WHERE t2.rtime = tmp.rtime;
复制代码 查看数据库历史增长情况(含undo和temp)
- WITH tmp
- AS (
- SELECT min(rtime) rtime
- ,sum(tablespace_usedsize_kb) tablespace_usedsize_kb
- ,sum(tablespace_size_kb) tablespace_size_kb
- FROM (
- SELECT rtime
- ,e.tablespace_id
- ,(e.tablespace_usedsize) * (f.block_size) / 1024 tablespace_usedsize_kb
- ,(e.tablespace_size) * (f.block_size) / 1024 tablespace_size_kb
- FROM dba_hist_tbspc_space_usage e
- ,dba_tablespaces f
- ,v$tablespace g
- WHERE e.tablespace_id = g.TS#
- AND f.tablespace_name = g.NAME
- )
- GROUP BY rtime
- )
- SELECT tmp.rtime
- ,tablespace_usedsize_kb
- ,tablespace_size_kb
- ,(
- tablespace_usedsize_kb - LAG(tablespace_usedsize_kb, 1, NULL) OVER (
- ORDER BY tmp.rtime
- )
- ) AS DIFF_KB
- FROM tmp
- ,(
- SELECT min(rtime) rtime
- FROM tmp
- GROUP BY substr(rtime, 1, 10)
- ) t2
- WHERE t2.rtime = tmp.rtime;
复制代码 列出相关段在快照时间内得历史增长情况
- WITH tmp
- AS (
- SELECT min(rtime) rtime
- ,sum(tablespace_usedsize_kb) tablespace_usedsize_kb
- ,sum(tablespace_size_kb) tablespace_size_kb
- FROM (
- SELECT rtime
- ,e.tablespace_id
- ,(e.tablespace_usedsize) * (f.block_size) / 1024 tablespace_usedsize_kb
- ,(e.tablespace_size) * (f.block_size) / 1024 tablespace_size_kb
- FROM dba_hist_tbspc_space_usage e
- ,dba_tablespaces f
- ,v$tablespace g
- WHERE e.tablespace_id = g.TS#
- AND f.tablespace_name = g.NAME
- )
- GROUP BY rtime
- )
- SELECT tmp.rtime
- ,tablespace_usedsize_kb
- ,tablespace_size_kb
- ,(
- tablespace_usedsize_kb - LAG(tablespace_usedsize_kb, 1, NULL) OVER (
- ORDER BY tmp.rtime
- )
- ) AS DIFF_KB
- FROM tmp
- ,(
- SELECT min(rtime) rtime
- FROM tmp
- GROUP BY substr(rtime, 1, 10)
- ) t2
- WHERE t2.rtime = tmp.rtime;
复制代码
|
|