We have a custom script which identifies tablespace need more space to add as it hitting warning/critical threshold. One fine morning we identified system tablespace of a database reached warning threshold and consuming almost 150 GB of space.
As a normal reaction to this alert a new datafile was added by other dba.From that point of time series of thought popped up in my mind...
1. Why system tablespace need 150 GB of space?
2. What has changed in the database?
3. None of the similar database hosted for same application has grown like that.Then why this database?
Analysis started from that thought process
Lets catch big segments in the system tablespace whose sizes are more than 1 GB.I was surprised to get this big list.
select segment_name,segment_type,bytes/(1024*1024*1024) from dba_segments where tablespace_name='SYSTEM' and bytes/(1024*1024*1024)>1;
At this point I have no idea what these mviews are why it is contributing so much of space.I was little bit hesitant to drop as it is under system tablespace and naming convention gives an impression something automatically generated.
I have even extracted DDL of these mviews but no clue what to do with these...
At this point metalink search has helped me to find what are those mviews.
Below is the metalink note
How To Remove Invalid MV$$_ Materialized Views in the SYSTEM Schema (Doc ID 1571344.1)
I came to know those mviews are effect of running SQL access advisor and kill the process by timeout settings.
I can see same info when I ran awrinfo.sql
OWNER/ADVISOR TASK_ID/NAME CREATED EXE_DURATN EXE_CREATN HOW_C STATUS
-------------- -------------------------------- ---------------- ---------- ---------- ----- ------------
TASK_DESC
--------------------------------------------------------------------------------------------------------------
ERROR_MSG
--------------------------------------------------------------------------------------------------------------
SYS/SPM Evolve 23/SYS_AI_SPM_EVOLVE_TASK 08:50:24 (08/17) AUTO INITIAL
Description: Automatic SPM Evolve Task
Error Msg :
SYS/SQL Perfor 24/SYS_AI_VERIFY_TASK 08:50:24 (08/17) AUTO INITIAL
Description:
Error Msg :
SYS/SQL Access 25/SYS_AUTO_INDEX_TASK 08:50:24 (08/17) AUTO INITIAL
Description:
Error Msg :
SYS/Statistics 27/INDIVIDUAL_STATS_ADVISOR_TASK 08:51:07 (08/17) CMD INITIAL
Description:
Error Msg :
SYS/SQL Access 14043/SQLACCESS7862804 13:27:18 (01/09) 18,358 18,460 CMD FATAL ERROR
Description: SQL Access Advisor
Error Msg : ORA-04021: timeout occurred while waiting to lock object
Now ideally those mviews should be in invalid state without any data.
From this point climax starts.
Recently I have migrated character set of that database from UTF8 to AL32UTF8.
At the last step post conversion it was refreshing some mviews and I did not pay much attention to that. So during that step itself it has grown a lot and finally later point of time it has hit threshold.
Final solution:-Just executed below PL/SQL statement and it has dropped all mviews
exec DBMS_ADVISOR.DELETE_TASK('SQLACCESS7862804');
Still it did not release space.
So I had to resize datafiles upto highwater- mark using below document.
How to Resolve ORA-03297 When Resizing a Datafile by Finding
the Table Highwatermark [ID 130866.1]
Hope you have enjoyed...
