Oracle tablespace growth monitoring script

Create a tbs_usage table on the Data host to reflect the amount of data files used in the data. The tbs_timeid is the primary key of the table and is used as the id that uniquely identifies the tablespace of the database on the current day to construct tbs.

Create a tbs_usage table on the Data host to reflect the amount of data files used in the data. The tbs_timeid is the primary key of the table and is used as the id that uniquely identifies the tablespace of the database on the current day to construct tbs.

Due to the recent large increase in business volume, the growth rate of tablespaces has become very fast, and customers have begun to worry about the growth rate of tablespaces. Therefore, we also raised the need to monitor the growth of tablespaces on a daily basis. Based on the customer's needs, I wrote a simple script here. The main idea is to insert the growth rate of the tablespace queried on a daily basis into the self-built table, and then construct a query statement, it reflects the growth rate of the tablespace. The specific implementation is not as follows:

Create a tbs_usage table on the Data host to reflect the amount of data files used in the data. The tbs_timeid is the primary key of the table and is used as the id that uniquely identifies the tablespace of the database on the current day. The tbs_timeid is df. tablespace_name | "-" | (sysdate)
1. pansky users are responsible for daily management. Currently, they are mainly used to monitor the table space data volume.
SQL> create user pansky identified by pansky default tablespace users quota 50 M on users;
User created.

SQL> grant create session to pansky;
Grant succeeded.

SQL> grant create table to pansky;
Grant succeeded.

SQL> grant select on dba_data_files to pansky;
Grant succeeded.

SQL> grant select on dba_free_space to pansky;
Grant succeeded.

2. Create a tbs_usage table as a pansky user
Create table tbs_usage
As
SELECT df. tablespace_name | "-" | (sysdate) tbs_timeid, df. tablespace_name | "-" | (sysdate-1) ys_tbs_timeid, df. tablespace_name,
COUNT (*) datafile_count,
ROUND (SUM (df. BYTES)/1048576) size_mb,
ROUND (SUM (free. BYTES)/1048576, 2) free_mb,
ROUND (SUM (df. BYTES)/1048576-SUM (free. BYTES)/1048576, 2) used_mb,
ROUND (MAX (free. maxbytes)/1048576, 2) maxfree,
100-ROUND (100.0 * SUM (free. BYTES)/SUM (df. BYTES), 2) pct_used,
ROUND (100.0 * SUM (free. BYTES)/SUM (df. BYTES), 2) pct_free, (sysdate) time
FROM dba_data_files df,
(SELECT tablespace_name,
File_id,
SUM (BYTES) BYTES,
MAX (BYTES) maxbytes
FROM dba_free_space
Group by tablespace_name, file_id) free
WHERE df. tablespace_name = free. tablespace_name (+)
AND df. file_id = free. file_id (+)
Group by df. tablespace_name
Order by 8;

3. Create a primary key constraint
Alter table tbs_usage add constraint tbs_usage_pk_tbs_timeid primary key (tbs_timeid );

4. Run the update_tbs_info.sh script to update the tablespace information of the database at 07:30 every day on the crontab.
30 07 ***/Oracle10g/update_tbs_info.sh
The content of the update_tbs_info.sh script is as follows:
#! /Bin/ksh
# FileName: update_tbs_info.sh
# CreateDate: 2011-10-09
# Discription: take the basic information to insert into the table tbs_usage
PATH =/usr/kerberos/bin:/usr/local/bin:/usr/X11R6/bin:/home/oracle/bin: /home/oracle/bin:/oracle10g/app/oracle/product/10.2.0/db_1/bin; export PATH

ORACLE_SID = zgscdb1; export ORACLE_SID
ORACLE_BASE =/oracle10g/app/oracle; export ORACLE_BASE
ORACLE_HOME =/oracle10g/app/oracle/product/10.2.0/db_1; export ORACLE_HOME
PATH = $ ORACLE_HOME/bin: $ PATH; export PATH

Date>/oracle10g/log/update_tbs_info.log
Sqlplus pansky/pansky < >/Oracle10g/log/update_tbs_info.log 2> & 1
Insert into pansky. tbs_usage
SELECT df. tablespace_name | "-" | (sysdate) tb_timeid, df. tablespace_name | "-" | (sysdate-1) y s_tb_timeid, df. tablespace_name,
COUNT (*) datafile_count,
ROUND (SUM (df. BYTES)/1048576) size_mb,
ROUND (SUM (free. BYTES)/1048576, 2) free_mb,
ROUND (SUM (df. BYTES)/1048576-SUM (free. BYTES)/1048576, 2) used_mb,
ROUND (MAX (free. maxbytes)/1048576, 2) maxfree,
100-ROUND (100.0 * SUM (free. BYTES)/SUM (df. BYTES), 2) pct_used,
ROUND (100.0 * SUM (free. BYTES)/SUM (df. BYTES), 2) pct_free, sysdate time
FROM dba_data_files df,
(SELECT tablespace_name,
File_id,
SUM (BYTES) BYTES,
MAX (BYTES) maxbytes
FROM dba_free_space
Group by tablespace_name, file_id) free
WHERE df. tablespace_name = free. tablespace_name (+)
AND df. file_id = free. file_id (+)
Group by df. tablespace_name
Order by 8;
Commit;
EOF
Echo>/oracle10g/log/update_tbs_info.log

4. the SQL statement used to query the table space usage in the database. In the following example, the table space usage in 2011-10-08 and the increase in the table space volume (MB) compared with that in are displayed, and sorted in descending order according to pct_used.
Set linesize 150
Col tablespace_name for a22
Select. tablespace_name,. datafile_count,. size_mb,. free_mb,. used_mb,. maxfree,. pct_used,. pct_free, to_char (. time, "yyyy-mm-dd hh24: mi") time, (. USED_MB-b.USED_MB) increase_mb from pansky. tbs_usage a, pansky. tbs_usage B
Where a. YS_TBs_TIMEid = B. TBs_TIMEid
And. time> = to_date ("2011-11-02", "yyyy-mm-dd") and. time <to_date ("2011-11-03", "yyyy-mm-dd") order by pct_used desc;