if there R many savepoint i've created and i want to rollback up a particular savepoint i know the particular transaction that i want to rollback upto but if i forget the name of that particular transaction's savepoint name then what i should do at that time ur say that go up-down and page-up-down this is not a perfect anwser sir b'coz if have created three savepoint and clear screen then what should do there to know the name ?????


i just want to say that is there any query or table that stores the savepoint name. i know that savepoints R loggical. but there must be some way to know the name of savepoint.

Take medication this will solve the problem of savepoint.
Sorry but couldnt help it.
Never heard of such a thing of forgetting names of objects or program contructs in your Code.

so...there is no way to forget the name of savepoint that you made.
Please Don't take it as I'm trying to tease you.
But it still looks possible to me....

Like you said, if you made statements on a texteditor like Notepad, there should be no way to forget the name of savepoint. But, what if you made a statement into SQL*PLUS directly?

Even if you still don't see the possibilty or reason that happen the thing, just tell me if there is the way to query the name of existing savepoints like querying data dictionary view. That's all I want to know.

Sorry for bothering you with stupid question again.

>>Sorry for bothering you with stupid question again.

The argument is not that the question is "stupid" but that such a situation is not realistic.

Any such serious piece of code like this must be in a script or program file to start with.

As you say that "after creating a savepoint if you cleared the SQL*Plus screen and you forgot the name of savepoint that you created, now how to know the name"

The point is that the moment you clear the screen of SQL*Plus, it indicates that you are NOT interested in any of the things (contents) displayed on the screen (that is why you are clearing it, right?) and DO NOT NEED it or else why would you clear the screen if you needed its contents.

So, it means that you are not interested in the savepoints you created either?

It is similar to saying that I ran a bunch of SQL*Plus commands at my SQL*Plus prompt and then later cleared the screen. Now I want those commands back. Are they stored somewhere for me to retreive?

You can spool the sqlplus session if you "R" so forgetfull.
"R" you trying to hack into somebody's CODE ?I BET knowing the savepoint wont help.
As someone said above
The only solution is open a TAR with ORACLE HEALTH SUPPORT
for errors : ORA-AMNESIA.
If you are a novice/expert you should be reading more of concepts manual.Pls get off this savepoint there are more interesting things in ORACLE.More to life then fooling and savepointing
This will be a SAVINGPOINT for you

This was the best part in this thread. Atleast some solution.

If you really need to go back to a savepoint, but have forgotten the name of it, maybe it would be best to just rollback. You'll have to start over from the last commit, but you are trying to 'lose' some part of the transaction anyway, so maybe you aren't losing too much by going back (guess it depends on how many savepoints you made and how far back you wanted to go on whether or not you're 'losing out' b by doing a full rollback).

I agree that if the transaction is so complex that you are making a lot of savepoints, it will be in a script somewhere. If not, then you should definitely be spooling so you can script it later without having to reinvent the wheel. Also, adopting a standard naming scheme may be helpful (first savepoint - savepointA for example - not as flashy as 'savept_insertx93' but the name of the savepoint isn't what matters as long as you know where the savepoint is and what it reflects).

Dennis

Hi all!

The following commands show how to get the names of the savepoints created.This works even if you clear the screen before creating the second savepoint.

SQL> insert into emp (empno, ename) 
       2  values (9995, 'vso'); 

     1 row created. 

     SQL> savepoint pt1; 

     Savepoint created. 

     SQL> insert into emp (empno, ename) 
       2  values (9994, 'vso'); 

     1 row created. 

     SQL> savepoint pt2; 

     Savepoint created. 

     SQL> alter session set events 
       2  'immediate trace name savepoints level 1'; 

     Session altered. 
  
A trace file is generated in the user_dump_directory.  The 
content of the trace file is included in the following:


Dump file /u04/app/oracle/admin/R805/udump/r805_ora_18763.trc 
Oracle8 Enterprise Edition Release 8.0.5.2.1 - Production 
With the Partitioning and Objects options 
PL/SQL Release 8.0.5.2.0 - Production 
ORACLE_HOME = /u04/app/oracle/product/8.0.5 
System name:    SunOS 
Node name:      rtcsol1 
Release:        5.6 
Version:        Generic_105181-17 
Machine:        sun4u 
Instance name: R805 
Redo thread mounted by this instance: 1 
Oracle process number: 8 
Unix process pid: 18763, image: oracleR805 

*** 2000.04.18.14.11.37.000 
*** SESSION ID:(7.391) 2000.04.18.14.11.37.000 
==================================================== 
SAVEPOINT FOR CURRENT PROCESS 
------------------------------ 
   flag: 0x1 
   name: PT2 
   dba: 0x80020e, sequence #: 0, record #: 9, savepoint #: 131 
   status: VALID, next: 10a0868 
   name: PT1 
   dba: 0x80020e, sequence #: 0, record #: 7, savepoint #: 109 
   status: VALID, next: 0 
  
regards,
Vidya 

Thanks for that :)

Regards..

Praveen

i use

Oracle9i Enterprise Edition Release 9.2.0.1.0..

We have a stored procedure that processes say 1 to 500 rows..For the records that are not processed due to some business check WE FIRE A ROLLBACK TO THE SAVEPOINT...This is to ensure the rest that are processed are to be committed...

LOOP
SAVEPOINT XXXX;
<<BUSINESS LOGIC>>
<<ON CONDITION OF BUSINESS LOGIC>>
<<DMLS>>
ROLLBACK TO XXXX;
<<BUSINESS LOGIC>>
<DMLS>>
<<ON CONDITION OF B.L>>
ROLLBACK TO XXXX;
END LOOP;

Is this a right approach !! or there is any better approach available for this..

Your guidance is highly appreciated..

Rgds