Question: I need some scripts for showing
the growth of tablespaces over time periods. I need to show
the starting tablespace size and the end tablespace size, computing
the tablespace growth for the time period.
Answer: There are many ways to
compute tablespace growth:
database_growth_reports
tracking_oracle_database_tables_growth
table growth reports
For growth reports tat the tablespace level, we see these
scripts for tablespace growth:
set feed off
column
"tablespace_name" heading "Tablespace | Name" format a20
column "FileCount" heading "File | Count" format 999999
column "Size(MB)" heading "Size | (MB)" format
999,999,999.99
column "Free(MB)" heading "Free | (MB)"
format 999,999,999.99
column "Used(MB)" heading "Used |
(MB)" format 999,999,999.99
column "Max Ext(MB)" heading
"Max Ext | (MB)" format 999,999,999
column "%Free"
heading "% | Free" format 999.99
column "%Free Ext"
heading "% | Free Ext" format 999.99
column "Graph"
heading "Graph" format a11
column tablespace_name heading
"Tablespace | Name" format a20
SELECT
ts.tablespace_name, "File Count",
TRUNC("SIZE(MB)", 2) "Size(MB)",
TRUNC(fr."FREE(MB)",
2) "Free(MB)",
TRUNC("SIZE(MB)" -
"FREE(MB)", 2) "Used(MB)",
df."MAX_EXT" "Max
Ext(MB)",
(fr."FREE(MB)" / df."SIZE(MB)") *
100 "% Free",
RPAD('*', TRUNC(CEIL((fr."FREE(MB)"
/ df."SIZE(MB)") * 100)/10), '*') "Graph"
FROM
(SELECT tablespace_name,
SUM (bytes) / (1024 * 1024) "FREE(MB)"
FROM
dba_free_space
GROUP BY
tablespace_name) fr,
(SELECT tablespace_name, SUM(bytes)
/ (1024 * 1024) "SIZE(MB)", COUNT(*)
"File Count",
SUM(maxbytes) / (1024 * 1024) "MAX_EXT"
FROM
dba_data_files
GROUP BY tablespace_name) df,
(SELECT
tablespace_name
FROM dba_tablespaces) ts
WHERE
fr.tablespace_name = df.tablespace_name (+)
AND
fr.tablespace_name = ts.tablespace_name (+)
ORDER BY "%
Free" desc
/
prompt
prompt "The last line shows
the Tablespace wich is growing too large..."
prompt
dba_hist_seg_stat_usage.sql
--
****************************************************
-- This tablespace growth report
requires AWR license
--
****************************************************
set feedback on
select * from (select
c.tablespace_name,c.segment_name "object name",b.object_type,
sum(space_used_delta) / 1024 / 1024
"Growth (MB)"
from
see code depot for full tablespace growth script
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.owner ='XXXX'
group by
c.tablespace_name,
c.segment_name,
b.object_type)
order by 3 asc;
dba_hist_tbspc_usage.sql
--
****************************************************
-- This tablespace growth report
requires AWR license
--
****************************************************
select
to_char (sp.begin_interval_time,'dd-mm-yyyy')
days,
ts.tsname,
max(round((tsu.tablespace_size* dt.block_size
)/(1024*1024),2) ) cur_size_mb,
max(round((tsu.tablespace_usedsize*
dt.block_size )/(1024*1024),2)) usedsize_mb,
from
see code depot for full tablespace growth script
dba_hist_tbspc_space_usage tsu,
dba_hist_tablespace_stat
ts,
dba_hist_snapshot sp,
dba_tablespaces dt
where
tsu.tablespace_id= ts.ts#
and
tsu.snap_id = sp.snap_id
and
ts.tsname =
dt.tablespace_name
and
ts.tsname not in ('SYSAUX','SYSTEM')
group by
to_char (sp.begin_interval_time,'dd-mm-yyyy'),
ts.tsname
order by ts.tsname, days;
segment_tablespace_growth_report.sql
--
****************************************************
-- This tablespace growth report
requires AWR license
--
****************************************************
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
set line 5000
column "SEGMENT_NAME" justify left
format A30
column "TABLESPACE_NAME" justify left format A30
select * from (select c.tablespace_name,c.segment_name,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
see code depot for full tablespace growth script
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where
begin_interval_time > trunc(sysdate) - 10
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 = 'S_PARTY'
group by
c.tablespace_name,
c.segment_name,
to_char(end_interval_time, 'MM/DD/YY')
order by
c.tablespace_name,
c.segment_name,
to_date(mydate, 'MM/DD/YY'));
--
****************************************************
-- This tablespace growth report
requires AWR license
--
****************************************************
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
column "segment_name" justify left
format a30
column "tablespace_name" justify left format a30
set linesize 150
set pages 80
set feedback off
set line 5000
select * from
(select
c.tablespace_name,c.segment_name,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
see code depot for full tablespace growth script
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where
begin_interval_time >
trunc(sysdate) - 10
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 = 's_party'
group by
c.tablespace_name,
c.segment_name,
to_char(end_interval_time,
'mm/dd/yy')
order by
c.tablespace_name,
c.segment_name,
to_date(mydate, 'mm/dd/yy'));
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
