How to find which datafiles can be shrinked

The last time I was struggling to find available space in an Oracle database I run into this article from AskTom.

I copy the script in here so that I can have it ready for the next time I'll need it. Note, that the script identifies the limit up to which each datafile can be shrinked to and produces all necessary ALTER statements required to shrink each one of them.

set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings  format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report

column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/

select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) -
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
/

column cmd format a75 word_wrapped

select 'alter database datafile '''||file_name||''' resize ' ||
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )  || 'm;' cmd
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
  and ceil( blocks*&&blksize/1024/1024) -
      ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/

How to retrieve and use Oracle TIMESTAMPs in Java

A few rules about using TIMESTAMPs in Java:

1. Never make assumptions about things like the timezone in the database and/or the application server.
2. It is preferable to retrieve the TIMESTAMP (or Date) field from the database as a string using the appropriate format. You can then easily convert it to java.sql.Timestamp and use it in Java.
• If you have to compare values that are stored in the database, it is better to do it in your SELECT statement and return something appropriate (i.e. time difference) to Java.
• If you must return something to use it for a comparison in Java, then prefer to return the TIMESTAMP after you have ensured that this is converted to GMT (UTC) time.

How to do it:

• Retrieve the GMT value of a TIMESTAMP as a string that can be converted to java.sql.Timestamp. In the following example change_tm is a Timestamp filed. We are using SYS_EXTRACT_UTC to convert it to GMT.

SELECT 
     TO_CHAR(CHANGE_TM, 'yyyy-mm-dd hh24:mi:ss') CHANGE_TM,
     TO_CHAR(SYS_EXTRACT_UTC(CHANGE_TM), 'yyyy-mm-dd hh24:mi:ss') GMT_CHANGE_TM
FROM TIME_TBL
WHERE  ID = :b1;



• Convert the selected value to a java.sql.Timestamp by simply calling Timestamp.valueof().
• Compare the selected value with the current time of the application server using a Calendar. The Calendar will provide useful information like the offset from GMT.

     Calendar nowCal =GregorianCalendar.getInstance();
     System.out.println("Date now: " + nowCal.get(Calendar.YEAR) + "/" +
                         nowCal.get(Calendar.MONTH) + "/" +
                         nowCal.get(Calendar.DAY_OF_MONTH) + " " +
                         nowCal.get(Calendar.HOUR_OF_DAY) + ":" +
                         nowCal.get(Calendar.MINUTE) + ":" +
                         nowCal.get(Calendar.SECOND) +
                         " Offset from GMT " +
                         nowCal.get(Calendar.ZONE_OFFSET) +
                         " Timezone: " +
                         nowCal.getTimeZone().getDisplayName());

     long now = nowCal.getTimeInMillis();

     System.out.println("Diff before now and last change time: " +
                    ((now - rec.getGmtChangeTm().getTime() - nowCal.get(Calendar.ZONE_OFFSET)) / 1000 / 60) +
                    " minutes");