2 Replies Latest reply: Apr 23, 2013 8:08 AM by User511360-OC RSS

    ORA-04031 while trying to import schema to XE

    User511360-OC
      Hello. I have Amazon Linux "micro instance" EC2 (so, virtual machine) that has 613 MB of memory. I installed Oracle XE 11.2.0.2 and now try to import schema from 11.2 oracle server standart edition:
      impdp system/pwd dumpfile=mydump schemas=myschema

      But I got the following error:
      ...
      Processing object type SCHEMA_EXPORT/TABLE/TABLE
      Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
      UDI-04031: operation generated ORACLE error 4031
      ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","SELECT job_id FROM v$datapum...","SQLA","tmp")

      Is it 613 MB of RAM enough for running XE ? Here is memory output:

      SQL> show sga

      Total System Global Area 250523648 bytes
      Fixed Size 2225472 bytes
      Variable Size 239078080 bytes
      Database Buffers 4194304 bytes
      Redo Buffers 5025792 bytes
        • 1. Re: ORA-04031 while trying to import schema to XE
          clcarter
          613 MB of RAM enough for running XE ?
          The recommended minimum is 512MB, so in theory it should be enough.
          Total System Global Area 250523648 bytes
          That's less than 250MB. With most Oracle installs, for 11gR2, I'd prefer to have at least 512MB for the SGA. But you have to leave some room for the OS, and PGA is a separate memory allocation for the instance as well. May be able to shrink PGA, and give a bit more room either to SGA_TARGET or MEMORY_TARGET, depends on what's been tweeked, and what is still at the default settings.

          Take a look at the non default startup parameters, in your alert log find the last startup, the lines between "System parameters with non-default values:" and maybe a date stamp next, paste in what you've got we might can help with some suggestions to try out.
          • 2. Re: ORA-04031 while trying to import schema to XE
            User511360-OC
            Thank you for answer, unfortunately I have no time to investigate. I migrated to another instance with 1,7GB of RAM and now import is OK.