Dear All,

I've got a big table in Oracle 8i. There are 3 Mio records in a Datafile
with a size of 2GB. Now, i must modify a column in a table.

I create a big rollback segment to set a transaction, but nothing works.
Oracle tells me a ORA-01562 WITHOUT a Segment Number?????

What can i do?

Here my last statements:
22:10:32 archiv1:tsAdress>set transaction use rollback segment rbbroll;

Transaktion wurde gesetzt.

22:10:32 archiv1:tsAdress> alter table tbl_adress1 modify (name char(40));
alter table tbl_adress1 modify (name char(40))
*
FEHLER in Zeile 1:
ORA-01562: Fehler beim Erweitern der Rollback-Segmentnummer .

Why won't Oracle use that temporary rollback segment?

Hope someone can help
Ralf

"Ralf Bender" <ralf.bender@arcor.de> wrote in message
news:40479fdf$0$14823$9b4e6d93@newsread4.arcor-online.net...
| Dear All,
|
| I've got a big table in Oracle 8i. There are 3 Mio records in a Datafile
| with a size of 2GB. Now, i must modify a column in a table.
|
| I create a big rollback segment to set a transaction, but nothing works.
| Oracle tells me a ORA-01562 WITHOUT a Segment Number?????
|
| What can i do?
|
| Here my last statements:
| 22:10:32 archiv1:tsAdress>set transaction use rollback segment rbbroll;
|
| Transaktion wurde gesetzt.
|
| 22:10:32 archiv1:tsAdress> alter table tbl_adress1 modify (name char(40));
| alter table tbl_adress1 modify (name char(40))
| *
| FEHLER in Zeile 1:
| ORA-01562: Fehler beim Erweitern der Rollback-Segmentnummer .
|
| Why won't Oracle use that temporary rollback segment?
|
| Hope someone can help
| Ralf

alter table is DDL, not DML -- could it be that the alter transaction does
not affect DDL? (not exactly sure, but might be worth looking into)

;-{ mcs

On Thu, 4 Mar 2004 16:45:20 -0500, "Mark C. Stock" <mcstockX@Xenquery .com>
wrote:

>"Ralf Bender" <ralf.bender@arcor.de> wrote in message
>news:40479fdf$0$14823$9b4e6d93@newsread4.arcor-online.net...
>| Here my last statements:
>| 22:10:32 archiv1:tsAdress>set transaction use rollback segment rbbroll;
>|
>| Transaktion wurde gesetzt.
>|
>| 22:10:32 archiv1:tsAdress> alter table tbl_adress1 modify (name char(40));
>| alter table tbl_adress1 modify (name char(40))
>| *
>| FEHLER in Zeile 1:
>| ORA-01562: Fehler beim Erweitern der Rollback-Segmentnummer .
>|
>| Why won't Oracle use that temporary rollback segment?
>
>alter table is DDL, not DML -- could it be that the alter transaction does
>not affect DDL? (not exactly sure, but might be worth looking into)

Exactly - DDL does a commit both before and after, so it's not the same
transaction as the one that had its rollback segment set.

--
Andy Hassall <andy@andyh.co.uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk> / <http://www.andyhsoftware.co.uk/space>

On Thu, 04 Mar 2004 21:59:54 +0000, Andy Hassall <andy@andyh.co.uk>
wrote:

>
> Exactly - DDL does a commit both before and after, so it's not the same
>transaction as the one that had its rollback segment set.

And as it is DDL it will use the SYSTEM rollback segment by default.
The set transaction statement of the OP is useless.


--
Sybrand Bakker, Senior Oracle DBA

Take all the rollback segments, except the temporary one, offline.
(Offcourse you cannot offline the SYSTEM rollback segment).
And now do the operation.
An ALTER TABLE can use non-system rollback segment.
Only the recursive SQL generating UNDO and REDO to UNDO$ will
mandatorily go to the SYSTEM rollback segment.
regards
Srivenu