Collected posts: Oracle, SQL, PL/SQL, Performance, Security...(More than 300 blogs)

dimanche 1 février 2015

How to get Oracle system change number (SCN) ?




The SYSTEM CHANGE NUMBER = A stamp that defines a committed version of a database at a point in time. Oracle assigns every committed transaction a unique SCN.
USER_A@MYDB 01022015 09:24:45> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
    2544927


USER_A@MYDB 01022015 09:24:46> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 2544927


The User_A should have the privileges execute on DBMS_FLASHBACK and SELECT on V_$DATABASE




mercredi 19 novembre 2014

Getting the difference between Dates



To get the difference between  two dates;
The below function return the interval time between two dates:

create or replace function 
get_elapsed (pstart in date , pend in date )
 return interval day to second
as
begin
    return (cast(pend as timestamp)- cast(pstart as timestamp))  DAY TO SECOND;
end ;



SELECT GET_ELAPSED(
to_date('19/11/2014 12:10:20', 'DD/MM/YYYY HH24:MI:SS'), to_date('19/11/2014 13:10:20', 'DD/MM/YYYY HH24:MI:SS') 
)  ELAPSED
 FROM DUAL;

ELAPSED:
+000000000 01:00:00

We can extract hour :

SELECT EXTRACT(hour from GET_ELAPSED(to_date('19/11/2014 12:10:20',
                                   'DD/MM/YYYY HH24:MI:SS'),
                           to_date('19/11/2014 13:10:20',
                                   'DD/MM/YYYY HH24:MI:SS'))) HOUR
 
FROM DUAL;

HOUR:
1

We can extract hours,minutes and seconds 



SELECT
   GET_ELAPSED(to_date('19/11/2014 12:10:20',
                                   'DD/MM/YYYY HH24:MI:SS'),
                           to_date('19/11/2014 13:00:00',
                                   'DD/MM/YYYY HH24:MI:SS') ) ELAPSED,
 
   EXTRACT(HOUR
FROM GET_ELAPSED(to_date('19/11/2014 12:10:20',
                                   'DD/MM/YYYY HH24:MI:SS'),
                           to_date('19/11/2014 13:00:00',
                                   'DD/MM/YYYY HH24:MI:SS')) ) HOUR,
             EXTRACT(MINUTE
FROM GET_ELAPSED(to_date('19/11/2014 12:10:20',
                                   'DD/MM/YYYY HH24:MI:SS'),
                           to_date('19/11/2014 13:00:00',
                                   'DD/MM/YYYY HH24:MI:SS')) ) MINUTE,      
           EXTRACT(
SECOND FROM GET_ELAPSED(to_date('19/11/2014 12:10:20',
                                   'DD/MM/YYYY HH24:MI:SS'),
                           to_date('19/11/2014 13:00:00',
                                   'DD/MM/YYYY HH24:MI:SS')) )
SECOND                                          
 
FROM DUAL;



    ELAPSED                 HOUR MINUTE SECOND
+000000000 00:49:40 0 49 40


You can refer to Database SQL Language Reference to get more details about Interval type  and extract function;