This discussion is archived
10 Replies Latest reply: Nov 18, 2012 6:37 PM by 871977 RSS

select data from undo tablespace?

871977 Newbie
Currently Being Moderated
Hi all,

Can i select data from undo segments in undo tablespace?

Thank you.
Dan.
  • 1. Re: select data from undo tablespace?
    JohnWatson Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.... Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.... Oracle ACE
    Currently Being Moderated
    Yep and we must say indirectly :) !

    Regards
    Aman....
  • 6. Re: select data from undo tablespace?
    rp0428 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.... Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thank Aman.
    Aman.... wrote:
    Yep and we must say indirectly :) !

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points