DUMP to HEX

DUMP function gives decimal presentation of a column contents bits.

select ch,dump(ch) dm 
from (
select 'DUMP to DEC' ch 
from dual
)
;

CH DM

DUMP to DEC Typ=96 Len=11: 68,85,77,80,32,116,111,32,68,69,67

Sometimes hexadecimal presentation would be nice

select ch
     , listagg(to_char((column_value).getnumberval(),'XX'))
        within group (order by rownum) hex 
 from (
  select ch,dump(ch) dm 
   from (
    select 'DUMP to HEX' ch 
      from dual
    )
  ) n
  ,xmltable(substr(n.dm,instr(n.dm,':')+2))
;

CH HEX

DUMP to HEX  44 55 4D 50 20 74 6F 20 48 45 58

Or maybe it is easier just to use the documented second parameter of dump function.

select ch,dump(ch,1016) dm 
 from (
  select 'DUMP to HEX' ch 
  from dual
 )
;


DUMP to HEX    Typ=96 Len=11 CharacterSet=AL32UTF8: 44,55,4d,50,20,74,6f,20,48,45,58

Is My Index Used

I am in Manchester. The UKOUG tech13 seminar super Sunday information overload gone. Three days still left. I got finally to meet Kyle Hailey. The man who visualized the ash. Even he and Doug Burns could not find the SQL query that has created the lock in a blocking session. Another a bit similar question that is often asked. Is an index used. You could set up monitoring on an index.

ALTER INDEX index_name_here MONITORING USAGE;

And after a while you can browse USED column of the V$OBJECT_USAGE view.
It tels is your undex been used. But it does not tell if the index is used wisely. ASH to the rescue. Possibly increase the time ash is stored. And here is a query that shows the clauses that have been using the index.

select sql_id,TO_CHAR(substr(sql_text,1,4000)) txt
  from dba_hist_sqltext where sql_id in ( 
  select sql_id 
    from dba_hist_sql_plan 
   where object_name = :index_name)
order by txt
;

If no index usage is noticed. The index is a possible candidate for dropping away. The case in V$OBJECT_USAGE.

But it might be so that the index is used in a non wisely manner. From there on you can interpret the plans. It might be that the full table scan would perform better.

And going to 12c there becomes the adaptivity of a cursor execution that will end up in the plan even it executes better in other execution paths. This was covered yesterday quite nicely by Nicolas Jardot. So for 12c the is the index used query here should be attached to dba_hist_active_sess_history. Future work to be done.

Before dropping consider making the index invisible for awhile.

Tables used

Having a view stack? Trying to find out tables a view is using?

Overview

 select d.referenced_name table_name, count(*) times, sum(count(*))over() tables
   from user_dependencies d 
  where d.referenced_type = 'TABLE'
  start with d.name = :name 
connect by   d.name = prior d.referenced_name 
        and  d.type = prior d.referenced_type
 group by d.referenced_name
 order by d.referenced_name
;

Hierarchy

select level,d.referenced_owner,d.referenced_name table_name, (select num_rows from dba_tables t where t.owner = d.referenced_owner and t.table_name = d.referenced_name) num_rows
  from dba_dependencies d 
 where d.referenced_type = 'TABLE'
 start with d.owner = :owner
        and d.name = :name 
 connect by d.owner = prior d.referenced_owner
        and d.name = prior d.referenced_name 
        and d.type = prior d.referenced_type
;

Not only restricted to views also eg. procedures have dependencies. Just give the root object name as a parameter to the query and you get the dependent tables out.

Counting Character Occurrences in SQL

Which characters and how many times they are used in a rows of a column. Just change following src to a desired query.

with src as (
   SELECT ename col FROM emp
)
SELECT letter
     , COUNT (*) cnt
  FROM (    
 SELECT SUBSTR (col, LEVEL, 1) letter
   FROM (
  SELECT col
       , ROWNUM rn
       , LENGTH (col) len
    FROM src
        )
 CONNECT BY LEVEL <= len
        AND rn = PRIOR rn
        AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
        )
GROUP BY letter
ORDER BY letter
;

This approach is not the most efficient way to do this. But it does the job. For a 10 million rows varchar2(30) column it took about half an hour to calculate the result. Maybe in database map reduce approach could be used to speedup the execution. Something like my euler17 implementation.