Tracking database tablespace growth

In many situations, a DBA is required to monitor the space allocations on many databases. A great deal of time may be spent in executing the commands to ascertain the current amount of the allocated space. There is no convenient tracking of the growth without placing the data into some type of report. The scripts provided here will allow tracking of growth for an Oracle database, and flag possible error conditions before they occur (of course this depends on the pace at which the database stores data and how often the scripts are executed.) Additionally, the data can be reported as statistics, by the DBA, to keep management informed of growth, plot expected growth, and document the requirement to obtain additional storage hardware.

This document will address the steps necessary to create a repository to store information about databases. A schedule process (via AT on Windows NT, or some other form of scheduler) can execute a batch job that will populate the data repositories. The repositories will be used to compare information that is obtained about each database that is monitored. This implementation addresses tablespace usage, max extent, and extent allocation errors. Appropriate userids that can access V$tables is required (example: system). Embedded passwords may be considered security risks at some locations. Modify scripts accordingly. Scripts may be compiled from alternate resources and are not considered as 'original' by the author. Note: These scripts should be executed from files, not sqlplus command line, in order to properly build the file output.

The repository database should be created on a non-production environment, although this is not a requirement, just a recommendation. Repository tables should be created in an appropriate tablespace.

These scripts are provided as is, without any warranty of accuracy or correctness. Always test scripts in a non-production environment, prior to use for production purposes. Use of scripts is always at your own risk.

Initial steps involve the creation of the tables (system) for the repository, as well as a dba contact table. This table is used to build the sql for the database links, and for reporting purposes to associate database sids with projects. After creating the tables, you generate the sql to populate the tables, scheduled per your requirements. You will need at least two executions of population of the tablespace_percentage table in order to have any information returned for a comparison.

1.2 Create Tables for Data Repository

NOTE: Change the tablespace per your site requirements.

CREATE TABLE SYSTEM.EXTENT_ERROR 
( 
    SID             VARCHAR2(16) NOT NULL, 
    TABLESPACE_NAME VARCHAR2(30) NOT NULL, 
    OWNER           VARCHAR2(30) NOT NULL, 
    SEGMENT_NAME    VARCHAR2(81) NOT NULL, 
    SEGMENT_TYPE    VARCHAR2(18) NOT NULL, 
    NEXT_EXTENT     NUMBER(32)       NULL, 
    MAX_EXTENTS     NUMBER(32)   NOT NULL, 
    TIME_STAMP      DATE             NULL 
) 
TABLESPACE OEM_REPOSITORY 
NOLOGGING 
PCTFREE 10 
PCTUSED 40 
INITRANS 1 
MAXTRANS 255 
STORAGE(INITIAL 128K 
        NEXT 128K 
        MINEXTENTS 1 
        MAXEXTENTS UNLIMITED 
        PCTINCREASE 0 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT) 
NOPARALLEL 
NOCACHE 
/ 

CREATE TABLE SYSTEM.MAX_EXTENTS 
( 
    SID          VARCHAR2(16) NOT NULL, 
    OWNER        VARCHAR2(30) NOT NULL, 
    SEGMENT_NAME VARCHAR2(81) NOT NULL, 
    SEGMENT_TYPE VARCHAR2(18) NOT NULL, 
    SEG_COUNT    NUMBER(32)   DEFAULT 0 NOT NULL, 
    SEG_MAX      NUMBER(32)   DEFAULT 0 NOT NULL, 
    EXTENTS      NUMBER(32)   NOT NULL, 
    MAX_EXTENTS  NUMBER(32)       NULL, 
    TIME_STAMP   DATE         NOT NULL 
) 
TABLESPACE OEM_REPOSITORY 
NOLOGGING 
PCTFREE 10 
PCTUSED 40 
INITRANS 1 
MAXTRANS 255 
STORAGE(INITIAL 128K 
        NEXT 128K 
        MINEXTENTS 1 
        MAXEXTENTS UNLIMITED 
        PCTINCREASE 0 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT) 
NOPARALLEL 
NOCACHE 
/ 

CREATE TABLE SYSTEM.TABLESPACE_PERCENTAGE 
( 
    SID             VARCHAR2(16) NOT NULL, 
    TABLESPACE_NAME VARCHAR2(30) NOT NULL, 
    PCTUSD          NUMBER(3)    NOT NULL, 
    TOTAL_BYTES     NUMBER(32)   NOT NULL, 
    TIME_STAMP      DATE         NOT NULL 
) 
TABLESPACE OEM_REPOSITORY 
NOLOGGING 
PCTFREE 10 
PCTUSED 40 
INITRANS 1 
MAXTRANS 255 
STORAGE(INITIAL 128K 
        NEXT 128K 
        MINEXTENTS 1 
        MAXEXTENTS UNLIMITED 
        PCTINCREASE 0 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT) 
NOPARALLEL 
NOCACHE 
/ 

1.3 Create DBA_Contacts Information

CREATE TABLE SYSTEM.DBA_CONTACTS 
( 
    SID           VARCHAR2(16)  NOT NULL, 
    DB_LINK       VARCHAR2(128) NOT NULL, 
    DBA_PRIMARY   VARCHAR2(64)  NOT NULL, 
    DBA_SECONDARY VARCHAR2(64)      NULL, 
    PROJECT_NAME  VARCHAR2(80)      NULL 
) 
TABLESPACE OEM_REPOSITORY 
NOLOGGING 
PCTFREE 10 
PCTUSED 40 
INITRANS 1 
MAXTRANS 255 
STORAGE(INITIAL 128K 
        NEXT 128K 
        MINEXTENTS 1 
        MAXEXTENTS UNLIMITED 
        PCTINCREASE 0 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT) 
NOPARALLEL 
NOCACHE 

Insert the appropriate information into the dba_contacts table. Example:

  
  SID                  DB_LINK DBA_PRIMARY   DBA_SECONDARY   PROJECT_NAME    
  ORC1 ORC1.WORLD      Jane Doe              None            DBA     

1.4 Create Database Links for Each Monitored Database

Using the DBA table and the link, you generate an output to create all of your links. You will need to modify this for each password to each database for the selected userid. Another option is to add the password into the dba_contact table, but this would be plain text and possibly a security violation at some sites.

select  'CREATE DATABASE LINK ' || DB_LINK ||   
        ' CONNECT  TO SYSTEM IDENTIFIED BY XXX USING '  
        || '''' ||  SID || '''' || ' ;' from dba_contacts t ;

Yields:

CREATE PUBLIC DATABASE LINK ORC1.WORLD 
    CONNECT TO SOME_USERID 
    IDENTIFIED BY XXX 
    USING 'orc1' 

Change the XXX to the appropritate password.

1.5 Update TNSNAMES.ORA File

Your tnsnames file must be in sync with the db_links. This means that the database links must have an entry for the sid in the LOCAL tnsnames file (i.e. the tnsnames.ora file on the same machine where you built the repository.)

################################################################## 
#  TMS - Oracle 8 database 
# 
#  Oracle 7 client 
ORC1.world = 
  (DESCRIPTION = 
    (ADDRESS_LIST = 
        (ADDRESS = 
          (COMMUNITY = tcp.world) 
          (PROTOCOL = TCP) 
          (Host = abc.def.ghi.com) 
          (Port = 1521) 
        )        
    ) 
    (CONNECT_DATA = (SID = ORC1) 
    ) 
  ) 
#  Oracle 8 client 
ORC1.def.ghi.com = 
  (DESCRIPTION = 
    (ADDRESS_LIST = 
        (ADDRESS = 
          (COMMUNITY = tcp.world) 
          (PROTOCOL = TCP) 
          (Host = abc.def.ghi.com) 
          (Port = 1521) 
        )        
    ) 
    (CONNECT_DATA = (SID = ORC1) 
    ) 
  ) 

1.6 Create SQL To Populate the Repository tables

1.6.1 Create file tablespace.sql

REM   commit is necessary to close db link. 
set pages 0 feedback off 
spool load_tablespace_info.sql 
select 'insert into system.tablespace_percentage 
(select upper(thesid.NAME) SID,total.tablespace_name tsname 
 ,round ((1-nvl(sum(free.bytes),0)/(total.totalbytes))*100)  pctusd 
  ,round(total.totalbytes/1048576) Total_Bytes,SYSDATE Time_Stamp 
from 
(select t.tablespace_name, sum(t.bytes) totalbytes 
  from dba_data_files@' || (select dba_contacts.DB_LINK from dba_contacts
  where dba_contacts.db_link = dba_db_links.db_link) || '  t  

    group by t.tablespace_name ) total, dba_free_space@' || 
        (select dba_contacts.DB_LINK from dba_contacts   
        where dba_contacts.db_link = dba_db_links.db_link) || '  free, 
    (select whatsid.NAME from v$database@' || 
        (select dba_contacts.DB_LINK from dba_contacts   
        where dba_contacts.db_link = dba_db_links.db_link) || ' whatsid) thesid 
where 
total.tablespace_name = free.tablespace_name(+) 
group by total.tablespace_name,   total.totalbytes, thesid.NAME) ' || ' 
/ ' || ' 
commit ' || ' 
/ ' from dual, dba_db_links 
/ 
spool off 
@load_tablespace_info.sql 

1.6.2 Create file extent_error.sql

set pages 0 feedback off 
spool load_extent_error_info.sql 
REM Next Extent causes error for space 
 select 'insert into system.extent_error 
 (select upper(thesid.NAME) SID 
 ,s.tablespace_name 
 ,s.owner 
 ,s.segment_name 
 ,s.segment_type 
 ,s.next_extent 
 ,S.max_extents 
 ,SYSDATE 
        FROM  dba_segments@' || 
        (select dba_contacts.DB_LINK from dba_contacts 
         where dba_contacts.db_link = dba_db_links.db_link) || '  s, 
    (select whatsid.NAME from v$database@' || 
        (select dba_contacts.DB_LINK from dba_contacts   
        where dba_contacts.db_link = dba_db_links.db_link) || ' whatsid) thesid 
        WHERE NOT EXISTS (SELECT 1 
                  FROM dba_free_space@' || 
                  (select dba_contacts.DB_LINK from dba_contacts 
                  where dba_contacts.db_link = dba_db_links.db_link) || '  f

                        WHERE s.tablespace_name=f.tablespace_name 
                        HAVING max(f.bytes) > s.next_extent))' || ' 
/ ' || ' 
commit ' || ' 
/ ' from dual, dba_db_links 
/ 
spool off 
@load_extent_error_info.sql 

1.6.3 Create file max_extents.sql

set pages 0 feedback off 
spool load_max_extents_info.sql 
REM  Max extents 
 select 'insert into system.max_extents (select upper(thesid.NAME) SID 
 ,e.owner 
 ,e.segment_name 
 ,e.segment_type 
        ,count(*) seg_count 
        ,avg(max_extents) seg_max 
        ,extents 
        ,max_extents 
        ,SYSDATE Time_Stamp 
        FROM  dba_extents@' || (select dba_contacts.DB_LINK from dba_contacts
        where dba_contacts.db_link = dba_db_links.db_link) 

|| ' e , dba_segments@' || 
        (select dba_contacts.DB_LINK from dba_contacts 
         where dba_contacts.db_link = dba_db_links.db_link) || '  s, 
    (select whatsid.NAME from v$database@' || 
        (select dba_contacts.DB_LINK from dba_contacts   
        where dba_contacts.db_link = dba_db_links.db_link) || ' whatsid) thesid 
WHERE  e.segment_name = s.segment_name 
AND  e.owner        = s.owner 
AND extents + 5 > max_extents 
GROUP BY  
e.owner, e.segment_name, e.segment_type, extents, max_extents, thesid.NAME)' || ' 
/ ' || ' 
commit ' || ' 
/ ' from dual, dba_db_links 
/ 
spool off 
@load_max_extents_info.sql 

1.7 Create Batch files to execute the SQL

1.7.1 Create file tablespace.bat

sqlplusw system/systems_password@repository   @tablespace 
exit;

1.7.2 Create file extent_error.bat

sqlplusw system/systems_password@repository   @extent_error 
exit;

1.7.3 Create file max_extents.bat

sqlplusw system/systems_password@repository   @max_extents 
exit; 

1.8 Setup Scheduler

1.8.1 NT:

at 20:00 EVERY: M,F "d:my_scriptsextent_error.bat" 
at 21:00 EVERY: M,F "d:my_scriptsmax_extents.bat" 
at 22:00 EVERY: M,F "d:my_scriptstablespace.bat" 

1.9 Analyzing the Data

1.9.1 Tablespace Usage

set pages 60 
set linesize 130 
break on report on page on Oracle_SID skip 2 
comput sum of "Total_Bytes(M) Allocated" on Oracle_SID 
column instdate new_value v_instdate noprint 
SELECT TO_CHAR(sysdate,'-dd-Mon-yyyy') instdate FROM dual; 
prompt 
spool TableSpaceReport&v_instdate; 
ttitle 'Tablespaces' 
btitle 'Percentages' 
select sid as "Oracle_SID",tablespace_name as "Tablespace Name",
               total_bytes as "Total_Bytes(M) Allocated", 
pctusd as "Pct Used",to_char(time_stamp,'MM/DD/YYYY') as "Last Checked" 
from tablespace_percentage 
where to_char(time_stamp,'MM/DD/YYYY') = 
(select to_char(max(c.time_stamp),'MM/DD/YYYY') from tablespace_percentage c) 
--AND tablespace_name <> 'TEMP' 
--AND tablespace_name not like '%RB%' 
order by sid, time_stamp, pctusd desc, tablespace_name 
/ 
ttitle off 
btitle off 
spool off 
exit 

1.9.2 Compare Tablespace Percentages

break on report on page on sid skip 2 
column instdate new_value v_instdate noprint 
SELECT TO_CHAR(sysdate,'-dd-Mon-yyyy') instdate FROM dual; 
prompt 
ttitle 'Tablespaces' 
btitle 'Percentages - Negative number may indicate additional disk space allocated by DBA' 
break on Oracle_SID skip 2 
spool TableSpaceCompareReport&v_instdate; 
select distinct a.sid Oracle_SID 
,a.tablespace_name Tablespace 
,(a.pctusd - b.pctusd) as "Pct Changed" 
,a.pctusd "Pct Current Use" 
,to_char(b.time_stamp,'MM/DD/YYYY') as "Compare To" 
from tablespace_percentage a, tablespace_percentage b 
where to_char(a.time_stamp,'MM/DD/YYYY') = 
(select to_char(Max(d.time_stamp),'MM/DD/YYYY') from tablespace_percentage d) 
and  to_char(b.time_stamp,'MM/DD/YYYY') = 
to_char((select max(c.time_stamp) from tablespace_percentage c 
  where to_char(c.time_stamp,'MM/DD/YYYY')   <  
(select to_char(Max(e.time_stamp),'MM/DD/YYYY') from tablespace_percentage e)),'MM/DD/YYYY') 
and to_char(a.time_stamp,'MM/DD/YYYY') > to_char(b.time_stamp,'MM/DD/YYYY') 
and a.sid = b.sid 
and a.tablespace_name = b.tablespace_name 
and a.tablespace_name not in ('TEMP','RBS') 
and a.pctusd <> b.pctusd 
order by a.sid, a.pctusd desc,  a.tablespace_name 
/ 
ttitle off 
btitle off 
spool off 
exit 

1.9.3 Extent Errors

break on report on page on sid skip 2 
set pages 40 
ttitle 'Extent' 
btitle 'Errors' 
spool ExtentReport.txt 
select a.sid,a.tablespace_name,a.owner,a.segment_name,a.segment_type,
       a.next_extent,a.max_extents,a.time_stamp 
from extent_error a 
where to_char(a.time_stamp,'MM/DD/YYYY') = 
(select to_char(max(c.time_stamp),'MM/DD/YYYY') from tablespace_percentage c) 
AND a.tablespace_name <> 'TEMP' 
order by a.sid, a.time_stamp, a.tablespace_name 
/ 
ttitle off 
btitle off 
spool off 
exit 

1.9.4 Max Extents

 break on report on page on sid skip 2 
set pages 40 
ttitle 'Max' 
btitle 'Extents' 
spool MaxExtentReport.txt 
select a.sid,a.owner,a.segment_name,a.segment_type,a.seg_count,a.seg_max,
       a.extents,a.max_extents,a.time_stamp 
from max_extents a 
where to_char(a.time_stamp,'MM/DD/YYYY') = 
(select to_char(max(c.time_stamp),'MM/DD/YYYY') from tablespace_percentage c) 
AND a.owner <> 'SYS' 
order by a.sid, a.time_stamp, a.segment_name 
/ 
ttitle off 
btitle off 
spool off 
exit 

1.9.5 Project Totals

set echo off  feedback off 
column instdate new_value v_instdate noprint 
SELECT TO_CHAR(sysdate,'-dd-Mon-yyyy') instdate FROM dual; 
ttitle 'Projects' 
btitle 'Data' 
col Projects format a18 
spool ProjectDataReport&v_instdate 
select distinct d.project_name "Projects", sum(a.total_bytes)/1000 as "Total Bytes(G)" 
from tablespace_percentage a, dba_contacts d 
where a.time_stamp = (select max(b.time_stamp) from tablespace_percentage b 
where a.sid = b.sid) 
and a.sid=d.sid 
group by d.project_name 
/ 
select 'Total Storage: ' || round(sum( sum(a.total_bytes)/1000))|| ' (G)' as "Total Bytes(G)" 
from tablespace_percentage a, dba_contacts d 
where a.time_stamp = (select max(b.time_stamp) from tablespace_percentage b 
where a.sid = b.sid) 
and a.sid=d.sid 
group by d.project_name 
/ 
select  d.project_name "Projects", count(distinct d.sid) as "Number of Databases" 
from tablespace_percentage a, dba_contacts d 
where a.time_stamp = (select max(b.time_stamp) from tablespace_percentage b 
where a.sid = b.sid) 
and a.sid(+)=d.sid 
group by d.project_name 
/ 
spool off 
set echo off termout on feedback on 
exit

For More Information

• What do you think about this tip? E-mail the Edtior at tdichiara@techtarget.com with your feedback.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
• Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.