Simpler Navigation coming for Servers and Operating Systems
Coming soon: a much simpler Servers and Operating Systems section of the Community. We will combine many of the older boards, and you won't have to click through so many levels to get at the information you need. If you are looking for an older board and do not find it, check the consolidated boards, as the posts are still there.
Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Shell script for delteing rows in oracle DB table

Inter_1
Frequent Advisor

Shell script for delteing rows in oracle DB table

Hi,

I am trying to create shell script to do the followings, but I am not good in shell scriting yet.

1. Connect to an oracle DB
2. Delete every 5000 rows and commit table TBL_TEMP_TAKS, based on TASK_ID column.

It should find the MIN number on TAKS_ID columns and MAX number.
Then should delete MIN+5000 till MIN = MAX

I apprecitae your help.

Thanks
Andy

I like to fix things.
3 REPLIES
Hein van den Heuvel
Honored Contributor

Re: Shell script for delteing rows in oracle DB table

why?

If you need to delete all rows from a table, then you should use "TRUNCATE TABLE".

To delete multiple records from a table your best bet is probably still to find a way to express all records to be deleted and execute and commit that 1 statement.

While commiting 5000 deletes is faster than 5000 times commiting one record, commiting all deletes is faster still and should be used until you can explain why that is not good enough.

SQLplus does not like / do 'counting'.
You pretty much need PL/SQL for that.
Things like "rownum < 5000" only SUGGEST it is counting... it is still finding all candidates first.

You may want to check out the new (10.2)
COMMIT options like WRITE BATCH and NOWAIT.
http://oratips-ddf.blogspot.com/2008/02/dreaded-ora-01555.html
commit work write batch nowait

And for PL/SQL there is "BULK COLLECT "

http://www.oracle.com/technology/oramag/oracle/03-sep/o53asktom.html
Bulk Up Your Processing

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/tuning.htm#i48876

hth,
Hein.
Hein van den Heuvel
Honored Contributor

Re: Shell script for delteing rows in oracle DB table


Now, If I _had_ to chop this up in X-row sub queries, then I would use a temporary table to get a predictable, stable, result.

First do something like:

select TAKS_ID from
(select rownum x, TAKS_ID from
(select TAKS_ID from TBL_TEMP_TAKS order by TAKS_ID)
)
where mod(x,5000)=0;


Next, use your shell or perl script to select an entry and the next from the temp table to execute and commit:
DELETE TBL_TEMP_TAKS WHERE TAKS_ID BETWEEN ... AND ...;

You may want to add the first and/or last values through SQL or hardcoded in the script.

Using SQL you could use a 'nice':

select MIN(TAKS_ID) from TBL_TEMP_TAKS
union all
select TAKS_ID from
(select rownum x, TAKS_ID from
(select TAKS_ID from TBL_TEMP_TAKS order by TAKS_ID)
)
where mod(x,5000)=0;
union all
select MAX(TAKS_ID) from TBL_TEMP_TAKS

Or a brute-force:

select 0 "TAKS_ID" from dual;
union all
select TAKS_ID from
(select rownum x, TAKS_ID from
(select TAKS_ID from TBL_TEMP_TAKS order by TAKS_ID)
)
where mod(x,5000)=0;
union all
select 99999999 "TAKS_ID" from dual;

Good luck,
Hein.


Yogeeraj_1
Honored Contributor

Re: Shell script for delteing rows in oracle DB table

hi Andy,

Question: why use shell script when you can do the same thing using a scheduled job? (DBMS_JOB or DBMS_SCHEDULER)

You can simply write a procedure and schedule it to run at specific interval and do the purge operation accordingly.

if you need any assistance on this, please do let us know

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)