OraFAQ Forum: Server Utilities » Alter Shared pool

Home » RDBMS Server » Server Utilities » Alter Shared pool (ORACLE 11G,Linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Alter Shared pool [message #556354]

Sat, 02 June 2012 05:34

chanchu
Messages: 11
Registered: May 2012

Junior Member

SQL> show parameter shared_pool;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 6710886
shared_pool_size big integer 152M
SQL> alter system set shared_pool_size=180M;
alter system set shared_pool_size=180M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool

Report message to a moderator

Re: Alter Shared pool [message #556355 is a reply to message #556354]

Sat, 02 June 2012 05:39

chanchu
Messages: 11
Registered: May 2012

Junior Member

when i try to export schema i got error like "Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6.25 MB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE

UDE-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")
ORA-06512: at "SYS.KUPV$FT_INT", line 2904
ORA-06512: at "SYS.KUPC$QUE_INT", line 572
ORA-25254: time-out in LISTEN while waiting for a message
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3263
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4488
ORA-06512: at line 1"

then i tried to alter the shared pool

then i got like
"SQL> alter system set shared_pool_size=180M;
alter system set shared_pool_size=180M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool
"
how to solve this issue?

Report message to a moderator

Re: Alter Shared pool [message #556358 is a reply to message #556355]

Sat, 02 June 2012 09:18

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

how much RAM exists for this system?
Most folks allow Oracle to decide SHARED POOL SIZE.

07:12:53 SQL> show parameter shared_pool_size

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size		     big integer 0
07:13:04 SQL> show parameter sga

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
lock_sga			     boolean	 FALSE
pre_page_sga			     boolean	 FALSE
sga_max_size			     big integer 1488M
sga_target			     big integer 0

Report message to a moderator

Re: Alter Shared pool [message #556360 is a reply to message #556355]

Sat, 02 June 2012 09:32

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

how to solve this issue?

ALTER SYSTEM ... SCOPE=SPFILE; and restart.

Regards
Michel

Report message to a moderator

Previous Topic:	Transport Tablespace with database link
Next Topic:	Escape character for loading data via sql loader

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 03 16:50:19 CST 2025