H.Tonguç YILMAZ Blog

create table parent nologging parallel 8  PARTITION BY RANGE (line)
( PARTITION part1 VALUES LESS THAN (100) ,
PARTITION part2 VALUES LESS THAN (10000) ,
PARTITION part3 VALUES LESS THAN (100000) ) as select * from dba_source;
create table parent_clone nologging parallel 8 as select * from parent where 1 = 0;
EXEC DBMS_STATS.gather_table_stats(’CLON’, ‘parent’, estimate_percent => 10, block_sample => FALSE, degree => 16, granularity => ‘ALL’, cascade => TRUE) ;
EXEC DBMS_STATS.gather_table_stats(’CLON’, ‘parent_clone’, estimate_percent => 10, block_sample => FALSE, degree => 16, granularity => ‘ALL’, cascade => TRUE) ;
commit;
SELECT a.segment_name, a.partition_name, a.segment_type, a.blocks
FROM user_segments a
WHERE segment_name LIKE ‘%PARENT%’;

SEGMENT_NAME                                                                     PARTITION_NAME                 SEGMENT_TYPE           BLOCKS
——————————————————————————– —————————— —————— ———-
PARENT_CLONE                                                                                                    TABLE                     320
PARENT                                                                           PART1                          TABLE PARTITION          1280
PARENT                                                                           PART2                          TABLE PARTITION          1280
PARENT                                                                           PART3                          TABLE PARTITION           320

alter table parent
exchange partition part1 with table parent_clone
including indexes without validation;
alter table parent drop partition part1 ;
EXEC DBMS_STATS.gather_table_stats(’CLON’, ‘parent’, estimate_percent => 10, block_sample => FALSE, degree => 16, granularity => ‘ALL’, cascade => TRUE) ;
EXEC DBMS_STATS.gather_table_stats(’CLON’, ‘parent_clone’, estimate_percent => 10, block_sample => FALSE, degree => 16, granularity => ‘ALL’, cascade => TRUE) ;
commit;
SELECT a.segment_name, a.partition_name, a.segment_type, a.blocks
FROM user_segments a
WHERE segment_name LIKE ‘%PARENT%’;

SEGMENT_NAME                                                                     PARTITION_NAME                 SEGMENT_TYPE           BLOCKS
——————————————————————————– —————————— —————— ———-
PARENT_CLONE                                                                                                    TABLE                    1280
PARENT                                                                           PART2                          TABLE PARTITION          1280
PARENT                                                                           PART3                          TABLE PARTITION           320

Syntax for SPLIT PARTITION
ALTER TABLE