Re: Undo management manual

I DO SO WISH PEOPLE WOULD INCLUDE ERROR MESSAGE TEXT, and not just the numbers.

Some of us run on Windows. On Windows, you can't just knock up a quick 'oerr ora 1552' to find out what the hell you are talking about (and clever workarounds notwithstanding, this statement is definitely true when you install and re-install for testing purposes on a regular basis).

However, my trusty elves tell me that 1552 is the infamous "cannot use system rollback segment for non-system tablespace" error.

And since you receive this error when trying to create your first ever rollback segment, the issue is clear.

First the general principles. There is a system rollback segment, and it is used exclusively for updates to tables in the SYSTEM tablespace. You can;t use it for any updates which affect non-system tablespace. Your rollback segment tablespace is (I'm guessing here, but feel pretty confident) locally managed. That means that when a segment is created in it, we have to update that tablespace's bitmap to record the fact that an extent or two have been used. That bitmap is stored in the beginning of each tablespace's datafiles.

So can you see the logical conculsion here? You are trying to update something (the bitmap) in a non-SYSTEM tablespace. The only rollback segment you have is the SYSTEM one. It can't be used to record the rollback for updates in non-SYSTEM tablespace. Catch 22 huh?

The solution is simple: create a rollback segment in a DICTIONARY managed tablespace first. The creation of such a segment is only recorded in the data dictionary tables -and the data dictionary is in the SYSTEM tablespace, so the SYSTEM rollback segment will do just fine and dandy. With that one faux rollback segment in place, you can now create your proper rollback segments,because there is a non-SYSTEM rollback segment handling the rollback of the bitmap updates its creation involves. Once you bring that new rollback segment online, all will be fine -the original faux rollback segment can be dropped, and it will all still work.

I had a paper on exactly this issue at my website, until I was told to take it down. So I can't point you to it. But the short answer is:

create rollback segment temp1 tablespace SYSTEM; alter rollback segment temp1 online;
create rollback segment real1 tablespace <name of locally managed tablespace here>;
alter rollback segment real1 online;
alter rollback segment temp1 offline;
drop rollback segment temp1;
create tablespace real2 tablespace <etc>; and so on.

And please: always include the full text of error messages, not just the numbers. For many, it involves a trip to the Oracle website to check the error text documentation. Which means most people won't bother.... and then you deprive yourself of a lot of potential help. Regards
HJR "Michele brugo" <michele.brugo_nospam_at_pic-informatica.it> wrote in message news:h5uniu84dhjqb9lbgrgfrgvkb2t760qut8_at_4ax.com...
> Hi, i'm trying to create my first database on 9i (release 9.2.0)
>
> After create database (it runs succesfully) i'm trying to create
> tablespace for rollback.
> i'v setted undo_management = manual but when i'm trying to create a
> public (or not public) rollback segment on tablespace RBS
> i receive ora-01552
>
> this is only a test but i don't know what is wrong.
>
> Tia
>
> Mike
>
> (remove _nospam for reply in mail)
>
>
Received on Wed Jul 10 2002 - 04:57:47 CDT