ORA-04031: shared memory error

How to solve this error?

1. Find out which application is causing this error. Zero down on which package/procedure is loaded and try to keep it in shared pool by pinning it.
2. Sometimes, the application may not give the errors. In which case, set an event in init.ora, as follows and generate a trace file.

event = "4031 trace name errorstack level 2"

or with 9i and higher and spfiles you can issue

alter system set events='4031 trace name errorstack level 2';

What to look for in the trace?
The trace contains a dump of state objects, when the error occurs.

Look for 'load=X' a few lines below that 'name='[name of the object]. So, this error occurs at the time loading this object. Pin that object in the shared pool, thereby keeping it.

How to Pin objects to shared Pool?
Pinning objects to the shared pool is a key to tuning your shared pool. Having objects pinned will reduce fragmentation and changes of encountering the ORA-04031 error.

You must determine which objects to pin. These are particular to your own database, the application you are running, the size of your database, and the activity on your database.

You need to pin objects when any of these happen:
1. If you have encountered the ORA-04031 already and need to resolve it or
2. You can also pin large packages frequently used by the users. (It is better to pin necessary objects while startup of database.

Pinning a package to shared pool

1. Oracle automatically loads SYS.STANDARD, SYS.DBMS_STANDARD and SYS.DIUTIL.  Here is an example: 

 

pk1 is a package with a variable called dummy.  Assigning dummy to a value and then executing the package will load it into the shared pool: 

Example:-                                                                   

begin  

pk1.dummy := 0 ; /* THIS ASSIGNMENT TO THE DUMMY VARIABLE IS BY */  

end;               /*    EXECUTING THE PACKAGE. */   

                                                                            

2.Then you must pin the package. Here is an example: 

execute dbms_shared_pool.keep(owner.pk1);

Pinning stored procedure/function to shared pool

You can pin procedures and triggers with the dbms_shared_pool procedure. Either procedures or packages can be pinned with the 'P' flag, which is the default value (so you can leave it out). Triggers are pinned with 'R' and anonymous plsql blocks need any letter other than [p,P,r,R] as a flag.  Refer to dbmspool.sql for more documentation. 

Here is an example: 

execute dbms_shared_pool.keep(owner.trigger, 'R');

What objects should I pin?

You can check the x$ksmlru fixed table.  This table keeps track of the objects and the corresponding number of objects flushed out of the shared pool to allocate space for the load. These objects are stored and flushed out based on the Least Recently Used (LRU) algorithm. 

             

Because this is a fixed table, once you query the table, Oracle will automatically reset the table, thus, you can only query the table once. Suggestion for workaround: spool the output to a file so you can capture the output for analysis. 

       

describe x$ksmlru        

    

Table or View x$ksmlru          

Name                            Null?    Type             

------------------------------- -------- --------------            ADDR                                     RAW(4) 

INDX                                     NUMBER             

KSMLRCOM                                 VARCHAR2(20)             

KSMLRSIZ                                 NUMBER                     

KSMLRNUM                                 NUMBER            

 

KSMLRNUM stores the number of objects that were flushed to load the large object. KSMLRISZ stores the size of the object that was loaded (contiguous memory allocated) 

We do not need the other columns. 

 

Here is an example of a query you issue to find all the objects that are larger than size 5k which you may want to pin:    

select * from x$ksmlru where ksmlrsiz > 5000;    

                      

In general, pinning SYS.STANDARD ,SYS.DBMS_STANDARD & SYS.DIUTIL which are large packages used by Oracle, should help.   

 

SYS.DIUTIL is used only during generations of SQL*forms so it may not be necessary to pin this package in your production database. 

ORA-25153-Temporary Tablespace is Empty-Error solved

*** SOLVED ***

We have a linux server in our office, and yesterday the machine got restarted (power failure). We were able to bring up our Oracle database server afterwards but there was some problems with temporary tablespace.

We were running a procedure when we encountered this error:

ORA-25153: Temporary Tablespace is Empty

Once querying dba_temp_files view, there were no temp files being listed. Though the temporary file was residing in the oradata folder and the dba_tablespaces where the tablespace was present. 

The Solution:

As it was a temporary tablespace, I could add a tempfile to the same and made the procedure working:

ALTER TABLESPACE temp ADD tempfile '/fullpath.dbf' SIZE 50M;

The error ORA 25153 got solved.

NOTE: Do not give the same name as the existing file in oradata folder. Let the file REST IN PEACE, give a new name. For example if you have temp01.dbf existing create with temp02.dbf.

ORA-06502: PL/SQL: numeric or value error

This error mainly occurs due to one of the following:
1. If you assign NULL value to a NOT NULL field.
2. If you assign number which is larger than the precision of the field.
3. If you assign character values greater than the field width.

Simply stating the value being assigned is not a valid value which can be assigned to the string/numeric field.

For example:

declare
test varchar2(1);
begin
test := 'I am here';
end;

The above block gives the error:
ORA-06502: PL/SQL: numeric or value error: character buffer too small
ORA-06512: at line 4

Of course in this you are getting more information as to the character variable is too small to hold the value you are assigning.

Now consider the following example:

declare
test number(2);
begin
test := 100;
end;

The above block gives this error:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 4

So now you know more about this frequently occuring error.

Some tips to avoid this error:
1. If you are assigning some values from a table to a variable always use the %TYPE declaration.
For Ex:
declare
test my_table.my_field%TYPE;
begin
select my_field from my_table where id=1;
end;

The above declaration methodology is a very efficient one in handling ORA-06502 error.

2. If you are not assigning the values from a table, but rather from some calculation, then use proper validation methodology.
For Ex:
declare
sum_value number(2);
begin
sum_value := 10 + 90;
exception
when value_error then
dbms_output.put_line('Raise your error here');
end;

3. If you are concatenating two strings together also use the above WHEN VALUE_ERROR in exception block to validate your data.
4. It is better method to put a value assigning code inside a seperate BEGIN END block and validate it using EXCEPTION block.

Hope you are solving this error. Best of luck. For more information, comment in this post with your email id and I will reply back.

EXP-00056: Oracle Error when trying to export 10.2.0.3 database

I am trying to export a 10.2.0.3 database, and I am getting an error. I understand that one of the most common causes of these errors is that I am using a higher version of the export utility compared to my RDBMS version. But this is not the case. My database version is 10.2.0.3 and the exp version 10.2.0.3
EXP-00056: ORACLE error 6550 encountered ORA-06550: line 1, column 41:PLS-00302: component 'SET_NO_OUTLINES' must be declared ORA-06550: line 1, column 15: PL/SQL: Statement ignoredEXP-00000: Export terminated unsuccessfully

>> Possbile Solutions :

It could be that your Data Dictionary is not set to support this higher version of export. Sign on to your database as SYS and run the @?/rdbms/admin/catexp.sql script. If that doesn't fix the problem, try rerunning @?/rdbms/admin/catalog.sql and @?/rdbms/admin/catproc.sql scripts.

ORA-06502: PL/SQL: numeric or value error

The docs note this on the ORA-06502 error:

ORA-06502: PL/SQL: numeric or value error string

Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).

Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.

More Information by Anantha:

This error mainly occurs due to one of the following:
1. If you assign NULL value to a NOT NULL field.
2. If you assign number which is larger than the precision of the field.
3. If you assign character values greater than the field width.

Simply stating the value being assigned is not a valid value which can be assigned to the string/numeric field.

For example:

declare
test varchar2(1);
begin
test := 'I am here';
end;

The above block gives the error:
ORA-06502: PL/SQL: numeric or value error: character buffer too small
ORA-06512: at line 4

Of course in this you are getting more information as to the character variable is too small to hold the value you are assigning.

Now consider the following example:

declare
test number(2);
begin
test := 100;
end;

The above block gives this error:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 4

So now you know more about this frequently occuring error.

Some tips to avoid this error:
1. If you are assigning some values from a table to a variable always use the %TYPE declaration.
For Ex:
declare
test my_table.my_field%TYPE;
begin
select my_field from my_table where id=1;
end;

The above declaration methodology is a very efficient one in handling ORA-06502 error.

2. If you are not assigning the values from a table, but rather from some calculation, then use proper validation methodology.
For Ex:
declare
sum_value number(2);
begin
sum_value := 10 + 90;
exception
when value_error then
dbms_output.put_line('Raise your error here');
end;

3. If you are concatenating two strings together also use the above WHEN VALUE_ERROR in exception block to validate your data.
4. It is better method to put a value assigning code inside a seperate BEGIN END block and validate it using EXCEPTION block.

ORA-04031: Unable to allocate %s bytes of shared memory

ORA-04031: Unable to allocate %s bytes of shared memory

 

Solutions:

 

alter system set shared_pool_size=100M; -- size you have to decide based

alter system set large_pool_size=100M;   -- on sga_max_size

 

Oracle limits the increase of size up to SGA_MAX_SIZE parameter defined in the initialization parameter file. SGA memory can not be increase beyond SGA_MAX_SIZE. If SGA_MAX_SIZE parameter is not enough for increasing the memory of dynamic parameters, you will get a ORA-00384 error. In this case either you have to increase the SGA_MAX_SIZE parameter or decrease the memory of dynamic parameter.

 

alter system set db_cache_size=135m;

 

alter system set db_cache_size=135m

 

*

 

ERROR at line 1:

 

ORA-02097: parameter cannot be modified because specified value is invalid

 

ORA-00384: Insufficient memory to grow cache

 

 

alter system set  SGA_MAX_SIZE=150M scope=spfile;

 

System altered.

 

The above command change the  limit of max size  to 150M. You have to shutdown and restart the system to make this effect. As of Oracle 9.0.1.1.1, people mostly get ORA-03113: end-of-file on communication channel, when they  startup the database after shutdown immediate. The best solution for this is to exit from sqlplus and login again.

 

The following SQL query will help you to calculate the approximate size of SGA:

 

select SUM(VALUE)+(1024*1024) from v$parameter where name in (

 'db_16k_cache_size','db_2k_cache_size','db_32k_cache_size',

  'db_4k_cache_size','db_8k_cache_size','db_cache_size',

  'db_keep_cache_size','db_recycle_cache_size',

  'java_pool_size','large_pool_size',

  'shared_pool_size','log_buffer');

 

SUM(VALUE)+(1024*1024)

----------------------

             156762112

 

show parameter cache

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- --------------

db_16k_cache_size                    big integer 0

db_2k_cache_size                     big integer 0

db_32k_cache_size                    big integer 0

db_4k_cache_size                     big integer 0

db_8k_cache_size                     big integer 0

db_cache_advice                      string      OFF

db_cache_size                        big integer 33554432

 

DB_CACHE_SIZE is a replacement of DB_BLOCK_BUFFERS in the older version of oracle 8i and before. The other parameters db_8k_cache_size, db_4k_cache_size, db_32k_cache_size, db_2k_cache_size, db_16k_cache_size have the initial default value "0". You can change values of these parameters dynamically.

 

To configure, the multiple block size feature,  alter the system as : -

 

Syntax :- ALTER SYSTEM SET <PARAMETER NAME> = < NEW SIZE> [M|K]

 

Let us say your block size is 4K and you want to configure database for creating a tablespace of block size 4K and 8K respectively.

 

For tablespace of blocksize 4K, alter the database as

 

ALTER SYSTEM SET DB_2K_CACHE_SIZE = 4M;

 

System altered.

 

For Tablespace of blocksize 8K, alter the database as

 

ALTER SYSTEM SET DB_8K_CACHE_SIZE = 8M;

 

System altered.

 

ALTER SYSTEM SET DB_8K_CACHE_SIZE = 50M;

 

System altered.

This is a brand new error message with Oracle 10g.

 

Here is what Oracle has to say:

All instances supporting the service requested by the client reported that they were blocking the new connections. This condition may be temporary, such as at instance startup.

 

And here are what Oracle advices as action:

Attempt the connection again. If error persists, then contact the administrator to check the status of the instances.

 

And what we have to say:

This error usually crops up when you are starting up your database, or just started up your database. This is actually a known problem with Oracle 10g where a SHUTDOWN IMMEDIATE is followed by a STARTUP MOUNT. If this where the case it would be solved by issuing a SHUTDOWN to the database and then again issuing a STARTUP. This should solve the problem.

 

However there might be some scenarios where this error might occur even if you are starting your database after a restart of the operating system (In fact I got this error after rebooting my Linux machine). I also did not issue any STARTUP MOUNT command but just issued STARTUP. But I got another error which I did not notice while startup of the database. The error was ORA-00205: error in identifying control file, check alert log for more info. I checked for my control files and somebody had deleted them.

 

Now I was in a big problem. But later on when I noticed it was my drives which where missing. Somebody have removed my auto mount scripts of file system, and my files where not mounted.

 

I mounted my file system and then SHUTDOWN my database and then STARTUP once again. Then once again I played with my LSNRCTL for a STOP and START. Then I connected. It was a success.

 

This error occurs when the session initiating to connect to Oracle database fails due to domain mismatch between oracle database server and a client trying to connect.

 

What Oracle says is the cause: The authentication service failed to retrieve the credentials of a user.

What Oracle says to be done to resolve: Enable tracing to determine the exact error.

 

Now more about this error

This error occurs usually in a windows domain where the domain of the server where database is installed is different from that of the client machine trying to connect, to prove this correct try to connect to database from the server machine.

 

If the connection is obtained from within the server you can be sure that the cause is different domain.

 

Now if you are in a development or test environment you can bypass this security measure by changing the sqlnet.ora file (The file is available in ORACLE_HOME/network/admin folder).

 

Modify the parameter SQLNET.AUTHENTICATION_SERVICES in the file. It would have been

 

SQLNET.AUTHENTICATION_SERVICES= (NTS),

 

Now change the parameter value to:

 

SQLNET.AUTHENTICATION_SERVICES= (NONE).

 

Try connecting from the client machine and it should connect.

 

Note: This should be treated as a temporary solution rather that a final one. Read note of caution below for further explanation.

 

 

Note of caution: By modifying this parameter you are bypassing the default security provided by the domain. Make sure that this parameter is set to NTS in a production environment.

 

ORACLE_HOME is to be replaced with the database server home. That is if you have installed database in D:\Oracle, then you should search the file sqlnet.ora inside D:\Oracle\network\admin folder.

 

 

EXP-00037: EXPORT VIEW NOT COMPATIBLE

Cause: Incompatibility

 

Action:

1. Export the data with the Export utility of the lowest database version involved.
2. Import the data with the Import utility of the target database.

ORA-00980: synonym translation is no longer valid

This tip comes from Vikash Varma, Oracle DBA at Intelligent Consulting, in New Jersey.

"ORA-00980: synonym translation is no longer valid" is a common error encountered in a development environment. This can happen for many reasons.

Some of them are:

1. You created a synonym on non-existing object by mistake.
• For example, you created a synonym on SCOTT.DEPT where either the SCOTT schema in not present or the DEPT table is missing.
2. You dropped an object but you did not drop the synonyms referencing the object.
3. You dropped a user, but you did not drop synonyms referencing the objects owned by that user.

When an object is dropped, synonyms referring to the object are not dropped. The following script lists all such invalid synonyms:

select * from dba_synonyms s

where table_owner not in('SYSTEM','SYS')

and db_link is null

and not exists

(select 1

from dba_objects o

where s.table_owner=o.owner

and s.table_name=o.object_name);

The following script generates DDL to drop synonyms whose translation is no longer valid. USE WITH CAUTION.

rem 

rem  Exludes SYS and SYSTEM users

rem

select 'drop '||decode (s.owner,'PUBLIC','PUBLIC SYNONYM ',

'SYNONYM'||s.owner||'.')||s.synonym_name||';'

from dba_synonyms  s

where table_owner not in('SYSTEM','SYS')

and db_link is null

and not exists

     (select  1

      from dba_objects o

      where s.table_owner=o.owner

      and s.table_name=o.object_name)

/

Read more, a mini Tip on Synonyms

ORA-12154: TNS:could not resolve the connect identifier specified

This error frequently occurs in case where there is corrupt or incorrect configuration detail in tnsnames.ora file. The error is traced mostly in local copy of the configuration file. It can be also related to incorrect database "default" name specified in sqlnet.ora file.

If you are using local naming (TNSNAMES.ORA file):
Make sure that the following is done:

Make sure that the connect string is defined somewhere in the NAMES.DIRECTORY_PATH defined in $ORACLE_HOME/network/admin/sqlnet.ora file.

Make sure that the tnanames.ora file exists in the $ORACLE_HOME/network/admin directory and it is accessible for the current user. In Unix/Linux there might be restrictions imposed by system administrators on file read privileges.

Make sure that the entry for the connect string is existing in the tnsnames.ora file. Check for spelling errors. (The entry in tnsnames.ora is not case-sensitive. i.e, you may use an entry called mydomain in tnsnames.ora and you can call it using MYDOMAIN).

Check that the entry in tnsnames.ora is correct. You can ensure this by copying the whole connect string details, remove space and enter characters (make the entire definition in one single line) and then you can use this as connect string. (Copy the entire definition of a connect string and paste it in a word processor and remove the characters)

Make sure that the sqlnet.ora is having correct NAMES.DEFAULT value.

Listener is listening for the correct ORACLE_HOME. Check the current ORACLE_HOME and check the tnsnames.ora and sqlnet.ora in the directory of the current ORACLE_HOME. If this is wrong, then change the ORACLE_HOME and try connecting once again.

If you are using directory naming:

Verify that "LDAP" is listed as one of the values of the NAMES.DIRETORY_PATH parameter in the Oracle Net profile (SQLNET.ORA).

Verify that the LDAP directory server is up and that it is accessible.

Verify that the net service name or database name used as the connect identifier is configured in the directory.

Verify that the default context being used is correct by specifying a fully qualified net service name or a full LDAP DN as the connect identifier.

If you are using easy connect naming:

Verify that "EZCONNECT" is listed as one of the values of the NAMES.DIRETORY_PATH parameter in the Oracle Net profile (SQLNET.ORA).

Make sure the host, port and service name specified are correct.

Try enclosing the connect identifier in quote marks. See the Oracle Net Services Administrators Guide or the Oracle operating system specific guide for more information on naming.

Links:
Configuring TNS file
Ora-Code

PL SQL ERROR 201: SRW.MESSAGE must be declared

If you are unable to compile your reports or plls in Oracle 10g Version, due to the error like

PL SQL ERROR 201: SRW.___ must be declared, then check the existence of the following file and its privilege:

rwrun.jar in /Oracle10gAS/reports/jlib folder.

Where Oracle10gAS is the folder where 10g Application server is installed.

You will normally get this error only in UNIX/Linux flavors, because this is related to privilege rights. If you are getting this error in Windows also, then it is quietly possible that the file is missing.

The file must have the privilege 755 or -rwx-r-x-r-x.

ORA-01401 has been discarded in Oracle 10g

A new error message ORA-12899 has been created instead of the dreaded ORA-01401 error message in Oracle 10g version.

What is the benefit of changing this error ?


Well while this new error message is displayed Oracle will also display the field name in which the NULL was not allowed.

"ORA-01401: Inserted value too large for column"

ORA-12899: value too large for column
"DB"."TABLE_NAME"."FIELD_NAME" (actual: 6, maximum:5)

This was a nice enhancement.

Ask Tom Article:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::
P11_QUESTION_ID:7143933880166

ORA-02429: Cannot drop index used for enforcement of unique/primary key

To simulate the error do the following:

create table temp_index as select object_name from user_objects where object_type='INDEX';

create table testing(testid number primary key, testchar varchar2(200));

Find out the index created for the primary key by the following statement:

select object_name from user_objects where object_type='INDEX'
minus
select object_name from temp_index;

Drop the index by issuing the following statement:
drop index SYS_C00249876;

You will get the error ORA-02429: Cannot drop index used for enforcement of unique/primary key

In Index organized tables this problem will be more. You will not be able to drop either the primary key or index.

Consider the example:
CREATE TABLE temp1 (
testid NUMBER (3) NOT NULL,
CONSTRAINT pk_temp1
PRIMARY KEY ( testid))
ORGANIZATION INDEX ;

alter table temp1 drop constraint pk_temp1;

You will get the following error:
ORA-25188: Cannot drop/disable/defer the primary key constraint for index-organized tables or sorted hash cluster

drop index pk_temp1;
You will get the following error:
ORA-02429: Cannot drop index used for enforcement of unique/primary key