Re: Are one row, one column tables "acceptable"?

> Hans, sorry if my phrasing was poor, but I don't create one table per
> tablespace. I have one main tablespace, APPDATA, which holds all the main
> tables for my application. I also have APPINDEX for all the indexes, and
> APPUSERS and APPTEMP.

Not an issue. Just wanted to clarify since the phrasing reminded me of a client who did have severe disk space problems.

> We actually have -tons- of diskspace available; about 300 GB free (raid 5).
> Since the disks aren't separate, I figured it was better to have smaller
> tablespaces, to avoid fragmentation, and to reduce the physical access time.
> Is my reasoning valid? I toyed with the idea of creating a 10 GB tablespace
> for my main areas, and never having to worry about tablespace ever again-
> but wound up using a 50 MB tablespace instead. (laughs) Although, our old
> system, Oracle 7, does have very small tablespaces; and our support was
> always anxious about extending our tablespace. The same fear might have
> "autoextended itself" onto me!
>

Oracle 7 and Oracle8/9 are sufficiently different that many of your fears and negative disk/table-space experiences from 7.x days can be set aside. Especially true in 9i with newer management aspects.

> I was planning on adding APPSTATIC for this little table, and the
> possibility for future tables. It's the same thing that you and Ed P agreed
> on. (Thanks for the input, Ed! I appreciate it!) But that familiar fear of
> our support team seemed to have resurfaced in my message; that's probably
> why I asked about an 8k tablespace. Looking back, I can see how that made
> no sense at all. In fact, I'm not sure why we ever listened to support. I
> found a 100 MB rollback segment in the system area that they created. Going
> through old logs, I found out that it was part of a big table archive
> operation- but they forgot to turn off the 100MB rollback segment, and we
> were running off it for 2 years!
>

<grin> With disk prices the way they are today, I don't see the sense in spending too much time minimizing tablespace sizes. I haven't create a tablespace less than 10MB in the past 5 years. I would certainly encourage you to create the APPSTATIC tablespace and consider keeping it primarily read-only as discussed.

> Daniel mentioned that 10i phases out the dictionary managed tablespaces,
> which goes back to your mention of an LMT early in this discussion (message
> ID <3E8FCE58.DA81CCE1_at_telusplanet.net> ). I read a bit about the LMT on the
> net after you mentioned it, but I didn't realize it was being phased out.
> It makes me wonder about going back to my initial setup, and recreating
> those tablespaces to be locally managed; but that'd be a hell of an export
> that I don't think would be wise on a production system. Maybe later this
> year, right after we get the 9i upgrade, I'll have a chance... anyway,
> should I just tell this APPSTATIC tablespace to be locally managed, with a
> uniform size of 256K? (That's the only mention of LMTs in my 8i DBA
> Handbook, by Oracle/Osborne Press.)

The Oracle9i Database Admininstrator's Guide has an excellent chapter on managing tablespaces and on managing LMTs. I encourage you to read that. It is freely available on Oracle's Technet site at http://otn.oracle.com

/Hans Received on Wed Apr 09 2003 - 13:51:33 CDT