Hello
there is this table that was partition and had billions of blocks, so I was asked to look for the none compressed partitions and compress the which I did. After compressing, I check the indexes and they were all "unusable" so I had to make them usable before I can compress. I don't know why in some circumstances, I had to drop that index and rebuild it. but when I want to create a global index, it takes forever to create and fail with error of temp space. and I cant reduce the water mark without those index.Do any one have any idea on how I can go about the creation of this index? and is there any advice and direction I get?
bellow are some of the sql I used and this in 11g
alter table PJMOW.LOAD_FACTOR MOVE PARTITION SYS_P24413 COMPRESS;
alter table PJMOW.LOAD_FACTOR MOVE PARTITION SYS_P24788 COMPRESS;
alter table PJMOW.LOAD_FACTOR MOVE PARTITION SYS_P25150 COMPRESS;
alter table PJMOW.LOAD_FACTOR MOVE PARTITION SYS_P25534 COMPRESS;
alter table PJMOW.LOAD_FACTOR MOVE PARTITION SYS_P25909 COMPRESS;
alter table PJMOW.LOAD_FACTOR MOVE PARTITION SYS_P25909 COMPRESS update index; ( this syntax never worked) failed with error that some of the partition could not be updated)
CREATE UNIQUE INDEX LOAD_SHIFT_FACTOR_PK
ON PJMOW.LOAD_FACTOR(FLOWGATE_NERC_ID);
errors I get
ORA-1013 signalled during: ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP...
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
WARNING: Heavy swapping observed on system in last 5 mins.
pct of memory swapped in [17.45%] pct of memory swapped out [11.49%].
Please make sure there is no memory pressure and the SGA and PGA
are configured correctly. Look at DBRM trace file for more details.