主页 / user-21042

Rachcha's questions

Martin Hope
Rachcha
Asked: 2013-07-20 00:09:57 +0800 CST
  • 0

我在本地开发机器上运行 Oracle 11g R1。

我一直在尝试运行一个匿名块来测试FORALLOracle 中插入 25,000 条记录的语句。

我收到以下错误：

ORA-04031: unable to allocate 4708660 bytes of shared memory ("shared pool","DECLARE
  -- temp_rec temp%R...","PL/SQL SOURCE","PLD: Source Heap")
04031. 00000 -  "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
*Cause:    More shared memory is needed than was allocated in the shared
           pool.
*Action:   If the shared pool is out of memory, either use the
           dbms_shared_pool package to pin large packages,
           reduce your use of shared memory, or increase the amount of
           available shared memory by increasing the value of the
           INIT.ORA parameters "shared_pool_reserved_size" and
           "shared_pool_size".
           If the large pool is out of memory, increase the INIT.ORA
           parameter "large_pool_size".

我尝试在这里和这里查看，但无法解决此问题。

我尝试了以下方法：

ALTER SYSTEM FLUSH BUFFER_CACHE;  
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM SET cursor_sharing = 'SIMILAR' SCOPE=BOTH;

请帮忙。

编辑

我正在使用的代码如下：

DECLARE
  TYPE t_varchar IS TABLE OF varchar(512 char);
  biz_hierarchy t_varchar := t_varchar();
  project_team t_varchar := t_varchar();
  user_roles t_varchar := t_varchar();
  username t_varchar := t_varchar();
  sso t_varchar := t_varchar();
BEGIN
  SELECT *
  BULK COLLECT INTO biz_hierarchy, project_team, user_roles, username, sso
  FROM ... -- Query fetches 25000 records

  FORALL i IN biz_hierarchy.FIRST..biz_hierarchy.LAST
    INSERT INTO temp VALUES (biz_hierarchy(i), project_team(i),
                              user_roles(i), username(i), sso(i));

END;
/

Oracle 返回的错误没有提到行号。如果我尝试加载 5000 条记录，则代码块运行成功。当我尝试使用 25000 时失败。

oracle oracle-11g