1 2 3 Previous Next 33 Replies Latest reply on Apr 24, 2012 5:05 PM by 929791 Go to original post
      • 15. Re: ORA-04031:unable to allocate 140 bytes of shared memory.
        929791
        Yes, u r right, here is my scripts and outputs~~:


        ALTER SYSTEM SET MEMORY_MAX_TARGET=2G SCOPE=SPFILE;
        ALTER SYSTEM SET sga_target=1G SCOPE=SPFILE;

        restarted

        SELECT name, value
        FROM   v$parameter
        WHERE  name IN ('pga_aggregate_target', 'sga_target')
        UNION
        SELECT 'maximum PGA allocated' AS name, TO_CHAR(value) AS value
        FROM   v$pgastat
        WHERE  name = 'maximum PGA allocated';

        output:

        maximum PGA allocated     86233088
        pga_aggregate_target     419430400
        sga_target     494927872


        still don't know why i can't modified this.
        • 16. Re: ORA-04031:unable to allocate 140 bytes of shared memory.
          Fran
          could you do ALTER without problems? .please, check at alert.log any error.

          please, show us the output of:


          sho parameter sga_max_size;
          sho parameter memory_target;
          sho parameter memory_max_target;
          • 17. Re: ORA-04031:unable to allocate 140 bytes of shared memory.
            929791
            Here goes the alert.log:

            Mon Apr 23 19:47:45 2012
            ALTER SYSTEM SET memory_max_target='2G' SCOPE=SPFILE;
            Mon Apr 23 19:48:23 2012
            ALTER SYSTEM SET sga_target='1G' SCOPE=SPFILE;

            restarted

            Mon Apr 23 19:48:53 2012
            Cannot set memory_target (1073741824) < sga_target (1073741824) min pga_aggregate_target (12582912)+


            run:

            sho parameter sga_max_size;
            sho parameter memory_target;
            sho parameter memory_max_target;

            output:

            sga_max_size 616M
            memory_target 1G
            memory_max_target 1G

            run:

            SELECT SUM(value) FROM V$SGA

            output:

            644468736



            Best Regards
            • 18. Re: ORA-04031:unable to allocate 140 bytes of shared memory.
              Fran
              SGA+PGA can't have a higher value of memory_target this is why you can't change the value of sga_target.

              Fist, could you add more memory_target? (memory_target can't be higher than memory_max_target, if it necessary set the same value of both or set it a higher value) If yes, do it.
              Second, set higher value of SGA_TARGET again (sga_target can't be higher than sga_max_size, same like memory_target and Memory_max_target)

              HTH
              • 19. Re: ORA-04031:unable to allocate 140 bytes of shared memory.
                929791
                Thanks very much! I did

                ALTER SYSTEM SET sga_target=600M SCOPE=SPFILE;

                which is smaller than the sga_max_size 616;

                then restart, no error found in log; then :

                sho parameter sga_target;

                output:

                sga_target 472M

                I was not able to change the value.........
                • 20. Re: ORA-04031:unable to allocate 140 bytes of shared memory.
                  839439
                  AnneBonny wrote:
                  Here goes the alert.log:

                  Mon Apr 23 19:47:45 2012
                  ALTER SYSTEM SET memory_max_target='2G' SCOPE=SPFILE;
                  Mon Apr 23 19:48:23 2012
                  ALTER SYSTEM SET sga_target='1G' SCOPE=SPFILE;

                  restarted

                  Mon Apr 23 19:48:53 2012
                  Cannot set memory_target (1073741824) < sga_target (1073741824) min pga_aggregate_target (12582912)+


                  run:

                  sho parameter sga_max_size;
                  sho parameter memory_target;
                  sho parameter memory_max_target;

                  output:

                  sga_max_size 616M
                  memory_target 1G
                  memory_max_target 1G

                  run:

                  SELECT SUM(value) FROM V$SGA

                  output:

                  644468736



                  Best Regards
                  Hi
                  is this your database? give the info about the below

                  1.) what is your RAM size ?
                  2.) show parameter sga
                  3.) show parameter pool

                  check the below link :

                  http://neeraj-dba.blogspot.in/2011/05/ora-04030-out-of-process-memory.html
                  http://neeraj-dba.blogspot.in/2011/05/ora-04030-out-of-process-memory.html


                  --neeraj                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                  • 21. Re: ORA-04031:unable to allocate 140 bytes of shared memory.
                    929791
                    This time I restarted oracle, it corrupted~! T.T

                    when I tried to connect, it shows:

                    ORA-01034: ORACLE not available
                    ORA-27101: shared memory realm does not exist

                    A possile reason is SGA requires more space than was allocated for it

                    OH...............No.....................
                    • 22. Re: ORA-04031:unable to allocate 140 bytes of shared memory.
                      Fran
                      No, Oracle isn't corrupted. Just you change sga_target to a non-valid value (Probably, too low).

                      Just modified the value in the pfile and try to startup again the database with option "pfile='path_pfile'"
                      1 person found this helpful
                      • 23. Re: ORA-04031:unable to allocate 140 bytes of shared memory.
                        929791
                        Impressive...Thanks a lot. Now i can login via pfile now. This is the content of my "SPFILEXE.ORA", what should i change if i wanna login via spfile without error again....


                        xe.__db_cache_size=205520896
                        xe.__java_pool_size=4194304
                        xe.__large_pool_size=8388608
                        xe.__oracle_base='C:\oraclexe\app\oracle'#ORACLE_BASE set from environment
                        xe.__pga_aggregate_target=427819008
                        xe.__sga_target=645922816
                        xe.__shared_io_pool_size=104857600
                        xe.__shared_pool_size=314572800
                        xe.__streams_pool_size=0
                        *.audit_file_dest='C:\oraclexe\app\oracle\admin\XE\adump'
                        *.compatible='11.2.0.0.0'
                        *.control_files='C:\oraclexe\app\oracle\oradata\XE\control.dbf'
                        *.db_name='XE'
                        *.DB_RECOVEC C" A[  RY_FILE_DEST_SIZE=10G
                        *.DB_RECOVERY_FILE_DEST='C:\oraclexe\app\oracle\fast_recovery_area'
                        *.diagnostic_dest='C:\oraclexe\app\oracle\.'
                        *.dispatchers='(PROTOCOL=TCP) (SERVICE=XEXDB)'
                        *.job_queue_processes=4
                        *.memory_max_target=2147483648
                        *.memory_target=1073741824
                        *.open_cursors=300
                        *.pga_aggregate_target=0
                        *.remote_login_passwordfile='EXCLUSIVE'
                        *.sessions=20
                        *.sga_max_size=1073741824
                        *.sga_target=629145600
                        *.shared_pool_size=230686720
                        *.shared_servers=4
                        *.undo_management='AUTO'
                        *.unC C" Yf do_tablespace='UNDOTBS1'


                        Thanks again for your time
                        • 24. Re: ORA-04031:unable to allocate 140 bytes of shared memory.
                          Fran
                          you are using XE Oracle, i hate it and will recommend you to install a real oracle database. XE is good if you want to use sql's but too bad if you want to try manage databases.

                          By default XE has:

                          1G = memory_max_target and memory_target (i heared that can't be more, but not sure) <--if not defined, dont do anything.
                          256M =pga_aggregate_target
                          768M = sga_max_size/ sga_target
                          did you modified any value more?

                          try with this values, if you can start database and all is ok, recreate spfile with this pfile "create spfile='XXXX/xXXX/' from pfile='xxxxx/xxxx'"
                          If not please told us.

                          HTH
                          • 25. Re: ORA-04031:unable to allocate 140 bytes of shared memory.
                            929791
                            Well...Excuse me again.....the SPFILEXE.ORA file i use to startup the database is:

                            xe.__db_cache_size=205520896
                            xe.__java_pool_size=4194304
                            xe.__large_pool_size=8388608
                            xe.__oracle_base='C:\oraclexe\app\oracle'#ORACLE_BASE set from environment
                            xe.__pga_aggregate_target=268435456
                            xe.__sga_target=805306368
                            xe.__shared_io_pool_size=104857600
                            xe.__shared_pool_size=157286400
                            xe.__streams_pool_size=0
                            *.audit_file_dest='C:\oraclexe\app\oracle\admin\XE\adump'
                            *.compatible='11.2.0.0.0'
                            *.control_files='C:\oraclexe\app\oracle\oradata\XE\control.dbf'
                            *.db_name='XE'
                            *.DB_RECOVC C" W ERY_FILE_DEST_SIZE=10G
                            *.DB_RECOVERY_FILE_DEST='C:\oraclexe\app\oracle\fast_recovery_area'
                            *.diagnostic_dest='C:\oraclexe\app\oracle\.'
                            *.dispatchers='(PROTOCOL=TCP) (SERVICE=XEXDB)'
                            *.job_queue_processes=4
                            *.memory_target=1024M
                            *.open_cursors=300
                            *.remote_login_passwordfile='EXCLUSIVE'
                            *.sessions=20
                            *.shared_servers=4
                            *.undo_management='AUTO'
                            *.undo_tablespace='UNDOTBS1'

                            but it throw following errors:


                            SQL> startup
                            ORA-01078: failure in processing system parameters
                            ORA-01565: error in identifying file 'C:\oraclexe\app\oracle\product\11.2.0\serv
                            er\dbs/spfileXE.ora'
                            ORA-27046: file size is not a multiple of logical block size
                            OSD-04012: ?????????????? (OS 2558)
                            SQL>

                            Is the shared pool size wrong?



                            Regards
                            • 26. Re: ORA-04031:unable to allocate 140 bytes of shared memory.
                              929791
                              the above spfile is the one I generated from pfile,

                              and I modified directly in "SPFILEXE.ORA" the sax_max_size/sax_target/Pga_aggregate_target.....to the values as u specified. Did i omit important steps or?
                              • 27. Re: ORA-04031:unable to allocate 140 bytes of shared memory.
                                jgarry
                                AnneBonny wrote:
                                the above spfile is the one I generated from pfile,

                                and I modified directly in "SPFILEXE.ORA" the sax_max_size/sax_target/Pga_aggregate_target.....to the values as u specified. Did i omit important steps or?
                                Er, you mean you edited the spfile? Don't do that. You can change things in the pfile and create an spfile, or with alter and scope commands. The parameters with double underscores are the values Oracle was using, it modifies them as part of automatic memory management.

                                You are using XE, which as noted previously has explicit limitations. http://docs.oracle.com/cd/E17781_01/license.112/e18068/toc.htm#BABJBGGA It is not supported, but there are bugs that are fixed in the real versions, perhaps you are hitting one.

                                Perhaps if you cut and pasted your original query and error and alert log from the 4031 error with default init parameters we could help you better.
                                • 28. Re: ORA-04031:unable to allocate 140 bytes of shared memory.
                                  929791
                                  Sorry for offering the incomplete information. here is the log on my loggin via pfile, create spfile, and login through spfile:

                                  Tue Apr 24 07:52:37 2012
                                  Starting ORACLE instance (normal)
                                  LICENSE_MAX_SESSION = 0
                                  LICENSE_SESSIONS_WARNING = 0
                                  Picked latch-free SCN scheme 2
                                  Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
                                  Autotune of undo retention is turned on.
                                  IMODE=BR
                                  ILAT =18
                                  LICENSE_MAX_USERS = 0
                                  SYS auditing is disabled
                                  Starting up:
                                  Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production.
                                  Using parameter settings in client-side pfile C:\ORACLEXE\APP\ORACLE\ADMIN\XE\PFILE\PFILE2INIT.ORA on machine PC-20111022JVCS
                                  System parameters with non-default values:
                                  sessions = 172
                                  memory_target = 1G
                                  control_files = "C:\ORACLEXE\APP\ORACLE\ORADATA\XE\CONTROL.DBF"
                                  compatible = "11.2.0.0.0"
                                  db_recovery_file_dest = "C:\oraclexe\app\oracle\fast_recovery_area"
                                  db_recovery_file_dest_size= 10G
                                  undo_management = "AUTO"
                                  undo_tablespace = "UNDOTBS1"
                                  remote_login_passwordfile= "EXCLUSIVE"
                                  dispatchers = "(PROTOCOL=TCP) (SERVICE=XEXDB)"
                                  shared_servers = 4
                                  job_queue_processes = 4
                                  audit_file_dest = "C:\ORACLEXE\APP\ORACLE\ADMIN\XE\ADUMP"
                                  db_name = "XE"
                                  open_cursors = 300
                                  diagnostic_dest = "C:\ORACLEXE\APP\ORACLE"
                                  Tue Apr 24 07:53:06 2012
                                  PMON started with pid=2, OS id=5856
                                  Tue Apr 24 07:53:06 2012
                                  PSP0 started with pid=3, OS id=5852
                                  Tue Apr 24 07:53:07 2012
                                  VKTM started with pid=4, OS id=5892 at elevated priority
                                  VKTM running at (10)millisec precision with DBRM quantum (100)ms
                                  Tue Apr 24 07:53:07 2012
                                  GEN0 started with pid=5, OS id=5872
                                  Tue Apr 24 07:53:07 2012
                                  DIAG started with pid=6, OS id=5880
                                  Tue Apr 24 07:53:07 2012
                                  DBRM started with pid=7, OS id=864
                                  Tue Apr 24 07:53:07 2012
                                  DIA0 started with pid=8, OS id=5868
                                  Tue Apr 24 07:53:07 2012
                                  MMAN started with pid=9, OS id=5836
                                  Tue Apr 24 07:53:07 2012
                                  DBW0 started with pid=10, OS id=5840
                                  Tue Apr 24 07:53:07 2012
                                  LGWR started with pid=11, OS id=5888
                                  Tue Apr 24 07:53:07 2012
                                  CKPT started with pid=12, OS id=5848
                                  Tue Apr 24 07:53:07 2012
                                  SMON started with pid=13, OS id=624
                                  Tue Apr 24 07:53:07 2012
                                  RECO started with pid=14, OS id=3412
                                  Tue Apr 24 07:53:07 2012
                                  MMON started with pid=15, OS id=5216
                                  Tue Apr 24 07:53:07 2012
                                  starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
                                  starting up 4 shared server(s) ...
                                  ORACLE_BASE from environment = C:\oraclexe\app\oracle
                                  Tue Apr 24 07:53:07 2012
                                  MMNL started with pid=16, OS id=212
                                  Tue Apr 24 07:53:09 2012
                                  ALTER DATABASE MOUNT
                                  Successful mount of redo thread 1, with mount id 2662549925
                                  Database mounted in Exclusive Mode
                                  Lost write protection disabled
                                  Completed: ALTER DATABASE MOUNT
                                  Tue Apr 24 07:53:15 2012
                                  ALTER DATABASE OPEN
                                  Beginning crash recovery of 1 threads
                                  Started redo scan
                                  Completed redo scan
                                  read 25 KB redo, 23 data blocks need recovery
                                  Started redo application at
                                  Thread 1: logseq 261, block 67092, scn 6438829
                                  Recovery of Online Redo Log: Thread 1 Group 1 Seq 261 Reading mem 0
                                  Mem# 0: C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_1_7OXDX3L7_.LOG
                                  Completed redo application of 0.02MB
                                  Completed crash recovery at
                                  Thread 1: logseq 261, block 67142, scn 6460346
                                  23 data blocks read, 23 data blocks written, 25 redo k-bytes read
                                  Thread 1 advanced to log sequence 262 (thread open)
                                  Thread 1 opened at log sequence 262
                                  Current log# 2 seq# 262 mem# 0: C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_7OXDX6DQ_.LOG
                                  Successful open of redo thread 1
                                  SMON: enabling cache recovery
                                  [3168] Successfully onlined Undo Tablespace 2.
                                  Undo initialization finished serial:0 start:733875 end:734717 diff:842 (8 seconds)
                                  Verifying file header compatibility for 11g tablespace encryption..
                                  Verifying 11g file header compatibility for tablespace encryption completed
                                  Tue Apr 24 07:53:20 2012
                                  SMON: enabling tx recovery
                                  Database Characterset is AL32UTF8
                                  Opening with Resource Manager plan: INTERNAL_PLAN_XE
                                  Tue Apr 24 07:53:21 2012
                                  Starting background process VKRM
                                  Tue Apr 24 07:53:21 2012
                                  VKRM started with pid=22, OS id=3612
                                  replication_dependency_tracking turned off (no async multimaster replication found)
                                  Starting background process QMNC
                                  Tue Apr 24 07:53:24 2012
                                  QMNC started with pid=24, OS id=1284
                                  Tue Apr 24 07:53:26 2012
                                  Completed: ALTER DATABASE OPEN
                                  Tue Apr 24 07:53:32 2012
                                  Starting background process CJQ0
                                  Tue Apr 24 07:53:32 2012
                                  CJQ0 started with pid=30, OS id=5016
                                  Tue Apr 24 07:53:34 2012
                                  db_recovery_file_dest_size of 10240 MB is 9.12% used. This is a
                                  user-specified limit on the amount of space that will be used by this
                                  database for recovery-related files, and does not reflect the amount of
                                  space available in the underlying filesystem or ASM diskgroup.
                                  Tue Apr 24 07:58:26 2012
                                  Starting background process SMCO
                                  Tue Apr 24 07:58:26 2012
                                  SMCO started with pid=26, OS id=5020
                                  Tue Apr 24 08:04:58 2012
                                  ALTER SYSTEM SET memory_target='1G' SCOPE=MEMORY;
                                  Tue Apr 24 08:08:53 2012
                                  ALTER SYSTEM SET pga_aggregate_target='256M' SCOPE=MEMORY;
                                  Tue Apr 24 08:11:23 2012
                                  Shutting down instance (immediate)
                                  Stopping background process SMCO
                                  Shutting down instance: further logons disabled
                                  Stopping background process QMNC
                                  Tue Apr 24 08:11:25 2012
                                  Stopping background process CJQ0
                                  Stopping background process MMNL
                                  Stopping background process MMON
                                  License high water mark = 5
                                  All dispatchers and shared servers shutdown
                                  ALTER DATABASE CLOSE NORMAL
                                  Tue Apr 24 08:11:28 2012
                                  SMON: disabling tx recovery
                                  SMON: disabling cache recovery
                                  Tue Apr 24 08:11:29 2012
                                  Shutting down archive processes
                                  Archiving is disabled
                                  Archive process shutdown avoided: 0 active
                                  Thread 1 closed at log sequence 262
                                  Successful close of redo thread 1
                                  Completed: ALTER DATABASE CLOSE NORMAL
                                  ALTER DATABASE DISMOUNT
                                  Completed: ALTER DATABASE DISMOUNT
                                  ARCH: Archival disabled due to shutdown: 1089
                                  Shutting down archive processes
                                  Archiving is disabled
                                  ARCH: Archival disabled due to shutdown: 1089
                                  Shutting down archive processes
                                  Archiving is disabled
                                  Tue Apr 24 08:11:31 2012
                                  Stopping background process VKTM
                                  Tue Apr 24 08:11:34 2012
                                  Instance shutdown complete
                                  Tue Apr 24 08:19:10 2012
                                  Starting ORACLE instance (normal)
                                  LICENSE_MAX_SESSION = 0
                                  LICENSE_SESSIONS_WARNING = 0
                                  Picked latch-free SCN scheme 2
                                  Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
                                  Autotune of undo retention is turned on.
                                  IMODE=BR
                                  ILAT =18
                                  LICENSE_MAX_USERS = 0
                                  SYS auditing is disabled
                                  Starting up:
                                  Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production.
                                  Using parameter settings in server-side pfile C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\INITXE.ORA
                                  System parameters with non-default values:
                                  sessions = 172
                                  spfile = "C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DBS\SPFILEXE.ORA"
                                  memory_target = 1G
                                  control_files = "C:\ORACLEXE\APP\ORACLE\ORADATA\XE\CONTROL.DBF"
                                  compatible = "11.2.0.0.0"
                                  db_recovery_file_dest = "C:\oraclexe\app\oracle\fast_recovery_area"
                                  db_recovery_file_dest_size= 10G
                                  undo_management = "AUTO"
                                  undo_tablespace = "UNDOTBS1"
                                  remote_login_passwordfile= "EXCLUSIVE"
                                  dispatchers = "(PROTOCOL=TCP) (SERVICE=XEXDB)"
                                  shared_servers = 4
                                  job_queue_processes = 4
                                  audit_file_dest = "C:\ORACLEXE\APP\ORACLE\ADMIN\XE\ADUMP"
                                  db_name = "XE"
                                  open_cursors = 300
                                  diagnostic_dest = "C:\ORACLEXE\APP\ORACLE"
                                  Tue Apr 24 08:19:34 2012
                                  PMON started with pid=2, OS id=4404
                                  Tue Apr 24 08:19:34 2012
                                  PSP0 started with pid=3, OS id=6088
                                  Tue Apr 24 08:19:35 2012
                                  VKTM started with pid=4, OS id=5916 at elevated priority
                                  VKTM running at (10)millisec precision with DBRM quantum (100)ms
                                  Tue Apr 24 08:19:35 2012
                                  GEN0 started with pid=5, OS id=5776
                                  Tue Apr 24 08:19:35 2012
                                  DIAG started with pid=6, OS id=3800
                                  Tue Apr 24 08:19:35 2012
                                  DBRM started with pid=7, OS id=4992
                                  Tue Apr 24 08:19:35 2012
                                  DIA0 started with pid=8, OS id=1092
                                  Tue Apr 24 08:19:35 2012
                                  MMAN started with pid=9, OS id=2168
                                  Tue Apr 24 08:19:35 2012
                                  DBW0 started with pid=10, OS id=1152
                                  Tue Apr 24 08:19:35 2012
                                  LGWR started with pid=11, OS id=4928
                                  Tue Apr 24 08:19:35 2012
                                  CKPT started with pid=12, OS id=6072
                                  Tue Apr 24 08:19:35 2012
                                  SMON started with pid=13, OS id=4936
                                  Tue Apr 24 08:19:35 2012
                                  RECO started with pid=14, OS id=5572
                                  Tue Apr 24 08:19:35 2012
                                  MMON started with pid=15, OS id=6108
                                  Tue Apr 24 08:19:35 2012
                                  starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
                                  starting up 4 shared server(s) ...
                                  ORACLE_BASE from environment = C:\oraclexe\app\oracle
                                  Tue Apr 24 08:19:35 2012
                                  ALTER DATABASE MOUNT
                                  Tue Apr 24 08:19:35 2012
                                  MMNL started with pid=16, OS id=2196
                                  Successful mount of redo thread 1, with mount id 2662563543
                                  Database mounted in Exclusive Mode
                                  Lost write protection disabled
                                  Completed: ALTER DATABASE MOUNT
                                  Tue Apr 24 08:19:40 2012
                                  ALTER DATABASE OPEN
                                  Thread 1 opened at log sequence 262
                                  Current log# 2 seq# 262 mem# 0: C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_7OXDX6DQ_.LOG
                                  Successful open of redo thread 1
                                  SMON: enabling cache recovery
                                  Spfile C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DBS\SPFILEXE.ORA is in old pre-11 format and compatible >= 11.0.0; converting to new H.A.R.D. compliant format.
                                  [3228] Successfully onlined Undo Tablespace 2.
                                  Undo initialization finished serial:0 start:2316755 end:2317145 diff:390 (3 seconds)
                                  Verifying file header compatibility for 11g tablespace encryption..
                                  Verifying 11g file header compatibility for tablespace encryption completed
                                  SMON: enabling tx recovery
                                  Database Characterset is AL32UTF8
                                  Opening with Resource Manager plan: INTERNAL_PLAN_XE
                                  Starting background process VKRM
                                  Tue Apr 24 08:19:43 2012
                                  VKRM started with pid=22, OS id=6036
                                  replication_dependency_tracking turned off (no async multimaster replication found)
                                  Starting background process QMNC
                                  Tue Apr 24 08:19:46 2012
                                  QMNC started with pid=24, OS id=568
                                  Completed: ALTER DATABASE OPEN
                                  Tue Apr 24 08:19:52 2012
                                  Starting background process CJQ0
                                  Tue Apr 24 08:19:52 2012
                                  CJQ0 started with pid=26, OS id=3404
                                  Tue Apr 24 08:19:53 2012
                                  db_recovery_file_dest_size of 10240 MB is 9.12% used. This is a
                                  user-specified limit on the amount of space that will be used by this
                                  database for recovery-related files, and does not reflect the amount of
                                  space available in the underlying filesystem or ASM diskgroup.
                                  Tue Apr 24 08:23:46 2012
                                  Shutting down instance (normal)
                                  Shutting down instance: further logons disabled
                                  Tue Apr 24 08:23:46 2012
                                  Stopping background process CJQ0
                                  Stopping background process QMNC
                                  Stopping background process MMNL
                                  Stopping background process MMON
                                  License high water mark = 3
                                  All dispatchers and shared servers shutdown
                                  ALTER DATABASE CLOSE NORMAL
                                  Tue Apr 24 08:23:51 2012
                                  SMON: disabling tx recovery
                                  SMON: disabling cache recovery
                                  Tue Apr 24 08:23:52 2012
                                  Shutting down archive processes
                                  Archiving is disabled
                                  Archive process shutdown avoided: 0 active
                                  Thread 1 closed at log sequence 262
                                  Successful close of redo thread 1
                                  Completed: ALTER DATABASE CLOSE NORMAL
                                  ALTER DATABASE DISMOUNT
                                  Completed: ALTER DATABASE DISMOUNT
                                  ARCH: Archival disabled due to shutdown: 1090
                                  Shutting down archive processes
                                  Archiving is disabled
                                  ARCH: Archival disabled due to shutdown: 1090
                                  Shutting down archive processes
                                  Archiving is disabled
                                  Tue Apr 24 08:23:53 2012
                                  Stopping background process VKTM
                                  Tue Apr 24 08:23:55 2012
                                  Instance shutdown complete
                                  Tue Apr 24 08:26:05 2012
                                  Starting ORACLE instance (normal)
                                  LICENSE_MAX_SESSION = 0
                                  LICENSE_SESSIONS_WARNING = 0
                                  Picked latch-free SCN scheme 2
                                  Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
                                  Autotune of undo retention is turned on.
                                  IMODE=BR
                                  ILAT =18
                                  LICENSE_MAX_USERS = 0
                                  SYS auditing is disabled
                                  Starting up:
                                  Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production.
                                  Using parameter settings in server-side pfile C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\INITXE.ORA
                                  System parameters with non-default values:
                                  sessions = 172
                                  spfile = "C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DBS\SPFILEXE.ORA"
                                  memory_target = 1G
                                  control_files = "C:\ORACLEXE\APP\ORACLE\ORADATA\XE\CONTROL.DBF"
                                  compatible = "11.2.0.0.0"
                                  db_recovery_file_dest = "C:\oraclexe\app\oracle\fast_recovery_area"
                                  db_recovery_file_dest_size= 10G
                                  undo_management = "AUTO"
                                  undo_tablespace = "UNDOTBS1"
                                  remote_login_passwordfile= "EXCLUSIVE"
                                  dispatchers = "(PROTOCOL=TCP) (SERVICE=XEXDB)"
                                  shared_servers = 4
                                  job_queue_processes = 4
                                  audit_file_dest = "C:\ORACLEXE\APP\ORACLE\ADMIN\XE\ADUMP"
                                  db_name = "XE"
                                  open_cursors = 300
                                  diagnostic_dest = "C:\ORACLEXE\APP\ORACLE"
                                  Tue Apr 24 08:26:33 2012
                                  PMON started with pid=2, OS id=236
                                  Tue Apr 24 08:26:33 2012
                                  PSP0 started with pid=3, OS id=3540
                                  Tue Apr 24 08:26:33 2012
                                  VKTM started with pid=4, OS id=5652 at elevated priority
                                  VKTM running at (10)millisec precision with DBRM quantum (100)ms
                                  Tue Apr 24 08:26:33 2012
                                  GEN0 started with pid=5, OS id=3184
                                  Tue Apr 24 08:26:33 2012
                                  DIAG started with pid=6, OS id=5612
                                  Tue Apr 24 08:26:33 2012
                                  DBRM started with pid=7, OS id=5696
                                  Tue Apr 24 08:26:33 2012
                                  DIA0 started with pid=8, OS id=5800
                                  Tue Apr 24 08:26:33 2012
                                  MMAN started with pid=9, OS id=5624
                                  Tue Apr 24 08:26:33 2012
                                  DBW0 started with pid=10, OS id=5196
                                  Tue Apr 24 08:26:33 2012
                                  LGWR started with pid=11, OS id=3336
                                  Tue Apr 24 08:26:33 2012
                                  CKPT started with pid=12, OS id=1640
                                  Tue Apr 24 08:26:33 2012
                                  SMON started with pid=13, OS id=1440
                                  Tue Apr 24 08:26:33 2012
                                  RECO started with pid=14, OS id=3516
                                  Tue Apr 24 08:26:33 2012
                                  MMON started with pid=15, OS id=5216
                                  Tue Apr 24 08:26:33 2012
                                  MMNL started with pid=16, OS id=4508
                                  Tue Apr 24 08:26:33 2012
                                  starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
                                  starting up 4 shared server(s) ...
                                  ORACLE_BASE from environment = C:\oraclexe\app\oracle
                                  Tue Apr 24 08:26:33 2012
                                  alter database mount exclusive
                                  Successful mount of redo thread 1, with mount id 2662515577
                                  Database mounted in Exclusive Mode
                                  Lost write protection disabled
                                  Completed: alter database mount exclusive
                                  alter database open
                                  Thread 1 opened at log sequence 262
                                  Current log# 2 seq# 262 mem# 0: C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_7OXDX6DQ_.LOG
                                  Successful open of redo thread 1
                                  SMON: enabling cache recovery
                                  Tue Apr 24 08:26:45 2012
                                  [4456] Successfully onlined Undo Tablespace 2.
                                  Undo initialization finished serial:0 start:2738550 end:2739642 diff:1092 (10 seconds)
                                  Verifying file header compatibility for 11g tablespace encryption..
                                  Verifying 11g file header compatibility for tablespace encryption completed
                                  Tue Apr 24 08:26:45 2012
                                  SMON: enabling tx recovery
                                  Database Characterset is AL32UTF8
                                  Opening with Resource Manager plan: INTERNAL_PLAN_XE
                                  Tue Apr 24 08:26:46 2012
                                  Starting background process VKRM
                                  Tue Apr 24 08:26:46 2012
                                  VKRM started with pid=23, OS id=576
                                  replication_dependency_tracking turned off (no async multimaster replication found)
                                  Starting background process QMNC
                                  Tue Apr 24 08:26:51 2012
                                  QMNC started with pid=24, OS id=6088
                                  Completed: alter database open
                                  Tue Apr 24 08:27:05 2012
                                  Starting background process CJQ0
                                  Tue Apr 24 08:27:05 2012
                                  CJQ0 started with pid=30, OS id=1932
                                  Tue Apr 24 08:27:06 2012
                                  db_recovery_file_dest_size of 10240 MB is 9.12% used. This is a
                                  user-specified limit on the amount of space that will be used by this
                                  database for recovery-related files, and does not reflect the amount of
                                  space available in the underlying filesystem or ASM diskgroup.
                                  Tue Apr 24 08:27:16 2012
                                  Starting background process SMCO
                                  Tue Apr 24 08:27:16 2012
                                  SMCO started with pid=31, OS id=3964
                                  Tue Apr 24 08:32:25 2012
                                  XDB installed.
                                  XDB initialized.



                                  Then I tried :

                                  ALTER SYSTEM SET sga_max_size=768M scope=both;

                                  output:
                                  02095. 00000 - "specified initialization parameter cannot be modified"
                                  *Cause:    The specified initialization parameter is not modifiable


                                  Thanks again
                                  • 29. Re: ORA-04031:unable to allocate 140 bytes of shared memory.
                                    929791
                                    this is my current spfile:

                                    xe.__db_cache_size=205520896
                                    xe.__java_pool_size=4194304
                                    xe.__large_pool_size=8388608
                                    xe.__oracle_base='C:\oraclexe\app\oracle'#ORACLE_BASE set from environment
                                    xe.__pga_aggregate_target=427819008
                                    xe.__sga_target=645922816
                                    xe.__shared_io_pool_size=104857600
                                    xe.__shared_pool_size=314572800
                                    xe.__streams_pool_size=0
                                    *.audit_file_dest='C:\oraclexe\app\oracle\admin\XE\adump'
                                    *.compatible='11.2.0.0.0'
                                    *.control_files='C:\oraclexe\app\oracle\oradata\XE\control.dbf'
                                    *.db_name='XE'
                                    *.DB_RECOVC C" W ERY_FILE_DEST_SIZE=10G
                                    *.DB_RECOVERY_FILE_DEST='C:\oraclexe\app\oracle\fast_recovery_area'
                                    *.diagnostic_dest='C:\oraclexe\app\oracle\.'
                                    *.dispatchers='(PROTOCOL=TCP) (SERVICE=XEXDB)'
                                    *.job_queue_processes=4
                                    *.memory_target=1024M
                                    *.open_cursors=300
                                    *.remote_login_passwordfile='EXCLUSIVE'
                                    *.sessions=20
                                    *.shared_servers=4
                                    *.undo_management='AUTO'
                                    *.undo_tablespace='UNDOTBS1'