How to estimate the historical growth of oracle databases and database objects

Source: Internet
Author: User

How to estimate the historical growth of oracle databases and database objects

----- Database growth in the last seven days. This is only an estimate.

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
Sn. snap_id = s. snap_id
And begin_interval_time> sysdate-8
Order
Begin_interval_time
/

Here are two similar script scripts:

Scripts: View historical database Growth


View historical database Growth
In this example, the historical growth of all tablespaces in the database is calculated to calculate the historical growth of the database.
-- Does not include undo and temp

With tmp
(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
(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


######################################## ######################
SQL Script: lists the historical changes of the space used by the objects in a snapshot:

Column owner format a16
Column object_name format a36
Column start_day format a11
Column block_increase format 9999999999.

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,
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 ('17-FEB-2014 ', 'dd-MON-RRRR ')
And to_timestamp ('25-FEB-2014 ', '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;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.