Title: Analyzing Free Space in Tablespaces Over Three Months
Author:Kaushik Mukhopadhyay, a DBA in Kellogg Australia (Pty) Ltd. for Tata Consultancy Services in Calcutta, India. The main purpose of this script is to monitor the growth of tablespaces over a period of time so that the DBA can extend the tablespaces before applications report out of space. This script is very useful in a distributed environment with a large number of databases, where it is very difficult to keep track of the growth of the tablespaces. This script records the free space in tablespaces in a user-defined table for a three month period. This table may be created in SYSTEM schema or in any schema with DBA privileges. This script needs to be run on weekly basis, for example, each Monday morning, and the statistics about free space are populated in that table. It also keeps the previous weeks' statistics in the table and records any changes in the tablespace sizes over the last week. Two scripts have been provided. The first script is for creating the table TABLOG, which keeps the record. The second script is a PL/SQL script which needs to be run once a week to record the statistics in TABLOG. In a UNIX environment, the second script can be scheduled to run by putting it in 'cron' entry and calling this script from a shell program. The table TABLOG can be queried to get the statistics about the tablespaces.Source/Text/Comments
rem SCRIPT 1
rem THIS SCRIPT NEEDS TO BE RUN ONLY ONCE FOR EACH DATABASE TO CREATE THE TABLE rem 'TABLOG' WHICH KEEPS THE STATISTICS.
rem This table analyzes the free space ( in % ) over last 3 months time
rem It also records any changes in the tablespace size and the week on which the rem changes have been made
create table tablog(
RUN_DATE DATE NOT NULL,
/* Last date when the table is populated */
TS_NAME VARCHAR2(32) NOT NULL,
/* Tablespace name */
TOTAL NUMBER(5) NOT NULL,
/* Total size of the tablespace in MB */
LAST_TOTAL NUMBER(5),
/* Last size before the tablespace was extended */
LAST_RUN_DATE DATE,
/* The date when the tablespace was last extended */
CURR_FREE NUMBER(5,2) NOT NULL,
/* Current free space in the tablespace in MB */
LAST_FREE_1 NUMBER(5,2),
/* Free space in tablespace 1 week back in MB */
LAST_FREE_2 NUMBER(5,2),
/* Free space in tablespace 2 weeks back in MB */
LAST_FREE_3 NUMBER(5,2),
/* Free space in tablespace 3 weeks back in MB */
MNTH2_FREE NUMBER(5,2),
/* Free space in tablespace 1 month back in MB */
MNTH1_FREE NUMBER(5,2))
/* Free space in tablespace 2 months back in MB */
/
rem SRIPT 2
rem THIS IS THE SECOND SCRIPT WHICH NEEDS TO BE RUN ONCE A WEEK
rem This script updates the table TABLOG for the tablespace information.
rem The table TAB_LOG keeps the information about free space of each tablespace
rem up to 3 months time. It logs the changes in the size of the tablespace
rem if there is a change in the size in the last week. Otherwise, it keeps the
rem record for the last update.
declare
cursor c1 is
select tablespace_name
from dba_tablespaces
order by tablespace_name;
tsp_name dba_tablespaces.tablespace_name%type;
init dba_tablespaces.initial_extent%type;
nxt dba_tablespaces.next_extent%type;
pct dba_tablespaces.pct_increase%type;
total dba_data_files.bytes%type;
prev_total dba_data_files.bytes%type;
last_total dba_data_files.bytes%type;
prev_total_1 dba_data_files.bytes%type;
old_free_1 tablog.curr_free%type;
old_free_2 tablog.curr_free%type;
old_free_3 tablog.curr_free%type;
old_month_free_1 tablog.curr_free%type;
old_month_free_2 tablog.curr_free%type;
old_month_free_3 tablog.curr_free%type;
run_date date;
prev_run_date date;
last_run_date date;
frag number(5,2);
free number(5,2);
remain number(7,2);
period number(1);
begin
open c1;
loop
fetch c1 into tsp_name;
exit when c1%notfound;
select sum(bytes/1024/1024)
into total
from dba_data_files
where tablespace_name = tsp_name;
select sum(bytes/1024/1024)
into remain
from dba_free_space
where tablespace_name = tsp_name;
select (100*remain)/total
into free
from dual;
select (100 - (max(bytes)/sum(bytes))*100)
into frag
from dba_free_space
where tablespace_name = tsp_name;
begin
select run_date,
curr_free,
total,
last_total,
last_run_date,
last_free_1,
last_free_2,
last_free_3,
mnth2_free,
mnth1_free
into run_date,
old_free_1,
prev_total,
prev_total_1,
last_run_date,
old_free_2,
old_free_3,
old_month_free_2,
old_month_free_1,
old_month_free_3
from tablog
where ts_name = tsp_name;
select ceil(months_between(sysdate,run_date))
into period
from dual;
if (run_date+7 <= sysdate) then
if (total > prev_total) then
last_total := prev_total;
prev_run_date := run_date;
else
last_total := prev_total_1;
prev_run_date := last_run_date;
end if;
if ( period > 0 ) then
insert into tablog values(sysdate, tsp_name, total, last_total, prev_run_date,
free, old_free_1, old_free_2, old_free_3,
old_month_free_2, old_month_free_1 );
else
insert into tablog values(sysdate, tsp_name, total, last_total, prev_run_date,
free, old_free_1, old_free_2, old_free_3,
old_month_free_1, old_month_free_3 );
end if;
delete from tablog where run_date < sysdate
and ts_name = tsp_name;
end if;
exception
when no_data_found then
insert into tablog values(sysdate, tsp_name, total, null, null, free, null, null, null,
null, null);
end;
end loop;
commit;
close c1;
end;
/