database capacity planning for running database

storage administrator may not know the annual growth in database, while the database may keep growing. As DBA we need to regularly , say yearly, review the growth of critical database, before it is too late to realize that we have no space to grow in SAN.


Below are few areas to drill down for careful review. 

    1. Identify main contributers (tablespaces) to the growth. To achieve this, ideally you have job to record the tablespace used, total size daily , or simply can based the data file creation timestamp in dba_data_files.  Create a spreadshee to calculate space needed for coming two years, using dimension tablespace  and mount point name.



  2. Return space to disk by shrink down data files of over-allocated tablespace. Or even you can drop unused tablespace.
 
 
  3.  Check if any housekeeping job is not paused accidentally. If there is big table in the tablespace and keeps growing, check with application if housekeep can be taken.  
  
  4. Check tablespace with uniform extent size , especially for uniform size >= 10MB, make sure no space wasted due to forget to consider 8x8k or 4x32k header overhead for each data file.

fix the database date

SQL> select sysdate from dual;

SYSDATE
---------
03-JAN-11

SQL> !date
Tue May 31 09:33:05 SST 2011

SQL> alter system set fixed_date=none;

System altered.

SQL> select sysdate from dual;        

SYSDATE
---------
31-MAY-11