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");

How to calculate the size of tables and indexes

I have bookmarked these two posts from AskTom:

1. How to calculate the actual size of a table, and
2. dbms_space.free_space

Who's blocking who on what

The following queries are based on this excellent post.
 
/* Who's blocking who */
SELECT s1.username ||
       '@' ||
       s1.machine ||
       ' ( SID=' ||
       s1.SID ||
       ' )  is blocking ' ||
       s2.username ||
       '@' ||
       s2.machine ||
       ' ( SID=' ||
       s2.SID ||
       ' ) ' AS blocking_status
FROM   v$lock l1, v$session s1, v$lock l2, v$session s2
WHERE  s1.SID = l1.SID
AND    s2.SID = l2.SID
AND    l1.BLOCK = 1
AND    l2.request > 0
AND    l1.id1 = l2.id1
AND    l2.id2 = l2.id2;


/* Which object and row is blocked */
SELECT SID, DO.object_name, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
       DBMS_ROWID.rowid_create (1, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#)
FROM   v$session s, dba_objects DO
WHERE  s.row_wait_obj# = DO.object_id
AND    SID IN (
           SELECT DISTINCT s2.SID
           FROM            v$lock l1, v$session s1, v$lock l2, v$session s2
           WHERE           s1.SID = l1.SID
           AND             s2.SID = l2.SID
           AND             l1.BLOCK = 1
           AND             l2.request > 0
           AND             l1.id1 = l2.id1
           AND             l2.id2 = l2.id2);

How to get the stacktrace when an exception is raised in PL/SQL (use of DBMS_UTILITY.FORMAT_ERROR_BACKTRACE)

This article describes the use of function DBMS_UTILITY.FORMAT_ERROR_BACKTRACE which can be used to identify the exact line where an exception has occurred.

Note that the output of this function does not contain the actual error.

Therefore, when you are trying to log the details of the error, you have to log both sqlerrm and the output of this function to get the complete picture.

Example:

create or replace package body my_pack as
   procedure a as
   begin
      -- DO something that throws an exception
      ...
   end;

   procedure b as
   begin
      -- Call procedure a
      a;
   exception
   when others then
       -- Procedure a has thrown an exception
       DBMS_OUTPUT.PUT_LINE('An exception has occurred: ' || substr(sqlerrm, 1, 512));
       DBMS_OUTPUT.PUT_LINE('The trace of the error was: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
   end;
end;
/

Leap Year Check

This is a quick SQL statement to check if a year is a leap year:

SELECT :YEAR,
       DECODE (MOD (:YEAR, 4), 0, DECODE (MOD (:YEAR, 400), 0, 1, DECODE (MOD (:YEAR, 100), 0, 0, 1)), 0) AS leap_year
FROM   DUAL;

The above SQL returns 1 if :YEAR is a leap year. Else, returns 0.

How to use a flat file as an Oracle External Table

In this example, we define an External Table that corresponds to a fixed-length flat file.

A much better description can be found here.

The file must be present in an Oracle Directory.

The file is mapped to a table with a command similar to the following:

CREATE TABLE my_external_table
(
   field1    VARCHAR2(8)    ,
   field2    VARCHAR2(8)    ,
   field3    VARCHAR2(6) ,
   .....
   fieldn   VARCHAR2(40)
)
ORGANIZATION EXTERNAL 
   (
   TYPE oracle_loader
   DEFAULT DIRECTORY my_external_dir
   ACCESS PARAMETERS
      ( RECORDS DELIMITED BY NEWLINE
        FIELDS
        (
        FIELD1     POSITION(1:8) ,
        FIELD2     POSITION(9:16),
        FIELD3     POSITION(17:22),
        .....
        FIELDN    POSITION(1457:1496)
       )
     )
     LOCATION ('my_flat_file.dat')
  )
REJECT LIMIT UNLIMITED;

Once the table has been created, then it is possible to perform queries as with any normal table:

select *
from my_external_table
where rownum < 10;

Note that the ACCESS PARAMETERS section can contain any valid sql*loader statement.

... and how to get all letters from a word

The function provided below returns the letters in a single word along with their position in the word:

CREATE OR REPLACE TYPE     TYP_WORD_LETTER AS OBJECT
(
  letter VARCHAR2(2),
  position  number(2)
)
/

CREATE OR REPLACE TYPE TYP_WORD_LETTER_COL
AS TABLE OF TYP_WORD_LETTER;
/

CREATE OR REPLACE FUNCTION get_word_letters (v_word VARCHAR2)
   RETURN typ_word_letter_col PIPELINED
AS
   ret_val   NUMBER;
   aletter   VARCHAR2 (2);
   pos       PLS_INTEGER  := 1;
BEGIN
   LOOP
      aletter := SUBSTR (v_word, pos, 1);
      PIPE ROW (NEW typ_word_letter (aletter, pos));
      pos := pos + 1;
      EXIT WHEN pos > LENGTH (v_word);
   END LOOP;

   RETURN;
END;
/

How to break a phrase into words in Oracle

This is a technique to get the individual words in a phrase.
It is based on the use of:

• PIPELINED functions, and
• Regular Expressions
  

First we need a object type and a corresponding collection (to be used by the PIPELINED function).

CREATE OR REPLACE TYPE     typ_word AS OBJECT
(
  text VARCHAR2(64)
)
/

CREATE OR REPLACE TYPE TYP_word_COL
AS TABLE OF TYP_word;
/

The function itself:

CREATE OR REPLACE FUNCTION get_words (v_sentence VARCHAR2)
   RETURN typ_word_col PIPELINED
AS
   occurence   PLS_INTEGER   := 1;
   aword       VARCHAR2 (64);
BEGIN
   LOOP
      aword := REGEXP_SUBSTR (v_sentence, '\w+', 1, occurence);

      IF aword IS NULL
      THEN
         RETURN;
      END IF;

      PIPE ROW (NEW typ_word(aword));
      occurence := occurence + 1;
   END LOOP;
END;
/

How to calculate time difference from TIMESTAMPs

The following script reads a table containing user actions and calculates the time difference (in hours) between the first and last action per user and work day.

The field action_tm is of type TIMESTAMP.

SELECT TRUNC (action_tm) work_day, user_nm,
       MIN (action_tm) start_time,
       MAX (action_tm) end_time,
       ROUND (((TO_CHAR (MAX (action_tm), 'SSSSS') + TO_CHAR (MAX (action_tm), 'FF') * .000001) -
               (TO_CHAR (MIN (action_tm), 'SSSSS') + TO_CHAR (MIN (action_tm), 'FF') * .000001)) /60/60,2) work_hours
FROM user_actions a
GROUP BY TRUNC (action_tm), user_nm
ORDER BY TRUNC (action_tm);

How to encrypt/decrypt a field in Oracle

Oracle provides package DBMS_OBFUSCATION_TOOLKIT which can be used for encrypting/decrypting database fields.

There are a couple of things to remember about this package though:

1. The package provides PROCEDURES to perform encryption/decryption. This alone may not be very useful if you need to use plain SQL. So I provide two functions to wrap these procedures (see below).
2. The length of both the value to be encrypted and the encryption key must be exact multiples of 8. Therefore you need to pad the value with somethig (i.e blanks) to reach the desired length.
   

These are the functions:

CREATE OR REPLACE function encrypt_val(
   input_string       VARCHAR2,
   key_string         VARCHAR2 ) return varchar2 as
   encrypted_string   VARCHAR2 (2048);
BEGIN
   DBMS_OBFUSCATION_TOOLKIT.desencrypt (input_string => input_string, key_string => key_string,
                                        encrypted_string => encrypted_string);
   --DBMS_OUTPUT.put_line ('encrypted hex value : ' || RAWTOHEX (UTL_RAW.cast_to_raw (encrypted_string)));
   return encrypted_string;
END;
/

CREATE OR REPLACE FUNCTION decrypt_val (encrypted_string VARCHAR2, key_string VARCHAR2)
   RETURN VARCHAR2
AS
   decrypted_string   VARCHAR2 (2048);
BEGIN
   DBMS_OBFUSCATION_TOOLKIT.desdecrypt (input_string => encrypted_string, key_string => key_string,
                                        decrypted_string => decrypted_string);
--   DBMS_OUTPUT.put_line ('decrypted string output : ' || decrypted_string);
   RETURN decrypted_string;
END;
/

Example:

The following statement reads a table containing users and encrypts the username (i.e. in order to generate a default password). The actual value to be encrypted is the user name right padded with blanks so that the total length is a multiple of 8.

SELECT user_nm val,
       encrypt_val (RPAD (user_nm, 8 * (FLOOR (LENGTH (user_nm) / 8) + 1)), 'mykey678') enc_val
FROM   ref_user;
        
To retrieve the decrypted value, you need to both decrypt and TRIM:

SELECT val, enc_val, TRIM (decrypt_val (enc_val, 'mykey678')) dec_val
FROM   (
        SELECT user_nm val,
               encrypt_val (RPAD (user_nm, 8 * (FLOOR (LENGTH (user_nm) / 8) + 1)), 'mykey678') enc_val
        FROM   ref_user       
        );

How to make indexes UNUSABLE

The following sample procedure makes indexes UNUSABLE.

This can be helpful when trying to load many thousands or millions of records in a table and you want to avoid index maintenance in order to speed up the loading process.

Make sure to set SKIP_UNUSABLE_INDEXES to TRUE before trying to insert into any table with UNUSABLE indexes. This can be done by including the following statement in your PL/SQL code:

EXECUTE IMMEDIATE 'ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE';

This procedure can handle both partitioned and non-partitioned indexes.

   PROCEDURE set_indexes_unusable
   AS
      idxs       pkg_mig_exec.vtable;
      tot_idxs   PLS_INTEGER;
      stmnt      VARCHAR2 (256);
   BEGIN
      SELECT 'alter index ' || owner || '.' || index_name || ' unusable'
      BULK COLLECT INTO idxs
      FROM   all_indexes
      WHERE  owner = 'MY_SCHEMA'
      AND    table_name IN
                  ('TABLE1', 'TABLE2', 'TABLE3')
      AND    partitioned = 'NO'
      AND    uniqueness <> 'UNIQUE';

      tot_idxs := SQL%ROWCOUNT;

      FOR i IN 1 .. tot_idxs
      LOOP
         pkg_mig_utils.log_info ('Executing: ' || idxs (i));

         EXECUTE IMMEDIATE idxs (i);
      END LOOP;

-- Handle partitioned indexes

      SELECT index_owner || '.' || index_name
      BULK COLLECT INTO idxs
      FROM   all_ind_partitions
      WHERE  index_owner = 'MY_SCHEMA' AND partition_name = 'PARTITION_A';

      tot_idxs := SQL%ROWCOUNT;

      FOR i IN 1 .. tot_idxs
      LOOP
         stmnt :=
            'alter index ' || idxs (i)
            || ' modify partition PARTITION_A unusable';
         pkg_mig_utils.log_info ('Executing : ' || stmnt);

         EXECUTE IMMEDIATE stmnt;
      END LOOP;
   END;

Unusable indexes must be rebuilt in order to become usable again.

PROCEDURE rebuild_unusable_indexes
   AS
      idxs       stmnttable;
      tot_idxs   PLS_INTEGER;
      stmnt      VARCHAR2 (1024);
   BEGIN
      SELECT 'alter index ' || owner || '.' || index_name || ' rebuild'
      BULK COLLECT INTO idxs
      FROM   all_indexes
      WHERE  owner = 'MY_SCHEMA'
      AND    status = 'UNUSABLE'
      AND    partitioned = 'NO';

      tot_idxs := SQL%ROWCOUNT;

      FOR i IN 1 .. tot_idxs
      LOOP
         pkg_mig_utils.log_info ('Executing: ' || idxs (i));

         EXECUTE IMMEDIATE idxs (i);
      END LOOP;

      SELECT 'ALTER INDEX ' || index_owner || '.' || index_name || ' REBUILD PARTITION ' || partition_name
      BULK COLLECT INTO idxs
      FROM   all_ind_partitions a
      WHERE  index_owner = 'MY_SCHEMA'
      AND    partition_name = 'PARTITION_A'
      AND    status = 'UNUSABLE';

      tot_idxs := SQL%ROWCOUNT;

      FOR i IN 1 .. tot_idxs
      LOOP
         pkg_mig_utils.log_info ('Executing: ' || idxs (i));

         EXECUTE IMMEDIATE idxs (i);
      END LOOP;
   END;

How to estimate table stats (table only)

DBMS_STATS.gather_table_stats (
        ownname => SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA'),
        tabname => '',
        estimate_percent => SYS.DBMS_STATS.auto_sample_size,
        method_opt => 'FOR ALL COLUMNS SIZE 1 ',
        DEGREE => NULL, CASCADE => FALSE,
        no_invalidate => FALSE);

Script to rebuild all indexes

DECLARE
   v_tbl_nm    VARCHAR2 (32) := '';
--
   TYPE oranametable IS TABLE OF VARCHAR2 (32);

   indxs       oranametable;
   tot_indxs   PLS_INTEGER;
BEGIN
   SELECT index_name
   BULK COLLECT INTO indxs
   FROM   all_indexes a
   WHERE  a.owner = SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA')
   AND    a.table_name = v_tbl_nm;

   tot_indxs := SQL%ROWCOUNT;

   FOR i IN 1 .. tot_indxs
   LOOP
      EXECUTE IMMEDIATE 'alter index ' || indxs (i) || ' rebuild';
   END LOOP;
END;

How (and when) to use PIPELINED PL/SQL functions in Oracle

There have been several cases where I needed to return a list of records after performing complex processing in Oracle.

I found out that the preferred method to do it is by using PIPELINED PL/SQL functions.

The last time I have done this was a few weeks ago where I had to “dig” into a log of user actions and extract information about user’s activity on a daily basis.

Other cases where I have used the same technique in the past were involving:

• Complex validations and return of validation results (warnings, errors, etc.).
• Tree walking and joining with other tables.

In all these cases I needed to query several tables and use temporary tables to collect intermediate results.

An example of the use of PIPELINED functions can be found here.

Here I summarize the steps must be taken when I have to implement something similar.

1.       Create an Object Type with the necessary fields.

CREATE OR REPLACE TYPE TYP_DAILY_USER_TOTALS AS OBJECT (
   user_nm            VARCHAR2 (16),
   act_dt             DATE,
   tot_proc           NUMBER (6),
   tot_in_progress    NUMBER (6),
   tot_cancelled      NUMBER (6),
   tot_failed         NUMBER (6),
   tot_compl          NUMBER (6),
   avg_assign_tm      NUMBER (6),
   median_assign_tm   NUMBER (6),
   start_tm           DATE,
   end_tm             DATE
);

2.       Create a collection

CREATE OR REPLACE TYPE TYP_DAILY_USER_TOTALS_COL AS TABLE OF typ_daily_user_totals;

3.       Implement a function returning a collection

CREATE OR REPLACE FUNCTION get_daily_user_totals(v_user_nm varchar2)
   RETURN typ_daily_user_totals_col PIPELINED
AS
   PRAGMA AUTONOMOUS_TRANSACTION;

   TYPE daily_totals_rec IS RECORD (
      act_dt             DATE,
      tot_proc           NUMBER (6),
      tot_in_progress    NUMBER (6),
      tot_cancelled      NUMBER (6),
      tot_failed         NUMBER (6),
      tot_compl          NUMBER (6),
      avg_assign_tm      NUMBER (6),
      median_assign_tm   NUMBER (6),
      start_tm           DATE,
      end_tm             DATE
   );

   TYPE daily_totals_tbl IS TABLE OF daily_totals_rec;
   daily_totals   daily_totals_tbl;
BEGIN

/*
   Do whatever is needed to collect your results, i.e. in a global temporary table
*/
 
   . . . . . . .

/*
   Collect your results in a PL/SQL table
*/
 
   SELECT   act_dt,
            tot_proc,
            tot_in_progress,
            tot_cancelled,
            tot_failed,
            tot_compl,
            avg_assign_tm,
            median_assign_tm,
            start_tm,
            end_tm
   BULK COLLECT INTO daily_totals
   FROM  g_daily_user_totals;

/*
   Commit or rollback to clean up the temporary tables and close the autonomous transaction
*/

   ROLLBACK;

/*
   Loop to pipe collected results
*/

   FOR i IN 1 .. daily_totals.COUNT ()
   LOOP

      PIPE ROW (NEW typ_daily_user_totals (
           v_user_nm,
           daily_totals (i).act_dt,
           daily_totals (i).tot_proc,
           daily_totals (i).tot_in_progress,
           daily_totals (i).tot_cancelled,
           daily_totals (i).tot_failed,
           daily_totals (i).tot_compl,
           daily_totals (i).avg_assign_tm,
           daily_totals (i).median_assign_tm,
           daily_totals (i).start_tm,
           daily_totals (i).end_tm));
   END LOOP;

   RETURN;
END;


4.       Select from the function

    SELECT user_nm, act_dt, tot_proc, tot_in_progress, tot_cancelled, tot_failed, tot_compl,
           avg_assign_tm, median_assign_tm, start_tm, end_tm
    FROM   TABLE (get_daily_user_totals(:aUser));

Some notes on the above:

1. If it is necessary to write into any table (as in the example above), then we must declare our function to work as an AUTONOMOUS TRANSACTION (see PRAGMA AUTONOMOUS_TRANSACTION).
2. The TABLE resulting from the select statement above can be joined with other tables and, possibly, create a view.
3. There have been cases where I have used a view (to make life of Java developers easier). In this case, if our PIPELINED function requires a parameter, then this can be passed via a context (using SYS_CONTEXT). I do not have an example ready for this, but I remember an article in Oracle Magazine describing this technique.

PL/SQL code to validate a Greek VAT Number (ΑΦΜ)

FUNCTION check_afm (v_afm VARCHAR2)
      RETURN PLS_INTEGER
   AS
      resultvalid   PLS_INTEGER := 0;
      l_digit       PLS_INTEGER;
      s             PLS_INTEGER := 0;
      m             PLS_INTEGER;
   BEGIN
      IF LENGTH (v_afm) != 9
      THEN
         RETURN 0;
      END IF;

      IF v_afm = '000000000'
      THEN
         RETURN 0;
      END IF;

      IF    REGEXP_SUBSTR (v_afm, '\d{9}') IS NULL
         OR REGEXP_SUBSTR (v_afm, '\d{9}') != v_afm
      THEN
         RETURN 0;
      END IF;

      FOR i IN 1 .. 8
      LOOP
         l_digit := TO_NUMBER (SUBSTR (v_afm, i, 1));
--        DBMS_OUTPUT.PUT_LINE ( 'l_digit = ' || l_digit );
         s := s + l_digit * POWER (2, 9 - i);
--        DBMS_OUTPUT.PUT_LINE('s = ' || s );
      END LOOP;

      m := MOD (s, 11);

--    DBMS_OUTPUT.PUT_LINE('s = ' || s );
      IF (m = 10)
      THEN
         m := 0;
      END IF;

      IF m = ASCII (SUBSTR (v_afm, 9, 1)) - 48
      THEN
         RETURN 1;
      END IF;

      RETURN 0;
   END;

Prevent users from hitting a submit button twice in a JSF application

I had this small JSF application and I was trying to implement something to prevent users from hitting a submit button twice.

Initially I tried to use Javascript to disable the button (from the onclick of the button).

However, when the button was disabled, the form was not submitted.

I found this from IBM and used it with a small change (added a “return” in the onclick event).

The overall concept is to implement a javascript function that detects that the submit button has been already clicked and call it from the onclick event of the form.

This is the javascript code:

<script>
var requestSubmitted = false;
       function submitRequest() {
           if (!requestSubmitted ) {
                     requestSubmitted  = true;
                    return true;
                }

              alert('Already submitted...')
              return false;
        }
</script>


And this is how it is invoked from the form :

<h:form id="searchForm" onsubmit="javascript:return submitRequest();">

Of course the button is not disabled, but at least the form is not being re-submitted.

How to calculate factorial in Oracle

I found this script to calculate the factorial of number n:

---

SELECT ROUND (EXP (SUM (LN (n))))
FROM (
      SELECT LEVEL AS n
      FROM DUAL
      CONNECT BY LEVEL <= :n);

---

Unfortunately, I cannot find the blog/forum where this was originally posted.

There are other implementations, but I found that this one is executing faster.