How to avoid the persistent state of package’s components?

August 4, 2014

Generally the persistent state of package’s component is useful, we don’t want to eliminate it.
Persistency means that the lifetime of package components(variables, cursors,exception definitions, etc.)
start with the instatiation of package and finish with the end of session.
Many developers collect, for example, the necessary exception definitions in a particular package and
later everybody can use them as many times in their session as they want without reloading them into memory.
Sometimes inconvenient, that our session “remembers” the previous state of package components.
Typical scenario when a package is instantiated in one session and later we modify the code of the package in an other session. After this modification in the first session we want to refer to an arbitrary component of the changed component we will get the following error message:

 
ORA-04068: existing state of packages has been discarded 
ORA-04061: existing state of package "HR.CS" has been invalidated 

Many times when we want to implement an algoritm with recursion, we need one or more “global variables”.
Let see an example:
Consider the algoritm for the classical “Tower of Hanoi”:
(http://www.mathsisfun.com/games/towerofhanoi.html)

 
CREATE OR REPLACE PROCEDURE hanoi(first pls_integer, 
second pls_integer, n pls_integer)
is
third pls_integer;
BEGIN
IF n=1 THEN
dbms_output.put_line(first||' => '||second);
RETURN;
END IF;
CASE 
WHEN  first * second=2 THEN third:=3;
WHEN  first * second=3 THEN third:=2;
WHEN  first * second=6 THEN third:=1;
END CASE;
hanoi(first, third,n-1);
hanoi (first,second,1);
hanoi(third,second,n-1);
END;
/

Whe want to run procedure for 3 level tower then program generates the necessary steps for moving the tower from the first (“1″) position to the second(“2″).
exec hanoi(1,2,3) — we want to move a 3 level tower from the first position to the second

 
PROCEDURE HANOI compiled
anonymous block completed
1 => 2
1 => 3
2 => 3
1 => 2
3 => 1
3 => 2
1 => 2

Let’s count the number of the steps.
Not surprisingly, the result : 7. (2**3 -1 , where the 3 is the height of the tower).
We want to see step number before printing step instruction.
Not very easy! We can not use a local variabe, we need a “global” variable.
The first attempt could be the following:
Let’s create a package , called hanoi_pkg!

 
CREATE OR REPLACE PACKAGE hanoi_pkg
is
procedure hanoi(first pls_integer, 
second pls_integer, n pls_integer);
END hanoi_pkg;
/
CREATE OR REPLACE PACKAGE BODY hanoi_pkg
IS
i pls_integer:=0;
PROCEDURE hanoi(first pls_integer, 
second pls_integer, n pls_integer)
is
third pls_integer;
BEGIN
IF n=1 THEN
i:=i+1;
dbms_output.put_line(i||'. step: '||first||' => '||second);
RETURN;
END IF;
CASE 
WHEN  first * second=2 THEN third:=3;
WHEN  first * second=3 THEN third:=2;
WHEN  first * second=6 THEN third:=1;
END CASE;
hanoi(first, third,n-1);
hanoi (first,second,1);
hanoi(third,second,n-1);
END;
END hanoi_pkg;
/

and run the procedure:
exec hanoi_pkg.hanoi(1,2,3)

 
anonymous block completed
1. step: 1 => 2
2. step: 1 => 3
3. step: 2 => 3
4. step: 1 => 2
5. step: 3 => 1
6. step: 3 => 2
7. step: 1 => 2

The result -seems to be- correct.
Please, run again:
exec hanoi_pkg.hanoi(1,2,3)

 
anonymous block completed
8. step: 1 => 2
9. step: 1 => 3
10. step: 2 => 3
11. step: 1 => 2
12. step: 3 => 1
13. step: 3 => 2
14. step: 1 => 2

The problem is that our session remembers the latest value of the counter!
The reason is that the components of the package are persistent.
The private variable “i” (see i pls_integer:=0;) is also persistent, even this is a private (not public) variable!
How to solve the problem?
Please use the SERIALLY_REUSABLE pragma ;
The modified package:

 
CREATE OR REPLACE PACKAGE hanoi_pkg
is
PRAGMA SERIALLY_REUSABLE ;
procedure hanoi(first pls_integer, 
second pls_integer, n pls_integer);
END hanoi_pkg;
/
CREATE OR REPLACE PACKAGE BODY hanoi_pkg
IS
i pls_integer:=0;
PRAGMA SERIALLY_REUSABLE ;
PROCEDURE hanoi(first pls_integer, 
second pls_integer, n pls_integer)
is
third pls_integer;
BEGIN
IF n=1 THEN
i:=i + 1;
dbms_output.put_line(i||'. step: '||first||' => '||second);
RETURN;
END IF;
CASE 
WHEN  first * second=2 THEN third:=3;
WHEN  first * second=3 THEN third:=2;
WHEN  first * second=6 THEN third:=1;
END CASE;
hanoi(first, third,n-1);
hanoi (first,second,1);
hanoi(third,second,n-1);
END;
END hanoi_pkg;
/

Now we test the modification:
exec hanoi_pkg.hanoi(1,2,3)

 
anonymous block completed
1. step: 1 => 2
2. step: 1 => 3
3. step: 2 => 3
4. step: 1 => 2
5. step: 3 => 1
6. step: 3 => 2
7. step: 1 => 2

Run again – when the package has been instantiated already!)
exec hanoi_pkg.hanoi(1,2,3)

 
anonymous block completed
1. step: 1 => 2
2. step: 1 => 3
3. step: 2 => 3
4. step: 1 => 2
5. step: 3 => 1
6. step: 3 => 2
7. step: 1 => 2

Finally we can state, that with
PRAGMA SERIALLY_REUSABLE ; statement we removed the instantiated package from the memory.
This can be very useful when we continously develop a package, while other sessions want to use it.

Thanks for visting my site!
My e-mail has changed:
laszlo.czinkoczki@webvalto.hu
but
czinkoczkilaszlo@gmail.com
is still alive.

Temporal validity(history) in Oracle 12c

December 16, 2013

In Oracle 11g You can store the previous states of the a table in a Flashback Archive.
Now, starting with Oracle 12c, You can store the old and current states of the rows in the same table.
This is extremely important for dimensonal tables in a Datawarehouse,
because we may want to keep the whole history of a dimension table.
(Consider SCD2 Dimensions)
We can explicitly or implicitly define date/timestamp type columns
that are responsible to store the “lifetime” period of a particular row.
Consider the following scenario:
1. We create a table with invisible(hidden) timestamp columns:

CREATE TABLE my_emp_hidden(
  empno NUMBER,
  last_name VARCHAR2(30),
  PERIOD FOR user_valid_time);

2. Let’s see the generated DDL statement for the table:

 
SET LONG 10000
SELECT DBMS_METADATA.GET_DDL('TABLE','MY_EMP_HIDDEN','HR') FROM DUAL;
SQL_SCRIPT
-----------
CREATE TABLE "HR"."MY_EMP_HIDDEN" 
   (	"EMPNO" NUMBER, 
	"LAST_NAME" VARCHAR2(30 BYTE), 
	 CONSTRAINT "USER_VALID_TIME9A523" CHECK (USER_VALID_TIME_START < USER_VALID_TIME_END) ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" 
  ILM ENABLE LIFECYCLE MANAGEMENT  ;

We can observe a check constraint referring to two (invisible) columns:
USER_VALID_TIME_START and USER_VALID_TIME_END
If you want to see the invisible columns then consider
the following SELECT statement (see Julian Dyke’s blog:

http://www.juliandyke.com/Blog/?p=419)

COL NAME FORMAT A22
COL COL# FORMAT 999
SELECT name,col#,intcol#,segcol#,TO_CHAR (property,'XXXXXXXXXXXX') PROPERTY
FROM sys.col$
WHERE obj# =
(
  SELECT obj# FROM sys.obj$ WHERE name = 'MY_EMP_HIDDEN'
);
NAME                   COL#    INTCOL#    SEGCOL# PROPERTY    
---------------------- ---- ---------- ---------- -------------
USER_VALID_TIME_START     0          1          1            20 
USER_VALID_TIME_END       0          2          2            20 
USER_VALID_TIME           0          3          0         10028 
EMPNO                     1          4          3             0 
LAST_NAME                 2          5          4             0 

The value of COL# is zero for the invisible columns!
3. Now we populate the table with the following 3 rows:

 
INSERT INTO my_emp_hidden
(empno,last_name,USER_VALID_TIME_START,USER_VALID_TIME_END)
VALUES (100, 'King', to_timestamp('01-Jan-10'), to_timestamp('02-Jun-12'));
INSERT INTO my_emp_hidden
(empno,last_name,USER_VALID_TIME_START,USER_VALID_TIME_END)
VALUES (101, 'Kochhar', to_timestamp('01-Jan-11'), to_timestamp('30-Jun-12'));
INSERT INTO my_emp_hidden
(empno,last_name,USER_VALID_TIME_START,USER_VALID_TIME_END)
VALUES (102, 'De Haan', to_timestamp('01-Jan-12'),NULL);

4. Using the ENABLE_AT_VALID_TIME procedure of DBMS_FLASHBACK_ARCHIVE package
we can see the ALL rows or CURRENT rows only!

EXEC DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME('ALL')
SELECT * FROM my_emp_hidden;
anonymous block completed
     EMPNO LAST_NAME                    
---------- ------------------------------
       100 King                           
       101 Kochhar                        
       102 De Haan                        

EXEC DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME('CURRENT')
SELECT * FROM my_emp_hidden;
anonymous block completed
     EMPNO LAST_NAME                    
---------- ------------------------------
       102 De Haan                        

We can see only one row in the second scenario, because we have only one current
(visible) row!
For SQL Tuning experts:

Executions:1 | is_bind_sensitive:N  | is_bind_aware: N | Parsing schema:HR | Disk reads:0 | Consistent gets:183
SQL_ID  445uvm4xj0ar8, child number 0
-------------------------------------
SELECT * FROM my_emp_hidden
 
Plan hash value: 764633393
 
-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| MY_EMP_HIDDEN |     1 |    60 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter((("T"."USER_VALID_TIME_START" IS NULL OR 
              SYS_EXTRACT_UTC("T"."USER_VALID_TIME_START")<=SYS_EXTRACT_UTC(SYSTIMESTAMP(
              6))) AND ("T"."USER_VALID_TIME_END" IS NULL OR 
              SYS_EXTRACT_UTC("T"."USER_VALID_TIME_END")>SYS_EXTRACT_UTC(SYSTIMESTAMP(6))
              )))
 

5.Valid AS OF PERIOD FOR queries:

-- Returns only King.
SELECT * from my_emp_hidden AS OF PERIOD 
FOR user_valid_time TO_TIMESTAMP('01-Jun-10');
    EMPNO LAST_NAME                    
---------- ------------------------------
       100 King        
-- Returns  King and Kochhar.
SELECT * from my_emp_hidden AS OF PERIOD 
FOR user_valid_time TO_TIMESTAMP('01-Jun-11');
     EMPNO LAST_NAME                    
---------- ------------------------------
       100 King                           
       101 Kochhar                        

-- Returns Kochhar and De Haan
SELECT * from my_emp_hidden AS OF PERIOD 
FOR user_valid_time TO_TIMESTAMP('03-Jun-12');
     EMPNO LAST_NAME                    
---------- ------------------------------
       101 Kochhar                        
       102 De Haan                        
-- Returns all rows
SELECT * from my_emp_hidden VERSIONS PERIOD 
FOR user_valid_time 
BETWEEN TO_TIMESTAMP('02-jun-11') AND TO_TIMESTAMP('01-May-12');
     EMPNO LAST_NAME                    
---------- ------------------------------
       100 King                           
       101 Kochhar                        
       102 De Haan                        
-- Returns no rows
SELECT * from my_emp_hidden AS OF PERIOD 
FOR user_valid_time TO_TIMESTAMP('01-Jun-09');
no rows selected

If you want to explicitly define the time constraint columns for a table,
consider the documentation
(http://docs.oracle.com/cd/E16655_01/appdev.121/e17620/adfns_design.htm#ADFNS967)
or briefly here:

EXEC DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME('CURRENT')
CREATE TABLE my_emp(
  empno NUMBER,
  last_name VARCHAR2(30),
  start_time DATE,
  end_time DATE,
PERIOD FOR user_valid_time (start_time, end_time));

INSERT INTO my_emp VALUES (100, 'Ames', '01-Jan-10', '30-Jun-11');
INSERT INTO my_emp VALUES (101, 'Burton', '01-Jan-11', '30-Jun-11');
INSERT INTO my_emp VALUES (102, 'Chen', '01-Jan-12', null);
 
-- Valid Time Queries --
 
-- AS OF PERIOD FOR queries:
 
-- Returns only Ames.
SELECT * from my_emp AS OF PERIOD FOR user_valid_time TO_DATE('01-Jun-10');

-- Returns  Ames and Burton, but not Chen.
SELECT * from my_emp AS OF PERIOD FOR user_valid_time TO_DATE('01-Jun-11');

-- Returns no one.
SELECT * from my_emp AS OF PERIOD FOR user_valid_time TO_DATE( '01-Jul-11');

-- Returns only Chen.
SELECT * from my_emp AS OF PERIOD FOR user_valid_time TO_DATE('01-Feb-12');
 
-- VERSIONS PERIOD FOR ... BETWEEN queries:
 
-- Returns only Ames.
SELECT * from my_emp VERSIONS PERIOD FOR user_valid_time BETWEEN 
   TO_DATE('01-Jun-10') AND TO_DATE('02-Jun-10');

-- Returns Ames and Burton.
SELECT * from my_emp VERSIONS PERIOD FOR user_valid_time BETWEEN 
   TO_DATE('01-Jun-10') AND TO_DATE('01-Mar-11');

-- Returns only Chen.
SELECT * from my_emp VERSIONS PERIOD FOR user_valid_time BETWEEN 
   TO_DATE('01-Nov-11') AND TO_DATE('01-Mar-12');

-- Returns no one.
SELECT * from my_emp VERSIONS PERIOD FOR user_valid_time BETWEEN 
   TO_DATE('01-Jul-11') AND TO_DATE('01-Sep-11');
no rows selected
SELECT * FROM MY_EMP;
     EMPNO LAST_NAME                      START_TIME END_TIME
---------- ------------------------------ ---------- ---------
       102 Chen                           01-JAN-12    

.

This feature is VERY IMPORTANT whenever we want to keep previous versions of the rows
in the same table!
(ps: My e-mail changed! The current is:czinkoczkilaszlo@gmail.com)

Limiting the percentage of ordered rows retrieved in Oracle 12c

July 23, 2013

In a previous post I compared two solutions for the same ranking
problem. Now I would like to compare two solutions for the same
percentages report.
The goal of the query: Let's see the first 5% rows of the total
rows according to the salary in descending way.
Let's consider the solution in Oracle 12c, where we can use the
new FETCH {FIRST|NEXT}  <pct> PERCENT ROWS clause:
SELECT /*+ GATHER_PLAN_STATISTICS */  employee_id, last_name, salary
FROM employees  ORDER BY salary DESC  FETCH FIRST 5 PERCENT ROWS
ONLY;

EMPLOYEE_ID LAST_NAME      SALARY
----------- ------------ --------
100 King               24000
101 Kochhar            17000
102 De Haan            17000
145 Russell            14000
146 Partners           13500
201 Hartstein          13000

6 rows selected
One of the possible traditional solution uses
the cumulative distribution (CUME_DIST) analytic function.
SELECT /*+ GATHER_PLAN_STATISTICS */  E.*
FROM
(SELECT employee_id, last_name, salary,
CUME_DIST()  OVER( ORDER BY  Salary DESC) cum_dist
FROM employees  ORDER BY salarY DESC) E
WHERE E.CUM_DIST<=0.05;

EMPLOYEE_ID LAST_NAME     SALARY        CUM_DIST
----------- --------- ----------   -------------
100         King           24000   0.009345794393
101      Kochhar           17000   0.02803738318
102      De Haan           17000   0.02803738318
145      Russell           14000   0.03738317757
146     Partners           13500   0.04672897196
Let's consider the execution plan for that query which 
uses the new FETCH {FIRST|NEXT} <pct> PERCENT ROWS clause:
Executions:1 | is_bind_sensitive:N  | is_bind_aware: N | Parsing schema:HR | Disk reads:0 | Consistent gets:1555
SQL_ID  1jnwttv1rt22u, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */  employee_id, last_name, salary
FROM employees  ORDER BY salary DESC  FETCH FIRST 5 PERCENT ROWS ONLY

Plan hash value: 720055818

---------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |       |       |     3 (100)|          |
|*  1 |  VIEW               |           |   107 |  8453 |     3   (0)| 00:00:01 |
|   2 |   WINDOW SORT       |           |   107 |  1712 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEES |   107 |  1712 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=CEIL("from$_
subquery$_002"."rowlimit_$$_total"*5/100))

Operation_id:1 last ouput rows:6 Query block name: SEL$1
Operation_id:2 last ouput rows:107 Query block name: SEL$1
Operation_id:3 last ouput rows:107 Query block name: SEL$1
====================================================================================================

Now we examine the execution plan of the query that uses the CUME_DIST analytic function:

Executions:1 | is_bind_sensitive:N  | is_bind_aware: N | Parsing schema:HR | Disk reads:0 | Consistent gets:7
SQL_ID  0u2b7mdnfy5nh, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */  E.* FROM (SELECT employee_id,
last_name, salary, CUME_DIST()  OVER( ORDER BY  Salary DESC) cum_dist
FROM employees  ORDER BY salarY DESC) E WHERE E.CUM_DIST<=0.05

Plan hash value: 720055818

---------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |       |       |     3 (100)|          |
|*  1 |  VIEW               |           |   107 |  5671 |     3   (0)| 00:00:01 |
|   2 |   WINDOW SORT       |           |   107 |  1712 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEES |   107 |  1712 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("E"."CUM_DIST"<=.05)

Operation_id:1 last ouput rows:5 Query block name: SEL$2
Operation_id:2 last ouput rows:107 Query block name: SEL$2
Operation_id:3 last ouput rows:107 Query block name: SEL$2
====================================================================================================
We can observe that BOTH queries use the SAME EXECUTION plan,
but with different number of consistent gets (1551 versus 7!). 
Other metrics are same. 
I created a greater table:
CREATE TABLE big_emp(empno,last_name,first_name,salary)
AS
SELECT ROWNUM,E.last_name||ROWNUM,E.first_name||ROWNUM,E.salary
FROM employees E,employees D,employees F;
SELECT COUNTt(*)  FROM big_emp;

COUNT(*)
----------
1225043

I executed the following queries:

SELECT /*+ GATHER_PLAN_STATISTICS */  *
FROM big_emp  ORDER BY salary DESC  FETCH FIRST 1 PERCENT ROWS
ONLY;

SELECT /*+ GATHER_PLAN_STATISTICS */  E.*
FROM
(SELECT b.*,
CUME_DIST()  OVER( ORDER BY  Salary DESC) cum_dist
FROM big_emp b ORDER BY salarY DESC) E
WHERE E.CUM_DIST<=0.01;

Of course, I got the same execution plan.
But the were no big difference between the number of consistent gets.

Executions:1 | is_bind_sensitive:N  | is_bind_aware: N | Parsing schema:HR | Disk reads:0 | Consistent gets:6693
SQL_ID  5cv1yxs3aht9g, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */  E.* FROM (SELECT b.*, CUME_DIST()
OVER( ORDER BY  Salary DESC) cum_dist FROM big_emp b ORDER BY salarY
DESC) E WHERE E.CUM_DIST<=0.01

Plan hash value: 1432758025
Executions:1 | is_bind_sensitive:N  | is_bind_aware: N | Parsing schema:HR | Disk reads:6684 | Consistent gets:8059
SQL_ID  gg7n3x4px3tak, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */  * FROM big_emp  ORDER BY salary
DESC  FETCH FIRST 1 PERCENT ROWS ONLY

Plan hash value: 1432758025
The number of consistent gets were 
(6693- for traditional versus 8059 - new for feature)
As I saw we can use both solutions,but the FETCH clause
is more readable and user-friend.

Restricted access to PL/SQL subprograms in Oracle 12c

July 10, 2013

Prior to Oracle 12c everyone can refer to a subprogram (helper program) in an other PL/SQL program unit if that user has execute privilege for the helper object or owns it.
Now, in Oracle 12c the creator of the helper can determine that
which program units can refer to it, even the other users have execute privilege for the helper objects or they have the EXECUTE ANY PRIVILEGE system privilege.
Even the the owner of the helper object is same as the PL/SQL
subprogram’s owner, but if the dependent object is not entitled to use
the helper subprogram it can not refer to helper PL/SQL subprogram.
The new feature is that the helper PL/SQL subprogram can have
an ACCESSIBLE BY (subprogram1,subprogram2, …) clause where the creator can provide the access to the subprograms listed after the ACCESSIBLE BY keywords.
In the following example HR user who created the tax function provided access of the tax function to the depts procedure (owned by HR) and to depts2 owned by CZINK user.
Note that HR issued the suitable object privilege to czink.
Let’s see the definition of the tax function and the GRANT statement:

CREATE OR REPLACE FUNCTION tax(BASE NUMBER)
RETURN NUMBER
ACCESSIBLE BY (depts,czink.depts2)
IS
S NUMBER;
BEGIN
IF BASE<4000 THEN
  S:= 0.10;
ELSIF BASE<20000 THEN
  S:=0.25;
ELSE
  S:=0.3;
END IF;
RETURN BASE*S;
END;
/
GRANT EXECUTE ON tax TO czink;

Now HR user created a procedure called depts and executed it:

CREATE OR REPLACE
PROCEDURE depts(p_deptno  employees.department_id%TYPE)
AUTHID CURRENT_USER
IS
CURSOR c_emp(c_deptno employees.department_id%TYPE) IS
SELECT e.*, 12*salary*(1+NVL(commission_pct,0)) ANN_SAL
FROM employees e WHERE department_id=c_deptno;
manager employees.last_name%TYPE;
BEGIN
FOR r  IN c_emp(p_deptno) LOOP
    IF r.manager_id IS NOT NULL THEN
     SELECT last_name INTO MANAGER FROM employees
     WHERE employee_id=r.manager_id;
   ELSE
    manager:='No Manager';
   END IF;
   DBMS_OUTPUT.PUT_LINE(c_emp%ROWCOUNT||'. name:='||r.last_name||' salary:'||r.salary||
   ' tax:'|| tax(r.salary) ||  ' Manager:'||manager||' '||r.manager_id||' Annual Salary:'||r.ann_sal);
END LOOP;
EXCEPTION
 WHEN OTHERS THEN
dbms_output.put_line('The error:'||DBMS_UTILITY.FORMAT_ERROR_STACK );
END;
/
exec depts(90)
anonymous block completed
1. name:=King salary:24000 tax:7200 Manager:No Manager  Annual Salary:288000
2. name:=Kochhar salary:17000 tax:4250 Manager:King 100 Annual Salary:204000
3. name:=De Haan salary:17000 tax:4250 Manager:King 100 Annual Salary:204000

However if HR wants to create a depts2 procedure with the
following code, Oracle produces an error message, because the depts2 procedure WAS NOT ENTITLED to refer to the tax function:

CREATE OR REPLACE
PROCEDURE depts2(p_deptno  employees.department_id%TYPE)
AUTHID CURRENT_USER
IS
CURSOR c_emp(c_deptno employees.department_id%TYPE) IS
SELECT e.*, 12*salary*(1+NVL(commission_pct,0)) ANN_SAL
FROM employees e WHERE department_id=c_deptno;
manager employees.last_name%TYPE;
BEGIN
FOR r  IN c_emp(p_deptno) LOOP
    IF r.manager_id IS NOT NULL THEN
     SELECT last_name INTO MANAGER FROM employees
     WHERE employee_id=r.manager_id;
   ELSE
    manager:='No Manager';
   END IF;
   DBMS_OUTPUT.PUT_LINE(c_emp%ROWCOUNT||'. name:='||r.last_name||' salary:'||r.salary||
   ' tax:'|| tax(r.salary) ||  ' Manager:'||manager||' '||r.manager_id||' Annual Salary:'||r.ann_sal);
END LOOP;
EXCEPTION
 WHEN OTHERS THEN
dbms_output.put_line('The error:'||DBMS_UTILITY.FORMAT_ERROR_STACK );
END;
/
Error(17,14): PLS-00904: insufficient privilege to access object TAX

Now CZINK user wants to create and execute a depts2 procedure
referring to the tax function owned by HR:

(Supposed that CZINK user has it’s own employees table)

CREATE OR REPLACE
PROCEDURE depts2(p_deptno  employees.department_id%TYPE)
AUTHID CURRENT_USER
IS
CURSOR c_emp(c_deptno employees.department_id%TYPE) IS
SELECT e.*, 12*salary*(1+NVL(commission_pct,0)) ANN_SAL
FROM employees e WHERE department_id=c_deptno;
manager employees.last_name%TYPE;
BEGIN
FOR r  IN c_emp(p_deptno) LOOP
IF r.manager_id IS NOT NULL THEN
SELECT last_name INTO MANAGER FROM employees
WHERE employee_id=r.manager_id;
ELSE
manager:='No Manager';
END IF;
DBMS_OUTPUT.PUT_LINE(c_emp%ROWCOUNT||'. name:='||r.last_name||' salary:'||r.salary||
' tax:'|| HR.tax(r.salary) ||  ' Manager:'||manager||' '||r.manager_id||' Annual Salary:'||r.ann_sal);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('The error:'||DBMS_UTILITY.FORMAT_ERROR_STACK );
END;
/
exec depts2(90)
anonymous block completed
1. name:=King salary:24000 tax:7200 Manager:No Manager  Annual Salary:288000
2. name:=Kochhar salary:17000 tax:4250 Manager:King 100 Annual Salary:204000
3. name:=De Haan salary:17000 tax:4250 Manager:King 100 Annual Salary:204000

Of course, if CZINK user created a procedure
(referring to HR’s tax function) with different name than depts2
then CZINK user would get the same error message.
(The DBA role was assigned to the CZINK user in my example)

Comment and benefits of using the ACCESSIBLE BY clause:

1. You can provide access to a helper PL/SQL programs only for those
PL/SQL subprograms which are really need to refer to them.
2. The restriction made for PL/SQL subprograms not for users.
3. Even if a user has a DBA role or “just” the
EXECUTE ANY PROCEDURE the user won’t be able to use the helper
PL/SQL subprogram, unless it(the helper program) allows
to access directly to the invoker program.
4. You can specify the ACCESSIBLE BY clause on package level
(not for individual members), like this:

CREATE OR REPLACE PACKAGE taxes ACCESSIBLE BY (depts,czink.depts2)
IS
FUNCTION tax1(BASE NUMBER) RETURN NUMBER;
FUNCTION tax2(BASE NUMBER) RETURN NUMBER;
END taxes;
/
CREATE OR REPLACE PACKAGE BODY taxes
IS
FUNCTION tax1(BASE NUMBER) RETURN NUMBER
IS
S NUMBER;
BEGIN
IF    BASE<4000 THEN   S:= 0.10;
ELSIF BASE<20000 THEN   S:=0.25;
ELSE  S:=0.3;
END IF;
RETURN BASE*S;
END tax1;
FUNCTION tax2(BASE NUMBER) RETURN NUMBER
IS
S NUMBER;
BEGIN
IF BASE<4000 THEN   S:= 0.10;
ELSE   S:=0.3;
END IF;
RETURN BASE*S;
END tax2;
END taxes;
/

Work with WITH option in Oracle 12c!

July 9, 2013

Starting Oracle 9.0  – according to SQL 1999 standard – Oracle introduced the WITH option which can be considered
as the extension of the inline views.
Let’s see an “old” example:

WITH
dept_costs  AS
(SELECT department_name, SUM(salary) as dept_total
FROM employees, departments
WHERE employees.department_id =
departments.department_id
GROUP BY department_name),
avg_cost  AS
(SELECT SUM(dept_total)/COUNT(*) as dept_avg
FROM   dept_costs)
SELECT * FROM dept_costs
WHERE dept_total >
(SELECT dept_avg FROM  avg_cost)
ORDER BY department_name;

(The origin of this statement is Oracle course,titled:
Oracle Database: SQL Fundamentals I
Volume II • Student Guide).
In Oracle 12c we have new enhancements for this kind of SQL statement:
We can define “in-line” functions or procedures after the WITH clause.
In the following example we are looking for those departments, where
total salary of the department is greater than the maximum of the average of the total salaries:

WITH
FUNCTION tax(p_amount IN NUMBER)
RETURN NUMBER
IS
m  NUMBER;
BEGIN
IF p_amount <8000 THEN
m:=0.08;
ELSIF p_amount <18000 THEN
m:=0.25;
ELSE
m:=0.3;
END IF;
RETURN p_amount * m;
END;
emp_costs  AS (
SELECT d.department_name dept_name,e.last_name, e.salary AS salary,
tax(e.salary) AS tax_amount
FROM   employees e JOIN departments d
ON     e.department_id = d.department_id),
dept_costs    AS (
SELECT dept_name, SUM(salary) AS dept_sal,SUM(tax_amount) tax_sum,
AVG(salary) avg_sal
FROM   emp_costs GROUP BY dept_name)
SELECT * FROM   dept_costs
WHERE  dept_sal > (SELECT MAX(avg_sal) FROM dept_costs)
ORDER BY dept_name;
/

The result is:

DEPT_NAME             DEPT_SAL    TAX_SUM    AVG_SAL
------------------------------ ---------- ---------- ----------
Accounting               20308       5077      10154
Executive                58000      15700 19333.3333
Finance                  51608       9094 8601.33333
IT                       28800       3834       5760
Purchasing               24900       3862       4150
Sales                   304500      62083 8955.88235
Shipping                156400      15266 3475.55556

7 rows selected.

The execution plan is the following ( not very simple!):

Plan hash value: 38700341

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                           |       |       |     7 (100)|          |
|   1 |  TEMP TABLE TRANSFORMATION      |                           |       |       |            |          |
|   2 |   LOAD AS SELECT                |                           |       |       |            |          |
|   3 |    HASH GROUP BY                |                           |    27 |   621 |     3   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                |                           |       |       |            |          |
|   5 |      NESTED LOOPS               |                           |   106 |  2438 |     3   (0)| 00:00:01 |
|   6 |       TABLE ACCESS FULL         | EMPLOYEES                 |   107 |   749 |     3   (0)| 00:00:01 |
|*  7 |       INDEX UNIQUE SCAN         | DEPT_ID_PK                |     1 |       |     0   (0)|          |
|   8 |      TABLE ACCESS BY INDEX ROWID| DEPARTMENTS               |     1 |    16 |     0   (0)|          |
|   9 |   SORT ORDER BY                 |                           |    27 |  1512 |     4   (0)| 00:00:01 |
|* 10 |    VIEW                         |                           |    27 |  1512 |     2   (0)| 00:00:01 |
|  11 |     TABLE ACCESS FULL           | SYS_TEMP_0FD9D6694_1CABAF |    27 |   621 |     2   (0)| 00:00:01 |
|  12 |     SORT AGGREGATE              |                           |     1 |    13 |            |          |
|  13 |      VIEW                       |                           |    27 |   351 |     2   (0)| 00:00:01 |
|  14 |       TABLE ACCESS FULL         | SYS_TEMP_0FD9D6694_1CABAF |    27 |   621 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
10 - filter("DEPT_SAL">)

====================================================================================================</pre>

We can use the WITH option with plsql_declarations clause
in DML statements as well.
Let’s consider the following example where we want to compute the
tax amount of the salaries and modify the tax_amount column,
but we DON’T WANT TO USE the stored tax function if exists at all!
First we create a copy of the employees table and modify the
structure with a new column

CREATE TABLE newmp AS SELECT * FROM employees;
ALTER TABLE newemp ADD tax_amount NUMBER(10,2);

Now we modify the content of the tax_amount column
(which is originally empty) with each employee’s tax amount:

UPDATE     /*+ WITH_PLSQL */ newemp E
SET tax_amount=(WITH FUNCTION TAX(P_AMOUNT IN NUMBER)
RETURN NUMBER
IS
M  NUMBER;
BEGIN
IF P_AMOUNT <8000 THEN
M:=0.08;
ELSIF P_AMOUNT <18000 THEN
M:=0.25;
ELSE
M:=0.3;
END IF;
RETURN P_AMOUNT*M;
END;
SELECT tax(salary) FROM employees m
WHERE m.employee_id=e.employee_id)
/

Observe that the WITH statement has a special hint.
The WITH_PLSQL hint only enables you to specify
the WITH plsql_declarations clause within the statement.
It is not an optimizer hint.(see Oracle 12c documentation)

Let’s check the result (only the first 10 rows are displayed):


SELECT *
FROM
(SELECT  last_name,salary,tax_amount from newemp
ORDER BY  salary DESC)
WHERE ROWNUM<=10;

LAST_NAME                          SALARY TAX_AMOUNT
-------------------------      ---------- ----------
King                                24000       7200
Kochhar                             17000       4250
De Haan                             17000       4250
Russell                             14000       3500
Partners                            13500       3375
Hartstein                           13000       3250
Greenberg                           12008       3002
Higgins                             12008       3002
Errazuriz                           12000       3000
Ozer                                11500       2875

10 rows selected.

The execution plan for the UPDATE is the following(simpler!)

Executions:1 | is_bind_sensitive:N  | is_bind_aware: N | Parsing schema:HR | Disk reads:0 | Consistent gets:505
SQL_ID  6s126hzr93g2d, child number 0
-------------------------------------
UPDATE  /*+ WITH_PLSQL */ newemp E SET tax_amount=(WITH FUNCTION
TAX(P_AMOUNT IN NUMBER) RETURN NUMBER IS M  NUMBER; BEGIN IF P_AMOUNT
<8000 THEN M:=0.08; ELSIF P_AMOUNT <18000 THEN M:=0.25; ELSE M:=0.3;
END IF; RETURN P_AMOUNT*M; END; SELECT tax(salary) FROM employees m
WHERE m.employee_id=e.employee_id)

Plan hash value: 759157450

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |               |       |       |   217 (100)|          |
|   1 |  UPDATE                      | NEWEMP        |       |       |            |          |
|   2 |   TABLE ACCESS FULL          | NEWEMP        |   107 |  1819 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |     8 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0)|          |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("M"."EMPLOYEE_ID"=:B1)

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

The greatest benefit of using PL/SQL functions or procedures that we
can use our (not stored) subprograms in queries without writing
procedural code.

Limiting the number of ordered rows retrieved in Oracle 12c

July 9, 2013

Starting with Oracle 12c You can limit the ordered result set of a query with FETCH clause after the ORDER BY clause.
Earlier You had to use analytic function for solving this problem.
Now  -with the new syntax- you can implement it with easier syntax,
but the execution plan is SAME, as you will see it.
Let’s consider the following ranking problem:
We would like to see the  first “n”   well paid people from employees
table using the traditional RANK analytic function:

SELECT  /*+ GATHER_PLAN_STATISTICS */  t.*
FROM(SELECT employee_id,last_name,salary,
RANK()
 OVER( ORDER BY  salary desc) rank from employees) t
WHERE t.rank<=2
100 King               24000         1
101 Kochhar            17000         2
102 De Haan            17000         2

As You could see that  the server fetched 3 rows because
the last 2 employee have the same salaries.
Now consider the new FETCH  opertor with FIRST/NEXT
(they are same!):

SELECT /*+ GATHER_PLAN_STATISTICS */  employee_id, last_name, salary
FROM employees
ORDER BY salary DESC
FETCH NEXT 5 ROWS ONLY ;
100 King                  24000
101 Kochhar               17000
102 De Haan               17000
145 Russell               14000
146 Partners              13500

Nice feature is the OFFSET clause, which enables us to fetch next “n”rows after “m” rows:

SELECT /*+ GATHER_PLAN_STATISTICS */  employee_id, last_name, salary
FROM employees
ORDER BY salary DESC OFFSET 5 ROWS
FETCH NEXT 5 ROWS ONLY  ;
201 Hartstein             13000
108 Greenberg             12008
205 Higgins               12008
147 Errazuriz             12000
168 Ozer                  11500

Now let’s see the execution plans! You will see – even the number of the rows are different – the SAME EXECUTION PLAN was used
by the optimizer, first for the RANK function:

Executions:1 | is_bind_sensitive:N  | is_bind_aware: N | Parsing schema:HR | Disk reads:0 | Consistent gets:275
SQL_ID  fb0u8y9g33ykp, child number 0
-------------------------------------
SELECT  /*+ GATHER_PLAN_STATISTICS */  t.* FROM (SELECT
employee_id,last_name,salary,  RANK() OVER( ORDER BY  salary desc) rank
from employees) t  WHERE t.rank<=2

Plan hash value: 2698234872

--------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |       |       |     3 (100)|          |
|*  1 |  VIEW                    |           |   107 |  5671 |     3   (0)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|           |   107 |  1712 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | EMPLOYEES |   107 |  1712 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("T"."RANK"<=2)
2 - filter(RANK() OVER ( ORDER BY INTERNAL_FUNCTION("SALARY") DESC )<=2)

Operation_id:1 last ouput rows:3 Query block name: SEL$2
Operation_id:2 last ouput rows:3 Query block name: SEL$2
Operation_id:3 last ouput rows:107 Query block name: SEL$2
====================================================================================================

Let’s consider the execution plan for the first FETCH operator:
(without OFFSET option)

Executions:1 | is_bind_sensitive:N  | is_bind_aware: N | Parsing schema:HR | Disk reads:0 | Consistent gets:7
SQL_ID  3hp4dwmhuvp7b, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */  employee_id, last_name, salary
FROM employees  ORDER BY salary DESC FETCH NEXT 5 ROWS ONLY

Plan hash value: 2698234872

--------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |       |       |     3 (100)|          |
|*  1 |  VIEW                    |           |   107 |  7062 |     3   (0)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|           |   107 |  1712 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | EMPLOYEES |   107 |  1712 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=5)
2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("SALARY") DESC
)<=5)

Operation_id:1 last ouput rows:5 Query block name: SEL$1
Operation_id:2 last ouput rows:5 Query block name: SEL$1
Operation_id:3 last ouput rows:107 Query block name: SEL$1
====================================================================================================

We can observe – again – the same plan hash value used by the optimizer.

Executions:1 | is_bind_sensitive:N  | is_bind_aware: N | Parsing schema:HR | Disk reads:0 | Consistent gets:9
SQL_ID  1wg200mm8st4t, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */  employee_id, last_name, salary
FROM employees  ORDER BY salary DESC OFFSET 5 ROWS FETCH NEXT 5 ROWS
ONLY

Plan hash value: 2698234872

--------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |       |       |     3 (100)|          |
|*  1 |  VIEW                    |           |   107 |  7062 |     3   (0)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|           |   107 |  1712 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | EMPLOYEES |   107 |  1712 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(("from$_subquery$_002"."rowlimit_$$_rownumber"<=CASE  WHEN
(5>=0) THEN 5 ELSE 0 END +5 AND "from$_subquery$_002"."rowlimit_$$_rownumber">
5))
2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("SALARY") DESC
)<=CASE  WHEN (5>=0) THEN 5 ELSE 0 END +5)

Operation_id:1 last ouput rows:5 Query block name: SEL$1
Operation_id:2 last ouput rows:10 Query block name: SEL$1
Operation_id:3 last ouput rows:107 Query block name: SEL$1
=================================================================================================

The ROW_NUMBER and a searched CASE conditional expression were used.

We can see that these nice new features use the same operators
and options however they will be popular – I guess – because the
syntax is easier to learn it.

(The next entry will focus on FECTH with PERCENT feature)

Store all consistent states of a table: FLASHBACK ARCHIVE

January 25, 2013

Before Oracle 11g it was great challenge to keep previous consistent states of a table.
One of the available method was to store the state (eg. rows of the table) In the UNDO segment. Using the following statements  or similar to them:

 ALTER SYSTEM SET UNDO_RETENTION = 3600;
 ALTER TABLESPACE UNDOTBS1 RETENTION GUARANTEE ;

we can solve the problem, but the solution is VERY EXPENSIVE! Why?
Because we have to store and keep the previous data for each table that will consume huge amount of memory and unnecessary.
The solution is the FLASHBACK ARCHIVE which was introduced in Oracle11g.
Let’s consider the following scenario.
We want to keep all consistent state of our – very important – employees table.
1. Having the FLASHBACK ARCHIVE ADMINISTER system privilege we can create an archive with the following statement:

CREATE FLASHBACK ARCHIVE  archive_cl
 TABLESPACE EXAMPLE QUOTA 1 M RETENTION 1 DAY;

Sorry, one day is not enough!

ALTER FLASHBACK ARCHIVE archive_cl MODIFY RETENTION 1 MONTH;

2. We create a copy of the original employees table:

CREATE TABLE emp3
AS
SELECT * FROM employees;
SELECT COUNT(*) FROM emp3;
COUNT(*)
----------
107

Note, if we had an archived table we have to remove the archive from the table before drop it:

ALTER TABLE EMP3 NO FLASHBACK ARCHIVE ;
DROP TABLE emp3 PURGE;

3. Now we assign an archive object to the table:

ALTER TABLE EMP3  FLASHBACK ARCHIVE archive_cl;

4. It is well known  if we issue  DML statements against a table then the “old” data
will be available in the undo segment temporarly (before COMMIT;).
When the commit is processed these data must be transferred into the desgnated achive
object. We must allow  row movement for this table:

ALTER TABLE EMP3 ENABLE ROW MOVEMENT;

5. We can query the archived tables:

SELECT * FROM DBA_FLASHBACK_ARCHIVE_TABLES;
TABLE_NAME                     OWNER_NAME                     FLASHBACK_ARCHIVE_NAME                 ARCHIVE_TABLE_NAME                                    STATUS
------------------------------ ------------------------------ -------------------------------------- ----------------------------------------------------- --------
EMP3                           ORA1                           ARCHIVE_CL                             SYS_FBA_HIST_185850                                   ENABLED

6. Let’s implement a very simple transaction:

DELETE EMP3 WHERE EMPLOYEE_ID<200;
COMMIT;
SELECT * FROM emp3;
EMPLOYEE_ID FIRST_NAME LAST_NAME   EMAIL      PHONE_NUMBER        HIRE_DATE          JOB_ID     SALARY  COMM MANAGER_ID DEPARTMENT_ID
----------- ---------- ----------- ---------- ------------------- ------------------ ---------- ------ ----- ---------- -------------
203 Susan      Mavris      SMAVRIS    515.123.7777        07-JUN-94          HR_REP       6500              101            40
206 William    Gietz       WGIETZ     515.123.8181        07-JUN-94          AC_ACCOUNT   8300              205           110
202 Pat        Fay         PFAY       603.123.6666        17-AUG-97          MK_REP       6000              201            20
201 Michael    Hartstein   MHARTSTE   515.123.5555        17-FEB-96          MK_MAN      13000              100            20
205 Shelley    Higgins     SHIGGINS   515.123.8080        07-JUN-94          AC_MGR      12000              101           110
204 Hermann    Baaa        HBAER      515.123.8888        07-JUN-94          PR_REP      10000              101            70
200 Jennifer   Whalen      JWHALEN    515.123.4444        17-SEP-87          AD_ASST      4400              101            10

7 rows selected.

7. We can check (with a flashback query) what timestamp is suitable for us and after with FLASHBACK TABLE statement  we can withdraw a previous state of this table:

SELECT * FROM emp3 as of TIMESTAMP
SYSTIMESTAMP - INTERVAL '&Couple_of_minutes' MINUTE;
FLASHBACK TABLE EMP3 TO  TIMESTAMP
 SYSTIMESTAMP - INTERVAL '&Couple_of_minutes' MINUTE;
SELECT * FROM emp3;
.
.
172 Elizabeth  Bates       EBATES     011.44.1343.529268  24-MAR-99          SA_REP       7300   .15        148            80

EMPLOYEE_ID FIRST_NAME LAST_NAME   EMAIL      PHONE_NUMBER        HIRE_DATE          JOB_ID     SALARY  COMM MANAGER_ID DEPARTMENT_ID
----------- ---------- ----------- ---------- ------------------- ------------------ ---------- ------ ----- ---------- -------------
173 Sundita    Kumar       SKUMAR     011.44.1343.329268  21-APR-00          SA_REP       6100   .10        148            80
174 Ellen      Abel        EABEL      011.44.1644.429267  11-MAY-96          SA_REP      11000   .30        149            80
175 Alyssa     Hutton      AHUTTON    011.44.1644.429266  19-MAR-97          SA_REP       8800   .25        149            80
176 Jonathon   Taylor      JTAYLOR    011.44.1644.429265  24-MAR-98          SA_REP       8600   .20        149            80
177 Jack       Livingston  JLIVINGS   011.44.1644.429264  23-APR-98          SA_REP       8400   .20        149            80
178 Kimberely  Grant       KGRANT     011.44.1644.429263  24-MAY-99          SA_REP       7000   .15        149
179 Charles    Johnson     CJOHNSON   011.44.1644.429262  04-JAN-00          SA_REP       6200   .10        149            80

107 rows selected.

Let’s suppose we create an other transaction:

DELETE EMP3 WHERE EMPLOYEE_ID<200;
COMMIT;
SELECT * FROM emp3;

EMPLOYEE_ID FIRST_NAME LAST_NAME   EMAIL      PHONE_NUMBER        HIRE_DATE          JOB_ID     SALARY  COMM MANAGER_ID DEPARTMENT_ID
----------- ---------- ----------- ---------- ------------------- ------------------ ---------- ------ ----- ---------- -------------
201 Michael    Hartstein   MHARTSTE   515.123.5555        17-FEB-96          MK_MAN      13000              100            20
204 Hermann    Baaa        HBAER      515.123.8888        07-JUN-94          PR_REP      10000              101            70
205 Shelley    Higgins     SHIGGINS   515.123.8080        07-JUN-94          AC_MGR      12000              101           110
100 Steven     King        SKING      515.123.4567        17-JUN-87          AD_PRES     24000                             90
101 Neena      Kochhar     NKOCHHAR   515.123.4568        21-SEP-89          AD_VP       17000              100            90
102 Lex        De Haan     LDEHAAN    515.123.4569        13-JAN-93          AD_VP       17000              100            90
108 Nancy      Greenberg   NGREENBE   515.124.4569        17-AUG-94          FI_MGR      12000              101           100
114 Den        Raphaely    DRAPHEAL   515.127.4561        07-DEC-94          PU_MAN      11000              100            30
145 John       Russell     JRUSSEL    011.44.1344.429268  01-OCT-96          SA_MAN      14000   .40        100            80
146 Karen      Partners    KPARTNER   011.44.1344.467268  05-JAN-97          SA_MAN      13500   .30        100            80
147 Alberto    Errazuriz   AERRAZUR   011.44.1344.429278  10-MAR-97          SA_MAN      12000   .30        100            80
148 Gerald     Cambrault   GCAMBRAU   011.44.1344.619268  15-OCT-99          SA_MAN      11000   .30        100            80
149 Eleni      Zlotkey     EZLOTKEY   011.44.1344.429018  29-JAN-00          SA_MAN      10500   .20        100            80
150 Peter      Tucker      PTUCKER    011.44.1344.129268  30-JAN-97          SA_REP      10000   .30        145            80
151 David      Bernstein   DBERNSTE   011.44.1344.345268  24-MAR-97          SA_REP       9500   .25        145            80
156 Janette    King        JKING      011.44.1345.429268  30-JAN-96          SA_REP      10000   .35        146            80
157 Patrick    Sully       PSULLY     011.44.1345.929268  04-MAR-96          SA_REP       9500   .35        146            80
162 Clara      Vishney     CVISHNEY   011.44.1346.129268  11-NOV-97          SA_REP      10500   .25        147            80
163 Danielle   Greene      DGREENE    011.44.1346.229268  19-MAR-99          SA_REP       9500   .15        147            80
168 Lisa       Ozer        LOZER      011.44.1343.929268  11-MAR-97          SA_REP      11500   .25        148            80
169 Harrison   Bloom       HBLOOM     011.44.1343.829268  23-MAR-98          SA_REP      10000   .20        148            80
170 Tayler     Fox         TFOX       011.44.1343.729268  24-JAN-98          SA_REP       9600   .20        148            80
174 Ellen      Abel        EABEL      011.44.1644.429267  11-MAY-96          SA_REP      11000   .30        149            80

23 rows selected.

From DBA_FLASHBACK_ARCHIVE_TABLES we now the name of the archive table, let’s see the content of it:

SELECT rid, STARTSCN,ENDSCN ,xid,OPERATION, EMPLOYEE_ID , SALARY,lAST_NAME, DEPARTMENT_ID FROM SYS_FBA_HIST_185850;

RID                    STARTSCN     ENDSCN XID              O EMPLOYEE_ID SALARY LAST_NAME   DEPARTMENT_ID
-------------------- ---------- ---------- ---------------- - ----------- ------ ----------- -------------
AAAtX6AAEAAAPSTAAJ                33254659                            100  24000 King                   90
AAAtX6AAEAAAPSTAAK                33254659                            101  17000 Kochhar                90
AAAtX6AAEAAAPSTAAL                33254659                            102  17000 De Haan                90
AAAtX6AAEAAAPSTAAM                33254659                            103   9000 Hunold                 60
AAAtX6AAEAAAPSUAAV     33256562   33328409 09001C00AB5F0000 I         103   9000 Hunold                 60
AAAtX6AAEAAAPSTAAN                33254659                            104   6000 Ernst                  60
AAAtX6AAEAAAPSUAAW     33256562   33328409 09001C00AB5F0000 I         104   6000 Ernst                  60
AAAtX6AAEAAAPSTAAO                33254659                            105   4800 Austin                 60
AAAtX6AAEAAAPSUAAX     33256562   33328409 09001C00AB5F0000 I         105   4800 Austin                 60
AAAtX6AAEAAAPSTAAP                33254659                            106   4800 Pataballa              60
AAAtX6AAEAAAPSUAAY     33256562   33328409 09001C00AB5F0000 I         106   4800 Pataballa              60
AAAtX6AAEAAAPSTAAQ                33254659                            107   4200 Lorentz                60
AAAtX6AAEAAAPSUAAZ     33256562   33328409 09001C00AB5F0000 I         107   4200 Lorentz                60
AAAtX6AAEAAAPSTAAR                33254659                            108  12000 Greenberg             100
AAAtX6AAEAAAPSTAAS                33254659                            109   9000 Faviet                100
<pre>

As we can see that – according to different transactions – some rows appear two times
(eg. the employee_id <200 and later salary<10000) with different SCN’s  and some rows
apear with one SCN.
We you want to use th flashback archive feature, please always visit the
flashback archive table (in our case SYS_FBA_HIST_185850)

LISTAGG function versus Tom Kyte’s STRAGG (UDF)

January 18, 2013

LISTAGG  (Oracle defined ) versus
Tom Kyte’s STRAGG (User Defined Aggregate Function)

As we know Oracle implemented the LISTAGG aggregate function in 11gR2.
With this function you can concatenate member row’s value in one grouped row.
The structure of the LISTAGG function is similar to other group functions,
but we have an extra WITHIN GROUP clause in the function definition.
Of course we can use this group function with other group functions:

SELECT department_id "Dept",SUM(salary),COUNT(*),
LISTAGG(last_name||':'||salary, ' | ')
WITHIN GROUP (ORDER By salary, last_name) list
FROM employees
WHERE department_id NOT IN (50,80,100,110)
GROUP BY  department_id;

Dept SUM(SALARY)   COUNT(*) LIST
---------- ----------- ---------- --------------------------------------------------------------------------------------
10        4400          1 Whalen:4400
20       19000          2 Fay:6000 | Hartstein:13000
30       24900          6 Colmenares:2500 | Himuro:2600 | Tobias:2800 | Baida:2900 | Khoo:3100 | Raphaely:11000
40        6500          1 Mavris:6500
60       28800          5 Lorentz:4200 | Austin:4800 | Pataballa:4800 | Ernst:6000 | Hunold:9000
70       10000          1 Baer:10000
90       58000          3 De Haan:17000 | Kochhar:17000 | King:24000

7 rows selected.

Oracle used a simple INDEX FULL SCAN operator with SORT GROUP BY.
Other example: we want to see all direct worker’s name in one row for each manager:

SELECT m.last_name mgr_name,
LISTAGG(e.last_name, ' | ')
WITHIN GROUP (ORDER By e.salary, e.last_name) workers
FROM employees e JOIN employees m
ON e.manager_id=m.employee_id
WHERE e.manager_id IS NOT NULL
GROUP BY m.last_name;
MGR_NAME                  WORKERS
------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------
Cambrault                 Kumar | Bates | Smith | Fox | Bloom | Ozer
De Haan                   Hunold
Errazuriz                 Banda | Ande | Lee | Marvins | Greene | Vishney
Fripp                     Olson | Marlow | Atkinson | Cabrio | Bissot | Dellinger | Bull | Sarchand
Greenberg                 Popp | Sciarra | Urman | Chen | Faviet
Hartstein                 Fay
Higgins                   Gietz
Hunold                    Lorentz | Austin | Pataballa | Ernst
Kaufling                  Philtanker | Gee | Perkins | Gates | Rogers | Mallin | Dilly | Chung
King                      Mourgos | Vollman | Kaufling | Weiss | Fripp | Zlotkey | Cambrault | Raphaely | Errazuriz | Hartstein | Partners | Russell | De Haan | Kochhar
Kochhar                   Whalen | Mavris | Baer | Greenberg | Higgins
Mourgos                   Vargas | Grant | Matos | Oconnell | Feeney | Davies | Walsh | Rajs
Partners                  Sewall | Doran | Smith | Mcewen | Sully | King
Raphaely                  Colmenares | Himuro | Tobias | Baida | Khoo
Russell                   Tuvault | Cambrault | Olsen | Hall | Bernstein | Tucker
Vollman                   Patel | Seo | Jones | Mccain | Stiles | Ladwig | Everett | Bell
Weiss                     Markle | Landry | Sullivan | Mikkilineni | Geoni | Fleaur | Nayer | Taylor
Zlotkey                   Johnson | Grant | Livingston | Taylor | Hutton | Abel

18 rows selected.

We can extend the LISTAGG function with analytic clause:

 SELECT department_id "Dept", last_name "Name",
LISTAGG(last_name, '; ')
WITHIN GROUP (ORDER By department_id, last_name)
OVER (PARTITION BY department_id ) emp
FROM employees
WHERE department_id NOT IN (50,80,100,110);
Dept Name                   EMP
---- ---------------------- ------------------------------------------------------
10   Whalen                 Whalen
20   Fay                    Fay; Hartstein
20   Hartstein              Fay; Hartstein
30   Baida                  Baida; Colmenares; Himuro; Khoo; Raphaely; Tobias
30   Colmenares             Baida; Colmenares; Himuro; Khoo; Raphaely; Tobias
30   Himuro                 Baida; Colmenares; Himuro; Khoo; Raphaely; Tobias
30   Khoo                   Baida; Colmenares; Himuro; Khoo; Raphaely; Tobias
30   Raphaely               Baida; Colmenares; Himuro; Khoo; Raphaely; Tobias
30   Tobias                 Baida; Colmenares; Himuro; Khoo; Raphaely; Tobias
40   Mavris                 Mavris
60   Austin                 Austin; Ernst; Hunold; Lorentz; Pataballa
60   Ernst                  Austin; Ernst; Hunold; Lorentz; Pataballa
60   Hunold                 Austin; Ernst; Hunold; Lorentz; Pataballa
60   Lorentz                Austin; Ernst; Hunold; Lorentz; Pataballa
60   Pataballa              Austin; Ernst; Hunold; Lorentz; Pataballa
70   Baer                   Baer
90   De Haan                De Haan; King; Kochhar
90   King                   De Haan; King; Kochhar
90   Kochhar                De Haan; King; Kochhar

19 rows selected.
 

The result is fine but we have to face some restrictions:

1. The maximum length is 4000 characters.
2. The ORDER BY clause and the window clause  can not be used in the OVER clause. We can specify the query partition clause only!
From the second restriction follows that we can not implement a
cumulative aggregation” for the argument(s) of the LISTAGG!
On of the nicest solution might be  Tom Kyte’s STRAGG implementation.
(See http://www.sqlsnippets.com/en/topic-11591.html)
As we know we can add an analytic clause to a user defined aggregation function!
Now we want to see the all previous names –according to the salary descending order – for each employee in each selected groups:

 SELECT d.department_id deptno ,d.department_name,
 stragg(e.last_name)
 OVER(PARTITION BY d.department_id order by e.last_name ROWS  UNBOUNDED PRECEDING ) names
 FROM departments d, employees e
 WHERE e.department_id=d.department_id
 and e.department_id NOT IN (50,80,100,110);
DEPTNO     DEPARTMENT_NAME      NAMES
---------- -------------------- -----------------------------------------------
10         Administration       Whalen
20         Marketing            Fay
20         Marketing            Fay,Hartstein
30         Purchasing           Baida
30         Purchasing           Baida,Colmenares
30         Purchasing           Baida,Colmenares,Himuro
30         Purchasing           Baida,Colmenares,Himuro,Khoo
30         Purchasing           Baida,Colmenares,Himuro,Khoo,Raphaely
30         Purchasing           Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
40         Human Resources      Mavris
60         IT                   Austin
60         IT                   Austin,Ernst
60         IT                   Austin,Ernst,Hunold
60         IT                   Austin,Ernst,Hunold,Lorentz
60         IT                   Austin,Ernst,Hunold,Lorentz,Pataballa
70         Public Relations     Baer
90         Executive            De Haan
90         Executive            De Haan,King
90         Executive            De Haan,King,Kochhar

19 rows selected.

You combine the STRAGG function with other analytic functions:

 SELECT d.department_id deptno,last_name,salary,
 ROW_NUMBER() OVER(PARTITION BY d.department_id order by e.SALARY DESC ) COUNTER,
 SUM(salary) OVER (PARTITION BY e.department_id ORDER BY salary DESC
 ROWS UNBOUNDED PRECEDING) cum_sum,
 stragg(e.last_name||': '||SALARY)
 OVER(PARTITION BY d.department_id ORDER BY e.salary DESC ROWS  UNBOUNDED PRECEDING ) EMP_DATA
 FROM departments d, employees e
 WHERE e.department_id=d.department_id
 AND  e.department_id NOT IN (50,80,100,110)
 ORDER BY d.department_id;

DEPTNO LAST_NAME   SALARY    COUNTER    CUM_SUM   EMP_DATA
------- ----------- ------ ---------- ----------  ---------------------------------------------------------------------------------------
10      Whalen        4400          1       4400   Whalen: 4400
20      Hartstein    13000          1      13000   Hartstein: 13000
20      Fay           6000          2      19000   Hartstein: 13000,Fay: 6000
30      Raphaely     11000          1      11000   Raphaely: 11000
30      Khoo          3100          2      14100   Raphaely: 11000,Khoo: 3100
30      Baida         2900          3      17000   Raphaely: 11000,Khoo: 3100,Baida: 2900
30      Tobias        2800          4      19800   Raphaely: 11000,Khoo: 3100,Baida: 2900,Tobias: 2800
30      Himuro        2600          5      22400   Raphaely: 11000,Khoo: 3100,Baida: 2900,Tobias: 2800,Himuro: 2600
30      Colmenares    2500          6      24900   Raphaely: 11000,Khoo: 3100,Baida: 2900,Tobias: 2800,Himuro: 2600,Colmenares: 2500
40      Mavris        6500          1       6500   Mavris: 6500
60      Hunold        9000          1       9000   Hunold: 9000
60      Ernst         6000          2      15000   Hunold: 9000,Ernst: 6000
60      Austin        4800          3      19800   Hunold: 9000,Ernst: 6000,Austin: 4800
60      Pataballa     4800          4      24600   Hunold: 9000,Ernst: 6000,Austin: 4800,Pataballa: 4800
60      Lorentz       4200          5      28800   Hunold: 9000,Ernst: 6000,Austin: 4800,Pataballa: 4800,Lorentz: 4200
70      Baer         10000          1      10000   Baer: 10000
90      King         24000          1      24000   King: 24000
90      De Haan      17000          2      41000   King: 24000,De Haan: 17000
90      Kochhar      17000          3      58000   King: 24000,De Haan: 17000,Kochhar: 17000

19 rows selected.

Before 11gR2 -based on Tom Kyte’s solution- we can solve this problem with an hierarchical query.
(http://tkyte.blogspot.co.uk/2007/03/stringing-them-up.html,
The original query caused an Oracle error for me).

The modified query is the following:

SELECT department_id,
COUNT(SUBSTR(sys_connect_by_path(last_name, ',' ),2)) counter,
MAX(SUBSTR(sys_connect_by_path(last_name, ',' ),2)) emp_name
FROM
(SELECT department_id, last_name,
ROW_NUMBER() OVER(PARTITION BY department_id ORDER BY last_name) rn
FROM employees)
WHERE department_id not in (50,80,100,110)
START WITH rn = 1
connect by
PRIOR rn = rn-1 AND PRIOR department_id = department_id
GROUP BY department_id
ORDER BY department_id;
DEPARTMENT_ID    COUNTER EMP_NAME
------------- ---------- -----------------------------------------------
10                     1 Whalen
20                     2 Fay,Hartstein
30                     6 Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
40                     1  Mavris
60                     5 Austin,Ernst,Hunold,Lorentz,Pataballa
70                     1 Baer
90                     3 De Haan,King,Kochhar

7 rows selected.

 

I like the LIKE operator!

January 16, 2013

Let suppose the we want to find specific workers using their names from the employees table.
First we want to list the available indexes for that table:

SELECT i.index_name,c.column_position,c.column_name
FROM USER_INDEXES I, USER_IND_COLUMNS C
WHERE i.index_name=c.index_name AND i.table_name='EMPLOYEES'
ORDER BY i.index_name,c.column_position;

LIKE8

As we can observe that the EMP_NAME_IX is compound index
which consists of two columns:
– LAST_NAME
– FIRST_NAME
in this order.
Now we want to find those people whose name starts with capital ‘K':

SELECT * FROM employees  WHERE  LAST_NAME like 'K%';

like1
We can observe that Oracle used the index with INDEX RANGE SCAN operation which is generally is a good choice.
However, if we add another – similar – predicate into the WHERE clause:

SELECT * FROM employees  WHERE  LAST_NAME LIKE 'K%' OR LAST_NAME LIKE 'L%';

like12

The optimizer has chosen  the FTS (Full Table Scan).
But when we put the indexed columns  into the column list only:

SELECT FIRST_NAME,LAST_NAME FROM employees
WHERE LAST_NAME like 'K%' OR LAST_NAME like 'L%';

like13

The optimizer used the INDEX FULL SCAN operation!
What about if we want to find those people whose name contains a lower ‘k’?

SELECT * FROM employees  WHERE  LAST_NAME LIKE '%k%';

like7

As you can see an FTS (Full Table Scan) was used by the optimizer, because
the the literal was inside the mask (‘%k%’) and all columns were projected.
However, if we are looking for the LAST_NAME first :

SELECT LAST_NAME FROM employees  WHERE  LAST_NAME LIKE '%k%';

like9

we can see that the optimizer used the INDEX FULL SCAN
which is probably faster than the FTS.
Recommendation: If you know only substring of the name,
please use the INDEX FULL SCAN operation,
after – having the full names- you can retrieve all columns using
INDEX RANGE SCAN with ‘literal%’ template.
Observe that if we use the second (not prefixed) column of the index in the WHERE clause and put either the first or other components(only!) in the column list, the optimizer will use an INDEX FULL SCAN operation.

SELECT LAST_NAME FROM employees  WHERE  FIRST_NAME LIKE 'K%';

LIKE10

Generally we can say that using the LIKE operator with well formatted mask and putting the necessary column in the select list we have a good chance for using the suitable index(es).
Please do not use SUBSTR function instead of  LIKE operator even if it is applicable!
(Only if you have a function based index for that expression)

Keep the latest modification’s timestamp for each row

December 19, 2012

It is well known that Oracle stores the latest modification’s timestamp
with SCN (System Change Number) on data block level.
Sometimes we want to keep this information on row level.
To achieve this feature we can use the ROWDEPENDENCIES clause in the CREATE TABLE statement.

DROP TABLE emp2 PURGE;
CREATE TABLE emp2
( employee_id NUMBER(6),
   last_name  VARCHAR2(25),
   dml        VARCHAR(20),
   salary     NUMBER(8,2))  ROWDEPENDENCIES;

With this option Oracle will allocate six bytes for storing the appropriate SCN for each row.
Note, that You can not add this clause later, with an ALTER TABLE statement. However you can use it in CTAS:

CREATE TABLE emp33 AS SELECT * FROM employees ROWDEPENDENCIES;

Let’s populate this table with the following INSERT SELECT statement:

INSERT INTO emp2
SELECT employee_id,  last_name , 'Insert First', salary
FROM employees WHERE salary<3000;
COMMIT;

 

Now check the result with ORA_ROWSCN pseudo column and convert it into timestamp:

SELECT  SCN_TO_TIMESTAMP(ORA_ROWSCN)  TIMESTAMP,
last_name , dml, salary,
TO_CHAR(ORA_ROWSCN,'999,999,999,999,999') ORA_ROWSCN
FROM emp2;

TIMESTAMP                         LAST_NAME   DML                  SALARY ORA_ROWSCN
--------------------------------- ----------- -------------------- ------ --------------------
19-DEC-12 12.11.42.000000000 PM   Oconnell    Insert First           2600           30,401,401
19-DEC-12 12.11.42.000000000 PM   Grant       Insert First           2600           30,401,401
19-DEC-12 12.11.42.000000000 PM   Baida       Insert First           2900           30,401,401
19-DEC-12 12.11.42.000000000 PM   Tobias      Insert First           2800           30,401,401
19-DEC-12 12.11.42.000000000 PM   Himuro      Insert First           2600           30,401,401
19-DEC-12 12.11.42.000000000 PM   Colmenares  Insert First           2500           30,401,401
19-DEC-12 12.11.42.000000000 PM   Mikkilineni Insert First           2700           30,401,401
19-DEC-12 12.11.42.000000000 PM   Landry      Insert First           2400           30,401,401
19-DEC-12 12.11.42.000000000 PM   Markle      Insert First           2200           30,401,401
19-DEC-12 12.11.42.000000000 PM   Atkinson    Insert First           2800           30,401,401
19-DEC-12 12.11.42.000000000 PM   Marlow      Insert First           2500           30,401,401
19-DEC-12 12.11.42.000000000 PM   Olson       Insert First           2100           30,401,401
19-DEC-12 12.11.42.000000000 PM   Rogers      Insert First           2900           30,401,401
19-DEC-12 12.11.42.000000000 PM   Gee         Insert First           2400           30,401,401

We can issue a MERGE statement (introduced in Oracle 9i, enhanced in Oracle 10g)
to modify the content of this table:
– Let’s increase the salary of those people whose salary less than or equal 2500$
– Delete those rows from the target table where the salary greater than 5000$
– Insert those rows from the source whose salary greater than 12000$
Consider the statement:

MERGE INTO emp2  c USING
(SELECT employee_id, last_name, 'x',salary FROM EMPLOYEES  ) e
ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN
 UPDATE SET
c.last_name = UPPER(e.last_name),
 c.dml       = 'Update',
c.salary    = e.salary * 2 WHERE e.salary <= 2500      DELETE WHERE (c.salary>5000)
 WHEN NOT MATCHED THEN
  INSERT VALUES(e.employee_id, e.last_name,'Insert Second', e.salary)
  WHERE e.salary>12000;
COMMIT;

Check the result with the following SELECT statement:

SELECT  SCN_TO_TIMESTAMP(ORA_ROWSCN)  TIMESTAMP,
last_name , dml, salary,
TO_CHAR(ORA_ROWSCN,'999,999,999,999,999') ORA_ROWSCN
FROM emp2;

TIMESTAMP                         LAST_NAME   DML                  SALARY ORA_ROWSCN
--------------------------------- ----------- -------------------- ------ --------------------
19-DEC-12 11.47.55.000000000 AM   Oconnell    Insert First           2600           30,397,931
19-DEC-12 11.47.55.000000000 AM   Grant       Insert First           2600           30,397,931
19-DEC-12 11.47.55.000000000 AM   Baida       Insert First           2900           30,397,931
19-DEC-12 11.47.55.000000000 AM   Tobias      Insert First           2800           30,397,931
19-DEC-12 11.47.55.000000000 AM   Himuro      Insert First           2600           30,397,931
19-DEC-12 11.51.27.000000000 AM   COLMENARES  Update                 5000           30,398,452
19-DEC-12 11.47.55.000000000 AM   Mikkilineni Insert First           2700           30,397,931
19-DEC-12 11.51.27.000000000 AM   LANDRY      Update                 4800           30,398,452
19-DEC-12 11.51.27.000000000 AM   MARKLE      Update                 4400           30,398,452
19-DEC-12 11.47.55.000000000 AM   Atkinson    Insert First           2800           30,397,931
19-DEC-12 11.51.27.000000000 AM   MARLOW      Update                 5000           30,398,452
19-DEC-12 11.51.27.000000000 AM   OLSON       Update                 4200           30,398,452
19-DEC-12 11.47.55.000000000 AM   Rogers      Insert First           2900           30,397,931
19-DEC-12 11.51.27.000000000 AM   GEE         Update                 4800           30,398,452
19-DEC-12 11.51.27.000000000 AM   PHILTANKER  Update                 4400           30,398,452
19-DEC-12 11.47.55.000000000 AM   Seo         Insert First           2700           30,397,931

You can see two the different SCN numbers with two different timestamps accordingly.


Follow

Get every new post delivered to your Inbox.