Truncate subpartition doesn't remove data

[sambavan]

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

[davey23uk]

evidence?

[sambavan]

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

[slimdave]

Try

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

[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.

[marist89]

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