Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 494
  • Last Modified:

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
0
lulubell-b
Asked:
lulubell-b
  • 5
  • 5
  • 2
  • +1
3 Solutions
 
slightwv (䄆 Netminder) Commented:
Define: "not working as expected".

Rollback will 'undo' all non-commited transactions.  Are you doing intermittent commits?
0
 
sdstuberCommented:
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;
0
 
lulubell-bAuthor Commented:
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.
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
lulubell-bAuthor Commented:
What are intermittent commits? I'm unsure.
0
 
sdstuberCommented:
these would be intermittent commits,  

delete yourtable where id = 1;
commit;
delete yourtable where id = 2;
commit;
delete yourtable where id = 3;
commit;
delete yourtable where id = 4;
commit;

rollback;  -- this won't do anything

the commits could be in a loop too, they don't have to spaced literally between sql statements like that


0
 
sdstuberCommented:
are you doing any DDL?  alter/create/drop/truncate, etc?  DDL  does an implicit commit
0
 
slightwv (䄆 Netminder) Commented:
Look through all the code.  I bet different functions/procedures/pieces of it are doing commits that you don't know about.
0
 
lulubell-bAuthor Commented:
Within my procedure I'm doing

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

I'm confused.

0
 
ajexpertCommented:
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
0
 
lulubell-bAuthor Commented:
I do not have any commits in the procedures and I'm positive that the value is returning correctly from the second procedure.
0
 
sdstuberCommented:
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
0
 
sdstuberCommented:
and remember, the word "commit" doesn't need to be in the code.

any DDL statement will commit for you.

and some built in packages will commit for you too
0
 
lulubell-bAuthor Commented:
Thank you, still unsure why the rollback isn't working correctly. I will revisit later. Thank you
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 5
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now