This discussion is archived
2 Replies Latest reply: Feb 2, 2013 2:19 AM by 767217 RSS

Drop many partitions and shared pool

767217 Newbie
Currently Being Moderated
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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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"?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points