Database Tuning (MOSC)

MOSC Banner

Locks on big table when I delete rows using rowid

edited Feb 27, 2014 11:13AM in Database Tuning (MOSC) 4 commentsAnswered

Hello,

I try to delete rows in  big table T1  (1 To, more than 152220000 rows ) under some condition . I use a  bloc PLSQL  based on rowid . but I have a locks,

  I need your help to avoid locks and/or to speed processing.

  1. Create temporary table contains a rowid of rows to delete  on table T1

      

       Create table TMP_tab1 as

Select  T1.rowid T1_rowid,0 isDone

From  T1

Inner join …

Where T1.status=’expired’…

2 -  No logging table and index

alter table TMP_tab1 nologging;

      alter table T1 nologging;

      CREATE index isDone_idx on TMP_tab1(isDone);

3 -  Delete  by  5000 and commit. (

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center