set a rollback segment for a specific user
Steven
I'm wondering if it is possible to set a specific rollback segment for a
user so that user only uses the specified rollback segment. My problem is
that sometimes I perform large transactions and want to use the large
rollback segment. However, I cannot specify the rollback segment using "set
transaction......" because I am accessing the data through an application.
Any help will be appreciated,
Regards,
Steve.
Epicentre team A
Yes you can assign a rollback segment to specific transaction.
SET TRANSACTION USE ROLLBACK SEGMENT nom du segment.
----------------------------------------------------------------------------
------------------------------
Très cordialement
Denis Jeanneret - Directeur Technique, Division ORACLE
----------------------------------------------------------------------------
------------------------------
"Steven" <steven....@gmx.net> wrote in message
news:ahrlbh$vi73t$1...@ID-82797.news.dfncis.de...
Niall Litchfield
1. Only run your large transactions in off hours having first taken all
other rollback segments offline. This may well not be attractive to you.
2. Size all rollback segments equally and large enough to hold the data
required for your largest transaction. This would be the preferred advice in
these days of exceptionally cheap storage.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
"Steven" <steven....@gmx.net> wrote in message
news:ahrlbh$vi73t$1...@ID-82797.news.dfncis.de...
Pete Sharman
not guaranteed that other users won't use the same rollback segment.
HTH. Additions and corrections welcome.
Pete
SELECT standard_disclaimer, witty_remark FROM company_requirements;
Daniel Morgan
Which leads to the obvious question. Why hasn't Oracle implemented private
rollback segments? I can assign a user a default tablespace, a temp tablespace,
why not user-specific rollback? It seems to me it would be a technical no-brainer.
CREATE USER xyz
IDENTIFIED BY xyz
DEFAULT TABLESPACE abc
TEMPORARY TABLESPACE def
ROLLBACK SEGMENT ghi
QUOTA ... ON
It would just be one more way for DBAs to control resource utilization and I/O.
Daniel Morgan
Pete Sharman
>
>Pete Sharman wrote:
>
snip
>
>Which leads to the obvious question. Why hasn't Oracle implemented private
>rollback segments? I can assign a user a default tablespace, a temp tablespace,
>why not user-specific rollback? It seems to me it would be a technical
>no-brainer.
>
>CREATE USER xyz
>IDENTIFIED BY xyz
>DEFAULT TABLESPACE abc
>TEMPORARY TABLESPACE def
>ROLLBACK SEGMENT ghi
>QUOTA ... ON
>
>It would just be one more way for DBAs to control resource utilization and I/O.
>
>Daniel Morgan
>
You're asking me why Development hasn't done something? Zip idea here I'm
afraid! :)
In any case, I think you're more likely to see the opposite to waht you're
after. As AUM (automatic undo management) becomes smarter and smarter at doing
its job, you're probably going to see more automatic stuff happening, rather
than more control for the DBA. Of course, this is just my navel gazing, so I
could be completely wrong!
Daniel Morgan
If they think they are going to be successful by turning the Oracle RDBMS into SQL
Server for UNIX they are going to lose a lot.
Provided automated management is fine. But they should keep adding features for
those skilled enough to want the very best.
Daniel Morgan
Karen
I think it is DBMS_TRANSACTION.USE_ROLLBACK_SEGMENT,
although I can be mistaken.
You probably do not need to research how to assign a rbs to a user.
Regs
AK
Karen
transaction?
Here are other options, equally or more inattractive:
3. Have one rollback segment for all transactions. Contention for segment
header is
your friend, but this might work.
4. Oracle assigns rollback segments in round-robin fashion, but according to the
number
of active transactions in the segment. Then, to "reserve" the one we need you
can simply
start several sessions and leave them running forever.
5. (This is crazy one, but hey). Before starting a transaction, online the
rollback segment,
assign to it, and ... immediately offline it. Oracle will block attempts of
other transactions
to use this segment, but it should let your transaction complete. So you are
left alone using
the rollback segment, maybe with just a few other transactions which might
squeeze in before
you offline it. The biggest challenge here is how to offline a rollback segment
in the same
transaction. The ALTER ROLLBACK SEGMENT command is a DDL and it will do a
commit internally, so your SET statement will not work. To work around it,
maybe you
can use an autonomous transaction just to offline a rollback segment. Let
others know if
you get this to work.
Maybe some of these will work for the OP? However, I think he just needs a
DBMS_TRANSACTION call.
Best regards
AK
PS. Btw, can someone explain to me what cross-posting is? Did I cross-post when
I answered
two different emails in the same thread? If so, is there something wrong with
it?
Steven
"Niall Litchfield" <n-litc...@audit-commission.gov.uk> wrote in message
news:3d415e0f$0$238$ed9e...@reading.news.pipex.net...
> You have only two options that I am aware of.
>
> 1. Only run your large transactions in off hours having first taken all
> other rollback segments offline. This may well not be attractive to you.
> 2. Size all rollback segments equally and large enough to hold the data
> required for your largest transaction. This would be the preferred advice
in
> these days of exceptionally cheap storage.
creating large rollback segments is appealing to me but won't this affect
performance of smaller transactions? We have lots of very small
transacitons just inserting one row, updating one row. Is this a major
problem?
Sybrand Bakker
"Karen" <ab...@ureach.com> wrote in message
news:3D42319E...@ureach.com...
[snip]
> PS. Btw, can someone explain to me what cross-posting is? Did I
cross-post when
> I answered
> two different emails in the same thread? If so, is there something wrong
with
> it?
>
>
cross-posting is posting
to comp.databases.oracle,
comp.databases.oracle.server,comp.databases.oracle.tools,
comp.databases.oracle.tools
Usually done by people who
a) don't know the distinction between the groups
b) think they get a bigger and better audience
Many newsreaders will be able to cope with cross-posts, provided the post
has the same message-id, but you still have to download the header in all
groups.
Answers will be posted to all groups
*Multi*posting is posting the same message independently to all groups.
Answers will NOT be posted to multiple groups, and other groups won't see
the question has already been responded to.
Many people, especially those that never lookup manuals, and never search
teh archives at http://groups.google.com do either one of them.
b) above is definitely not true. The top-10 responders monitor all Oracle
groups and there are providers who don't transmit comp.databases.oracle (the
original newsgroup) anymore
Both cross- and multiposting are just a waste of bandwith and a waste of
time, and as Usenet is a voluntarily business (with the exception of Thomas
Kyte probably no one gets paid for answering Usenet questions), that should
be avoided at all costs.
Regards
--
Sybrand Bakker
Senior Oracle DBA
to reply remove '-verwijderdit' from my e-mail address
Niall Litchfield
a
> transaction?
Unfortunately not. This is to ensure that a specific user account only gets
to use one named rollback segment. The OP is aware of SET TRANSACTION USE
ROLLBACK SEGMENT BLAH but can't use it (or the equivalents you suggest)
because this is a packaged app.
And cross posting means posting to more than one newsgroup in (vain) hope
that you'll get a larger audience or more replies. All it actually does is
waste bandwidth. Answering two questions (or more) in one post is fine so
keep it up.
Niall Litchfield
> creating large rollback segments is appealing to me but won't this affect
> performance of smaller transactions? We have lots of very small
> transacitons just inserting one row, updating one row. Is this a major
> problem?
I believe that the answer to this is no. the implication is only AFAIK in
terms of space requirements. Your small (OLTP I guess) transactions will
still only use the same number of blocks - probably one- that they always
did. That said I don't have any experiments to prove it. I will say that our
OLTP system uses 10mb extent sizes for rollback segments and these have
never caused a performance problem (except of course when we filled the
tablespace :-( ). It uses these relatively large rollback segs because we
also do online reporting on the same db.
Karen Abgarian
if it was his application or not.
Sorry for confusion I might have created and thanks to you and
Sybrand for explaining the cross-posting phenomenon to me
Regs
AK
Paul Brewer
news:ahsi4...@drn.newsguy.com...
Pete,
Yes, we'll use automatic undo management when it's fixed.
Meantime, those of us with production systems to run will soldier on with
rollback segments.
Paul
Pete Sharman
>
snip
>
>If they think they are going to be successful by turning the Oracle RDBMS into
>SQL
>Server for UNIX they are going to lose a lot.
>
>Provided automated management is fine. But they should keep adding features for
>those skilled enough to want the very best.
>
>Daniel Morgan
>
Totally agree. In fact, to turn Oracle into SQL*Server you'd need to ADD a lot
more parameters - TABLE_LOCKING=ALWAYS and RUN_FAST=FALSE spring to mind
immediately! ;)
I have been making a fairly similar point to Development myself at some recent
meetings, and I believe they understand where I (and others) are coming from
here. It's great to make the database easier to manage, and I can see why they
want to do that. But it shouldn't be at the expense of taking away flexibility
from the advanced users UNTIL we're guaranteed that the database does a better
job than manually fiddling with things does. I think we're starting to see the
tide turn that way on some things (LMT's vs. DMT's for example). Do you want to
go back to the days when you use to have to manage everything with a fine tooth
comb? Probably not. Let's free up the DBA's time from mindless drivel that can
be automated and leave them free to worry about more important things like
performance, availability, capacity planning and so on. I ain't sayin' Oracle's
perfect. No software is, so there's always a need for someone who knows their
stuff, but let's worry about the RIGHT stuff, not the boring stuff.
Richard Foote
I think the answer to your question is the very big danger to performance
for the great many who would abuse such an option.
Currently, Oracle does a pretty good job of automatically spreading the load
across the available rollback segments (even better so in 9i). Potential
issues with specifying a RBS for a user includes:
1) Performance issues if users are not correctly spread across the RBSs
(note if you can set it for one user, you can set it for all)
2) How to determine the amount of undo a particular group of users will
generate at a particular point in time. Real danger of specific RBS being
overloaded, while other RBS have no load if this is not calculated correctly
(and it is virtually impossible to calculate correctly)
3) Issues of specific RBS being available
When I close my eyes and imagine all the issues that can arise if RBSs were
assigned to individual users, it's not a pretty sight. The fact that Oracle
spreads undo load per transaction is actually a very clever and efficient
way to manage things. The fact that undo segments now can interact and
"borrow" storage from each other is very nice (when it works :(.
The option to set a transaction to use a specific RBS was the way to have
some control over this (which is now of course unnecessary in 9i (when it
works :( again.
I for one would really question such a user option that assigned an RBS to a
user. It would cause many more issues than it solved.
Then again, I could be wrong :)
Richard
"Daniel Morgan" <dmo...@exesolutions.com> wrote in message
news:3D41797B...@exesolutions.com...
Niall Litchfield
> Totally agree. In fact, to turn Oracle into SQL*Server you'd need to ADD
a lot
> more parameters - TABLE_LOCKING=ALWAYS and RUN_FAST=FALSE spring to mind
> immediately! ;)
Not to mention SINGLE_PLATFORM=TRUE, and DB_BLOCK_SIZE="8K SO THERE."
<snip>
> It's great to make the database easier to manage, and I can see why they
> want to do that. But it shouldn't be at the expense of taking away
flexibility
> from the advanced users UNTIL we're guaranteed that the database does a
better
> job than manually fiddling with things does. I think we're starting to
see the
> tide turn that way on some things (LMT's vs. DMT's for example). Do you
want to
> go back to the days when you use to have to manage everything with a fine
tooth
> comb?
I'm in two minds about this. I do think Oracle should be easier to manage. I
rather suspect that most people who use it do just install take all the
defaults and run for a while (until they get a consultant in) maybe. To be
honest this ought to be an acceptable approach to software installation
(just as it is with hardware). Of course actually having sensible defaults
would help in this!
OTOH I don't believe that we can ever 'guarantee that the database does a
better job than manually fiddling does'. I suspect that *for the skilled
professional* it will always be possible to outperform the softwares
automatic (but general) choices by specific manual tuning.
I believe we are getting to the point where this 'manual fiddling about'
(surely high performance technology engineering <g>) becomes more and more
pointless since the software is 'good enough'. The question then becomes do
you leave the ability to manually tune or do you just say this is too
dangerous and more often than not will leave the database in a worse state
than it was before.
Daniel Morgan
I would totally agree with you with one exception.
That is the very large number of systems on which I have been called in where
they used:
SET TRANSACTION ...
to try to beat the rollback segments and ended up making such a huge and
horrific mess as to be worthy of little but pity.
Nothing Oracle could do could be worse that what developers do when they find a
tool, misunderstand it, misapply it, and misuse it.
It would be nice to have an option that actually did what people seem to think
they need.
For me ... I'd like it for doing those 2:00am batch jobs that need one massive
RBS when everything else on the system is OLTP.
Daniel Morgan
Daniel Morgan
This doesn't actually work for a number of reasons. Among them:
1. Everyone goes for the largest RBS
2. There is no guarantee that the specific RBS you are trying to get is
available.
But that certainly has not stopped numerous developers, perhaps you too, into
using it without understanding what it can potentially do to a system which is
bring it to its knees.
Daniel Morgan
Pete Sharman
>
>"Pete Sharman" <peter....@oracle.com> wrote in message
>news:ahvbn...@drn.newsguy.com...
>> Totally agree. In fact, to turn Oracle into SQL*Server you'd need to ADD
>a lot
>> more parameters - TABLE_LOCKING=ALWAYS and RUN_FAST=FALSE spring to mind
>> immediately! ;)
>
>Not to mention SINGLE_PLATFORM=TRUE, and DB_BLOCK_SIZE="8K SO THERE."
><snip>
Oh yes, I'd forgotten those ones! :)
Totally agree again. The problem Development faces I believe is that there just
aren't enough Jonathan's out there (when are we gonna be able to clone this
guy?!). First impressions count for a lot, so there are a lot of people out
there that think Oracle is hard to manage. I don't believe it is. I believe
it's complex, as it needs to be to include all the functionality it has. Does
that mean it's hard to manage? No, but it generally does mean that you may want
someone who knows what they're doing to help you when you're using more complex
functionality. If you move to Oracle simply as a datastore (a spreadsheet on
steroids), then it will generally do what you want without too much work. If
you want more advanced functionality, I don't think it's too much to understand
that it comes sometimes at the price of more complexity from the management
perspective.
WHat it basically boils down to is a shortage of "skilled professionals". We
need to walk a fine line between making the database less difficult to manage
and making the database less flexible to manage. How well we walk that line is
probably open to debate, but I can see why things are moving the way they are.
Daniel Morgan
I agree and would add that ... part of the reason people perceive Oracle as hard,
and/or expensive, to manage is that in truth they never received decent training. An
OCP class is not training. Oracle Education is truly not training. So where does one
really go to learn how to take an H/P or Sun box with nothing on it other than the
O/S and install and configure Oracle? Where do you learn how to prepare for
different types of apps with hundreds or thousands of users, less than perfect disk
configurations, and a SysAdmin that thinks the answer to every problem is RAID5?
What we really need is hands-on training equivalent to what other professions have
for their practitioners.
And for those of us earning in six figures ... be careful what you wish for. If they
turn Oracle into SQL Server Plus we will be getting paid what SQL Server types get
... and that is not on my list of financial losses I would like to sign up for.
Daniel Morgan
Niall Litchfield
> I agree and would add that ... part of the reason people perceive Oracle
as hard,
> and/or expensive, to manage is that in truth they never received decent
training. An
> OCP class is not training. Oracle Education is truly not training.
I disagree Oracle education is training (/pedant though it may not be
education /pedant). The trouble is education only gets you so far. As Connor
said to me in an entirely different post "experience is what you get when
you were expecting something else". training doesn't substitute for
experience - it merely prompts it. IMO anyway.
I wan't to be clear though I learned a lot from oracle training. It was
worth the money. Actually let me say that again with emphasis ORACLE
TRAINING IS WORTH THE CASH. The problem is that folk think you can do 3
weeks worth of classes and end up with Jonathon/Nuno/Sybrand/Connor's level
of expertise. You can't. It does on the whole though prevent you from
commiting planned stupidity;this is A Good Thing (tm) The problem is not
the training its the expectations. We can argue over who is responsible for
these. I won't be budged on oracle education being value for money though.
> And for those of us earning in six figures
Please don't do this to me you know it hurts <g>.
Niall Litchfield
> Totally agree again. The problem Development faces I believe is that
there just
> aren't enough Jonathan's out there (when are we gonna be able to clone
this
> guy?!)
However I'd be willing to bet that a lot of Jonathan's extensive knowledge
comes from some key attributes that ISTM he possesses.
1. He reads and learns. You know - the documentation.
2. He tries things out in a controlled way and documents them to see if they
work as advertised.. This is really the key test.
3. Um and he takes things to the limit
Vis from 2001
"Have you seen what happens
to I/O on the SYSTEM tablespace when your
database has 25,000 tablespaces !!!
(Unless you set events 10061 and 10269, or
hack sql.bsq of course).
"
I somehow rather suspect that the above scenario wasn't part of QA for 9i.
In other words there will be more Jonathans when more of us start
documenting our claims, testing and refining them. This probably requires
the db to be more flexible and more documented rather than less.
Cheers.
Richard Foote
Oracle Education most certainly *IS* training. A few points.
1) The core DBA curriculum is primarily aimed at the new/novice Oracle DBA.
It's vital that the basics, the foundation knowledge and skills be in place
before anyone can develop into a master. Oracle Education courses are
excellent in developing these core skills and competences. Once in place,
professional growth based on an correct understanding of how Oracle
functions can take place.
2) Some courses are better (eg. Backup and Recovery) than others (eg.
Performance Tuning). None are perfect but overall they serve their purpose.
It's often the quality or otherwise of an instructor and the additional
input they provide that makes or breaks a particular course.
3) The fact the Oracle courses are so tied with OCP is unfortunate. OCP has
its weaknesses (no argument there), but that shouldn't distract from the
importance and indeed relevance of most of the training available.
4) These courses should only be viewed as a *START* to the learning process.
Nobody seriously suggests that a couple of weeks of training means you are a
fully competent, Jonathon level guru. The fact that the OCP program suggests
otherwise is, as I said unfortunate. But what it does mean is that you have
all the necessary prerequisites to professionally grow and develop without
incorrect assumptions or blind spots hindering or warping this. It's the
subsequent experience and the trials and errors of being a DBA in a
professional environment that completes the process (if it's ever really
complete). And this takes time ...
5) Value for money is a difficult one. I personally believe Oracle training
is too expensive and that some of the courses are a bit long and verbose.
However, it was a rare customer who left my classes not satisfied with the
training they received. Many, many of them have subsequently developed into
excellent DBAs. How they would have developed without the training or with
different training is difficult to say but much feedback suggests that the
training they did receive was an important and worthwhile first few steps.
My thoughts :)
Richard
"Niall Litchfield" <niall.li...@dial.pipex.com> wrote in message
news:3d45ae79$0$12039$cc9e...@news.dial.pipex.com...
Vladimir M. Zakharychev
news:3d431...@mk-nntp-1.news.uk.worldonline.com...
> Yes, we'll use automatic undo management when it's fixed.
Is it broken? Missed that if it is... :(
--
Vladimir Zakharychev (b...@dpsp-yes.com) http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.
Daniel Morgan
I agree. I have received value for my time at Oracle Education. But you are
correct that it is oriented toward the novice and is actually too expensive if
the intention is to sell the product. I think, however, Oracle Ed is set up as
its own profit center rather than set up as a means of pumping out more
developers and DBAs that will recommend buying Oracle's products.
The weakness I observed were as follows:
1. Too much reliance on PowerPoint slides
2. Too little actual hands-on
3. The instructors had little, if any, real-world experience with the products.
They knew the material they were teaching well but often could not relate it to
business problems
4. The presenters never made mistakes that required debugging. Leaving students
with little ability to handle syntactic and logical errors.
Daniel Morgan
Richard Foote
I think the last points you make are on the whole valid. They're all issues
that OU tries to address, with varying degrees of success (none to minimal
:)
Regards
Richard
"Daniel Morgan" <dmo...@exesolutions.com> wrote in message
news:3D46ABB1...@exesolutions.com...
Howard J. Rogers
> I agree. I have received value for my time at Oracle Education. But you
are
> correct that it is oriented toward the novice and is actually too
expensive if
> the intention is to sell the product. I think, however, Oracle Ed is set
up as
> its own profit center rather than set up as a means of pumping out more
> developers and DBAs that will recommend buying Oracle's products.
>
> The weakness I observed were as follows:
> 1. Too much reliance on PowerPoint slides
That's a bad trainer, not bad training per se. Actually, that's a dreadful
trainer, and they should be chased out of the profession (but, sadly, rarely
are).
> 2. Too little actual hands-on
That's a lecture, not a training course then. Same issue as above: a bad
trainer.
> 3. The instructors had little, if any, real-world experience with the
products.
Er, see above!
> They knew the material they were teaching well but often could not relate
it to
> business problems
I'll buy that as a legitimate criticism of Oracle training in general: you
can get lucky and find trainers who have done the real world in the past,
but their real world experience then gets progressively more and more out of
date. It would be good if OU could institute 'sabbaticals', where trainers
spent a few weeks every few months in Premium Services or something similar
getting their hands dirty again. But that's expensive (it means you lose a
trainer and his income-generating capabilities for a few weeks every few
months), so it's a bit of a non-starter (at least in Aus-land). Net result
is that you end up being correct on this point.
> 4. The presenters never made mistakes that required debugging. Leaving
students
> with little ability to handle syntactic and logical errors.
>
Now I can't work out whether you had bad trainers or good ones! This one
sounds like superman!! I'm forever making mistakes... and (as you suggest)
never mind when I do because it's good practice for me, and good for
students to see my sensational powers of logical deduction working out where
I went wrong!!
Regards
HJR
> Daniel Morgan
>
Paul Brewer
9i r1, yes, broken, allegedly.
9i r2, fixed, allegedly.
This is only from what I have read.
I must confess I haven't yet investigated for myself.
Regards,
Paul
"Vladimir M. Zakharychev" <b...@dpsp-yes.com> wrote in message
news:ai5h22$qfq$1...@babylon.agtel.net...
Richard Foote
The other way around.
It worked fine in 9i Rel 1.
Can cause ora-600 in 9i Rel 2
Cheers
Richard
"Paul Brewer" <pa...@paul.brewers.org.uk> wrote in message
news:3d497f1c$1...@mk-nntp-1.news.uk.worldonline.com...
Paul Brewer
news:1ap29.51048$Hj3.1...@newsfeeds.bigpond.com...
> Hi Paul,
>
> The other way around.
>
> It worked fine in 9i Rel 1.
>
> Can cause ora-600 in 9i Rel 2
>
> Cheers
>
> Richard
Richard,
Thanks for the correction. Hmm...second one today; you're keeping me in line
vey well ;-)
Still, I think I'll keep AUM low on the list for a while.....
Paul