RE: RE: Locally Managed Tablespaces

Jared

   I'm with you on this one. I switched our production tablespaces (except system) to autoextend several years ago and couldn't be happier. I used to scrupulously check the free space in tablespaces, but over the years, being a solo DBA, as more instances were added, this took longer and longer and meanwhile my available space window kept shrinking. I wrote scripts to help, but there are always decisions involved. Since switching to autoextend, we've experienced only a couple of incidents where something ran the disk out of space, and these turned out to be easier to deal with than the out of tablespace calls from the users.

   We use big RAID sets, so it is a matter of checking a couple of RAID sets vs. checking hundreds of tablespaces. There is also the advantage of less wasted space. If you leave enough free space in each tablespace to accommodate the largest next extent, that adds up. With LMT and autoextend, there is zero free space on most of these tablespaces.

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
From: Jared Still [mailto:jkstill_at_cybcon.com] Sent: Saturday, March 22, 2003 5:49 PM
To: Multiple recipients of list ORACLE-L Subject: Re: RE: Locally Managed Tablespaces

AUTOEXTEND can be abused for sure, but it can also be a big time saver.

Say you want to load 100 gigabytes of data, and you have 5 disks to spread it out on. You opt for 5 files of 4 gig each on each disk.

That gives you 20 files to create in your tablespace. Creating 100 gig of datafiles takes awhile. If you start each file out as 500m with a next size of 500m and a max of 4g, you can defer the time spent creating the files to load time, rather than waiting around for 100g of files to be created before you start loading.

Still takes the same amount of time, but you get to go home earlier. :)

Jared

On Friday 21 March 2003 18:23, Jacques Kilchoer wrote:
> Well, my first suggestion would be to buy a software package from a
> reputable software company that lets you predict object growth and an
> estimate of when your tablespace will be full. Contact me for more
details.
>
> :)
>
> But seriously, you can write a report that shows the number of extents and
> the amount of freespace in each tablespace, and review the report
> periodically (say once a week). Which is what I did back in my production
> DBA days. I imagine you could have a database procedure that checks the
> free space in a tablespace and sends you an e-mail, or even pages you if
> you have e-mail forwarded to a pager.
> Setting the datafiles to autoextend just pushes the problem back to the OS
> level - how do you know when your disks will be full?
>
> > -----Original Message-----
> > From: Ryan [mailto:rgaffuri_at_cox.net]
> >
> > so for normal business you should not use autoextend? You
> > should monitor it
> > yourself? What are some tips for monitoring the database to
> > see if you need
> > to extend your tablespace manually? Do you use DBMS_ALERT and
> > read the v$
> > views and then broadcast a message if you need to extend a tablespace?

Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1"
Content-Transfer-Encoding: quoted-printable
Content-Description: 
----------------------------------------

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may