Re: ORA-01562 & ORA-01547 ERRORS

Hi,
  If you are not the DBA or cannot be bothered to change the Rollback Segment size then:

Use a cursor in a PL/SQL script to return the the RowId, delete the record using this and commit every <Count> rows. Where <count does not blow your Rollback segment - i use values between 100 .. 1000.

Disadvantages:

1. On large tables, it can take a while...
2. More code

Advantages:
  Will work on any system with PL/SQL.

Any comments, questions etc then please email me.

Good luck

Graham

Vijay Vardhineni 604-1835 <vvardh01> wrote:

>This is a multi-part message in MIME format.
 

>---------------------------------10071846333651405421731969183
>Content-Transfer-Encoding: 7bit
>Content-Type: text/plain; charset=us-ascii
 

>If you want to delete all records from a table, then you can
>use TRUNCATE TABLE command. Truncate works faster than delete
>because it will not use rollback segments. Please note that
>You cannot rollback your TRUNCATE statement. It is always
>better to use TRUNCATE command for deleting records from large
>tables. Delete command may take hours to delete data from
>large tables where TRUNCATE TABLE command will do it very
>quickly. If you are going to insert many rows into this table
>again, then it may be better to use
 

> TRUNCATE TABLE owner.table_name REUSE STORAGE ;
 

> Where "owner" is the owner of the table you want to truncate.
>and "table_name" is the name of the table you want to
>truncate. If you use the option "REUSE STORAGE" then Oracle
>will keep the extents that are already allocated to this table
>so that it can use for future inserts. If you don't use the
>"REUSE STORAGE" option then Oracle will deallocate all the
>extents and it may lead to Tablespace Fragmentation.
 

> If you don't want to use the "REUSE STORAGE" option, then
>issue the following command.
 

> TRUNCATE TABLE owner.table_name;
 

> In the above statement "owner" is the owner of the table you
>want to truncate. and "table_name" is the name of the table
>you want to truncate.
 

>Vijay Vardhineni
>Oracle DBA
>EDS, PLANO
 
>---------------------------------10071846333651405421731969183
>Content-Transfer-Encoding: 7bit
>Content-Type: text/plain
 

>From: kmizuta_at_jp.oracle.com (Ken Mizuta)
>Reply-To: kmizuta_at_jp.oracle.com
>Newsgroups: comp.databases.oracle
>Subject: Re: ORA-01562 & ORA-01547 ERRORS
>Date: 15 Jul 96 18:37:53 GMT
>Organization: Oracle Corporation
>Mime-Version: 1.0
>Content-Type: text/plain
>Content-Transfer-Encoding: 7bit
>Message-ID: <31EA9001.2F5D_at_jp.oracle.com>
>References: <4rrtvh$paj_at_news.ios.com>
 

>Mike Stenzler wrote:
>>
>> new to oracle dba duties....
>>
>> while attempting a global delete of approx. 107,000 rows from a table
>> I keep encountering the errors listed in the subject line.
>>
>> 01562 hints that there may be a databse space problem
>> 01547 is not in my server error message reference!
>>
>> I was able to perform my delete by changing the where clause &
>> deleteing smaller (much!) groups of rows at a time.
>>
>> Any ideas? If you need more detailed info let me know.
>>
>> TIA - mike
>>
>> mstenzler_at_rxr.com
>> rxrgroup_at_mail.idt.net

>When you delete 107,000 rows from a table it trys to write those
>107,000 rows into the rollback segment. ORA-1562 is mentioning
>space problems with the rollback segment. If this is something that
>you don't do very often, then my suggestion would be to
>create a large rollback segment which you make active only when you
>need it. You can assign a session a rollback segment by
>alter session use rollback ....;
>If it is something that you do quite frequently, then you may need
>to rethink the rollback segment sizes.

>--
> ____________________________________________
> / Kenichi Mizuta
> //// / Oracle Corporation (Redwood Shores, CA)
> |0 0| / Applications Division
>_ooO_ \U/_Ooo_/ email: kmizuta_at_us.oracle.com
>The comments and opinions expressed herein are mine and
>do not necessarily represent those of Oracle Corporation.
 

>---------------------------------10071846333651405421731969183--
Received on Sat Jul 20 1996 - 00:00:00 CEST