[Oracle] When I would like to add the new partition tables.

I have had data_all table on dbuser user.

1. Create tablespace for dbuser

CREATE TABLESPACE data_JUN_2007 DATAFILE '+DATA' SIZE 1G AUTOEXTEND ON
NEXT 100M MAXSIZE UNLIMITED NOLOGGING EXTENT MANAGEMENT LOCAL SEGMENT
SPACE MANAGEMENT AUTO;

CREATE TABLESPACE data_JUL_2007 DATAFILE '+DATA' SIZE 1G AUTOEXTEND ON
NEXT 100M MAXSIZE UNLIMITED NOLOGGING EXTENT MANAGEMENT LOCAL SEGMENT
SPACE MANAGEMENT AUTO;

CREATE TABLESPACE IND_data_JUN_2007 DATAFILE '+DATA' SIZE 500M AUTOEXTEND
ON NEXT 50M MAXSIZE UNLIMITED NOLOGGING EXTENT MANAGEMENT LOCAL SEGMENT
SPACE MANAGEMENT AUTO;

CREATE TABLESPACE IND_data_JUL_2007 DATAFILE '+DATA' SIZE 500M AUTOEXTEND
ON NEXT 50M MAXSIZE UNLIMITED NOLOGGING EXTENT MANAGEMENT LOCAL SEGMENT
SPACE MANAGEMENT AUTO;



ALTER USER dbuser QUOTA UNLIMITED ON data_JUN_2007;
ALTER USER dbuser QUOTA UNLIMITED ON data_JUL_2007;
ALTER USER dbuser QUOTA UNLIMITED ON IND_data_JUN_2007;
ALTER USER dbuser QUOTA UNLIMITED ON IND_data_JUL_2007;



2. add the new partitions to data_all and allocate

ALTER TABLE "dbuser"."data_ALL" ADD PARTITION "data_JUN_2007" VALUES
LESS THAN (TO_DATE('7/1/2007','MM/DD/YYYY')) TABLESPACE "data_JUN_2007"
UPDATE INDEXES;

ALTER TABLE "dbuser"."data_ALL" ADD PARTITION "data_JUL_2007" VALUES
LESS THAN (TO_DATE('8/1/2007','MM/DD/YYYY')) TABLESPACE "data_JUL_2007"
UPDATE INDEXES;

alter table data_all modify partition data_JUN_2007 allocate extent
(size 6144M);
alter table data_all modify partition data_JUL_2007 allocate extent
(size 6144M);



3. create script for rebuild index partitions to new tablespaces

----------------------------------
select 'ALTER INDEX '||index_name || ' REBUILD PARTITION data_JUN_2007
TABLESPACE IND_data_JUN_2007 ONLINE COMPUTE STATISTICS;' from
useR_indexes where table_name='data_ALL';
----------------------------------



----------------------------------
select 'ALTER INDEX '||index_name || ' REBUILD PARTITION data_JUL_2007
TABLESPACE IND_data_JUL_2007 ONLINE COMPUTE STATISTICS;' from
useR_indexes where table_name='data_ALL';
----------------------------------


---------------------------------
select 'alter index '||index_name || ' modify partition data_JUN_2007
allocate extent (size 100M); ' from useR_indexes where
table_name='data_ALL';
---------------------------------



---------------------------------
select 'alter index '||index_name || ' modify partition data_JUL_2007
allocate extent (size 100M); ' from useR_indexes where
table_name='data_ALL';


4. Check

select table_name, partition_name, tablespace_name from user_tab_partitions where table_name='DATA_ALL';


select index_name ,partition_name, tablespace_name from user_ind_partitions where index_name in (select index_name from user_indexes where table_name='DATA_ALL') order by index_name;