split PMAX partition with records inside

--my evn 10.2.0.2
Few things to take note during re-distribute records to new partitions:
1. Archived log gnerated , as rowmovement occurs. Rowid changed is the evidance. Here is my observation.
--rowid before split

select rowid, BILL_REF_NO, BILL_REF_RESETS from arbor.bill_invoice_detail partition(P01560_1) where BILL_REF_NO=155000001;

ROWID BILL_REF_NO BILL_REF_RESETS
------------------ ----------- ---------------
AAHsyTADUAABdzkAAD 155000001 1
AAHsyTADUAABdzkAAC 155000001 1
AAHsyTADUAABdzkAAB 155000001 1
AAHsyTADUAABdzkAAA 155000001 1

--split partition
10:55:50 SQL> alter table bill_invoice_detail split partition pmax
10:55:50 2 at (156000000,2) into (partition p01560_1 tablespace cust_bill_inv_det, partition pmax tablespace cust_bill_inv_det);

Table altered.

Elapsed: 00:09:44.28
11:05:34 SQL>
11:05:34 SQL> alter table bill_invoice_detail split partition pmax
11:05:34 2 at (157000000,2) into (partition p01570_1 tablespace cust_bill_inv_det, partition pmax tablespace cust_bill_inv_det);

Table altered.

Elapsed: 00:00:20.77
11:05:55 SQL>
11:05:55 SQL>
11:05:55 SQL> alter table bill_invoice_detail split partition pmax
11:05:55 2 at (158000000,2) into (partition p01580_1 tablespace cust_bill_inv_det, partition pmax tablespace cust_bill_inv_det);

Table altered.

Elapsed: 00:00:00.10
11:05:55 SQL>
11:05:55 SQL> alter table bill_invoice_detail split partition pmax
11:05:55 2 at (159000000,2) into (partition p01590_1 tablespace cust_bill_inv_det, partition pmax tablespace cust_bill_inv_det);

Table altered.

--rowid after split

select rowid, BILL_REF_NO, BILL_REF_RESETS from arbor.bill_invoice_detail partition(P01560_1) where BILL_REF_NO=155000001;

ROWID BILL_REF_NO BILL_REF_RESETS
------------------ ----------- ---------------
AAJM0sADXAABdzkAAG 155000001 1
AAJM0sADXAABdzkAAH 155000001 1
AAJM0sADXAABdzkAAI 155000001 1
AAJM0sADXAABdzkAAJ 155000001 1

2. Local index with records became unusable. Need to manually rebuild it.


11:20:07 SQL> exec dbms_stats.gather_table_stats(ownname=>'ARBOR', tabname=>'BILL_INVOICE_DETAIL');
BEGIN dbms_stats.gather_table_stats(ownname=>'ARBOR', tabname=>'BILL_INVOICE_DETAIL'); END;

*
ERROR at line 1:
ORA-20000: index "ARBOR"."BILL_INVOICE_DETAIL_PK" or partition of such index is in unusable state
ORA-06512: at "SYS.DBMS_STATS", line 13159
ORA-06512: at "SYS.DBMS_STATS", line 13179
ORA-06512: at line 1

13:24:09 SQL> select PARTITION_NAME, status from dba_ind_partitions where index_name='BILL_INVOICE_DETAIL_PK';

PARTITION_NAME STATUS
------------------------------ --------
P01470_1 USABLE
P01480_1 USABLE
P01490_1 USABLE
P01500_1 USABLE
P01510_1 USABLE
P01520_1 USABLE
P01530_1 USABLE
P01540_1 USABLE
P01550_1 USABLE
P01560_1 UNUSABLE
P01570_1 UNUSABLE
P01580_1 USABLE
P01590_1 USABLE
PMAX USABLE

14 rows selected.

13:28:27 SQL> alter index arbor.BILL_INVOICE_DETAIL_PK rebuild partition P01560_1 ;

Index altered.

Elapsed: 00:04:27.71
13:33:26 SQL> alter index arbor.BILL_INVOICE_DETAIL_PK rebuild partition P01570_1 ;

Index altered.

Elapsed: 00:01:47.79
13:36:04 SQL> select PARTITION_NAME, status from dba_ind_partitions where index_name='BILL_INVOICE_DETAIL_PK';

PARTITION_NAME STATUS
------------------------------ --------
P01470_1 USABLE
P01480_1 USABLE
P01490_1 USABLE
P01500_1 USABLE
P01510_1 USABLE
P01520_1 USABLE
P01530_1 USABLE
P01540_1 USABLE
P01550_1 USABLE
P01560_1 USABLE
P01570_1 USABLE
P01580_1 USABLE
P01590_1 USABLE
PMAX USABLE

14 rows selected.

ora-25143 can not change next extent size for locally managed tablespace with uniform size

--can not change next extent size for locally managed tablespace with uniform size.

SYS@FMSP> alter tablespace FMS_CUSTOMER_INDEX default storage (next 1048576);
alter tablespace FMS_CUSTOMER_INDEX default storage (next 1048576)
*
ERROR at line 1:
ORA-25143: default storage clause is not compatible with allocation policy



fms02@/software/orafms/admin/FMSP/bdump> oerr ora 25143
25143, 00000, "default storage clause is not compatible with allocation policy"
// *Cause: default storage clause was specified for a tablespace with
// AUTOALLOCATE or UNIFORM policy
// *Action: Omit the storage clause

Temporary segments do not span tablespaces of Temporary Tablespaces Group

I took for granted that single session with one big sort can benefit from tablespace group.
However, it is wrong ! No wonder our data warehouse often hit ora-1652.
--before make change , temp1,2,3 size is 16gb,16gb,19gb respectively
--This is means available temporary range is 16gb to 19gb.

The relevant metalink doc is 245645.1 and 248712.1

Reason is quite simple , Temporary segments do not span tablespaces. This easy to understand, same as other segment.


-- change support id to use small temp3 , which is the default database temporary tablespace (check from database_properties table)

-- remove temp1, temp2,temp3 from temp_group

alter tablespace TEMP1 tablespace group '';
alter tablespace TEMP2 tablespace group '';
alter tablespace TEMP3 tablespace group '';

--drop temp2

drop tablespace temp2 including contents and datafiles;

--epxand temp1;

--shrink temp3

--after make change , temp1,3 size is 40gb,10gb respectively
--assign application id to use big temporary tablespace


Since we don't parallel DML & have limited diskspace, tablespace group does't help.

From this practice, I think tablespace group is only good if you have lots of tablespace to create tablespaces with same size for round-robin assignment fashion.
Ideally, can create them on separate disks to reduce I/O contention.


How come important things is missed out in many articles searched by Google !?