Split partition in table (Oracle 10g)

deniska

Oracle 10g

We have one table with huge amount of data

create table part_tab

(

  pt_id            NUMBER,

  pt_name          VARCHAR2(30),

  pt_date          DATE default SYSDATE,

  pt_lob           CLOB,

  pt_status        VARCHAR2(2)

)

partition by range (pt_date)

(

  partition PRT1 values less than (TO_DATE('2012-01-01', 'YYYY-MM-DD')),

  ...

  partition PRT2 values less than (TO_DATE('2014-09-01', 'YYYY-MM-DD')),

  partition PRT_MAX values less than (MAXVALUE)

  )

The table has

- Primary Key on pt_id column with unique index (1 Different table has FK constraint that refers to this PK)

- Composite index on pt_date and pt_name columns

The Task is to split partition(PRT_MAX) to a different tablespace

New partition(s) won't have data at the monet of creation

What is the best strategy for that?

Can I use following?

alter table part_tab split partition PRT_MAX at(TO_DATE('2019-08-01', 'YYYY-MM-DD')) into

(PARTITION PRT3 tablespace old_tablespace, PARTITION PRT_MAX tablespace new_tablespace);

or I have to add clob column in this script for clobs to be created in different tablespace later?

alter table part_tab split partition PRT_MAX at(TO_DATE('2019-08-01', 'YYYY-MM-DD')) into

(PARTITION PRT3 tablespace old_tablespace  LOB (pt_lob) store as (TABLESPACE old_tablespace), PARTITION PRT_MAX tablespace new_tablespace   LOB (pt_lob) store as (TABLESPACE new_tablespace));

Questions

1 Considering that new partition(s) in new_tablespace wont have data(during creation/splitting) can I use only one of these scripts without dropping/creating indexes/PK/FK?

I created demo table (with a couple of rows) and checked. All indexes(2) were valid.

If there is better way, please tell me about it. Should I think more about clobs?

John Thorton

Post SQL & results that show a problem that needs to be solved.

What problem are you trying to solve?

How do I ask a question on the forums?

deniska

I just want to add new partitions to an existing table(with club column and indexes)  in different tablespace.

And I asked some questions about this operation.

Mustafa_KALAYCI

if you want to move your lob segment too then you must use your second alter statement (the one with clob). I don't know what kind of indexes you have on table so it is hard to say. Do you have local indexes or global or both? you can use "update indexes" clause in alter statement so your indexes will be remain valid but splitting will take much more time. how many data do you have in your max partition and how many rows will be split from max partition?

Jonathan Lewis

How carefully did you test the two options - I've run up a couple of sample in 10.2.0.4 and neither option does quite what you want to do.

The general principle of "split partition" is that if either of the two new partitions would be empty Oracle decides to rename the existing partitions and create one new empty partition, but if there is data for both new partitions Oracle creates and populates two partitions and drops the original.  To see what it has done you need to look at the object_id and data_object_id from user_objects; and you also need to check the status from user_indexes and user_ind_partitions.

If you don't specify the LOB storage then the new (empty) lobs go into the current tablespace of the existing LOB.  If you specify the old and new locations for the LOB partitions then the old lob content is copied (in this version of Oracle) and the PK index becomes invalid and the relevant partitions of the other index become invalid.  You can "update indexes" with the split command - but then that still means you recreate the PK index.

You might like to test the split without specifying a tablespace for the OLD partitions, but specifying the tablespace for the new paritions - in a quick test this seemed to leave the indexes and index partitions valid, and simply rename the old partitions, while just creating new empty partitions.

(NB 10g is old - the split partition code on 12.2 where I reran the tests seemed to be cleverer and worked perfectly with the second version of your split partition code).

Regards

Jonathan Lewis