10 Replies Latest reply: Nov 18, 2012 8:37 PM by 871977 RSS

    select data from undo tablespace?

    871977
      Hi all,

      Can i select data from undo segments in undo tablespace?

      Thank you.
      Dan.
        • 1. Re: select data from undo tablespace?
          JohnWatson
          SELECT is part of the ISO standard SQL language. Undo segments are an Oracle proprietary data structure.
          Think this through: can a non-proprietary language understand a proprietary structure? What sort of of object can SQL understand?
          • 2. Re: select data from undo tablespace?
            Girish Sharma
            Can i select data from undo segments in undo tablespace?
            Yes, but not through SQL, it can be through dump undo segment.
            UNDO segment dump

            To dump an undo segment header use the command function

            ALTER SYSTEM DUMP UNDO_HEADER 'segment_name';

            Regards
            Girish Sharma
            • 3. Re: select data from undo tablespace?
              Aman....
              Undo segments are designed not to be selected by you-the end user, directly but to enforce a mechanism in Oracle DB called Read Consistancy which is going to ensure that you won't ever get a data that's not committed returned back to you and if that data which you are trying to select now, is dirty , its read consistent version would be returned to you by applying the Undo image. So the answer of your question is a no and hopefully. the reasoning should be clear to you as well.

              HTH
              Aman....
              • 4. Re: select data from undo tablespace?
                JohnWatson
                This sort of thing could be considered to be selecting from an undo segment:
                orcl> select count(*) from emp;
                
                  COUNT(*)
                ----------
                        14
                
                orcl> delete from emp;
                
                14 rows deleted.
                
                orcl> commit;
                
                Commit complete.
                
                orcl> select * from emp as of timestamp(sysdate - 5/1440);
                
                     EMPNO ENAME      JOB              MGR HIREDATE                 SAL       COMM     DEPTNO
                ---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
                      7369 SMITH      CLERK           7902 17-12-80 00:00:00        800                    20
                      7499 ALLEN      SALESMAN        7698 20-02-81 00:00:00       1600        300         30
                      7521 WARD       SALESMAN        7698 22-02-81 00:00:00       1250        500         30
                      7566 JONES      MANAGER         7839 02-04-81 00:00:00       2975                    20
                      7654 MARTIN     SALESMAN        7698 28-09-81 00:00:00       1250       1400         30
                      7698 BLAKE      MANAGER         7839 01-05-81 00:00:00       2850                    30
                      7782 CLARK      MANAGER         7839 09-06-81 00:00:00       2450                    10
                      7788 SCOTT      ANALYST         7566 19-04-87 00:00:00       3000                    20
                      7839 KING       PRESIDENT            17-11-81 00:00:00       5000                    10
                      7844 TURNER     SALESMAN        7698 08-09-81 00:00:00       1500          0         30
                      7876 ADAMS      CLERK           7788 23-05-87 00:00:00       1100                    20
                      7900 JAMES      CLERK           7698 03-12-81 00:00:00        950                    30
                      7902 FORD       ANALYST         7566 03-12-81 00:00:00       3000                    20
                      7934 MILLER     CLERK           7782 23-01-82 00:00:00       1300                    10
                
                14 rows selected.
                
                orcl>
                • 5. Re: select data from undo tablespace?
                  Aman....
                  Yep and we must say indirectly :) !

                  Regards
                  Aman....
                  • 6. Re: select data from undo tablespace?
                    rp0428
                    >
                    Can i select data from undo segments in undo tablespace?
                    >
                    Due to Oracle's read-consistency you probably do it all the time and don't even realize it.

                    What is it you are really wanting to do?
                    • 7. Re: select data from undo tablespace?
                      871977
                      Hi Girish Sharma ,

                      SQL> ALTER SYSTEM DUMP UNDO HEADER "_SYSSMU10_3550978943$";

                      System altered.

                      SQL>

                      The above statement ran successfull but no result return?

                      Please help.
                      Dan.
                      • 8. Re: select data from undo tablespace?
                        rp0428
                        The dump is to a trace file at <ORACLE_BASE>/diag/rdbms/< dbname >/< dbname >/trace for Oracle Database 11g and higher and USER_DUMP_DEST for Oracle Database 10g and earlier.
                        • 9. Re: select data from undo tablespace?
                          Aman....
                          Would you kindly explain that for what reason you are even doing this dumping of Undo Segments?

                          Aman....
                          • 10. Re: select data from undo tablespace?
                            871977
                            Thank Aman.
                            Aman.... wrote:
                            Yep and we must say indirectly :) !

                            Regards
                            Aman....
                            Yes, Indirectly access to undo data.