| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: ORA-01562
Christoph,
replies embedded .
Regards,
Norman.
Tel: 0113 289 6265 Fax: 0113 289 3146 URL: http://www.Lynx-FS.com -------------------------------------
-----Original Message-----
From: Christoph Kukulies [mailto:kuku_at_accms33.physik.rwth-aachen.de]
Posted At: Wednesday, June 19, 2002 2:41 PM
Posted To: server
Conversation: ORA-01562
Subject: Re: ORA-01562
>> I logged in as sys/password as normal into the DB (Is this correct?)
SYSTEM would also have worked, as would any dba enabled user.
>> Did ALTER ROLLBACK SEGMENT RBS1 SHRINK ;
>> and got 'rollback segment not found'.
You need to specify the name of your own rollback segment, not mine.
Sorry !
>> Tried RBS1ORCL.ORA and got illegal option.
You would have done, I suspect that this is the name of the OS file
where Oracle creates the rollback segments.
>> Then I tried rb0 and rb1 as an argument. Both worked.
Good guess :o)
>> But I saw no touch on the date of the RBS1ORCL.ORA file.
>> (maybe it wouldn't show in a touching of that file either)
Forget touch in Oracle - it makes no sense. Oracle doesn't do file
update 'right now'.
>> Can I show the rollback segment that is in the game somehow?
select * from dba_rollback_segs;
or
select * from v$rollname;
You'll need to be a dba user (sys/system/some other dba enabled user) or have appropriate privs to select from the avove views. The first tells you everything you ever wanted to know about all your rollback segments, the second gives you their names and numbers.
>> I did then blindly the following:
>> ALTER ROLLBACK SEGMENT RB0 SHRINK ;
>> ALTER ROLLBACK SEGMENT RB0 STORAGE (maxextents 20000);
>> ALTER ROLLBACK SEGMENT RB1 SHRINK ;
>> ALTER ROLLBACK SEGMENT RB1 STORAGE (maxextents 20000);
>> COMMIT; <<<< Is this necessary? Or does it have side effects on
pending
>> transactions?
>> All four statements worked.
The shrink part is unnecessary.
The rest is ok.
The commit is not required. It will have committed any outstanding
transactions in the session you were logged in to, but will not affect
any other sessions. And, when you do any DDL in Oracle (create xxx, drop
xxx, truncate table etc) an implicit commit takes place.
>> I repeated it for the owner of the TABLEs (who had DBA privs, don't
>> know whether it counts).
It only needs doing once - by any dba user. Ordinary users won't have
the privs. Take great care in allowing other users dba access though. If
more people know the passwords, they can get you into a lot of bother
with dba rights.
>> Then I ran again the table creation job and it worked this time.
Yippee !
>> Now I wonder which magic had made it to work :-O
Extending the rollback segments to 20000 extents was the magic.
Previously, they were allowed to extend to 121 extents, and when this
limit is reached, Oracle barfed and rolled back your transaction. A
transaction has to finish in the same rollback segment that it started
in, so it cannot jump over and use another one - even if it (or they)
have free space to allow the transaction to continue.
>> Many thanks for helping
Welcome.
Received on Wed Jun 19 2002 - 09:43:48 CDT
 |
 |