1 2 Previous Next 16 Replies Latest reply: May 17, 2012 5:38 PM by jgarry RSS

    Table where savepoints are saved

    851804
      Could anyone tell please tell me in which table of the database are all the savepoints saved?

      Can I also see the date when the savepoint was created?

      Thank you.
        • 1. Re: Table where savepoints are saved
          AliD
          select * from v$restore_point;
          It has all info you need.
          • 2. Re: Table where savepoints are saved
            Karan
            I think OP asked for savepoints in sql not oracle restore points...
            • 3. Re: Table where savepoints are saved
              912595
              Karan wrote:
              I think OP asked for savepoints in sql not oracle restore points...
              And what are savepoints in your definition? Curious to know :)
              • 4. Re: Table where savepoints are saved
                851804
                What privileges are required to view the table?
                • 5. Re: Table where savepoints are saved
                  Jonathan Lewis
                  909592 wrote:
                  Karan wrote:
                  I think OP asked for savepoints in sql not oracle restore points...
                  And what are savepoints in your definition? Curious to know :)
                  In Oracle's definition - and through a demonstration of same:
                  SQL> drop table t1;
                  
                  Table dropped.
                  
                  SQL> create table t1 (n1 number);
                  
                  Table created.
                  
                  SQL> insert into t1 values (1);
                  
                  1 row created.
                  
                  SQL> savepoint aaa;
                  
                  Savepoint created.
                  
                  SQL> insert into t1 values(2);
                  
                  1 row created.
                  
                  SQL> rollback to savepoint aaa;
                  
                  Rollback complete.
                  
                  SQL> select * from t1;
                  
                          N1
                  ----------
                           1
                  
                  1 row selected.
                  These savepoints are available only in the session memory.

                  Regards
                  Jonathan Lewis
                  • 6. Re: Table where savepoints are saved
                    912595
                    Thanks for reminding me, i forgot this as i had zero practical experience on savepoints.
                    • 7. Re: Table where savepoints are saved
                      Girish Sharma
                      Sir,
                      These savepoints are available only in the session memory.
                      I am bit confused here and wish to request to please comment on it. Because on the other side docs are saying :

                      ALL_WM_MODIFIED_TABLES

                      ALL_WM_MODIFIED_TABLES contains information about all version-enabled tables. This view have a column savepoint whose description is "Name of the savepoint associated with the most recent modification, or LATEST if a savepoint does not yet exist is the workspace."

                      http://docs.oracle.com/cd/E14072_01/appdev.112/e11826/long_views.htm

                      I am not sure what exactly are version enabled tables and how do i see savepoint name "aaa" as shown by you in your example please or I am mixing wrong things here ?

                      Regards
                      Girish Sharma
                      • 8. Re: Table where savepoints are saved
                        Jonathan Lewis
                        Girish Sharma wrote:
                        Sir,
                        These savepoints are available only in the session memory.
                        I am bit confused here and wish to request to please comment on it. Because on the other side docs are saying :

                        ALL_WM_MODIFIED_TABLES

                        ALL_WM_MODIFIED_TABLES contains information about all version-enabled tables. This view have a column savepoint whose description is "Name of the savepoint associated with the most recent modification, or LATEST if a savepoint does not yet exist is the workspace."

                        http://docs.oracle.com/cd/E14072_01/appdev.112/e11826/long_views.htm
                        Is this the ONLY entry in the entire Oracle documentation set that mentions the word savepoint ?
                        In your position I would go to tahiti.oracle.com and do a search on "savepoint" - after noting several hundred hits, I would look at the example and refine my search by looking for "rollback to savepoint".
                        This would give me a smaller number of hits - and I would select the option to search the 11.2 library for the phrase.
                        The first hit in that library would be a reference to the Concepts manual, which would take me to this link:

                        http://docs.oracle.com/cd/E11882_01/server.112/e25789/transact.htm

                        Regards
                        Jonathan Lewis
                        • 9. Re: Table where savepoints are saved
                          Girish Sharma
                          Sir,

                          Basically I am confused, because you are saying "savepoints are available only in the session memory" while docs are saying that "Oracle stores savepoint names in ALL_WM_MODIFIED_TABLES view for version-enabled tables." I just raised my confusion; not disagreement with you, because suppose I started first transation and then after 100 DMLs, I said savepoint aaa, then I started next 100 transations and then savepoint bbb, again 200 transactions and then savepoint ccc; after running 150 transactions, I forgot the name of first savepoint name i.e. "aaa", how do I come to know the "aaa"; obviously session is running, so savepoints information should be in session memory, but how do I know the name of savepoint; so I just guess that then this ALL_WM_MODIFIED_TABLES view will tell me the name of savepoint i.e. "aaa", but all its guess, I have't tried, just trying to get help from you please, so that may be OP will also benefited or probably he/she is too looking for the answer please.

                          I just guess that Oracle writes each and everything in either v$views or x$ tables; so that they can be retrieved at any desired time/call.

                          Regards
                          Girish Sharma
                          • 10. Re: Table where savepoints are saved
                            Aman....
                            Girish,

                            Savepoints are , to be simply put, pointers or markers. There is no requirement for Oracle to store them anywhere. They are created only for the time of the transaction and after that, they are removed. I don't see any reason or benefit to store them. For your question that one is issuing a lot of statements and now has forgotten the name of the savepoint, well you are already doing things wrong. You shouldn't issue so many DML statement and than issue a rollback or commit. A commit would be still okay but for a rollback, it would be a lot of stuff left to be undone.

                            Just my 2 cents.

                            Regards
                            Aman....
                            • 11. Re: Table where savepoints are saved
                              amardeep.sidhu
                              Aman.... wrote:
                              You shouldn't issue so many DML statement and than issue a rollback or commit.
                              So are you suggesting that we should commit after every statement ? :P
                              • 12. Re: Table where savepoints are saved
                                Aman....
                                amardeep.sidhu wrote:
                                Aman.... wrote:
                                You shouldn't issue so many DML statement and than issue a rollback or commit.
                                So are you suggesting that we should commit after every statement ? :P
                                Well, almost like that just change every to few :-P :-D .

                                Aman....
                                • 13. Re: Table where savepoints are saved
                                  Karan
                                  Then this view i think must also contain how many dml's it has saved till now and how many DML's it has removed till now, because if a transaction A inserts 100 rows and savepoint A is created and then again 100 rows inserted, and then Trans A rollsback to savepoint A , it must release the locks which follow the rolled back savepoint so that another transaction B or a fresh transaction C can perform DML on those trailing 100 inserts performed after first 100 inserts... looks like an interesting view, will do some testing once i have my test db running.

                                  Regards
                                  Karan
                                  • 14. Re: Table where savepoints are saved
                                    Jonathan Lewis
                                    Girish Sharma wrote:
                                    Sir,

                                    Basically I am confused, because you are saying "savepoints are available only in the session memory" while docs are saying that "Oracle stores savepoint names in ALL_WM_MODIFIED_TABLES view for version-enabled tables."
                                    My point was that in the concepts guide we can learn about a basic concept named the savepoint. This is a transient thing that exists only within the lifetime of a single transaction.

                                    You are looking at workspace management, which is an option for which there is an entire development manual: http://docs.oracle.com/cd/E11882_01/appdev.112/e11826/toc.htm
                                    When you say "the docs say ..." what you really mean (presumably) is that the "Workspace Manager Developers' Guide says ...".

                                    It is unfortunate that Oracle often uses the same word in different contexts to mean different things - but when you have a description in the concepts manual, and a description in the development guide for an option that very few people are likely to be using, you should allow for the possibility that the two documents are talking about two different things.

                                    I haven't read the Workspace manuals for at least 5 years - but given your confusion I'd check whether Oracle meant the same thing by the word savepoint in this case: for example:
                                    <blockquote>
                                    how do you declare a savepoint in WM anyway - is it simply the syntax that I used above - if so then perhaps WM is a special case where you can find "ordinary" savepoints sometimes.
                                    if I create a version-enabled table, enable SQL_trace, and then issue: savepointXXX, can I see an insert into a WM table in the trace file ?
                                    </blockquote>

                                    I intended no reprimand by my earlier comment - I was merely highlighting a strategy for addressing your own problem.

                                    Regards
                                    Jonathan Lewis
                                    1 2 Previous Next