use of db_file_multiblock_read_count parameter in oracle

db_file_multiblock_read_count parameter in oracle
================================================


"Multiple block sizes were implemented to transport tablespaces."


That is what they primarily were implemented to facilitate it .

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

Questions:-
===================

1) We have LOB column tables and I see lot of chainig/migration.
If we move the LOB column tables to a bigger blocksize tablespace.
Will that not reduce the migration?
2) I think the The max block size possible is 32k on 9i.
How do reduce the migration for such big column tables as average
row length is more than 100k?

Ans:-

answer from Tom katys :
===================

1) lob columns are stored inline ONLY if they are 4000 bytes or less.
If they are bigger than a varchar2 - the are automagically moved out
of line into the lob segment.

It could be that a simple alter table t move with a larger pctfree
to set aside sufficient space for updates is what is needed
(and that moving to a larger blocksize without changing pctfree
would not *fix a darn thing*).

If you are using lobs - I feel pretty confident in stating
"the average row length is NOT 100K!!!!",
the lobs are stored OUT OF LINE, in the lob segment,
when they exceed 4000 bytes.

(and if they were 100k, just give up, I don't care what your
block size is, it won't matter)


to move indexes to a tablespace of 16k, helps me to keep memory
only for indexes, and control better the index memory usage.
For me (a small database) makes sense.

Consider (if ANYTHING) the keep and recycle pools, but
using multiple block sizes for "tuning" isn't really something recommended.


I've not advised to use multiple blocksizes, use the keep and recycle pools
if you want to segregate things out.
use keep and recycle pools if you want multiple buffer caches

ROW_NUMBER, RANK, DENSE_RANK IN ORACLE

Top-n Query or 3th highest row by sql quiery in oracle .

Using ROW_NUMBER, RANK, DENSE_RANK

=====================================

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 13 13:06:27 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn scott@test107
Enter password:
Connected.
SQL>
SQL>
SQL> break on deptno skip 1
SQL>
SQL> select deptno, ename,sal,
2 row_number()
3 over (partition by deptno
4 order by sal desc)rn,
5 rank()
6 over (partition by deptno
7 order by sal desc)rnk,
8 dense_rank()
9 over (partition by deptno
10 order by sal desc)drnk
11 from emp
12 order by deptno, sal desc
13 /

DEPTNO ENAME SAL RN RNK DRNK
---------- ---------- ---------- ---------- ---------- ----------
10 KING 5000 1 1 1
CLARK 2450 2 2 2
MILLER 1300 3 3 3

20 SCOTT 3000 1 1 1
FORD 3000 2 1 1
JONES 2975 3 3 2
ADAMS 1100 4 4 3
SMITH 1000 5 5 4

30 BLAKE 2850 1 1 1

DEPTNO ENAME SAL RN RNK DRNK
---------- ---------- ---------- ---------- ---------- ----------
30 ALLEN 1600 2 2 2
TURNER 1500 3 3 3
MARTIN 1250 4 4 4
WARD 1250 5 4 4
JAMES 950 6 6 5


14 rows selected.

SQL>
SQL> select ename,sal,
2 row_number()
3 over (order by sal desc)rn,
4 rank()
5 over (order by sal desc)rnk,
6 dense_rank()
7 over (order by sal desc)drnk
8 from emp
9 order by sal desc
10 /

ENAME SAL RN RNK DRNK
---------- ---------- ---------- ---------- ----------
KING 5000 1 1 1
FORD 3000 2 2 2
SCOTT 3000 3 2 2
JONES 2975 4 4 3
BLAKE 2850 5 5 4
CLARK 2450 6 6 5
ALLEN 1600 7 7 6
TURNER 1500 8 8 7
MILLER 1300 9 9 8
WARD 1250 10 10 9
MARTIN 1250 11 10 9

ENAME SAL RN RNK DRNK
---------- ---------- ---------- ---------- ----------
ADAMS 1100 12 12 10
SMITH 1000 13 13 11
JAMES 950 14 14 12

14 rows selected.

SQL> ed
Wrote file afiedt.buf

1 select ename,sal,rn,rnk,drnk
2 from (
3 select ename,sal,
4 row_number()
5 over (order by sal desc)rn,
6 rank()
7 over (order by sal desc)rnk,
8 dense_rank()
9 over (order by sal desc)drnk
10 from emp
11 order by sal desc
12 )
13* where rn=5
SQL> /

ENAME SAL RN RNK DRNK
---------- ---------- ---------- ---------- ----------
BLAKE 2850 5 5 4

SQL> ed
Wrote file afiedt.buf

1 select ename,sal,rn,rnk,drnk
2 from (
3 select ename,sal,
4 row_number()
5 over (order by sal desc)rn,
6 rank()
7 over (order by sal desc)rnk,
8 dense_rank()
9 over (order by sal desc)drnk
10 from emp
11 order by sal desc
12 )
13* where rn=3
SQL>
SQL>
SQL> /

ENAME SAL RN RNK DRNK
---------- ---------- ---------- ---------- ----------
FORD 3000 3 2 2

SQL>
SQL>
SQL>

Autonomous transaction in oracle

Autonomous transaction
==========================================
Structure:-
-----------

Declare
PRAGMA AUTONOMOUS_TRANSACTION;
.......
begin
…………….
……………
commit; ---or rollback;
End ;


Autonomous transactions perform an independent transaction, and return to the calling transaction without affecting it. The autonomous transaction has no link to the calling transaction, so only committed data can be shared by both transactions.
Oracle originally exposed Autonomous Transactions to developers as a mechanism for committing information on the side whilst not affecting the main event. They wanted to give us a way to record what happened in a job regardless of success or failure of the job itself, and without changing the transaction semantics of the main transaction.


The following types of PL/SQL blocks can be defined as autonomous transactions:
• Stored procedures and functions.
• Local procedures and functions defined in a PL/SQL declaration block.
• Packaged procedures and functions.
• Type methods.
• Top-level anonymous blocks.


Example


SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 5 11:19:42 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn scott@test107
Enter password:
Connected.
SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION dfr_name (passedinid IN NUMBER)
2 RETURN VARCHAR2
3 IS
4 PRAGMA AUTONOMOUS_TRANSACTION;
5 LAST VARCHAR2 (30);
6 BEGIN
7 SELECT emp.ename
8 INTO LAST
9 FROM emp
10 WHERE empno = passedinid AND ROWNUM = 1;
11
12 UPDATE emp
13 SET sal = sal + 100
14 WHERE empno = passedinid;
15
16 COMMIT;
17 RETURN LAST;
18 END;
19 /

Function created.

SQL> show error
No errors.
SQL>
SQL> select * from emp where empno='7369';

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 900
20


SQL> set linesize 1000
SQL> set pagesiz 100
SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 900 20

SQL>
SQL> select dfr_name('7369') from dual;

DFR_NAME('7369')
------------------------------------------------------------------------------------------

SMITH

SQL>
SQL>
SQL> select * from emp where empno='7369';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 1000 20

SQL>
SQL>
Autonomous transaction
=====================

Autonomous transaction feature is helpful for
1. A mutating table error in a trigger in which I am querying data from the table from which the trigger is fired.
2. I want to log an error so that when I rollback my business transaction, I do not lose my error log entry.
3. I need to keep an audit trail of every row that is retrieved by a set of queries in my application


A user-defined function cannot execute any DML (insert, update, delete, merge) if it is to be called inside an SQL statement, unless that function is marked as autonomous.
If you mark it as autonomous and also commit/rollback before leaving the function, then the SQL engine will let your function execute one or more DML statement(s).
The reason that this restriction is relaxed is that an autonomous transaction function executes in its own "transaction space" and does not share any locks with the session from which the SQL statement was executed.
This means that the function cannot see any of the uncommitted changes in that (or any other) session. It also means that any DML changes made and committed will not affect the results of the currently executing SQL statement.

If we want to use Autonomous Transactions in a known safe and efficient way, then we can keep just two simple rules in mind:
1) An Autonomous Transaction cannot see uncommitted changes from the parent transaction.
2) Autonomous Transactions should be used as “add on” capabilities which are essentially irrelevant to an otherwise wholly consistent piece of code.

Day wise total SQL Query with opening and colsing balance

Example-1
==============

with t as
(
select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
--- select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
--- select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all
select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
select 3 as daynum, 'DR' as trantype, -500 as amount from dual
)
select *
from t
model
dimension by (daynum,cast(trantype as /*VARCHAR2(15)*/chAR(15)) trantype)
measures (amount)
rules upsert all
( amount[for daynum from 1 to 4 increment 1,'Opening balance'] = 0
, amount[any,'Closing balance'] order by daynum = sum(amount)
[daynum <= cv(daynum),trantype != 'Closing balance']
, amount[daynum != 1,'Opening balance'] = amount[cv()-1,'Closing balance']
)
order by daynum
, decode(trantype,'Opening balance',1,'CR',2,'DR',3,'Closing balance',4)
/


===========================
EXAMPLE-2
===========================

with tran as
(
select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
select 1 as daynum, 'DR' as trantype, 1 as amount from dual union all
select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
select 1 as daynum, 'DR' as trantype, 1 as amount from dual union all
select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all
select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
select 3 as daynum, 'DR' as trantype, -500 as amount from dual
), days as
( SELECT LEVEL daynum FROM DUAL CONNECT BY LEVEL <= 4)
SELECT daynum
, DECODE (trantype,'OB','Opening Balance','CB','Closing Balance',trantype) descr
, CASE
/* For opening balance/closing balance
amount will = sum of previous days transactions
*/
WHEN (trantype IN ('OB','CB'))
THEN NVL (SUM(amount) OVER (ORDER BY daynum,DECODE (trantype,'OB',1,'CB',3,2)),0)
ELSE amount
END amount
FROM
(
SELECT d.daynum
, CASE
WHEN ( GROUPING_ID (d.daynum,1)>0
AND GROUPING_ID (d.daynum,2)>0
)
THEN t.trantype
WHEN ( GROUPING_ID (d.daynum,1)>0)
THEN 'OB'
WHEN ( GROUPING_ID (d.daynum,2)>0)
THEN 'CB'
END trantype
, t.amount
FROM tran t
, days d
WHERE d.daynum = t.daynum(+)
/* ROWNUM required to preserve duplicate transactions */
GROUP BY GROUPING SETS ((d.daynum,1),(d.daynum,2),(d.daynum,t.trantype,t.amount,ROWNUM))
)
WHERE trantype IS NOT NULL /* Do not show NULL transaction
for days with no transaction
(outer join)
*/
ORDER BY daynum,DECODE (trantype,'OB',1,'CB',3,2)
/


===========================
EXAMPLE-3
===========================


with t as
2 (
3 select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
4 select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
5 select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
6 select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all
7 select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
8 select 3 as daynum, 'DR' as trantype, -500 as amount from dual
9 ),
10 data
11 as
12 ( select daynum, r
13 from (select level daynum from dual connect by level <= 4),
14 (select level r from dual connect by level <= 2 )
15 )
16 select daynum,
17 r,
18 ttype,
19 amt,
20 case when ttype in ( 'close', 'open' )
21 then sum(samt) over (order by daynum, r)
22 end theamount
23 from (
24 select data.daynum,
25 data.r,
26 case when (nvl(t.trantype,'CR') = 'CR' and data.r = 1 )
27 then 'open'
28 when (nvl(t.trantype,'DR') = 'DR' and data.r = 2)
29 then 'close'
30 else t.trantype
31 end ttype,
32 case when (t.trantype = 'CR' and r = 2) or (t.trantype = 'DR' and r=1)
33 then t.amount
34 end amt,
35 case when (t.trantype = 'DR' and r = 2)
36 then sum(case when r=1 then t.amount end) over (partition by
data.daynum)
37 else 0
38 end samt
39 from t, data
40 where data.daynum = t.daynum (+)
41 )
42 order by daynum, r, decode( ttype, 'open', 1, 'CR', 2, 'DR', 3, 'close', 4 )
43 /



===========================
EXAMPLE-4
===========================



SQL> with my_tab as (select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
2 select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
3 select 1 as daynum, 'CR' as trantype, 400 as amount from dual union all
4 select 1 as daynum, 'DR' as trantype, -500 as amount from dual union all
5 select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
6 select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all
7 select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
8 select 3 as daynum, 'DR' as trantype, -500 as amount from dual union all
9 select 4 as daynum, 'CR' as trantype, 900 as amount from dual union all
10 select 5 as daynum, 'CR' as trantype, 900 as amount from dual union all
11 select 5 as daynum, 'CR' as trantype, 200 as amount from dual union all
12 select 5 as daynum, 'DR' as trantype, -500 as amount from dual union all
13 select 6 as daynum, 'DR' as trantype, -200 as amount from dual),
14 dummy as (select 1 id, 'Opening Balance' descr from dual union all
15 select 2 id, 'CR' descr from dual union all
16 select 3 id, 'DR' descr from dual union all
17 select 4 id, 'Closing Balance' descr from dual)
18 select mt.daynum,
19 d.descr,
20 mt.trantype,
21 decode(d.id, 1, 0,
22 2, amount,
23 3, amount,
24 4, sum(decode(d.id, 1, 0, 4, 0, amount)) over (partition by mt.daynum))
amo
unt
25 from (select mt1.*, row_number() over (partition by mt1.daynum order by mt1.trantype) rn
26 from my_tab mt1) mt
27 left outer join dummy d partition by (id, descr) on (mt.trantype = d.descr)
28 where case when d.id = 1 and rn = 1 then 1
29 when d.id = 2 and mt.trantype = 'CR' then 1
30 when d.id = 3 and mt.trantype = 'DR' then 1
31 when d.id = 4 and rn = 1 then 1
32 else 0
33 end = 1
34 order by mt.daynum, d.id;



===========================
EXAMPLE-2
===========================

with t as (
select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all
select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
select 3 as daynum, 'DR' as trantype, -500 as amount from dual
)
select daynum,
trim(trantype),
amount
from t
model
dimension by (
daynum,
cast(trantype as char(15)) trantype
)
measures (
amount,
daynum + 1 dnum
)
rules upsert all (
dnum[1,'Opening balance'] = max(dnum)[any,any],
amount[for daynum from 1 to dnum[1,'Opening balance'] increment 1,'Opening balance'] = 0,
amount[any,'Closing balance'] order by daynum = sum(amount)[daynum <= cv(daynum),trantype != 'Closing balance'],
amount[daynum != 1,'Opening balance'] = amount[cv()-1,'Closing balance']
)
order by daynum,
decode(trantype,'Opening balance',1,'CR',2,'DR',3,'Closing balance',4)
/

Firing Order of same type trigger (In oracle 10g)

Firing Order of same type trigger (In oracle 10g)
Oracle fires multiple triggers in an unspecified, random order, if more than one trigger of the same type exists for a given statement; that is, triggers of the same type for the same statement are not guaranteed to fire in any specific order.

Although triggers of different types are fired in a specific order, triggers of the same type for the same statement are not guaranteed to fire in any specific order. For example, all BEFORE row triggers for a single UPDATE statement may not always fire in the same order. Design your applications so they do not rely on the firing order of multiple triggers of the same type.
The only thing, can say about the order of triggers firing :
• All of the BEFORE triggers will fire in some order.
• Then all of the BEFORE FOR EACH ROW triggers will fire in some order.
• Then all of the AFTER FOR EACH ROW triggers will fire in some order.
• Then all of the AFTER triggers will fire in some order.

Question: How can I order firing of same type oracle trigger?
Answer:
1. you must consolidate them into a single trigger.
2. All triggers be calls to packaged procedures

Question: Why does Oracle have multiple same-type triggers?
Answer:
The reason goes back to the original implementation of replication in Oracle Database. Materialized view logs used a database trigger to record the data manipulation language (DML) operations that had taken place on a given table.