Recently Analyzed Table List

Two useful queries to get a list of recently analyzed tables. Can be used to check when the tables were last analyzed. Normally oracle should analyze tables on its own.

SELECT owner,
       SUM (DECODE (NVL (num_rows, 9999999), 9999999, 0, 1)) analyzed,
       SUM (DECODE (NVL (num_rows, 9999999), 9999999, 1, 0)) not_analyzed,
       COUNT (table_name) total
  FROM all_tables
 WHERE owner NOT IN ('SYS', 'SYSTEM')
GROUP BY owner;

SELECT table_name,
       TO_CHAR (last_analyzed, 'MM/DD/YYYY HH24:MI:SS') last_analyzed
  FROM user_tab_columns
 WHERE     last_analyzed IS NOT NULL
       AND column_id = 1
       AND (SYSDATE - last_analyzed) < 30
ORDER BY 2;

Time difference

Query to get the time different between 2 date columns

select floor(((end_time - start_time)*24*60*60)/3600)
       || ' HOURS ' ||
       floor((((end_time - start_time)*24*60*60) -
       floor(((end_time - start_time)*24*60*60)/3600)*3600)/60)
       || ' MINUTES ' ||
       round((((end_time - start_time)*24*60*60) -
       floor(((end_time - start_time)*24*60*60)/3600)*3600 -
       (floor((((end_time - start_time)*24*60*60) -
       floor(((end_time - start_time)*24*60*60)/3600)*3600)/60)*60)))
       || ' SECS ' time_difference
from sub_email_jobs;

Datafile Usage on an Oracle Database

Two useful queries to get datafile usage on an Oracle database

SELECT SUBSTR (df.tablespace_name, 1, 20) "Tablespace Name",
       SUBSTR (df.file_name, 1, 40) "File Name",
       ROUND (df.bytes / 1024 / 1024, 2) "Size (M)",
       ROUND (e.used_bytes / 1024 / 1024, 2) "Used (M)",
       ROUND (f.free_bytes / 1024 / 1024, 2) "Free (M)",
       RPAD (' ' || RPAD ('X', ROUND (e.used_bytes * 10 / df.bytes, 0), 'X'),
             11,
             '-')
           "% Used"
  FROM dba_data_files df,
       (SELECT file_id, SUM (DECODE (bytes, NULL, 0, bytes)) used_bytes
          FROM dba_extents
        GROUP BY file_id) e,
       (SELECT MAX (bytes) free_bytes, file_id
          FROM dba_free_space
        GROUP BY file_id) f
 WHERE e.file_id = df.file_id AND df.file_id = f.file_id
ORDER BY df.tablespace_name, df.file_name;

SELECT SUBSTR (df.name, 1, 40) file_name,
       df.bytes / 1024 / 1024 allocated_mb,
       ( (df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
           used_mb,
       NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
  FROM v$datafile df, dba_free_space dfs
 WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id,
         df.name,
         df.file#,
         df.bytes
ORDER BY file_name;

Query to get a list of tables without any indexes

SELECT table_name
  FROM (SELECT table_name FROM user_tables
        MINUS
        SELECT table_name FROM user_indexes) orasnap_noindex
 WHERE table_name LIKE 'HSO%'
ORDER BY table_name

Updating a table based on rows from another table

Example One:

UPDATE a
   SET data =
           (SELECT data
              FROM b
             WHERE id1 = a.id1 AND id2 = a.id2)
 WHERE EXISTS
           (SELECT 1
              FROM b
             WHERE id1 = a.id1 AND id2 = a.id2);

Example two, can be used for multiple columns

UPDATE (SELECT a.data a_data, b.data b_data
          FROM a, b
         WHERE a.id1 = b.id1 AND a.id2 = b.id2)
   SET a_data = b_data;

Queries to delete duplicates in a table

Three useful queries that I use to delete duplicate rows from tables

DELETE FROM customers
 WHERE id IN (SELECT id
                FROM (SELECT id,
                             lastname,
                             firstname,
                             RANK ()
                             OVER (PARTITION BY lastname, firstname
                                   ORDER BY id)
                                 AS seqnumber
                        FROM customers)
               WHERE seqnumber > 1);

DELETE FROM table_name
 WHERE ROWID NOT IN 
 (SELECT MAX (ROWID) 
    FROM TABLE 
    GROUP BY duplicate_values_field_name);

SELECT *
  FROM moon.sub_globals
 WHERE ROWID IN (SELECT ROWID
                   FROM (SELECT ROWID,
                                subscriber_id,
                                sub_member_id,
                                RANK ()
                                OVER (
                                    PARTITION BY subscriber_id, sub_member_id
                                    ORDER BY ROWID)
                                    AS seqnumber
                           FROM moon.sub_globals) a
                  WHERE seqnumber > 1)

Problem with date format in JDBC session

The other day I came across this issue and spent some time figuring out what was going on. Hope it can help someone.

Issue:
Say there’s a stored procedure in Oracle named test_date which uses a variable of type DATE. The variable is not returned to java but simply used elsewhere in the procedure.
If I execute this procedure in oracle and check the date format, I see it’s using the format dd-mon-yyyy

If I execute this procedure over jdbc, the format I see is mm-dd-yyyy

This makes me believe that the date format used throughout the session is defined by the client (oracle vs. jdbc).

Fix:
Simple way to fix this is to alter the session at runtime and explicitly define the format used throughout the session
alter session set nls_date_format=''DD-MON-YYYY''