1 2 Previous Next 16 Replies Latest reply: Jan 3, 2013 9:51 AM by ibney RSS

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

    ibney
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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