post CREATE TABLE statement for partitioned table
I will post the code here but.. the code is not related to my question. My question is how I can verify my rows are being inserted in the partitions I want..
CREATE TABLE sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
STORAGE (INITIAL 100K NEXT 50K) LOGGING
PARTITION BY RANGE (time_id)
( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))
TABLESPACE tsa STORAGE (INITIAL 20K NEXT 10K)
, PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))
, PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
, PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
ENABLE ROW MOVEMENT;
You just query the data for a partition and see if it is the data that should be in there.
SELECT * FROM sales PARTITION (sales_q3_2006)
Examine the result set and see if that is the right data.
Using a view you can only see the partition info like high value, partition position, num_rows but you don't get the entire row info.
We can do one thing here, while inserting the row check the value of the partition key column. Using the value we can check in user_tab_partitions to identify the partition.
select partition_name, partition_position, high_value, num_rows, is_nested from user_tab_partitions
where table_name='<table_name>' ;
Here high value will show which row falls into which partition. It's slightly different for range, list, and interval partitions.
I hope it would be useful.
Excellent answer rp0428
I will test it.. Thank you for helping me!