Archive

Posts Tagged ‘DBA_HIST_TBSPC_SPACE_USAGE’

Tablespace growth history

March 11, 2015 3 comments

Recently we had a tablespace space run out and ended up in an application failure.  I have questioned my DBA and he just plainly blamed the application team members who loaded large number records without a prior notice.  A convincing answer, but you can’t really fool Oracle.

Oracle introduced a new DBA_TABLESPACE_USAGE_METRICS view from 10g onwards to report the space usage with in a tablespace.  I created a new tablespace and immediately space usage was reported in the view.

<pre>SQL> select * from DBA_TABLESPACE_USAGE_METRICS  where TABLESPACE_NAME='MYDATA';

TABLESPACE_NAME                USED_SPACE TABLESPACE_SIZE USED_PERCENT

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

MYDATA                                128           64000           .2


SQL> select USED_SPACE*8/1024,TABLESPACE_SIZE*8/1024 from DBA_TABLESPACE_USAGE_METRICS  where TABLESPACE_NAME='MYDATA';

USED_SPACE*8/1024 TABLESPACE_SIZE*8/1024

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

                1                    500

SQL> select file_name,AUTOEXTENSIBLE from dba_data_files where TABLESPACE_NAME='MYDATA';

FILE_NAME                                          AUT
-------------------------------------------------- ---
/oradata1/test/mydata01.dbf                        NO

 

Both the USED_SPACE and TABLESPACE_SIZE are reported in number of database blocks.  A non-auto extensible data file reported 500MB.  I have made the data file auto extensible and the size of the tablespace grown to MAXSIZE of the data file.


SQL> alter database datafile '/oradata1/test/mydata01.dbf' autoextend on;



Database altered.





SQL> select USED_SPACE*8/1024,TABLESPACE_SIZE*8/1024

from DBA_TABLESPACE_USAGE_METRICS  where TABLESPACE_NAME='MYDATA';



USED_SPACE*8/1024 TABLESPACE_SIZE*8/1024

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

                1             32767.9844



SQL>  select * from DBA_TABLESPACE_USAGE_METRICS  where TABLESPACE_NAME='MYDATA';



TABLESPACE_NAME                USED_SPACE TABLESPACE_SIZE USED_PERCENT

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

MYDATA                                128         4194302   .003051759

That is really a cool stuff.

Now this view is captured as part of the AWR report in the history tables as DBA_HIST_TBSPC_SPACE_USAGE.  So, it is easy for a DBA to report the growth of a tablespace with in the retention period.   Even though my DBA said, the failure was because of a sudden data growth, I know where to see the data growth.  And my report was –


SQL> select TABLESPACE_SIZE*8/1024/1024,TABLESPACE_MAXSIZE*8/1024/1024,TABLESPACE_USEDSIZE*8/1024/1024,RTIME

from DBA_HIST_TBSPC_SPACE_USAGE

where TABLESPACE_ID=33  order by SNAP_ID;



TABLESPACE_SIZE*8/1024/1024 TABLESPACE_MAXSIZE*8/1024/1024 TABLESPACE_USEDSIZE*8/1024/1024 RTIME

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

                         60                             60                      59.0448608 03/07/2015 12:00:06

                         60                             60                      59.0448608 03/07/2015 13:00:21

                         60                             60                      59.0448608 03/07/2015 14:00:03

                         60                             60                      59.0448608 03/07/2015 15:00:08

                         60                             60                      59.0448608 03/07/2015 16:00:19

                         60                             60                      59.0448608 03/07/2015 17:00:26

                         60                             60                      59.0448608 03/07/2015 18:00:29

                         60                             60                      59.0526733 03/07/2015 19:00:33

                         60                             60                      59.9413452 03/07/2015 20:00:37

                 62.9384766                             90                      60.0664063 03/07/2015 21:00:41

                 62.9384766                             90                      60.0664063 03/07/2015 22:00:50

                 62.9384766                             90                      60.2773438 03/07/2015 23:00:49

                 62.9384766                             90                      61.1416016 03/08/2015 00:00:07

                 62.9384766                             90                      61.2900391 03/08/2015 01:00:12

                 62.9384766                             90                      61.2900391 03/08/2015 03:00:19

                 62.9384766                             90                      61.2900391 03/08/2015 04:00:27

                 62.9384766                             90                      61.2900391 03/08/2015 05:00:31

                 62.9384766                             90                      61.2900391 03/08/2015 06:00:08

                 62.9384766                             90                      61.2900391 03/08/2015 07:00:10

                 62.9384766                             90                      61.2900391 03/08/2015 08:00:13

                 62.9384766                             90                      61.2900391 03/08/2015 09:00:14

                 62.9384766                             90                      61.2900391 03/08/2015 10:00:22

                 62.9384766                             90                      61.2900391 03/08/2015 11:00:22

                 62.9384766                             90                      61.2900391 03/08/2015 12:00:07

                 62.9384766                             90                      61.2900391 03/08/2015 13:00:34

                 62.9384766                             90                      61.2900391 03/08/2015 14:00:26

                 62.9384766                             90                      61.2900391 03/08/2015 15:00:31

                 62.9384766                             90                      61.2900391 03/08/2015 16:00:36

                 62.9384766                             90                      61.2900391 03/08/2015 17:00:39

                 62.9384766                             90                      61.2900391 03/08/2015 18:00:41

                 62.9384766                             90                      61.2900391 03/08/2015 19:00:43

                 62.9384766                             90                      61.2900391 03/08/2015 20:00:47

                 62.9384766                             90                      61.2900391 03/08/2015 21:00:52

The growth report is clearly showing me that the space was added at 03/07/2015 21:00:41 and the growth was really normal!  Thanks to oracle.