Wednesday, April 29, 2009

ORA-4030

This error indicates that the oracle server process is unable to allocate more memory from the operating system.This memory consists of the PGA
This could be caused by your process itself, like your process is just requesting too much memory, or some other reasons cause the operating system memory to be depleted, like a too big SGA or too many processes to be accomadated for the systems virtual memory (physical memory + swap space).

To resolve this error check the following:

1) Is there still sufficient memory available?
2) Is there an operating system limit set?
3) Is there an oracle limit set?
4) Which process is requesting too much memory?
5) How to collect information on what the process is actually doing?

References : Metalink Note 233869.1

Identify the usage of pfile or spfile

In oracle 9i and up spfile is the preferred way to start the instance.
In order to see if an instance has started with pfile or spfile as sys using sqlplus run :

select decode(count(*), 1, 'spfile', 'pfile' ) init_file_type
from v$spparameter whe rerownum=1 and isspecified='TRUE';

Also you can use the show parameter spfile.

In order to create a spfile from pfile the instance must be down.
Then run

create spfile from pfile; to use the default location which is $ORACLE_HOME/dbs on Unix and %ORACLE_HOME%/database on Windows.

In order to create the pfile if the instance has started with spfile run:

Create pfile from spfile;

Friday, April 10, 2009

ORA-07445: exception encountered: core dump [kkdliac()

Create or Replace view as select may fail with the following error

ORA-07445: Exception aufgetreten: CORE Dump [kkdliac()+72] [SIGSEGV] [Address not mapped to
object] [0x000000004] [] []

Call stack:

ksedmp ssexhd sigacthandler kkdliac cvwdrv opiexe opiosq0 opiall0 opial7 opiodr ttcpip

To be seeing this bug the following must be true:
1. A core dump is seen in kkdliac() during a create or alter view operation
2. The view would have been compiled with compilation errors
3. The select list is of the form: (select-list-item(s)), (table).*


Cause
ORA-07445 [kkdliac] error reported while creating or altering the view using "Create.. as Select"
due to Bug 2678804. Bug is fixed in 11g RDBMS version.

Details:
Executing a "CREATE .. FORCE VIEW" against a non-existent table might core
dump if the select list contains (table).* where table is the
non-existent table and another select list item precedes the (table).* entry.


Solution
To avoid this error

1) Create the view without compilation errors, like avoid creating a view against a non-existent table, and

2) Use the complete column names in select list, instead of "table_name.*"