Shulou(Shulou.com)05/31 Report--

How to write Oracle query table space daily growth and historical statistics of the script, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can get something.

Today, we mainly summarize some scripts for daily growth and historical statistics of Oracle tablespaces, for reference only.

Daily growth of 11g statistical table space

SELECT a.snap_id, c.tablespace_name ts_name, to_char (to_date (a.rtime, 'mm/dd/yyyy hh34:mi:ss'),' yyyy-mm-dd hh34: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 hh34:mi:ss') > = sysdate-30 order by a.tablespace_id, to_date (a.rtime,' mm/dd/yyyy hh34:mi:ss') desc

12c daily growth of statistical tablespace

SELECT a.snap_id, a.con_id, e.name pdbname, c.tablespace_name ts_name, to_char (to_date (a.rtime, 'mm/dd/yyyy hh34:mi:ss'),' yyyy-mm-dd hh34: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.tablespaceroomid= 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 hh34:mi:ss') > = sysdate-30 order by a.CONSTRAID desc a.tablespacedate (a.rtime,' mm/dd/yyyy hh34:mi:ss') desc

Estimate the historical growth of oracle database and database objects

This is only an estimate of the growth of the database in the last seven days.

Select sum (space_used_total) / 1024 / 1024 / 1024 "last 7 days db increase-G" from dba_hist_seg_stat s, dba_hist_seg_stat_obj o, dba_hist_snapshot sn where s.obj# = o.obj# and ssn.snap_id = s.snap_id and begin_interval_time > sysdate-8 order by begin_interval_time

View database historical growth

Here, the database history is calculated by calculating the historical growth of all the tablespaces in the database.

Does not include undo and 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

Including undo and 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

List the historical changes of the usage space of related segment objects during the snapshot time

Select obj.owner, obj.object_name, to_char (sn.BEGIN_INTERVAL_TIME, 'RRRR-MON-DD') start_day, sum (a.db_block_changes_delta) block_increase from dba_hist_seg_stat a, dba_hist_snapshot sn, dba_objects obj where sn.snap_id = a.snap_id and obj.object_id = a.obj# and obj.owner not in (' SYS') 'SYSTEM') and end_interval_time between to_timestamp (' 01 order by obj.owner order by obj.owner, 'DD-MON-RRRR') and to_timestamp (' 09 order by obj.owner, 'DD-MON-RRRR') group by obj.owner, obj.object_name, to_char (sn.BEGIN_INTERVAL_TIME,' RRRR-MON-DD') order by obj.owner, obj.object_name

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.