Jump to content


  • Content count

  • Joined

  • Last visited

Community Reputation

0 Neutral

About ayush

  • Rank
  1. Hey Donald, It is not a bug. We compress the previous week's data. Our table is partitioned on daily basic and sub partitioned on an integer column. For each week we have 5 partition and each partition has around 500 sub partition. Every week we run a task which will compress the data of last week. Basically, on a weekend we run the below command for 5 partitions: 1) alter table t1 modify partition p1 compress(forms outer loop for 5 partitions.) 2) alter table t1 MOVE SUBPARTITION p1_s1 (this will be in inner loop for all the 550 sub partitions) This step 2 is making the index unusable.
  2. Thanks Donald, it means i need to work with Shared Pool. We need to rebuild index because they become unusable. We have our fact tables partitioned on a daily basis. On weekends we compress the data for last week. Basically we change the partition property to compress and do an alter table move subpartition. So all the local indexes go into unusable state. I cannot use NOLOGGING because at tablespace level we have set parameter as force logging, for Data Guard.
  3. Thanks Donald for a quick reply. " The loads of SQL statements are serialized and locked in exclusive mode, such that only one process can load an object or a piece of an object at a time. " I just want to understand the meaning of this line. When you say an object, does it refer to the table or the subpartition of the table? As per my understanding, a subpartition of a table is a different object altogether, so 2 subpartition should not be blocking each other, if i have sufficient shared pool available. Please let me know if my understanding is correct or not. If my understanding is wrong then i need to re-look into the code design else I need to look into re-sizing the shared pool.
  4. Hi Donald, i am trying to build multiple indexes in parallel session i have table t1 which has partition p1 and sub partitions p1s1,p1s2,p1s3,p1s4......p1s500 from 15 parallel session i am picking 15 indexes and am trying to rebuild it. so my 15 session will be like: sess1: alter index i1 rebuild subpartition i1_p1s1 rebuild sess2: alter index i1 rebuild subpartition i1_p1s2 rebuild sess3: alter index i1 rebuild subpartition i1_p1s3 rebuild sess4: alter index i1 rebuild subpartition i1_p1s4 rebuild sess5: alter index i1 rebuild subpartition i1_p1s5 rebuild . . . . sess15: alter index i1 rebuild subpartition i1_p1s15 rebuild Now the problem i face is, these 14 sessions are waiting with library cache lock till session 1 completes it library cache load is it a normal behavior? I was under impression that subpartitions are different objects so one subpartition should not block the others work. machine details: num of instances: 4 num cpu : 80 num cpu core : 80 Oracle version: 11Gr2( 4 node RACS SGA: 300GB Shared pool: 30GB