Mass datafile resizing

Recently I needed to extend many datafiles on a database with more than 500 tablespaces because a lot of tablespaces were reaching the critical threshold.
Autoextend was not an option due to a bug I encountered on 10gR2 RAC on ASM and AIX.

The solution was the following script: it generates statements to autoextend datafiles with usage over a defined threshold (the “80” in the where clause) to low down the percentage below another defined threshold (the “75” in the select clause).

Prior to extend it’s possible to show how much space is required to do this mass resizing:

Plot Oracle historical statistics within SQL*Plus

More than often I’m asked to investigate “what happened yesterday when performance problems appeared”.

Sometimes I have the Enterprise Manager DB Console licensed, sometimes not. Sometimes I have direct SQL*Net access to the database that I may use to produce custom reports with my LAMP self-developed application. But it may happen that only an ssh access is granted to the db server.

That’s why I started to develop some little scripts to plot the trends of database timed statistics.

Let’s see this one:

SQL> @sysstat.sql
Enter a sysstat to search for: physical reads

----------- ------------------------------------------

2263124246 physical reads
4171507801 physical reads cache
297908839 physical reads cache prefetch
2589616721 physical reads direct
2564935310 physical reads direct (lob)
2663793346 physical reads direct temporary tablespace
473165409 physical reads for flashback new
3102888545 physical reads prefetch warmup
531193461 physical reads retry corrupt

9 rows selected.

Enter the desired stat_id: 2263124246
Enter the start date (YYYYMMDD) [defaults today] : 20080922
Enter the end date date (YYYYMMDD) [defaults today] : 20080922

---------------- -------- --------
physical reads   20080922 20080922

------------------------- ---------- -------------------------
22-SEP-08 AM          0
22-SEP-08 AM     120092
22-SEP-08 AM      35780
22-SEP-08 AM       4792
22-SEP-08 AM       4905
22-SEP-08 AM       7300
22-SEP-08 AM     234596
22-SEP-08 AM      24651
22-SEP-08 AM     481884
22-SEP-08 AM     130201
22-SEP-08 AM    1300306 **
22-SEP-08 AM     491857
22-SEP-08 PM     304702
22-SEP-08 PM    1023664 *
22-SEP-08 PM    8588180 ************
22-SEP-08 PM    2201615 ***
22-SEP-08 PM   17237098 *************************
22-SEP-08 PM    1606300 **
22-SEP-08 PM     451568
22-SEP-08 PM     137684
22-SEP-08 PM     203803
22-SEP-08 PM     536394
22-SEP-08 PM   10209783 **************
22-SEP-08 PM    6151663 *********

24 rows selected.

Oh! At 4.00 PM we had a lot of physical reads. Nice.

This is the code: