6 Replies Latest reply: Jun 20, 2010 10:57 AM by Lubiez Jean-Valentin RSS

    Truncating A Partitioned Table

    778931
      Hi,
      I am using oracle 10gr2 on OEL5.

      I have a ranged partitioned table with 400 partitioned having data. I want to truncate all the partitions in one go through one statement.

      Please guide.
        • 1. Re: Truncating A Partitioned Table
          660830
          Uhm...

          Truncate table <table name>

          It should work.

          Other way, you must do a loop on the USER_TAB_PARTITION (for exemaple) use the ALTER TABLE <table name> TRUNCATE PARTITION <partition name>.

          Bye,
          Antonio
          • 2. Re: Truncating A Partitioned Table
            Fahd.Mirza
            L'ascolto del venerdi wrote:
            Uhm...

            Truncate table <table name>

            It should work.
            It will fail with following error ORA-14155.

            regards
            • 3. Re: Truncating A Partitioned Table
              Nicolas.Gasparotto
              Fahd Mirza wrote:
              L'ascolto del venerdi wrote:
              Uhm...

              Truncate table <table name>

              It should work.
              It will fail with following error ORA-14155.
              Please, explain how truncate a partioned table would fail.
              Connected to:
              Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
              With the Partitioning, OLAP, Data Mining and Real Application Testing options
              
              SQL> CREATE TABLE tbl_part_rg ( id number(10), date# date)
                2  PARTITION BY RANGE (date#) (
                3  PARTITION p1 VALUES LESS THAN (TO_DATE('01-JAN-2007','DD-MON-YYYY')),
                4  PARTITION p2 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY')),
                5  PARTITION p3 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY')),
                6  PARTITION p4 VALUES LESS THAN (MAXVALUE));
              
              Table created.
              
              SQL> insert into tbl_part_rg values (1, to_date('31122006','DDMMYYYY'));
              
              1 row created.
              
              SQL> insert into tbl_part_rg values (2, to_date('31122008','DDMMYYYY'));
              
              1 row created.
              
              SQL> commit;
              
              Commit complete.
              
              SQL> truncate table tbl_part_rg;
              
              Table truncated.
              (note, truncate a partioned table invalidate indexes).

              Nicolas.
              • 4. Re: Truncating A Partitioned Table
                Fahd.Mirza
                Well when I tried it with :

                Alter table test truncate;
                and I received the error.

                Actually I was seeing that in context of truncating the partition. like this
                ALTER TABLE test TRUNCATE PARTITION t1;
                Thanks for the correction.

                regards
                • 5. Re: Truncating A Partitioned Table
                  778931
                  Truncating a partitioned table without mentioning the partitions doesn't disturb my partitions structure right?

                  When you say that it invalidates the index, do you mean index becomes unusable or disabled? Should I rebuild the index than or re create it?

                  thanks a ton.
                  • 6. Re: Truncating A Partitioned Table
                    Lubiez Jean-Valentin
                    Hello,

                    Should I rebuild the index than or re create it?
                    Yes, to rebuild the index is enough.

                    Then, you may check its status with the following query:
                    select index_name, status 
                    from dba_indexes 
                    where index_name = '<index>';
                    For partitioned Indexes you may better use the following query:
                    select index_name, partition_name, status 
                    from dba_ind_partitions
                    where index_name = '<index>';
                    NB: For this last query on dba_ind_partitions you should get the status USABLE.


                    Hope this help.
                    Best regards,
                    Jean-Valentin