Re: Undo management manual

Howard J. Rogers wrote:
>
> 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)
> >
> >

Howard - don't be lazy... you just use

SQL> exec dbms_output.put_line(sqlerrm(-1552));

on NT

:-)

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."