ORA-1654 Error in Alert Log file

Today while running health check report on one of our databases I came across the following error in the alert log file

ORA-1654: unable to extend index PERFSTAT.STATS$SQL_PLAN_USAGE_PK by 2048 in tablespace TOOLS
Tue Jan 27 05:00:09 2009
Errors in file c:\oracle\admin\ssg\udump\ssg_j000_2660.trc:
ORA-12012: error on auto execute of job 161
ORA-01654: unable to extend index PERFSTAT.STATS$SQL_PLAN_USAGE_PK by 2048 in tablespace TOOLS
ORA-06512: at "PERFSTAT.STATSPACK", line 1487
ORA-06512: at "PERFSTAT.STATSPACK", line 2442
ORA-06512: at "PERFSTAT.STATSPACK", line 91
ORA-06512: at line 1

Looking at this error on can easily guess what went wrong , TOOLS tablespace is full. Now how can we confirm this , here is the query with which you can check if your tablespace is running out of space.

Query to check Tablespace Usage :

set linesize 120
col tablespace_name format a22 word_wrapped heading "Tablespace Name"
col mb_free format a16 heading "Free MB"
col fragments format 99999999 heading "Fragments"
col biggest_bit format a16 heading "Biggest Bit"
col total_mb format a16 heading "Total MB"
SELECT
TSP.TABLESPACE_NAME T_NAME,
TSP.TOTAL_SPACE TOT_SPACE,
FREE.TOTAL_FREE,
ROUND(FREE.TOTAL_FREE /TSP.TOTAL_SPACE*100) PCT_FREE,
ROUND((TSP.TOTAL_SPACE - FREE.TOTAL_FREE),2) TOT_USED,
ROUND((TSP.TOTAL_SPACE - FREE.TOTAL_FREE)/TSP.TOTAL_SPACE*100) PCT_USED
FROM
(SELECT
TABLESPACE_NAME,
SUM(BYTES)/1024/1024 TOTAL_SPACE
FROM
DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) TSP,
(SELECT
TABLESPACE_NAME,
ROUND(SUM(BYTES)/1024/1024,2) TOTAL_FREE,
ROUND(MAX(BYTES)/1024/1024,2) MAX_FREE
FROM
DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) FREE,
(SELECT
TABLESPACE_NAME,
ROUND(MAX(NEXT_EXTENT)/1024/1024,2) MAX_NEXT_EXTENT
FROM DBA_SEGMENTS
GROUP BY TABLESPACE_NAME) NEXTEXT
WHERE
TSP.TABLESPACE_NAME = FREE.TABLESPACE_NAME (+)
AND TSP.TABLESPACE_NAME = NEXTEXT.TABLESPACE_NAME (+)
AND ((ROUND(FREE.TOTAL_FREE/TSP.TOTAL_SPACE*100)) <> FREE.MAX_FREE);

This will show you the data for all the tablespaces.

Now we need to check which datafile is associated with this tablespace tools

we can run the following query

SQL> select substr(file_name,1,50), tablespace_name from dba_data_files where tablespace_name='TOOLS';


SUBSTR(FILE_NAME,1,50) Tablespace Name
-------------------------------------------------- ----------------------
C:\ORACLE\ORADATA\SSG\TOOLS01.DBF TOOLS

Now we need to extend the datafile associated with this Tablespace.

we can now run the following command to do the same.

alter database datafile 'C:\ORACLE\ORADATA\SSG\TOOLS01.DBF' resize 2600M;

After running this command we can again run the Query to check Tablespace Usage and we should have enough space in Total_Free as shown in image below.


I have altered it to have 600MB more space as 2000 MB TOOLS TS was full so now it's showing those 600MB as free.

Cheers
Aashish