Tracking database tablespace growth
These scripts will allow tracking of growth for an Oracle database, and flag possible error conditions before they occur.
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
Create the SQL to insert the monitored database information into the repository.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 [email protected]' || (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, [email protected]' || (select dba_contacts.DB_LINK from dba_contacts where dba_contacts.db_link = dba_db_links.db_link) || ' free, (select whatsid.NAME from [email protected]' || (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 [email protected]' ||
(select dba_contacts.DB_LINK from dba_contacts
where dba_contacts.db_link = dba_db_links.db_link) || ' s,
(select whatsid.NAME from [email protected]' ||
(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 [email protected]' ||
(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 [email protected]' || (select dba_contacts.DB_LINK from dba_contacts
where dba_contacts.db_link = dba_db_links.db_link)
|| ' e , [email protected]' ||
(select dba_contacts.DB_LINK from dba_contacts
where dba_contacts.db_link = dba_db_links.db_link) || ' s,
(select whatsid.NAME from [email protected]' ||
(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/[email protected] @tablespace exit;
1.7.2 Create file extent_error.bat
sqlplusw system/[email protected] @extent_error exit;
1.7.3 Create file max_extents.bat
sqlplusw system/[email protected] @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
You can create batch jobs to schedule the reporting aspects. Follow the examples above.1.9.1 Tablespace Usage
- Modify the scripts according to your own requirements.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
- Modify the scripts according to your own requirements.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
- Modify the scripts according to your own requirements.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
- Modify the scripts according to your own requirements. 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
- Modify the scripts according to your own requirements.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 [email protected] 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.
