opiodr aborting process unknown ospid () as a result of ORA-28

I  have been seeing the message opiodr aborting process unknown ospid () as a result of ORA-28 in the alert log of my 11g database daily and frequently. The message will be more frequent when there is a heavy load in the server. 

I know that ORA-28 is a result of killing a session with alter session command
[oracle@dbtest ~]$ oerr ora 28
00028, 00000, "your session has been killed"
// *Cause:  A privileged user has killed your session and you are no longer logged on to the database.

// *Action: Login again if you wish to continue working.

You can give me many ideas about this message opiodr aborting process unknown ospid () as a result of ORA-28 like,
This is an informational message only. 
This is newly added to Oracle 11g. 
This is introduced to ensure that all the processes are monitored in the database and captured alerts for unusual exit of any process. 

But what if nobody is actually killing any sessions manually in the database?. 
I got some useful information from the oracle support site. Here are the links to access them

How to get compilation errors in oracle?

We DBAs used to compile invalid objects to make them valid. You may compile the whole database using utlrp.sql, or all invalid objects in a schema or even using manual scripts.

While compiling, if there are any errors in the code, it will end up with a warning as given below

SQL> alter procedure empsal compile ;
Warning: Procedure altered with compilation errors.

To get the actual errors with the object (here it is a procedure in the example) you have to run SHOW  ERRORS after compiling the object.

SQL> show errors
Errors for PROCEDURE EMPSAL:

LINE/COL ERROR
-------- -------------------------------------------------
3/1      PL/SQL: SQL Statement ignored
3/20     PLS-00201: identifier 'V_SAL' must be declared
3/26     PL/SQL: ORA-00904: : invalid identifier
4/1      PL/SQL: Statement ignored
4/23     PLS-00201: identifier 'V_SAL' must be declared

Here you can see the actual error with the procedure. (or function, package, package body etc)

If you have compiled many objects together with a script, and a few has the warning, then show errors will give you the errors with the latest compiled one only. In such cases you have to give the object type and object name to get errors related to a specific object. The complete Syntax of SHOW ERRORS is given below.

 SHOW ERRORS [{ FUNCTION | PROCEDURE | PACKAGE |   PACKAGE BODY | TRIGGER | VIEW   | TYPE | TYPE BODY | DIMENSION   | JAVA SOURCE | JAVA CLASS } [schema.]name]

The same for the given example is given below

SQL> show errors procedure empsal
Errors for PROCEDURE EMPSAL:

LINE/COL ERROR
-------- -------------------------------------------------
3/1      PL/SQL: SQL Statement ignored
3/20     PLS-00201: identifier 'V_SAL' must be declared
3/26     PL/SQL: ORA-00904: : invalid identifier
4/1      PL/SQL: Statement ignored
4/23     PLS-00201: identifier 'V_SAL' must be declared

SHOW ERRORS can be abbreviated to SHO ERR also.

SQL> sho err
Errors for PROCEDURE EMPSAL:

LINE/COL ERROR
-------- ---------------------------------------------------
3/1      PL/SQL: SQL Statement ignored
3/20     PLS-00201: identifier 'V_SAL' must be declared
3/26     PL/SQL: ORA-00904: : invalid identifier
4/1      PL/SQL: Statement ignored
4/23     PLS-00201: identifier 'V_SAL' must be declared

You can use SHOW ERRORS command if you are using SQLPLUS,SQLDeveleoper, TOAD or any such interfaces.

ORA-600 [kkqjpdpvpd: No join pred found.]


ORA-00600:  [kkqjpdpvpd: No join pred found.] is an internal error. There are known issues which causes this error.
You may have to raise an SR after looking in to the error look up tool described in 
ORA-600/ORA-7445 Error Look-up Tool *[ID 153788.1]*

Before going to submit the details to support we can go through the known issues related to this.

According to the Doc ID 1200518.1 : ORA-600 [kkqjpdpvpd: No join pred found.] there are five known bugs.
They are numbered as 16601689, 13501787, 10288630, 9240305 and 9031353
According to the notes the error can be from any of the following.
1. From SET query with GROUP BY using multi-level push down.
2. From A query with internal union all views may fail with internal error [kkqjpdpvpd: no join pred found.]
3. From optimizing a query with a full outer join which undergoes join predicate push down (JPPD).
Ref: https://blogs.oracle.com/optimizer/entry/basics_of_join_predicate_pushdown_in_oracle
4. From  join predicate pushdown for a query that has a view in side a view.
5. When attempting join predicate pushdown (JPPD) if the SQL contains a UNION ALL view and each branch contains at least two tables.
Also there are workaround for all these bugs. Go through the Support documents to know better on the topic.

Oracle 12c : PDB and CDB


Oracle Database 12c introduces a new multitenant architecture. It enables an Oracle database to contain a portable collection of schemas, schema objects, and nonschema objects that appears to an Oracle Net client as a separate database. This self-contained collection is called a pluggable database (PDB). A multitenant container database (CDB) is an Oracle database that includes zero, one, or many user-created PDBs

The screen shot below will give you a good idea about the  CDB and PDB in oracle 12c
-click on image to enlarge-

[content given below is just copied from Oracle document]

A container is either a PDB or the root container (also called the root). The root is a collection of schemas, schema objects, and nonschema objects to which all PDBs belong.
Every CDB has the following containers:
Exactly one root
The root stores Oracle-supplied metadata and common users. An example of metadata is the source code for Oracle-supplied PL/SQL packages . A common user is a database user known in every container  The root container is named CDB$ROOT.

Exactly one seed PDB
The seed PDB is a system-supplied template that the CDB can use to create new PDBs. The seed PDB is named PDB$SEED. You cannot add or modify objects in PDB$SEED.

Zero or more user-created PDBs
A PDB is a user-created entity that contains the data and code required for a specific set of features. For example, a PDB can support a specific application, such as a human resources or sales application. No PDBs exist at creation of the CDB. You add PDBs based on your business requirements.

Ref:
http://docs.oracle.com/cd/E16655_01/server.121/e17633/cdbovrvw.htm

ORACLE TEXT: implementing scoring text search engine with example

Oracle Text is a tool that enables you to build text query applications and document classification applications. Oracle Text provides indexing, word and theme searching, and viewing capabilities for text.
This post is related to text query functionality. Here I will explain how to use oracle scoring function with an example.

Using oracle text and score function we can implement  search functionality on several database columns, and results will be ordered by their relevance.We can implement searches on text columns using "LIKE" but it will not give you how relevant is each result.

Oracle text is already installed in oracle 11g and oracle 12c versions. If you are using oracle 10g Please refer this url to know how to install the oracle text extension http://download.oracle.com/docs/cd/B19306_01/install.102/e10319/initmedia.htm
Once installed, in order to run query on your text columns, you must add a index. Oracle Text provides three type of index for your text/documents, Below is the explanation copied from the oracle documentation.

CONTEXT: 
Use this index to build a text retrieval application when your text consists of large coherent documents. You can index documents of different formats such as Microsoft Word, HTML, XML, or plain text.
You can customize your index in a variety of ways.
This index uses CONTAINS clause
CTXCAT:
Use this index type to improve mixed query performance. Suitable for querying small text fragments with structured criteria like dates, item names, and prices that are stored across columns.
This index uses CATSEARCH clause
CTXRULE:
Use to build a document classification application. You create this index on a table of queries, where each query has a classification.
Single documents (plain text, HTML, or XML) can be classified by using the MATCHES operator.
This index use MATCHES clause

In order to use SCORING function, that returns results’ relevance, we have to use CONTEXT index.
Please follow the below example step by step to understand how to implement scoring function to a text column.
SQL> create table friends (id number, name varchar2(23), about varchar2(100));
SQL> CREATE INDEX scoreindx ON friends(about) INDEXTYPE IS CTXSYS.CONTEXT;
 Insert some rows...
SQL> select * from friends;
        ID NAME                    ABOUT
---------- ----------------------- -----------------------------------------
         1 fahad                   colq rmte frnd
         2 arun                    frnd frnd rmte
         3 javed                   colq colq colq
         4 rajeev                  colq colq frnd

Now let us run the following queries to check how score() function is working.
SQL> select name, score(1) as rating from friends where contains(about, 'frnd', 1)>0 order by rating desc;
 NAME                        RATING
----------------------- ----------
arun                             7
rajeev                           3
fahad                            3
 SQL> select name, score(1) as rating from friends where contains(about, 'colq', 1) >0 order by rating desc;
 NAME                        RATING
----------------------- ----------
javed                           10
rajeev                           7
fahad                            3
You can see that the output is ordered with respect to the relevance of the search term. The column rating in the output gives the relevance. 
If you want to search for more than one text the score() function can be used as below.
SQL> select name, score(1)+score(2) as rating from friends where contains(about, 'rmte', 1) >0 or contains(about, 'frnd', 2)>0 order by rating desc;
 NAME                        RATING
----------------------- ----------
arun                            11
fahad                            7
rajeev                           3
Normally we will not be aware of the number of words in the search string. So we may have to create the query dynamically in the application and run the same. 
Here below I am giving you an example to create the query with score() function dynamically with respect to the query term you provide. 
Here in this example I am searching for 'I am going to America' you can change this string to anything, and obviously you will be using a variable to give the search string in our application.

declare
   search_string varchar2(1000) :='i am going to america' ;
   words varchar2(25);
   select_string varchar2(1000) :='select name, ';
   cond_string varchar2(1000) := 'where ';
   wcount number ;
   statement varchar2(1000);
begin
   select length(search_string)-length(replace(search_string , ' ' ))+1 into wcount from dual;
   select_string:=select_string || 'score(1) as rating1';
   cond_string:= cond_string||' contains (about, '''|| search_string ||''', 1)>0';
   for i in 2..wcount+1 loop
       select regexp_substr(search_string, '[^ ]+', 1, i-1) into words  from dual;
       if i=2 then
            select_string:=select_string||', score('||i||')';
            cond_string:=cond_string||' or contains(about,''' || words||''','||i||')>0 ';
      else
            select_string:=select_string||'+score('||i||')';
            cond_string:=cond_string||' or contains(about,''' || words||''','||i||')>0 ';
      end if;
   end loop;
   statement:= select_string || ' as rating2 from friends '||chr(10) || cond_string ||' order by rating1 desc, rating2 desc';
   dbms_output.put_line(statement);
end;
/
Output will be as given below.

select name score(1) as rating1, score(2)+score(3)+score(4)+score(5)+score(6) as rating2 from friends
where  contains (about, 'i am going to america', 1)>0 contains(about,'am',2)>0 or contains(about,'going',3)>0 or contains(about,'to',4)>0 or contains(about,'america',5)>0 or
contains(about,'',6)>0  order by rating1 desc, rating2 desc

You can do modification in the script if the provided one does not satisfy your need. I hope this one will help you to create a new one. 
Ref:
http://docs.oracle.com/cd/B10501_01/text.920/a96517/cdefault.htm

SQL Loader with condition - example

Just follow the steps below. You will understand how to do SQL loader with condition. Condition can be written in the loader controlfile with WHEN clause. I am using scott as the working schema

1. Create a table in the SCOTT schema
 create table aaa (
   field1       varchar2(10),
   field2       varchar2(10)  
 );

  
2. Create a file data.dat  in /u01/ with the following contents. 
  
Banana;Fruit;
Lemon;Fruit;
Avocado;Fruit;
Ford;Car;
Pear;Fruit;
Apple;Fruit;

3. Create the controlfile control.ctl in   /u01/  with the following contents 

load data
infile '/u01/data.dat'
DISCARDFILE '/u01/emp.dsc'
insert
into table aaa when field2='Fruit'
fields terminated by ';'
(
  field1      char,
  field2      char
)


4. Run the SQL*Loader as given below
  

sqlldr control=/u01/control.ctl userid=scott/tiger

5. Check the discard file as well as the table scott.aaa to verify we have done what we were supposed to do ..