2 Replies Latest reply: Feb 2, 2013 4:19 AM by 767217 RSS

    Drop many partitions and shared pool

    767217
      I have table with many range partitions (5k), for example
      CREATE TABLE sales_range 
      (salesman_id  NUMBER(5), 
      salesman_name VARCHAR2(30), 
      sales_amount  NUMBER(10), 
      sales_date    DATE)
      tablespace users
      PARTITION BY RANGE(sales_date) 
      (
      PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')),
      PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')),
      PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')),
      PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY'))
      );
      I have to drop old partition and after dropping of first thousand got error ORA-04031: unable to allocate 4176 bytes of shared memory ('shared pool'...
      In library cache I found many object with status KEEP='YES', actually one record per one dropped partition and they allocated all free memory
      alter table sales drop partition sales_mar2000;
      alter table sales drop partition sales_apr2000;
      alter table sales drop partition sales_feb2000;
      
      select NAME, NAMESPACE, TYPE, KEPT, STATUS from V_$DB_OBJECT_CACHE where kept = 'YES' and owner = 'TST'
      
      NAME   NAMESPACE                                                       TYPE                                                             KEPT       STATUS             
      --------------------------------------------- ---------------------------------------------------------------- ---------- -------------------
      SALES  MULTI-VERSION OBJECT FOR TABLE                                   MULTI-VERSIONED OBJECT                                           YES        VALID               
      SALES  MULTI-VERSION OBJECT FOR TABLE                                   MULTI-VERSIONED OBJECT                                           YES        VALID               
      SALES  MULTI-VERSION OBJECT FOR TABLE                                   MULTI-VERSIONED OBJECT                                           YES        VALID               
      As I understand table is noneditionable object type so why can I see it in cache as MULTI-VERSIONED OBJECT?
      How could I drop many partitions without ORA-04031?

      Actually alter system flush shared_pool helps temporary fix issue, but this is not a good option.
      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
      PL/SQL Release 11.2.0.1.0 - Production
      CORE     11.2.0.1.0     Production
      NLSRTL Version 11.2.0.1.0 - Production
        • 1. Re: Drop many partitions and shared pool
          damorgan
          This:
          ORA-04031: unable to allocate 4176 bytes of shared memory ('shared pool'...
          has nothing to do with Edition Based Redefinition.

          You also wrote:
          Actually alter system flush shared_pool helps temporary fix issue, but this is not a good option.
          Why? This statement requires an explanation for us to help you further.

          Though I must confess I am fascinated by why anyone would have an 11.2.0.1 database. Wasn't there a single opportunity in the last couple of years to apply a patch?

          You may also want to run this query.
          select NAME, NAMESPACE, TYPE, KEPT, STATUS
          from V_$DB_OBJECT_CACHE
          where kept = 'YES'
          and namespace LIKE '%MULTI-VERSION%';
          You are misunderstanding the phrase "MULTI-VERSION'.
          • 2. Re: Drop many partitions and shared pool
            767217
            This was tested on other servers and result was the same.
            Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
            PL/SQL Release 11.2.0.2.0 - Production
            CORE     11.2.0.2.0     Production
            TNS for Solaris: Version 11.2.0.2.0 - Production
            NLSRTL Version 11.2.0.2.0 - Production
            
            Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
            PL/SQL Release 11.2.0.3.0 - Production
            CORE     11.2.0.3.0     Production
            TNS for Solaris: Version 11.2.0.3.0 - Production
            NLSRTL Version 11.2.0.3.0 - Production
            So what is this "MULTI-VERSIONED OBJECT"?