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

标题: 依靠dba_hist_tbspc_space_usage视图获得表空间的历史使用情况 [打印本页]

作者: 刘泽宇 时间: 2022-4-6 10:04
标题: 依靠dba_hist_tbspc_space_usage视图获得表空间的历史使用情况
dba_hist_tbspc_space_usage视图中存放了历史信息
select a.name, b.*
from v$tablespace a,
   (select tablespace_id,
            trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss')) datetime,
            round(max(tablespace_usedsize * 8 / 1024),2) used_size_MB
      from dba_hist_tbspc_space_usage
      where trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss')) >
            trunc(sysdate - 17)
      group by tablespace_id,
               trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss'))
      order by tablespace_id,
               trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss'))) b
where a.ts# = b.tablespace_id and a.name='USERS';

NAME                         TABLESPACE_ID DATETIME  USED_SIZE_MB
------------------------------ ------------- --------- ------------
USERS                                     4 07-MAR-22 7240.67
USERS                                     4 08-MAR-22 7252.05
USERS                                     4 09-MAR-22 7320.55
USERS                                     4 10-MAR-22 7404.98

DBA_HIST_TBSPC_SPACE_USAGE displays historical tablespace usage statistics.

Column	Datatype	NULL	Description
SNAP_ID	NUMBER		Unique snapshot ID
DBID	NUMBER	NOT NULL	Database ID for the snapshot
TABLESPACE_ID	NUMBER		Tablespace ID
TABLESPACE_SIZE	NUMBER		Tablespace size (in database blocks)
TABLESPACE_MAXSIZE	NUMBER		Maximum size of the tablespace (in database blocks)
TABLESPACE_USEDSIZE	NUMBER		Used size of the tablespace (in database blocks)
RTIME	VARCHAR2(25)		Runtime

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