[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 718
  • Last Modified:

send the script for db growth after certain period of time

to find out the approx growth in GB for a database after one week.
0
dbq
Asked:
dbq
1 Solution
 
jocaveCommented:
You would need some sort of baseline to compare against.

If you have the enterprise edition of the database and the performance and tuning pack, you could query the AWR (Automatic Workload Repository) tables to get this information.  You could either use the DBA_HIST_SEG_STAT table or the DBMS_SPACE.OBJECT_GROWTH_TREND function.
0
 
shru_0409Commented:
0
 
shru_0409Commented:
http://www.dbapool.com/dbscripts/

this link will also help u
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
sventhanCommented:
Hope this helps

column Percent of Total Disk Usage justify right format 999.99
column Space Used (MB) justify right format 9,999,999.99
column Total Object Size (MB) justify right format 9,999,999.99
set linesize 150
set pages 80
set feedback off
select * from (select to_char(end_interval_time, MM/DD/YY) mydate, sum(space_used_delta) / 1024 / 1024 Space used (MB), avg(c.bytes) / 1024 / 1024 Total Object Size (MB),
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) Percent of Total Disk Usage
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where begin_interval_time > trunc(sysdate)  &days_back
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and c.segment_name = &segment_name
group by to_char(end_interval_time, MM/DD/YY))
order by to_date(mydate, MM/DD/YY);

Weekly growth of database
set feedback off
set pages 80
set linesize 150
spool /tmp/weekly_growth.txt
ttitle Total Disk Used
select sum(space_used_delta) / 1024 / 1024 Space used (M), sum(c.bytes) / 1024 / 1024 Total Schema Size (M),
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) || % Percent of Total Disk Usage
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
see code depot for full script
where end_interval_time > trunc(sysdate)  &days_back
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and c.owner = &schema_name
and space_used_delta > 0;
title Total Disk Used by Object Type
select c.segment_type, sum(space_used_delta) / 1024 / 1024 Space used (M), sum(c.bytes) / 1024 / 1024 Total Space (M),
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) || % Percent of Total Disk Usage
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
see code depot for full script
where end_interval_time > trunc(sysdate)  &days_back
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and space_used_delta > 0
and c.owner = &schema_name
group by rollup(segment_type);
spool off

http://blogs.oracle.com/myadav//2008/08/using_oem_want_to_know_databas.html
0
 
sventhanCommented:
Also,

-- total DB size

select round((sum(bytes)/1048576/1024),2)
from V$datafile;

select round((sum(bytes)/1048576/1024),2)
from V$tempfile;

Take the sum of this two values which will be your total database size. Record this value daily/weekly/monthly basis and compare the difference.
0
 
DavidSenior Oracle Database AdministratorCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now