Re: Advantages/disadvantages of having tables within 'SYSTEM' TS

"Mikito Harakiri" <mikharakiri_at_ywho.com> wrote in message news:<aQFMa.6$eD3.174_at_news.oracle.com>...
> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message
> news:3f02bcb6$0$18489$ed9e5944_at_reading.news.pipex.net...
> > Tablespaces give the following benefits that the "one big pot" approach
> > doesn't.
> >
> > It does give space management benefits because of quotas.
>
> Why on earth would I give different quotas per each tablespace? For example,
> user Scott has 1G quota on SYSTEM tablespace and 20G on USERS -- that sounds
> stupid to me. Any realistic motivation?

Yes. user NEWGUY has a habit of trying to copy entire TB of data into test environment ts, not realizing you have set up synonyms for the read-only majority of it.

>
> > It enables physical separation of logically separate entities.
>
> There is no such thing as physical locality. For example, we might think
> that objects are collocated on disk, but striping invalidates it. Besides,
> files do exactly that. If motivation was providing file surrogate for raw
> devices, why didn't they just call it "file"?

Is so. Striping does not necessarily invalidate it, just changes how you deal with it. The performance ideal is to avoid hot-spots, which is another way of saying some things are overly colocated. Tablespaces are collections of files - you may want to move things among volume groups as you once moved them among hard disks.

>
> > It enables differential backups of data objects with different backup
> > requirements.
>
> What if DBA initially created all-encompassing tablespace USERS, but then
> realized that he have to reorganize it into 2 just because there are
> different backup requirements? Is tablespace reorganization easy? Wouldn't
> it be more straightforward to allow DBA to backup objects that meet a
> certain criteria?

He can do that with export, or dump to flat files if necessary. The idea of hot backup requirements is to be able to recover transactions.

>
> > In particular it means that a runaway user object cannot stop the db
> > entirely from working for simple space management reasons.
>
> Wouldn't it normally be solved by figuring out what datafiles is runaway
> object is, and putting those datafiles offline?

If other users share the ts, that is a problem. If you are in the system ts, that is a big problem. And you normally don't know about the runaway object until something complains.

>
> OK, you may have a point here, tablespaces is just some grouping of objects
> that might have some utility. However, given numerous other storage
> artifacts like extents, segments, blocks, partitions, etc, the whole
> storage design looks ridiculously complex.

It may look that way for historical reasons. But really, it couldn't be simplified much given the relative importance of transaction recovery and the variety of objects possible.

jg

--
@home.com is bogus.
http://www.signonsandiego.com/news/uniontrib/tue/business/news_1b8worldcom.html