Re: Is it possible to create a System tablespace with Local managed extent?

"Daniel A. Morgan" <dmorgan_at_exesolutions.com> wrote in message news:3AADC41A.6C128381_at_exesolutions.com...
> Leaving aside the personal insults and ill-humor for a moment ... I know
> I too am occasionally guilty of having a short-temper or writing things
> in jest and not having them taken that way ... I wish to acknowledge
> that I made a mistake.
>
> I took Oracle at its word that you could create a SYSTEM tablespace as
> locally managed and finally, today, got my hands on a server where I
> could put it to the test using version 8.1.7.
>
> You can't. Oracle's statements to the contrary are incorrect.

I knew that to be the case on 8.1.5, but I had hoped you'd be correct on 8.1.7. Glad we sorted it out.

>
> And a search of METALINK substantiates this as follows:
> ================================================
 [Snip]
> -------
>
> To address locally managed system tablespaces.
>
>
> SCOPE & APPLICATION
> -------------------
>
> Database Administrators, and Oracle Support employees.
>
>
> CREATING A LOCALLY MANAGED SYSTEM TABLESPACE
> --------------------------------------------
>
> The Oracle 8i Concepts guide page. 3-8, states that you can specify
> that
> the system tablespace be created as locally managed by including the
> extent
> management local clause in your create database script......It states
> as
> follows:
>
> "For the SYSTEM tablespace, you can specify EXTENT MANAGEMENT LOCAL in
> the
> CREATE DATABASE command"
>
> The truth however, is that neither Oracle version 8.1.5 OR 8.1.6
> supports
> the concept of a locally managed system tablespace.
>

And you're saying it doesn't work in 8.1.7 either? Bummer.

> In Oracle 8.1.5, any attempt to add the EXTENT MANAGEMENT LOCAL clause
> to
> your create database command will result in a syntax error OR an
> ORA-600 [3810] error. In Oracle 8.1.6, any attempt to do so, results
> in
> no error....But the clause is ignored.
>
> Locally managed system tablespaces are simply not yet possible.
>

 [Snip]
>
> So now that we have solved that problem let me address another matter
> that has been reported.
>
> That you must have a least one rollback segment in a dictionary managed
> tablespace before you can create rollback segments in a locally managed
> tablespace. On this one my test established that if this is the case,
> the rollback segment in the dictionary managed SYSTEM tablespace
> suffices.

That's true. I think I posted originally that the one non-system rollback segment can be created in the SYSTEM tablespace: where it's housed is irrelevant. But it must NOT be the one called SYSTEM -'cos that *is* the system rollback segment, and won't suffice. On the training courses (and I believe in the sample database supplied by Oracle for 8i installs) there's one called SYSROL (just to be bloody confusing about it), which is housed in the SYSTEM tablespace -but it isn't the system rollback segment, and therefore counts for the purposes of creating your first locally managed segment.

>I had no problem creating a locally managed tablespace in
> 8.1.7 and then creating a number of rollback segments within it.
>

So there must have been 2 rollback segments before you started. One called system, and one called something else, both housed in SYSTEM tablespace. That's fine (though it's not fine from the Oracle point of view -personally, I think the need for one dictionary, non-system rollback segment is a real pain in the butt, and makes me think that locally managed anything hasn't been properly thought through. Hopefully, 9i will resolve all these issues!)

Regards
HJR
> Daniel A. Morgan
>
Received on Tue Mar 13 2001 - 02:26:45 CST