January 05, 2006
Will my tablespace auto extend?
Today we had an alert message from one of our applications telling us that a tablespace in one of our Oracle databases had run out of space. As our DBA is working the night shift and his cover hadn't arrived in the office it was left to me (the backup DBA's backup) to figure out what was going on. My first question was "Will this tablespace auto extend?" Because if it would I could get back to my coffee.
Suprisingly enough I didn't know how to easily figure this out. I had a look at DBA_TABLESPACES but that didn't offer any obvious clues. A little searching led me to this tip of the week which amongst other things alerted me to the fact that in Oracle it's your data files that are set to auto extend, not the tablespace itself.
So given that I know that my USERS tablespace was having a spot of bother I tried this query;
SELECT file_name, autoextensible FROM dba_data_files WHERE tablespace_name = 'USERS'
And sure enough the autoextensible column for the single data file in this tablespace said NO. So I added another datafile and went back to reading Bloglines.
Posted by Andy Todd at January 05, 2006 08:14 AM