
	Home > Ask the Oracle Experts > Oracle Performance Tuning Questions & Answers > How to increase SGA_MAX_SIZE in Oracle

Ask The Oracle Expert: Questions & Answers

EMAIL THIS

How to increase SGA_MAX_SIZE in Oracle

EXPERT RESPONSE FROM: Phillip Bracken

		Pose a Question

		Other Oracle Categories

		Meet all Oracle Experts
		Become an Expert for this site

Oracle tips, scripts, and expert advice

Digg This! StumbleUpon Del.icio.us

QUESTION POSED ON: 22 May 2007
How can I increase SGA_MAX_SIZE in Oracle ? I got the error "ORA-03113: end-of-file on communication channel" during startup. Please help me with the steps to increase the SGA. I'm connected to Oracle9i Enterprise Edition Release 9.0.1.1.1 (Production) with the partitioning option.
SQL> connect sys as sysdba Enter password: ****** Connected. SQL> show sga; Total System Global Area 147615836 bytes Fixed Size 282716 bytes Variable Size 113246208 bytes Database Buffers 33554432 bytes Redo Buffers 532480 bytes SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string %ORACLE_HOME%DATABASESPFILE% ORACLE_SID%.ORA SQL> create pfile from spfile; File created. SQL> alter system set sga_max_size=20m scope=spfile; System altered. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORA-03113: end-of-file on communication channel

EXPERT RESPONSE
Increase the Oracle SGA_MAX_SIZE setting in your pfile that you created from your spfile. Then in SQL*Plus start up the database using that pfile as follows.

Startup pfile=/u00/oracle/admin/ora/pfile/init.ora

Then re-create your spfile.

Create spfile=/u00/oracle/admin/ora/pfile/spfile.ora 
  from pfile==/u00/oracle/admin/ora/pfile/init.ora

For more information on SGA_MAX_SIZE in Oracle :
Database fully utilized with hang

Digg This!

StumbleUpon

Del.icio.us

RELATED CONTENT

	Oracle Performance Tuning
	Can I use TKPROF in Oracle 10g?
	How to improve performance of column update
	Select on Oracle table takes hours
	Import of database dump takes 13 hours
	Slow query performance for customer with large data volume
	ORA-03297 when trying to resize tablespace
	Improve performance of SQL query
	SMON process consuming 100% CPU
	Total Oracle CPU time
	See tablespace utilization/database size without DBA privileges

Oracle database performance problems and tuning

Oracle 11g data compression

Varchar or number for better performance?

Do statistics on SYS-owned objects hurt performance in 10g?

Inside the Oracle 11g SQL Performance Advisor, part 1

Inside the Oracle 11g SQL Performance Advisor, part 2

Difference between driving table and driver table in Oracle

Best design for E-Business Suite on hard drive

20GB data dictionary causing performance problems

Using the cost-based optimizer to improve Database 10g performance

Online tablespace reorganization in Oracle 9i

Oracle error messages

ORA-12560 error with Oracle 10g Instant Client

Unable to view Oracle tables in NetBeans

IMP-00017 error message with Oracle import

Can't open database after failed backup export

Query on dblink returning ORA-12545 error

RMAN not working at command prompt

Unique constraint violations, "parent key not found" errors

Insufficient privileges error when creating stored procedure

IMP-00010 error with import from 10g to 9i

ORA-12801 error while loading seed data

RELATED RESOURCES

2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems

Search Bitpipe.com for the latest white papers and business webcasts

Whatis.com, the online computer dictionary

Search and Browse the Expert Answer Center
Search and browse more than 25,000 question and answer pairs from more than 250 TechTarget industry experts.

Browse our Expert Advice
Operating Systems IT Management Networking Database	Security Servers and Storage Applications and Development Career Center

SEARCH

TechTarget provides enterprise IT professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective IT purchase decisions and managing their organizations' IT projects - with its network of technology-specific Web sites, events and magazines.


TechTarget Corporate Web Site \| Media Kits \| Reprints \| Site Map All Rights Reserved, Copyright 2003 - 2008, TechTarget \| Read our Privacy Policy