Help with Alter Tablespace !!! [message #21219] |
Thu, 18 July 2002 10:47 |
Arpit
Messages: 99 Registered: March 2001
|
Member |
|
|
Hello,
Can someone please help me with this.
I was doing some mass data updates on my "development" instance and finally got this two error messages :-
ORA-01562: failed to extend rollback segment number 2
ORA-01650: unable to extend rollback segment R01 by 2560 in tablespace RBS
I looked at the error description for this two messages and this is what it says :-
>>oerr ora 01562
01562, 00000, "failed to extend rollback segment number %s"
// *Cause: Failure occurred when trying to extend rollback segment
// *Action: This is normally followed by another error message that caused
// the failure. You may take the rollback segment offline to perform
// maintainence. Use the alter rollback segment offline command
// to take the rollback segment offline.
>> oerr ora 01650
01650, 00000, "unable to extend rollback segment %s by %s in tablespace %s"
// *Cause: Failed to allocate an extent for rollback segment in tablespace.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
// files to the tablespace indicated.
Can someone please help with the command as to how should I alter the tablespace to add one more file to RBS.
OR
How should i turn the RBS offline in future if I'm doing mass data updates.
Any help on this asap would be highly appreciated.
Thanks!
Alpesh
|
|
|
Re: Help with Alter Tablespace !!! [message #21220 is a reply to message #21219] |
Thu, 18 July 2002 11:04 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
No, you will not be able to do your updates if you take the rollback segements offline. You need to either make your existing segments larger, or, as the message say, add a datafile.
The syntax for this is available many places online, including tahiti.oracle.com.
|
|
|
|
Re: Help with Alter Tablespace !!! [message #21233 is a reply to message #21219] |
Fri, 19 July 2002 00:34 |
hanu
Messages: 21 Registered: March 2002
|
Junior Member |
|
|
The existing the tablespace is full, so you need to add the datafile to the tablespace using the below command.
1. alter tablespace RBS add datafile "path of the file"
size 102400k;
or else if you know the existing datafile size, you can resize that file using
2.alter database datafile "file name" resize 204800k;
The command 1 is for adding a new datafile and the command 2 is resizing the existing datafile.
you can do offline of a rollback segment using the following command.
Alter rollback segment rbs1 offline;
But, when you restart your database the rbs1 also will be online depending on the parameter in your initORACLE_SID.ora file.
To resolve your problem you need not make the RBS1 offline just by following either 1 or 2 you can solve.
Hope this helps.
Hanu.
|
|
|