ORA-27102: out of memory Linux-x86_64 Error: 28: No space left on device

 ORA-27102: out of memory Linux-x86_64 Error: 28: No space left on device

When i try to increase sga_target from 50GB to 60GB i got above error.

the reason is shmall value is not configured accordingly for the SGA size.

[root@test_db_LAB ~]$  cat /etc/sysctl.d/97-oracle-database-sysctl.conf|grep  kernel.shmmni

kernel.shmmni = 4096

[root@test_db_LAB ~]$  cat /etc/sysctl.d/97-oracle-database-sysctl.conf|grep  kernel.shmall

kernel.shmall = 23592960

[root@test_db_LAB ~]$  cat /etc/sysctl.d/97-oracle-database-sysctl.conf|grep  kernel.shmmax

kernel.shmmax = 105374182400

Calculation for kernel.shmall  should be (SGA in bytes)/4096(kernel.shmmni)  = 15728640

as root user, set value 15728640 for shmall in file /etc/sysctl.d/97-oracle-database-sysctl.conf.

as root user, apply the changes. 

/sbin/sysctl -p /etc/sysctl.d/97-oracle-database-sysctl.conf

start the database.

How to generate trace file for a query in oracle database- tkprof creation for a query

 Most of the situation we may need to create a trace file for a query or a session which include PL/SQL block.

We can use below method to create trace file for the sql statements or PL/SQL blocks.

1. Enable tracing in a session where you are going to run a query with high level tracing 12.

ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

2. we will have number of tracefiles in database diag locaions, to identify the trace file generated by this session, you can set the identifier as below, so that file name includes identifier.

alter session set tracefile_identifier = 'mysessiontrace';

3.  run the query or PL/SQL block.

4. turn off the tracing.

alter session set events '10046 trace name context off';

you can find the trace file generated with name mysessiontrace, search as below.

ls -lrth *mysessiontrace*

If you want to generate tkprof of this trace file. use below 

tkprof orcl_mysessiontrace.90222_53078.trc orcl_mysessiontrace.90222_53078.txt SYS=NO

ORA-39083: Object type TYPE: failed to create with error, ORA-02304: invalid object identifier literal

 Error in import in logfile.

ORA-39083: Object type TYPE:"SCHEMA"."T_DKEG" failed to create with error:

ORA-02304: invalid object identifier literal

Failing sql is:

CREATE EDITIONABLE TYPE "SCHEMA"."T_DKEG"   OID '94C6DE8D33F322F1E0539417F30AF613' force as table of varchar2(32767);

Solution:

Your database have another object with OID already.

select owner,type_oid from dba_types where TYPE_OID='94C6DE8D33F322F1E0539417F30AF613';

 

OWNER           TYPE_OID

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

SCHEMA          94C6DE8D33F322F1E0539417F30AF613

so , while import you have to provide below parameter to avoid this issue. and type will generate now OID whiel import.

TRANSFORM=oid:n