Truncate subpartition doesn't remove data
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Truncate subpartition doesn't remove data

Hybrid View

  1. #1
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925

    Truncate subpartition doesn't remove data

    Hi Folks,

    This had been a mystry to me. I created a composite patrtition table on a 9i database, whose primary partition is of RANGE and the subpartition of LIST.

    Now when I issue



    ALTER TABLE table_name
    TRUNCATE SUBPARTITION sub_partition_name
    REUSE/DROP STORAGE;



    it says the "Table truncated". Then when I do a query on the subpartition, it fetches me all the data that was there before the truncate.

    Any thoughts on this?

    Thanx,
    Sam
    Last edited by sambavan; 08-10-2005 at 12:51 PM.
    Thanx
    Sam



    Life is a journey, not a destination!


  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    evidence?

  3. #3
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Here is the evidence


    1 select term_code, count(*)
    2 from academic.student_in_section
    3 where data_type ='HISTORY'
    4 and term_code = 200505
    5* group by term_code
    SQL> /

    TERM_C COUNT(*)
    ------ ----------
    200505 109

    SQL> alter table academic.student_in_section truncate subpartition term_200505_history
    2 reuse storage;

    Table truncated.

    SQL> select term_code, count(*)
    2 from academic.student_in_section
    3 where data_type ='HISTORY'
    4 and term_code = 200505
    5 group by term_code;

    TERM_C COUNT(*)
    ------ ----------
    200505 109

    SQL> alter table academic.student_in_section truncate subpartition term_200505_history
    2 drop storage;

    Table truncated.

    SQL> select term_code, count(*)
    2 from academic.student_in_section
    3 where data_type ='HISTORY'
    4 and term_code = 200505
    5 group by term_code;

    TERM_C COUNT(*)
    ------ ----------
    200505 109




    Thanx,
    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Try
    Code:
    Select count(*) from academic.student_in_section subpartition term_200505_history
    /
    ... then check your partitioning is really correct ... ie. that partition 200505 really does hold the values for 200505
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Thanx slimdave for the tip. The problem was at the base itself, on how the partition had been defined. That that we have fixed it, every one here is happy, that I'm happy.
    Thanx
    Sam



    Life is a journey, not a destination!


  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Quote Originally Posted by sambavan
    Thanx slimdave for the tip. The problem was at the base itself, on how the partition had been defined. That that we have fixed it, every one here is happy, that I'm happy.
    'cept maybe you truncated some data that wasn't supposed to be truncated...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width