Oracle‎ > ‎SQL‎ > ‎

Random Data

SQL to create random range-partitioned (by date) data:

create table p_objects
tablespace tbs1
partition by range(rdate) (
        partition p201301 values less than (to_date('2013/02/01','YYYY/MM/DD')),
        partition p201302 values less than (to_date('2013/03/01','YYYY/MM/DD')),
        partition p201303 values less than (to_date('2013/04/01','YYYY/MM/DD')),
        partition p201304 values less than (to_date('2013/05/01','YYYY/MM/DD')),
        partition p201305 values less than (to_date('2013/06/01','YYYY/MM/DD')),
        partition p201306 values less than (to_date('2013/07/01','YYYY/MM/DD')),
        partition p201307 values less than (to_date('2013/08/01','YYYY/MM/DD')),
        partition p201308 values less than (to_date('2013/09/01','YYYY/MM/DD')),
        partition p201309 values less than (to_date('2013/10/01','YYYY/MM/DD')),
        partition p201310 values less than (to_date('2013/11/01','YYYY/MM/DD')),
        partition p201311 values less than (to_date('2013/12/01','YYYY/MM/DD')),
        partition p201312 values less than (to_date('2014/01/01','YYYY/MM/DD'))
)
as select object_id
        , owner
        , object_name
        , object_type
        , to_date(trunc(dbms_random.value(
                to_char(to_date('2013/01/01','YYYY/MM/DD'),'J'),
                to_char(to_date('2013/12/31','YYYY/MM/DD'),'J')
                )),'J') rdate
from all_objects;

create index p_part_idx on p_objects (rdate, object_id) local
tablespace tbs1;

create index p_global_idx on p_objects (owner, object_name) global
tablespace tbs1;

exec dbms_stats.gather_table_stats('DTS','P_OBJECTS');

select partition_name, tablespace_name, num_rows
from user_tab_partitions
where table_name='P_OBJECTS'
order by partition_name;

Comments