Re: Disadvantage of Unlimited extent?

"Terry Porter" wrote:
> Excellent points from [...] Mr. Kevin Loney (if it is indeed you; )

Getting a little deep around here. Last time I checked I'm still me. Sorry for the delay in responding, my connection's been erratic.

> One place I find MAXEXTENTS UNLIMITED very useful is for Temporary
> tablespaces. It can be very useful for a database which has been
> essentially tuned with respect to its temporary tablespace need/usage, but
> then a new, unexpected (or terribly ineffecient) long-running sorted query
> gets run while you're on vacation, which might have reached maxextents
> otherwise but runs to completion instead. As long as you do not have
> autoextend turned on for your temporary tablespace, you don't have to worry
> about filling up a disk, since the worst that can happen is to fill the
> Temporary tablespace where the temp segments are being created.
>
> What is your opinion on this Mr. Loney?

Terry,

It's a judgment call, and you've made a judgment to fit your needs. You need to balance the short-term requirements ("Make my query run!") vs the long-term needs (manage your disk capacity needs). Most users don't care about the long- term needs - they just want the business processes to complete uninterrupted. There's nothing wrong with that focus on their behalf. It's up to the DBA to keep the disk infrastructure in place to support the system. See, even if you set the datafile to autoextend and the maxextents to unlimited, you can still hit a couple of limits: the maxsize of the datafile and the available free space on the disk.

It is my personal preference to control all the space in the database, including temp space and rollback segment space. By setting the temp segment maxextents to unlimited, you're allowing a single user to use unlimited temp space. That obviously meets your short-term needs; as long as it does not negatively impact your long-term needs it's fine. It's a judgment call.

And drop the Mr Loney stuff. Name's Kevin.

Kevin.
Now online at http://www.kevinloney.com.

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Wed Sep 30 1998 - 00:00:00 CDT