Andrew Channels Dexter Pinion

Wherein I write some stuff that you may like to read. Or not, its up to you really.

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

Advertisements