sdruss
asked on
Tablespace Creation with Autoextend
Given the following create tablespace statement:
CREATE BIGFILE TABLESPACE DATA01
DATAFILE '+DATA'
SIZE 5g
AUTOEXTEND ON NEXT 25M MAXSIZE 250G;
Initially the tablespace above is create with 5Gig. When do the 25Meg extension get added; when we are within 98% of the 5Gig being utilized?
CREATE BIGFILE TABLESPACE DATA01
DATAFILE '+DATA'
SIZE 5g
AUTOEXTEND ON NEXT 25M MAXSIZE 250G;
Initially the tablespace above is create with 5Gig. When do the 25Meg extension get added; when we are within 98% of the 5Gig being utilized?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Mark Geerlings
I agree with sdstuber. Also, keep in mind that space used (and/or available free space) still needs to be monitored at some level even if you use "autoextend" on your Oracle tablespaces. This just pushes the management of free space out one level, from the database to the O/S (or storage system). If your ASM disk group (or disk volume if you don't use ASM) ever has less available free space than what Oracle tries to use when "autoextend" attempts to expand a tablespace, you can still experience a database shutdown.
ASKER
So the extension is only made when additional space is necessary to fulfill the request? Just thinking out loud ... why not create tablespace with max and cut to the chase?
The max of a big file tablespace is 128 Terabytes. I suppose if you just happen to have that much space sitting around and you're sure you'll use it all, then go for it. If you might not use all of that, then it's better to let the space extend naturally as needed and not waste the storage on empty allocations.
Even if you pick an arbitrary smaller maximum, like 250Gb, if you don't know that you'll need it, then why allocate it?
If, on the other hand, you know you will use all of it, then there is no harm in preallocating.
Even if you pick an arbitrary smaller maximum, like 250Gb, if you don't know that you'll need it, then why allocate it?
If, on the other hand, you know you will use all of it, then there is no harm in preallocating.
My biggest problem with autoextend is a rogue query. Some query can go out there and suck up all the space that is out there unchecked. Then your system is down because there is not space anywhere and you have to undo whatever that rogue query did. This happens most with TEMP, but can happen with any tablespace.
I'm also not a fan of "autoextend". The space still needs to be managed at some level. I prefer to manage space used and free space inside the database, closer to where it is actually consumed. With "autoextend" the burden just shifts from a DBA to a system administrator or storage administrator, who only knows: "Oracle wants more space" but why or for what? A system or storage admin typically has no clue what may be using space inside the database.