This discussion is archived
6 Replies Latest reply: Jun 20, 2010 8:43 AM by 778931 RSS

Truncating A Partitioned Table

778931 Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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
    FahdMirza Oracle ACE
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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
    FahdMirza Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

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