ORA-27300, ORA-27301, ORA-27302 Errors

Last night this error has appeared in alert.log of the a database.

 

29-AUG-2014 01:20:15.04:
Errors in file $1$FBI2318:[TOMCAT.][BACKGROUND]NEWYORK_SIEG_BG_CJQ0_016.TRC;:
ORA-27300: OS system dependent operation:skgpspawn_error failed with status: 148
ORA-27301: OS failure message: error 148
ORA-27302: failure occurred at: skgpspawn3

29-AUG-2014 01:20:16.05:
Process J002 died, see its trace file

29-AUG-2014 01:20:16.06:
kkjcre1p: unable to spawn jobq slave process

29-AUG-2014 01:20:16.09:
Errors in file $1$FBI2318:[TOMCAT.][BACKGROUND]NEWYORK_SIEG_BG_CJQ0_016.TRC;:

 

 
The Error can be safely ignored as the job coordinator process tried to spawn a job slave when the Shutdown was in progress. It will return to try again and should be able to do.

 

HTH – Antonio NAVARRO

Temp Full Updating Statistics

This past night one scheduler chain failed with the error below;

 

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
ORA-12012: error on auto execute of job 1718726
ORA-06512: at SYS.DBMS_STATS line 13591
ORA-06512: at SYS.DBMS_STATS line 13947
ORA-06512: at SYS.DBMS_STATS line 14081
ORA-06512: at SYS.DBMS_STATS line 14045
ORA-06512: at line 2

 

This job update statistics, apparently has filled the temporal. The DBMS_STATS might do intensive use of temp. In this case have been created a new table, which has about 100 gigabytes of physical size, even if this table is analyzed using a low sampling, like 5%, can make a considerable expenditure of temp. The solution is to add more space to temporary tablespace, but in this case the table is a historical and not haven’t indexes with which to analyze it will not gain anything and always be accessed using FTS (maybe set a degree of 8 or 10 to the table can help), which is chosen lock statistics for this table.

 

HTH – Antonio NAVARRO

Where Are The Traditional Jobs in EM?

The truth I usually do not use in a regular way EM or GUI interfaces to perform administration tasks. I started working on VAX and only had black screens (monochrome). Today, a coworker asked me if I knew where I could see the jobs (not the scheduler) in the EM. We have been looking for it for a while without success. I’ve finally gone to MOS and look for notes on the subject and I have found this document Doc ID 1068736.1.

 
Literally says there is no GUI interface, from 10.2 to last release (12.1.0.2) for dbms jobs. The truth is that I have been very surprised. There are still many places where they are used, maybe by coming from migrations (older versions 7,8,9) and have not been updated to scheduler. I’m a fan of my scripts, but I understand that there are many people who could (or rather, need) use it in the EM.

 

HTH – Antonio NAVARRO

DROP TABLE Command Produces Multiple Locks

Are producing multiple locks in a database (lots of sessions locked), it seems that is doing a drop table, and this is causing locking. The session is killed and the lock is released. After a few minutes it throws and locks reoccurrence.

From the session that launches the DROP TABLE command gives the following error ;

 

ERROR at line 1:
ORA-04020: deadlock detected while trying to lock object CLOUD.ELEMENTS

 

This is not the table is trying to erase, investigating the table has FKs to other tables, such as above. FKs associated to the table are deleted and proceeds to drop the table without problems, is a table that does not use nobody.

 

Conclusions;

  • This type of DML operations and generally in large environments, must be performed at period of low activity.
  • Remove the constraints to and from the table to be erased.
  •  The locks on the dictionary are critical and very sensitive, if possible try avoid it.

 

HTH – Antonio NAVARRO

ORA-01476 Error Executing DBMS_STATS.GATHER_SCHEMA_STATS

Today has reached an alarm with the error below;

 

ORA-01476: divisor is equal to zero

 
This error has been produced executing  GATHER_TABLE_STATS procedure. This database is older, a 10.2.0.2 version. After researching I see there is a Doc ID 464440.1 note in MOS,  which refers to two possible bugs, bugs 5645718 and 6319761 both suggest the same solution to fix the problem.

 

alter system set events ‘38041 trace name context forever, level 16’ scope=both;

alter system set events ‘38041 trace name context forever, level 24’ scope=both;

 

Conclusions;

  • Execute the alter system solves the problem
  • This version is obsolete would have to go to latest versions, at least 11.2

 

HTH – Antonio NAVARRO

Special Columns Of Function-based Indexes

Many times we need to know which columns (in composite indexes) that make the index is function-based (BFI) instead of a normal index. If it is a simple index no mystery (index on only one column). In the case of multiple columns may be all, only one column or a few columns.

The more usual way, from what I’ve seen in my years of experience is to use the package DBMS_METADATA invoking the GET_DDL function, get the sql code and from there, make a study of it. The other less common way is by consulting the DBA_IND_EXPRESSIONS, as shown below;

 

 SELECT 
  COLUMN_POSITION,
  COLUMN_EXPRESSION    -- Long type
FROM  
   DBA_IND_EXPRESSIONS
WHERE INDEX_NAME = '<MY_BFI>'
/

 

 
HTH – Antonio NAVARRO

 

Insert Append Can Be A Wasted Space

Today has triggered an alarm about lack of space in a tablespace, examining what there are into the tablespace, a table appears it’s a historical data, occasionally is a process  deleting from this table, but to grow ahead this is not reusing space. After reviewing the ASH I see basically two types of DML statements are made against this table, a delete that responsible for maintaining only the last year and a insert keeping the most recent information. Seeing the same judgment was confirmed my suspicions that uses an append, the statement is as follows;

 

INSERT /*+ APPEND */ INTO …

 

This always inserting HWM above. All the space behind the High Water Mark is being lost.  Of course, this is the worst case.

 

Conclusions;

  •     To recover the space would have to rebuild the table.
  •     You can reclaim space by removing the append hint, but it perform the insert more slower, especially if it is massive like this case.

 

HTH – Antonio NAVARRO