Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Truncate Table Problem.

Re: Truncate Table Problem.

From: Bill Saxton <William.D.Saxton_at_CDC.COM>
Date: Fri, 8 Mar 1996 00:37:03 -0500
Message-Id: <>

01562, 00000, "failed to extend rollback segment (id = %s)"

// *Cause: Failure occurred when trying to extent rollback segment
// *Action: This is normally followed by another error message that caused
//         the failure. Shutdown, restart and then take appropriate action for
//         the error the caused the failure. If starting up the system
//         again doesn't solve the problem, it is possible that there is
//         an active transaction in the rollback segment and the system
//         can't roll it back for some reasons. Check the trace file
//         generated by the PMON process for more information.

01628, 00000, "max # extents (%s) reached for rollback segment %s"

// *Cause:  Tried to extend rollback segment already at maxexents value
// *Action: If maxextents storage parameter less than system allowable max,
//          raise this value. Consider upping the pctincrease value as well.

I'd say you need to down-scale your load by issuing 'commits' more often, or increase the size of your rollback segments and then retry the operation. If I'm reading this correctly your 'truncate' is not failing, rather your reload is?

At 09:45 AM 3/8/96 EST, you wrote:
> Hi,
> We are using SQL loader for loading data into a table with 'replace'
> option. It gives following error.
> ORA-01562: failed to extend rollback segment (id = 2)
> ORA-01628: max # extents (10) reached for rollback segment R01
> But oracle utilities manual say that 'replace' option uses oracle
> 'truncate table' command. Still we are getting this error.
> We tried to manually truncate the table before loading the data. The
> disadvantage of this method is only owner of the table can do this.
> In the oracle manual it is given that if a user has 'delete any table'
> system privilege then he can use 'truncate table' command. We logged
> in as owner of the table, and granted 'delete any table' to the user.
> Still we are unable to use 'truncate table' command. It gives the
> error insufficient privileges. Anyway 'delete any table' has the
> disadvantage that it allows to use truncate command on all tables
> under that user.
> We are stuck now. We are thinking of writing a Pro *C program which
> deletes the table 1000 rows at a time.
> Please tell me a efficient way of handling this situation.
> Thanks,
> Bopanna K K

  William D. Saxton             | email:
  Oracle Products Consultant    | phone: 612-482-3555
  Worldwide Support Services    | fax:   612-482-3988
  Control Data Systems, Inc.    |


Received on Fri Mar 08 1996 - 00:42:34 CST

Original text of this message