Oracle Database Size Growth Control

More recently, at work it has become necessary to control the growth of the size of the Oracle database. This was due to the fact that there is not much space left, something around 100 GB, and in two companies (large companies, insurance) the bases are growing rapidly.

To begin, we will create a DB_TABLESPACE_SIZE table in which we will store data about each day.

-- Create table
create table DB_TABLESPACE_SIZE
(
DB_TABLESPACE_NAME VARCHAR2(30),
TIME_SNAPSHOT DATE ,
FREE_SPACE NUMBER(20),
MAX_LIMIT NUMBER(20),
CURRENT_SIZE NUMBER(20),
AUTOEXTEND_ON NUMBER(20),
AVAILABLE_SIZE NUMBER(20),
USED_FOR_DATA NUMBER(20),
UNUSED_FOR_DATA NUMBER(20),
FILES_COUNT NUMBER(5),
MIN_UNALLOCATED NUMBER(20),
MAX_UNALLOCATED NUMBER(20),
MIN_AVAILABLE NUMBER(20),
MAX_AVAILABLE NUMBER(20),
MIN_USED NUMBER(20),
MAX_USED NUMBER(20),
MIN_UNUSED NUMBER(20),
MAX_UNUSED NUMBER(20)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the columns
comment on column DB_TABLESPACE_SIZE.FREE_SPACE
is ', (). UNUSED, ― , .' ;
comment on column DB_TABLESPACE_SIZE.MAX_LIMIT
is ' . ( AUTOEXTEND)' ;
comment on column DB_TABLESPACE_SIZE.CURRENT_SIZE
is ' ' ;
comment on column DB_TABLESPACE_SIZE.AVAILABLE_SIZE
is ', UNUSED+autoextend_on' ;
comment on column DB_TABLESPACE_SIZE.USED_FOR_DATA
is ', . ( "high watermark")' ;
comment on column DB_TABLESPACE_SIZE.UNUSED_FOR_DATA
is ', . .. , , UNUSED. ' ;

* This source code was highlighted with Source Code Highlighter .

Next, create a current_tablespace_size view. We’ll store current tablespace data and summarized data there.

create or replace view current_tablespace_size
(db_tablespace_name, time_snapshot, max limit on gb, current_size on gb, autoextend_on on gb, available_size on gb, used_for_data on gb, unused_for_data, free_space, files_count, min_unallocated, max_unallocated, min_available, max_available, min_used, max_used, min_unused, max_unused)
as
select DB_TABLESPACE_NAME, TIME_SNAPSHOT,
MAX_LIMIT/1024/1024/1024 ,
CURRENT_SIZE/1024/1024/1024,
AUTOEXTEND_ON/1024/1024/1024,
AVAILABLE_SIZE/1024/1024/1024 "AVAILABLE_SIZE",
USED_FOR_DATA/1024/1024/1024 "USED_FOR_DATA",
UNUSED_FOR_DATA/1024/1024 "UNUSED_FOR_DATA",
FREE_SPACE/1024/1024 "FREE_SPACE", "FILES_COUNT",
MIN_UNALLOCATED/1024/1024 "MIN_UNALLOCATED",
MAX_UNALLOCATED/1024/1024 "MAX_UNALLOCATED",
MIN_AVAILABLE/1024/1024 "MIN_AVAILABLE",
MAX_AVAILABLE/1024/1024 "MAX_AVAILABLE",
MIN_USED/1024/1024 "MIN_USED",
MAX_USED/1024/1024 "MAX_USED",
MIN_UNUSED/1024/1024 "MIN_UNUSED",
MAX_UNUSED/1024/1024 "MAX_UNUSED"
-- sum (CURRENT_SIZE)
from db_TABLESPACE_SIZE
where TIME_SNAPSHOT = ( select MAX (TIME_SNAPSHOT) from db_TABLESPACE_SIZE)
union select 'TOTAL' ,( select max (s.time_snapshot) from db_TABLESPACE_SIZE s ), null ,
( select sum (s.current_size/1024/1024/1024) from db_TABLESPACE_SIZE s where s.time_snapshot=( select max (s.time_snapshot) from db_TABLESPACE_SIZE s )), null , null , null , null , null , null , null , null , null , null , null , null , null , null from dual;

* This source code was highlighted with Source Code Highlighter .

Next, create a db_tablespace_size_by_date table to store the daily resizing of the database.

-- Create table
create table DB_TABLESPACE_SIZE_BY_DATE
(
DB_TABLESPACE_NAME VARCHAR2(30),
TIME_SNAPSHOT DATE ,
DEFF_SIZE NUMBER
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);


* This source code was highlighted with Source Code Highlighter .

Well, the last step is to write job to collect information every day and fill out our tables.

begin
sys.dbms_job.submit(job => :job,
what => 'insert into db_TABLESPACE_SIZE( "DB_TABLESPACE_NAME", "TIME_SNAPSHOT", "MAX_LIMIT", "CURRENT_SIZE",
"AUTOEXTEND_ON", "AVAILABLE_SIZE", "USED_FOR_DATA", "UNUSED_FOR_DATA", "FREE_SPACE", "FILES_COUNT","MIN_UNALLOCATED", "MAX_UNALLOCATED",
"MIN_AVAILABLE", "MAX_AVAILABLE","MIN_USED", "MAX_USED", "MIN_UNUSED", "MAX_UNUSED")
select F."TABLESPACE_NAME",
F."TIME",
F."LIMIT",
F."SIZE",
F."UNALLOCATED",
F."AVAILABLE",
F."USED",
F."UNUSED",
nvl(S.TOTAL_BYTES, 0) "FREE_SPACE",
F.FILES,
F.MIN_UNALLOCATED,
F.MAX_UNALLOCATED,
F.MIN_AVAILABLE,
F.MAX_AVAILABLE,
F.MIN_USED,
F.MAX_USED,
F.MIN_UNUSED,
F.MAX_UNUSED
from
( select tablespace_name,
sysdate "TIME",
SUM( CASE WHEN AUTOEXTENSIBLE=' 'YES' ' THEN MAXBYTES ELSE BYTES END ) "LIMIT",
SUM( BYTES ) "SIZE",
SUM( CASE WHEN AUTOEXTENSIBLE=' 'YES' ' THEN MAXBYTES - BYTES ELSE 0 END ) "UNALLOCATED",
SUM( CASE WHEN AUTOEXTENSIBLE=' 'YES' ' THEN MAXBYTES ELSE BYTES END - USER_BYTES ) "AVAILABLE",
SUM( USER_BYTES ) "USED",
SUM( BYTES - USER_BYTES ) "UNUSED",
COUNT( FILE_NAME ) "FILES",
MIN( CASE WHEN AUTOEXTENSIBLE=' 'YES' ' THEN MAXBYTES - BYTES ELSE null END ) "MIN_UNALLOCATED",
MAX( CASE WHEN AUTOEXTENSIBLE=' 'YES' ' THEN MAXBYTES ELSE BYTES END - BYTES ) "MAX_UNALLOCATED",
MIN( CASE WHEN AUTOEXTENSIBLE=' 'YES' ' THEN MAXBYTES ELSE BYTES END - USER_BYTES) "MIN_AVAILABLE",
MAX( CASE WHEN AUTOEXTENSIBLE=' 'YES' ' THEN MAXBYTES ELSE BYTES END - USER_BYTES) "MAX_AVAILABLE",
MIN( USER_BYTES ) "MIN_USED",
MAX( USER_BYTES ) "MAX_USED",
MIN( BYTES - USER_BYTES ) "MIN_UNUSED",
MAX( BYTES - USER_BYTES ) "MAX_UNUSED"
from dba_data_files
group by tablespace_name
) F left join dba_free_space_coalesced S on (F.TABLESPACE_NAME = S.TABLESPACE_NAME);
insert into db_tablespace_size_by_date ("DB_TABLESPACE_NAME","TIME_SNAPSHOT","DEFF_SIZE")
SELECT nvl(t1.db_tablespace_name, ' 'TOTAL' '),
MAX(t1.time_snapshot),
(SUM(t1.current_size / 1024 / 1024 / 1024) -
SUM(t1.free_space / 1024 / 1024 / 1024)) -
(SUM(t3.current_size / 1024 / 1024 / 1024) -
SUM(t3.free_space / 1024 / 1024 / 1024))
FROM db_tablespace_size t1, db_tablespace_size t3
WHERE t1.time_snapshot =
(SELECT MAX(t2.time_snapshot)
FROM db_tablespace_size t2
WHERE trunc(t2.time_snapshot) = trunc(SYSDATE))
AND t3.time_snapshot =
(SELECT MIN(t2.time_snapshot)
FROM db_tablespace_size t2
WHERE trunc(t2.time_snapshot) = trunc(SYSDATE-1))
AND t1.db_tablespace_name = t3.db_tablespace_name
GROUP BY CUBE(t1.db_tablespace_name);
commit;' ,
next_date => to_date( '15-02-2012 05:00:00' , 'dd-mm-yyyy hh24:mi:ss' ),
interval => 'trunc(SYSDATE,' 'hh' ')+1' );
commit ;
end ;
/

* This source code was highlighted with Source Code Highlighter .

A little about the code: The db_TABLESPACE_SIZE table is populated from dba_data_files and dba_free_space_coalesced. The first stores data about the sizes of the tablespace files, and the second size is freed inside the tablespace files.
In the db_tablespace_size_by_date table, go the difference between size and free_size, that is, in fact, we get the actual space occupied.
')
That's all, now every day information is collected about the size of the database and about the changes.