This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Jan 3, 2013 7:51 AM by ibney RSS

i want to know when we issue truncate table statement in oracle .

ibney Explorer
Currently Being Moderated
i want to know when we issue truncate table statement in oracle .No log will be write in redo log .But we can recover data using flashback or scn.I want to know where is the actually truncate table statement log is stored in oracle database.Please explain me in detail step by step .
  • 1. Re: i want to know when we issue truncate table statement in oracle .
    yoonas Expert
    Currently Being Moderated
    Hi,

    http://docs.oracle.com/cd/E11882_01/server.112/e17118/statements_10007.htm#SQLRF01707
    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2816964500346433991
    http://www.orafaq.com/faq/difference_between_truncate_delete_and_drop_commands

    Regards
    Yoonas
  • 2. Re: i want to know when we issue truncate table statement in oracle .
    ibney Explorer
    Currently Being Moderated
    Hi,
    I have truncated table after that i have restored that data.See below the example.I want to know from where it's restored.
    From which log file it's restored.

    create table mytab (n number, x varchar2(90), d date);
    alter table mytab enable row movement;

    Table altered.

    SQL> insert into mytab values (1,'Monsters of Folk',sysdate);

    1 row created.

    SQL> insert into mytab values (2,'The Frames',sysdate-1/24);

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> select CURRENT_SCN from v$database;

    CURRENT_SCN
    -----------
    972383

    SQL> select * from mytab;

    N
    ----------
    X
    --------------------------------------------------------------------------------
    D
    ---------
    1
    Monsters of Folk
    30-DEC-12

    2
    The Frames
    30-DEC-12

    N
    ----------
    X
    --------------------------------------------------------------------------------
    D
    ---------


    SQL> set lines 10000
    SQL> /

    N X D
    ---------- ------------------------------------------------------------------------------------------ ---------
    1 Monsters of Folk 30-DEC-12
    2 The Frames 30-DEC-12

    SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;

    TO_CHAR(SYSDATE,'
    -----------------
    20121230 09:29:24

    SQL> set timing on
    SQL> truncate table mytab;

    Table truncated.

    Elapsed: 00:00:15.75

    SQL> select * from mytab as of timestamp TO_TIMESTAMP('20121230 09:29:24','yyyymmdd hh24:mi:ss');

    N X D
    ---------- ------------------------------------------------------------------------------------------ ---------
    1 Monsters of Folk 30-DEC-12
    2 The Frames 30-DEC-12

    Elapsed: 00:00:00.28
    SQL> insert into mytab select * from mytab as of timestamp TO_TIMESTAMP('20121230 09:29:24','yyyymmdd hh24:mi:ss');

    2 rows created.

    Elapsed: 00:00:00.01
    SQL>
  • 3. Re: i want to know when we issue truncate table statement in oracle .
    Girish Sharma Guru
    Currently Being Moderated
    Oracle 11.2 Version TRUNCATE TABLE:
    Docs are saying :
    You cannot roll back a TRUNCATE TABLE statement, nor can you use a FLASHBACK TABLE statement to retrieve the contents of a table that has been truncated.
    http://docs.oracle.com/cd/E11882_01/server.112/e17118/statements_10007.htm#SQLRF01707

    Oracle 11.1 Version TRUNCATE TABLE:
    Docs are saying :
    You cannot roll back a TRUNCATE TABLE statement, nor can you use a FLASHBACK TABLE statement to retrieve the contents of a table that has been truncated.
    http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10007.htm#SQLRF01707

    Oracle 10.2 Version TRUNCATE TABLE:
    Docs are saying :
    You cannot roll back a TRUNCATE statement.
    http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10006.htm#SQLRF01707

    Oracle 10.1 Version TRUNCATE TABLE:
    Docs are saying :
    You cannot roll back a TRUNCATE statement.
    http://docs.oracle.com/cd/B14117_01/server.101/b10759/statements_10006.htm

    Oracle 9.2 Version TRUNCATE TABLE:
    Docs are saying :
    You cannot roll back a TRUNCATE statement.
    http://docs.oracle.com/cd/B10501_01/server.920/a96540/statements_107a.htm

    So, it means in you can not flashback table after truncate from Oracle version 11.1. Before 11.1 version you can flashback table after truncate, as docs are saying, but I shall give it a try on test instance.

    Regards
    Girish Sharma
  • 4. Re: i want to know when we issue truncate table statement in oracle .
    ibney Explorer
    Currently Being Moderated
    i used this version for rollback truncate statement. SQL*Plus: Release 11.2.0.1.0 Production on Sun Dec 30 09:14:08 2012

    I have successfully rollback truncate statement.Please see my above example for this .Please let me know it is rollback from flashback log or any other log.
  • 5. Re: i want to know when we issue truncate table statement in oracle .
    yoonas Expert
    Currently Being Moderated
    Not from log file but from undo

    http://docs.oracle.com/cd/E11882_01/appdev.112/e25518/adfns_flashback.htm#i1009447

    Regards
    Yoonas
  • 6. Re: i want to know when we issue truncate table statement in oracle .
    yoonas Expert
    Currently Being Moderated
    Not from log file but from undo

    http://docs.oracle.com/cd/E11882_01/appdev.112/e25518/adfns_flashback.htm#i1009447

    Regards
    Yoonas
  • 7. Re: i want to know when we issue truncate table statement in oracle .
    yoonas Expert
    Currently Being Moderated
    sorry, no idea how it got posted twice

    Edited by: yoonus on Dec 29, 2012 9:58 PM
  • 8. Re: i want to know when we issue truncate table statement in oracle .
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    did you check
    http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10007.htm
  • 9. Re: i want to know when we issue truncate table statement in oracle .
    JohnWatson Guru
    Currently Being Moderated
    I cannot repeat your result:
    orcl> create table mytab (n number, x varchar2(90), d date);
    
    Table created.
    
    orcl> alter table mytab enable row movement;
    
    Table altered.
    
    orcl>  insert into mytab values (1,'Monsters of Folk',sysdate);
    
    1 row created.
    
    orcl> insert into mytab values (2,'The Frames',sysdate-1/24);
    
    1 row created.
    
    orcl> commit;
    
    Commit complete.
    
    orcl> select CURRENT_SCN from v$database;
    
    CURRENT_SCN
    -----------
        9519726
    
    orcl> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;
    
    TO_CHAR(SYSDATE,'
    -----------------
    20121230 16:46:43
    
    orcl> truncate table mytab;
    
    Table truncated.
    
    orcl>  select * from mytab as of timestamp TO_TIMESTAMP('20121230 16:46:43','yyyymmdd hh24:mi:ss');
     select * from mytab as of timestamp TO_TIMESTAMP('20121230 16:46:43','yyyymmdd hh24:mi:ss')
                   *
    ERROR at line 1:
    ORA-01466: unable to read data - table definition has changed
    
    
    orcl> select * from v$version;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
    PL/SQL Release 11.2.0.3.0 - Production
    CORE    11.2.0.3.0      Production
    TNS for 32-bit Windows: Version 11.2.0.3.0 - Production
    NLSRTL Version 11.2.0.3.0 - Production
    
    orcl>
    and I cannot understand how you got the result you did, unless you have a flashback data archive enabled for the table. Did you do that?
  • 10. Re: i want to know when we issue truncate table statement in oracle .
    ibney Explorer
    Currently Being Moderated
    Yes .I have used a flashback data archive enabled for the table.From where that value is coming i wank to know that only.
  • 11. Re: i want to know when we issue truncate table statement in oracle .
    yoonas Expert
    Currently Being Moderated
    I did the same test but am also unable to reproduce the same result on 11.2.03.0

    See The Oracle Database Advanced Application Developer's Guide section General Guidelines for Oracle Flashback Technology
    http://docs.oracle.com/cd/E11882_01/appdev.112/e25518/adfns_flashback.htm#g1703520

    See what happens when you have Flashback Data Archive
    http://docs.oracle.com/cd/E11882_01/appdev.112/e25518/adfns_flashback.htm#BJFFDCEH

    Hope this answers your question

    http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_5010.htm

    Edited by: yoonus on Dec 30, 2012 10:31 PM
  • 12. Re: i want to know when we issue truncate table statement in oracle .
    saurabh Pro
    Currently Being Moderated
    By following your procedure i got the following error, while reteriving the data

    SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;


    TO_CHAR(SYSDATE,'YYYYMMDDHH24
    ---------------------------------------------------------------------------
    20121231 11:46:37


    SQL> truncate table test;

    Table truncated.

    Elapsed: 00:00:00.07

    SQL> select * from test as of timestamp TO_TIMESTAMP('20121231 11:46:37','yyyymmdd hh24:mi:ss');
    select * from test as of timestamp TO_TIMESTAMP('20121231 11:46:37','yyyymmdd hh24:mi:ss')
    *
    ERROR at line 1:
    ORA-01466: unable to read data - table definition has changed
  • 13. Re: i want to know when we issue truncate table statement in oracle .
    JohnWatson Guru
    Currently Being Moderated
    So I gave you the correct answer: the rows came from a flashback data archive. What you actually did was this:
    orcl> create tablespace tbs1 datafile size 20m;
    
    Tablespace created.
    
    orcl> CREATE FLASHBACK ARCHIVE DEFAULT fla1 TABLESPACE tbs1
      2    QUOTA 10G RETENTION 1 YEAR;
    
    Flashback archive created.
    
    orcl> ALTER FLASHBACK ARCHIVE fla1 SET DEFAULT;
    
    Flashback archive altered.
    
    orcl> create table mytab (n number, x varchar2(90), d date);
    
    Table created.
    
    orcl> alter table mytab flashback archive;
    
    Table altered.
    
    orcl> insert into mytab values (2,'The Frames',sysdate-1/24);
    
    1 row created.
    
    orcl> commit;
    You could have mentioned this in your first post :)
    If you look at the rowids before and after truncate, you will see that they do not change:
    orcl> select rowid,n from mytab;
    
    ROWID                       N
    ------------------ ----------
    AAAUDIAAEAAAADLAAA          2
    
    orcl> truncate table mytab;
    
    Table truncated.
    
    orcl> select rowid,n from mytab as of scn 9578926;
    
    ROWID                       N
    ------------------ ----------
    AAAUDIAAEAAAADLAAA          2
    
    orcl> select rowid,n from mytab;
    
    no rows selected
    
    orcl>
    so you can see that the rows remain exactly where they are after the truncation. If you do some more experiments you can reverse engineer the mechanism. You'll find some interesting results in dba_extents and dba_free_space and so on.
  • 14. Re: i want to know when we issue truncate table statement in oracle .
    ibney Explorer
    Currently Being Moderated
    I understand your ans.But i want to know where is the stored truncate statement log.But in the redo log no entry for truncate.but i want to from which log it is reversing value..
1 2 Previous Next

Legend

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