If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

well, believe it or not, that statement should work if you simply switch the lines with LOCAL and your STORAGE parameters; LOCAL has to be specified IMMEDIATELY BEFORE your specific partition attributes...no default parameters can be specified between LOCAL and your specific partition attributes.

anyway, i went ahead and typed out a case scenario for you. hopefully, this will clear up any confusion and problems you might encounter.

-- Shows us partitions and corresponding tablespace
-- for all 3 our indexes just created
SELECT index_name, partition_name, tablespace_name
FROM user_ind_partitions
WHERE index_name in ('EMPX1','EMPX2','EMPX3')
ORDER BY index_name, partition_name;

-- Okay! Now everything's up and working! Let's
-- go through a Fiscal Quarter End and build the
-- new partitions and indexes for our next
-- Fiscal Quarter.

-- Step 4: Split the table's Error Partition
-- to get a new table partition
--
-- Note that a new index partition will also be built
-- automatically using its default attributes (which
-- we just modified in the last step
ALTER TABLE emp SPLIT PARTITION emp_err AT (TO_DATE('10/01/2002','mm/dd/yyyy')) INTO
(PARTITION emp_100102 TABLESPACE emp1_ts
STORAGE ( INITIAL 16K NEXT 16K PCTINCREASE 0) LOGGING,
PARTITION emp_err);

-- Now let's check to confirm that the new
-- table partition went where we wanted it
-- to go...into the EMP1_TS Tablespace
SELECT partition_name, tablespace_name
FROM user_tab_partitions
WHERE table_name = 'EMP'
ORDER BY partition_name;

-- Okay that worked. Now let's confirm that
-- the new index partition was built into
-- the correct tablespace, EMPX2_TS
SELECT partition_name, tablespace_name
FROM user_ind_partitions
WHERE index_name = 'EMPX1'
ORDER BY partition_name;

-- EVERYTHING CHECKS OUT!!!
-- But let's do it again for the next Fiscal
-- Quarter, just to make sure.