Truncate subpartition doesn't remove data

Printable View

08-10-2005, 12:48 PM
sambavan

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
08-10-2005, 01:03 PM davey23uk evidence?
08-10-2005, 01:12 PM sambavan Here is the evidence :D 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
08-10-2005, 01:57 PM slimdave 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
08-10-2005, 05:24 PM 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.
08-10-2005, 06:30 PM marist89 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...