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; 

A Database Limits

• Datatype Limits 
• Physical Database Limits 
• Logical Database Limits 
• Process and Runtime Limits

Coskan Gundogar = Plan stability through Upgrade to 11G-Why is my plan changed?-Auto Adjusted Dynamic Sampling

Plan stability through Upgrade to 11G-Why is my plan changed?-Auto Adjusted Dynamic Sampling

SQL Patch: Another way to change the plan without changing the code

SQL Patch: Another way to change the plan without changing the code

Indexes: Structure, Splits and Free Space Management Internals from Christian Antognini (OOW14)

Indexes: Structure, Splits and Free Space Management Internals from Christian Antognini

Adaptive Query Optimization from Christian Antognini (OOW14)

Adaptive Query Optimization from Christian Antognini

Everything That Is Really Useful in Oracle Database 12c for Application Developers (OOW 2014, CON 2394

Everything That Is Really Useful in Oracle Database 12c for Application Developers (OOW 2014, CON 2394) from Lucas Jellema

PERL = DBI::db = disconnect invalidates 1 active statement handle (either destroy statement handles or call finish on them before disconnecting)

1. $dbh = DBI->connect("DBI:ODBC:$dsn",$user,$password)
2. my $statement=$dbh->prepare(q{select count(*) Total from tab;});
3. $statement->execute() or  die "executing: ", $dbh->errstr;
4. my $rowq = $statement->fetchrow_arrayref->[0];
5.  
6. $dbh->disconnect;

DBI::db=HASH(0x1d56afc)->disconnect invalidates 1 active statement handle (either destroy statement handles or call finish on them before disconnectin
g) at chb.pl line 6.


to avoid this you should add  $statement->finish();

1. $dbh = DBI->connect("DBI:ODBC:$dsn",$user,$password)
2. my $statement=$dbh->prepare(q{select count(*) Total from tab;});
3. $statement->execute() or  die "executing: ", $dbh->errstr;
4. my $rowq = $statement->fetchrow_arrayref->[0];
5.  
6. $statement->finish();
7. $dbh->disconnect;

Maximizing Database Performance: Performance Tuning with DB Time

DB-Engines Ranking : The 10 Most Popular DB Engines

Read more about the method of calculating the scores and trend popularity: http://db-engines.com/en/

ORA-00600: internal error code, arguments: [kcratr_scan_lastbwr]

ORA-00600: internal error code, arguments: [kcratr_scan_lastbwr]

KAREN MORTON : Understanding Oracle Optimizer Statistics

Understanding Oracle Optimizer Statistics

What's the Difference Between Oracle Transparent Data Encryption, Data Masking and Data Redaction?

Read ==>

By Troy Kitch on Jul 17, 2014

Oracle Database 12c on Oracle Linux 6 by Rich Soule

Here is a collection of posts on installing (a) Virtual Box (b) Oracle Enterprise Linux 6 (c) 12c Grid Infrastructure (Standalone, non-Clustered) and ASM (d) 12c Database with CDB and PDB.

Upgrade/Migrate/Consolidate to Oracle Database 12c

https://blogs.oracle.com/UPGRADE/entry/new_slides_workshop_recap_br
https://apex.oracle.com/pls/apex/f?p=202202:2:::::P2_SUCHWORT:migrate12c

Oracle Reserved words check use

Queries :

SELECT AO.OWNER, AO.OBJECT_NAME, AO.OBJECT_TYPE
  FROM ALL_OBJECTS AO,          OSRW
 WHERE AO.OBJECT_NAME = OSRW.KEYWORD
   AND AO.OWNER NOT IN ('SYS', 'PUBLIC', 'SYSMAN', 'XDB', 'CTXSYS', 'SYSTEM',
        'WMSYS', 'OWBSYS', 'ORDSYS', 'ORDDATA', 'OUTLN',
        'OLAPSYS', 'MDSYS', 'APEX_030200')
 ORDER BY 1, 2;

SELECT ATC.OWNER, ATC.TABLE_NAME, ATC.COLUMN_NAME
  FROM ALL_TAB_COLUMNS ATC, V$RESERVED_WORDS OSRW
 WHERE ATC.COLUMN_NAME = OSRW.KEYWORD
   AND ATC.OWNER NOT IN ('SYS', 'PUBLIC', 'SYSMAN', 'XDB', 'CTXSYS',
        'SYSTEM', 'WMSYS', 'OWBSYS', 'ORDSYS', 'ORDDA
TA', 'OUTLN', 'OLAPSYS', 'MDSYS', 'APEX_030200')
 ORDER BY 1, 2;

Script: Oracle hidden parameters

SELECT ksppinm  AS NAME,
       ksppity  AS TYPE,
       ksppstvl AS VALUE,
       ksppstdf AS ISDEFAULT
  FROM x$ksppi x
 INNER JOIN x$ksppcv y ON (x.indx = y.indx)