Link to home
Start Free TrialLog in
Avatar of lulubell-b
lulubell-b

asked on

Using ROLLBACK FUNCTION

Hello,

I'm trying to ROLLBACK records after a delete and it appears that it not working as expected. I'm am inserting in approx 20 tables then deleting the records from the source tables. I am then running an IF statement and if the criteria is not meant (or an error occurs) then rollback.

Do you have any ideas?

Thanks
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Define: "not working as expected".

Rollback will 'undo' all non-commited transactions.  Are you doing intermittent commits?
are you simply waiting for the rollback to complete?

you can monitor the amount of rollback used per transaction and extrapolate how long it might take to complete


SELECT d.tablespace_name,r.NAME,s.username, s.SID,s.serial#,p.spid os_pid , t.addr,t.used_ublk
FROM v$transaction t, v$rollname r, v$session s,v$process p,dba_rollback_segs d
WHERE t.addr = s.taddr
AND p.addr = s.paddr
AND t.xidusn = r.usn
AND d.segment_name = r.NAME
ORDER BY d.tablespace_name,r.NAME,s.username;
Avatar of lulubell-b

ASKER

I don't think so, I ran my procedure with the rollback imbedded and then looking at the table No data was rolled back. I'm confused, it works when I remove the delete statements, but doesnt seem to work when I add the delete statments back into the PL/SQL.
What are intermittent commits? I'm unsure.
SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
are you doing any DDL?  alter/create/drop/truncate, etc?  DDL  does an implicit commit
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Within my procedure I'm doing

INSERTs
DELETE
THEN CALLING ANOTHER PROCEDURE WHICH IS SELECTING AND RETURN A VALUE.

I'm confused.

Do you have COMMIT at any point in your procedure?

Possiblity is that your logic met all the CRITERIA written in IF condition so it didnt rollback
I do not have any commits in the procedures and I'm positive that the value is returning correctly from the second procedure.
can you post the code? and sample data/results that demonstrate the problem ?

the symptoms you describe and the mini-pseudocode snippets don't coincide
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you, still unsure why the rollback isn't working correctly. I will revisit later. Thank you