Good to know things oracle

http://www.joblagao.com/voices/joblagao-university/20-jda-technical-interview-questions


1. Enable trace for others session
begin
sys.DBMS_SYSTEM.set_sql_trace_in_session(264, 37690, true );
end;

first parameter - SID, and next thread#

here in promo - one session blocks many others - so I thought to trace what that session is doing etc.

2. Below query fails and reason is outer query is enclosed in braces.

(with t as (select * from emp)
              select * from t);

3.SQLCODE and SQLERRM, why I can't insert SQLCODE and SQLERRM Values directly into table? I can insert only while taking them into separate variables?
Ans:
Reason is that SQLCODE and SQLERRM are functions that only return valid values within exception blocks of PL/SQL code. When you issue an SQL statement it is passed to the SQL engine and as such those functions would be out of scope and not provide the correct values and the SQL engine would not be able to execute such functions.It is well documented in oracle docs.

You may write a user defined function and wrap these functions in them to implement the functionality .

The SQLERRM() is a function - and is only defined in the PL engine. Kind of the opposite of the DECODE() function that is only defined in the SQL engine.

Nothing prevents you from defining a user PL/SQL function (which can be used in SQL) and use that to wrap SQLERRM(), e.g.
create or replace function oraMessage( oraError number ) return varchar2 is
begin
  return( SQLERRM(oraError) );
end;
This can be quite handy if you have a message log table (for application code) that has recorded ORA errors (via SQLCODE) - and you want to display the associated error message.

4.I Have a column where number stored as 8.756412556312453E37 I want to display it as 

87564125563124530000000000000000000000

Ans:Use to_char function.

5. Simple example for bulk collect and forall(For interview)

DECLARE
    CURSOR c_item
    IS
        SELECT item, item_descr FROM stg_item;

    TYPE item_tab_type IS TABLE OF c_item%ROWTYPE;

    item_tab   item_tab_type;
BEGIN
    OPEN c_item;

    LOOP
        FETCH c_item BULK COLLECT INTO item_tab LIMIT 200000;

        EXIT WHEN item_tab.COUNT = 0;

        FORALL i IN 1 .. item_tab.COUNT
            INSERT INTO item ( item, item_descr)
                SELECT /*+ parallel */
                      item_tab ( i).item, item_tab ( i).item_descr FROM DUAL;

        COMMIT;
    END LOOP;
END;
/

Note: although changing table ITEM to nologging then doing a simple "insert /*+ append */ into item select from stg_item" seems like a better bet.

6.Utl_File or standard spool, which one to use and when?

Ans: UTL_FILE is a server-side I/O operation on a server file system. UTL_FILE can be integrated in application code but as it will write at server side, we should be having access to read file on server.

SPOOL is a SQL*Plus client I/O operation on a client file system.

Another options to achieve same by storing result in CLOB/BFile etc. in database and export that or read by any interface.

7.I have two tables NSK_LOC_TYPE and NSK_LOC where in NSK_LOC_TYPE a location and it's type will be there and in NSK_LOC there will be other details of this location.

I need to populate NSK_LOC table based on NSK_LOC_TYPE table.

If a location LOC1 is of type 3 then I need to insert into two records into NSK_LOC as LOC1 SHIP-LOC1
if a locaiton LOC2 is of type 6 then I need to insert only one record into NSK_LOC as LOC

How can we achieve using single insert and without using union

Ans:INSERT FIRST
WHEN LOC_TYPE = 3
THEN
      INTO NSK_LOC
    VALUES (LOC, LOC)
      INTO NSK_LOC
    VALUES (LOC, 'SHIP-' || LOC)
WHEN LOC_TYPE = 6
THEN
      INTO NSK_LOC
    VALUES (LOC, LOC)
    SELECT LOC, LOC_TYPE FROM NSK_LOC_TYPE;

8.Selecting 1 records in a recursion

I have a data like this:

select 'A' c1, 'B' c2, 100 as c3 from dual

union all

select 'B' c1, 'A' c2, 100 as c3 from dual

union all

select 'D' c1, 'C' c2, 100 as c3 from dual

union all

select 'C' c1, 'D' c2, 100 as c3 from dual


We see that the data has a recursive pattern on C1 and C2.. Like A --> B, B--> A or D-->C, C--> D.  When we have data like this I only need to get 2 records, Basically the 1st and the 3rd one. So the output should be :
select 'A' c1, 'B' c2, 100 as c3 from dual
union all
select 'D' c1, 'C' c2, 100 as c3 from dual

Ans:with data as (
select 'A' c1, 'B' c2, 100 as c3 from dual
union all
select 'B' c1, 'A' c2, 100 as c3 from dual
union all
select 'D' c1, 'C' c2, 100 as c3 from dual
union all
select 'C' c1, 'D' c2, 100 as c3 from dual
)
select c1, c2, c3
from (
  select d.*, row_number() over (partition by least(c1, c2), greatest(c1, c2) order by c1) rn
  from data d
)
where rn = 1;

9. Bitmap Indexes and Deadlocks

Bitmap indexes are not appropriate for tables that have lots of single row DML operations (inserts) and especially concurrent single row DML operations. Deadlock situations are the result of concurrent inserts as the following example shows: Open two windows, one for Session 1 and one for Session 2

Session 1	Session 2
create table bitmap_index_demo (   value varchar2(20) );
insert into bitmap_index_demo select decode(mod(rownum,2),0,'M','F')   from all_objects;
create bitmap index   bitmap_index_demo_idx   on bitmap_index_demo(value);
insert into bitmap_index_demo   values ('M'); 1 row created.
	insert into bitmap_index_demo   values ('F'); 1 row created.
insert into bitmap_index_demo   values ('F'); ...... waiting ......
ERROR at line 1: ORA-00060: deadlock detected while waiting for resource	insert into bitmap_index_demo   values ('M'); ...... waiting ......

10.Stats gathering auto commits the pending transactions in the session.