oracle 11g ons,Creating range paritions automatically

rp0428,

Thanks a lot that worked, I created needed paritions and exchanged them. One last thing is, If I see from a developer perspective, at the end data is in new table, that means code need to be changed to access the table with new name, so I tired dropping the old table after data is moved and renaming the new table with old table, even that worked, but after that am not able to insert any records into the new table, not sure why its behaving like that, I see the table has a virtual columns in it. Should I change my insert statements? Can you please advise.

-- Original table manually range partitioned on a TIMESTAMPE WITH LOCAL TIME ZONE column

SQL> CREATE TABLE Part_timezone_new1

(

USERID NUMBER,

ENTRYCREATEDZONE TIMESTAMP(6) with local time zone

)

PARTITION BY RANGE (ENTRYCREATEDZONE)

( PARTITION P0 VALUES LESS THAN (TIMESTAMP '2012-07-01 00:00:00.000000000+00:00'),

PARTITION P1 VALUES LESS THAN (TIMESTAMP '2012-08-01 00:00:00.000000000+00:00'),

PARTITION P2 VALUES LESS THAN (TIMESTAMP '2012-09-01 00:00:00.000000000+00:00'),

PARTITION P3 VALUES LESS THAN (TIMESTAMP '2012-10-01 00:00:00.000000000+00:00')

);

Table created.

SQL> insert into Part_timezone_new1 values (1, systimestamp - 270);

1 row created.

SQL> insert into Part_timezone_new1 values (1, systimestamp - 240);

1 row created.

SQL> insert into Part_timezone_new1 values (1, systimestamp - 210);

1 row created.

SQL> insert into Part_timezone_new1 values (1, systimestamp - 150);

1 row created.

SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,PARTITION_POSITION,INTERVAL from dba_tab_partitions where TABLE_NAME='PART_TIMEZONE_NEW1';

TABLE_NAME PARTITION_NAME HIGH_VALUE PARTITION_POSITION INT

------------------------------ ------------------------------ ---------------------------------------- ------------------ ---

PART_TIMEZONE_NEW P0 TIMESTAMP' 2012-07-01 00:00:00.000000000 1 NO

+00:00'

PART_TIMEZONE_NEW P1 TIMESTAMP' 2012-08-01 00:00:00.000000000 2 NO

+00:00'

PART_TIMEZONE_NEW P2 TIMESTAMP' 2012-09-01 00:00:00.000000000 3 NO

+00:00'

PART_TIMEZONE_NEW P3 TIMESTAMP' 2012-10-01 00:00:00.000000000 4 NO

+00:00'

-- New INTERVAL partitioned table using a new VIRTUAL column

SQL> CREATE TABLE Part_timezone_virtual_new12

(

USERID NUMBER,

ENTRYCREATEDZONE TIMESTAMP(6) with local time zone,

ENTRYCREATEDATE DATE GENERATED ALWAYS AS (cast(ENTRYCREATEDZONE as date)) VIRTUAL

)

PARTITION BY RANGE (ENTRYCREATEDATE) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))

(

PARTITION P0 VALUES LESS THAN (TO_DATE('1-7-2012', 'DD-MM-YYYY')),

PARTITION P1 VALUES LESS THAN (TO_DATE('1-8-2012', 'DD-MM-YYYY')),

PARTITION P2 VALUES LESS THAN (TO_DATE('1-9-2012', 'DD-MM-YYYY')),

PARTITION P3 VALUES LESS THAN (TO_DATE('1-10-2012', 'DD-MM-YYYY'))

)

;

Table created.

-- New work table to support the EXCHANGE PARTITION

SQL> CREATE TABLE Part_timezone_work_new1

(

USERID NUMBER,

ENTRYCREATEDZONE TIMESTAMP(6) with local time zone

);

Table created.

-- Swap the original partition with the work table

SQL> alter table Part_timezone_new1 exchange partition p0 with table part_timezone_work_new1;

Table altered.

-- Swap the work table data into the partition of the new table

SQL> alter table part_timezone_virtual_new1 exchange partition p0 with table part_timezone_work_new1;

Table altered.

-- Repeat the same for all parititons

SQL> alter table Part_timezone_new1 exchange partition p1 with table part_timezone_work_new1;

Table altered.

SQL> alter table part_timezone_virtual_new1 exchange partition p1 with table part_timezone_work_new1;

SQL> alter table Part_timezone_new1 exchange partition p2 with table part_timezone_work_new1;

Table altered.

SQL> alter table part_timezone_virtual_new1 exchange partition p2 with table part_timezone_work_new1;

Table altered.

SQL> alter table Part_timezone_new1 exchange partition p3 with table part_timezone_work_new1;

Table altered.

SQL> alter table part_timezone_virtual_new1 exchange partition p3 with table part_timezone_work_new1;

Table altered.

--- Check all the new records are transferred from Old table to new table

SQL> select count(*) from Part_timezone_new1;

COUNT(*)

----------

0

SQL> select count(*) from Part_timezone_virtual_new1;

COUNT(*)

----------

4

SQL> select count(*) from part_timezone_work_new1;

COUNT(*)

----------

0

--- Drop the old table after all the records are transferred to new table

drop table Part_timezone_new1;

--- Rename the new table to old table

alter table Part_timezone_virtual_new1 rename to Part_timezone_new1;

--- Check the count in the renamed table

SQL> select count(*) from Part_timezone_new1;

COUNT(*)

----------

4

-- Check if you are able to insert records for future date

SQL> insert into PART_TIMEZONE_NEW1 values (1,systimestamp + 30);

insert into PART_TIMEZONE_NEW1 values (1,systimestamp + 30)

*

ERROR at line 1:

ORA-00947: not enough values

SQL> insert into PART_TIMEZONE_NEW1 values (1,systimestamp + 30,'13-MAR-2013');

insert into PART_TIMEZONE_NEW1 values (1,systimestamp + 30,'13-MAR-2013')

*

ERROR at line 1:

ORA-54013: INSERT operation disallowed on virtual columnsEdited by: user12241421 on Feb 16, 2013 3:45 AM