Re: Newbie: Creating new database files

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Thu, 2 Nov 2006 03:03:26 GMT
Message-ID: <J831tt.GL5@igsrsparc2.er.usgs.gov>

Geoff Muldoon wrote:
> jim_at_ponder-stibbons.com says...
>

>> Muldoon <geoff.muldoon_at_trap.gmail.com> writes
>>>  But *why* you'd bother
>>> to set up separate tablespaces for each schema/user (read that as each
>>> "database" in Access terms) just to simulate what you used to do in Access
>>> is the real question. I can't think of a decent reason why you would.
>>>
>> Separating applications by tablespace is a very common practice.

>
> Commonly done doesn't necessarily mean sensibly done. I've seen similar
> debates here about the supposed benefits of separating tables and indexes
> into separate tablespaces. Advantages of separate tablespaces per app?
>
> Geoff M

Another reason to keep your app's segments in separate tablespaces is to help you keep one app's activity from affecting another as much as possible. If AppA and AppB are in the same tablespace and AppA has some rogue process extending a table by a large amount, AppB might run out of free space that it needs. In addition, I can put AppA's segments on a set of disk volumes and keep AppB's segments on another set of disk volumes, ensuring the I/O from the two apps are seperate. You cannot do this if they are in the same tablespace. Also, Tablespace Point In Time Restores will affect more than one app if you put all of the segements in the same tablespace.

I always make it part of my "best practices" to never mix application's segments in the same tablespace. This is not from any performance standpoint, but for reasons of manageability. And I consider all of the above...plus what Joel added...as being very sensible.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Received on Wed Nov 01 2006 - 21:03:26 CST