Convert from milliseconds since epoch to Oracle timestamp

Today I needed to convert from numbers in a log file originally generated using System.currentTimeMillis() in Java to Oracle timestamps and I wanted to do it with PL/SQL, without involving Java in the database. I love Java, but inside Oracle? No thanks.

Surely this is simple and surely the Internet abounds with examples? No and no. It is easy to create a date, but date columns loose precision. I needed to preserve the milliseconds. There are many examples for getting the number of milliseconds since the epoch from an Oracle timestamp, but not for creating a timestamp from a millisecond value.

After some thinking I ended up with the following code:


create or replace function millis_to_timestamp(p_millis in number)
  return timestamp is
  v_datePart date;
  v_millisPart number;
begin
  v_datePart := to_date('19700101', 'YYYYMMDD') + (p_millis / 86400000);
  v_millisPart := mod(p_millis, 1000);
  return to_timestamp(to_char(v_datePart, 'YYYYMMDDHH24MISS') ||
         substr(to_char(v_millisPart + 1000), 2, 3), 'YYYYMMDDHH24MISSFF');
end;

Not that pretty, but it works! Share and enjoy…

Categories: Database, Oracle

Stop Oracle XE from listening on port 8080

A default installation of Oracle Express Edition 11g has a web interface on port 8080 and listens for database connections on port 1521. When the database is used in a development environment this is often less than ideal, as port 8080 tends to clash with other web servers. To disable APEX and get rid of the web interface, set the port number to 0. Connect as a DBA user and run:

BEGIN
  -- Disable the web interface
  DBMS_XDB.SETHTTPPORT(0);
  -- Disable FTP just in case
  DBMS_XDB.SETFTPPORT(0);
END;

There are similarly named functions for getting the current ports as well.

Categories: Database, Oracle

Oracle performance tuning scripts

As a developer I rarely get access to the graphical consoles for Oracle. The DBA:s tend to keep them locked down. It is usually easier to get access to the built-in performance views. Over the years I have gathered a few scripts that help me diagnose performance issues. They are nothing fancy, I’m posting them partly for my own benefit as this makes them easy to find when I need them.

I have used the scripts for a long time, so I don’t remember their origin. A likely source is Tom Kyte’s excellent Effective Oracle by Design, a must read for anyone into Oracle performance tuning.

High-level view

select  wait_class, total_waits,
  round(100 * (total_waits / sum_waits),2) pct_waits,
  time_waited, round(100 * (time_waited / sum_time),2) pct_time
from (select wait_class, total_waits, time_waited
        from v$system_wait_class where wait_class != 'idle'),
     (select sum(total_waits) sum_waits, sum(time_waited) sum_time
        from v$system_wait_class where wait_class != 'idle')
  order by 5 desc;

Top wait events

select h.event "wait event",
       sum(h.wait_time + h.time_waited) "total wait time"
  from v$active_session_history h, v$event_name e
  where h.sample_time between sysdate - 1/24 and sysdate
  and h.event_id = e.event_id
  and e.wait_class  'idle'
  group by h.event
  order by 2 desc;

Table scans

select to_char(sn.begin_interval_time,'dd mon hh24:mi:ss') start_date,
       to_char(sn.end_interval_time,'dd mon hh24:mi:ss') end_date,
       newmem.value-oldmem.value fts
  from dba_hist_sysstat oldmem,
       dba_hist_sysstat newmem,
       dba_hist_snapshot sn
  where sn.snap_id = (select max(snap_id) from dba_hist_snapshot)
    and newmem.snap_id = sn.snap_id
    and oldmem.snap_id = sn.snap_id-1
    and oldmem.stat_name = 'table scans (long tables)'
    and newmem.stat_name = 'table scans (long tables)';

Top SQL

select h.user_id, u.username, sql.sql_text,
       sum(h.wait_time + h.time_waited) "total wait time"
  from v$active_session_history h,
       v$sqlarea sql,
       dba_users u,
       v$event_name e
  where h.sample_time between sysdate - 1/24 and sysdate
    and h.sql_id = sql.sql_id
    and h.user_id = u.user_id
    and h.sql_id is not null
    and e.event_id = h.event_id
    and e.wait_class  'idle'
   group by h.user_id,sql.sql_text, u.username order by 4 desc;

This is usually enough to point me in the right direction. It is even easier with the graphical user interface, but SQL always works!

Categories: Database, Oracle

Java 8 is here – learn how to use the new stuff!

Java 8 has finally been released! Time to try out all the new things. Most of it has been covered before (the previews have been out for some time and there have been talks at JavaOne, JFokus and many other conferences), but personally I found some gold nuggets in the newly released book Java 8 Lambdas: Pragmatic Functional Programming. It covers the basics, but it also includes valuable advice on debugging and unit testing lambda functions and how to use them effectively. Recommended!

Categories: Java

Primefaces 4.0 fails with WAS 8.5.5

The latest community version of Primefaces (4.0) doesn’t work with the JSF version that is bundled with WebSphere AS 8.5.5. There are strange errors, for example some buttons are not rendered. To solve the problem, downgrade to Primefaces 3.5 or bundle another JSF implementation with the web application. We downgraded to 3.5 – problem solved.

Categories: Java

Collecting runtime dependencies with Maven

Maven greatly simplifies Java development as it keeps track of and downloads dependencies. This is great, but what to do when the application is about to be released? The jar files can be copied from the local repository manually, but that is a pain. A much better way is to let Maven collect them:

<plugin>
  <groupId>org.apache.maven.plugins</groupId>
  <artifactId>maven-dependency-plugin</artifactId>
  <version>2.8</version>
  <executions>
    <execution>
      <id>copy-libs</id>
      <phase>prepare-package</phase>
      <configuration>
        <outputDirectory>
          ${project.build.directory}/libs
        </outputDirectory>
        <stripVersion>false</stripVersion>
        <includeScope>runtime</includeScope>
      </configuration>
      <goals>
        <goal>copy-dependencies</goal>
      </goals>
    </execution>
  </executions>
</plugin>

This copies all the transitive dependencies needed at runtime into the target/libs directory.

Categories: Java

JFokus 2014 highlights

JFokus is over for this year, but most sessions are available online (video is coming), including mine.

I particularly liked Navigating the Stream API, as I had already seen the Lambda talks at JavaOne. I also enjoyed Efficient HTTP Apis, covering HTTP/2. Third on my list comes HTML5/Rich Clients Using Java EE 7 by Reza Rahman. We share the same pragmatic and long-term attitude.

Overall it was great fun with many interesting sessions and discussions. Looking forward to next year!

Categories: Java
Follow

Get every new post delivered to your Inbox.