Put together JDBC URL for thin driver

An Oracle database server does not know the port the listener listens on, so we cannot retrieve it from the Oracle instance.

with BASE
     as (select sys_context('USERENV', 'DB_NAME') as DB_NAME
              , sys_context('USERENV', 'DB_UNIQUE_NAME') as DB_UNIQUE_NAME
              , sys_context('USERENV', 'DB_DOMAIN') as DB_DOMAIN
              , sys_context('USERENV', 'INSTANCE_NAME') as INSTANCE_NAME
              , sys_context('USERENV', 'ORACLE_HOME') as ORACLE_HOME
              , sys_context('USERENV', 'SERVER_HOST') as SERVER_HOST
              , sys_context('USERENV', 'SERVICE_NAME') as SERVICE_NAME
              , sys_context('USERENV', 'SESSION_USER') as SESSION_USER
           from dual)
select BASE.*
     , 'jdbc:oracle:thin:@' || SERVER_HOST || ':<port>:' || DB_NAME
          as JDBC_CONNECT_STRING_SID
     , 'jdbc:oracle:thin:@//' || SERVER_HOST || '<port>/' || SERVICE_NAME
          as JDBC_CONNECT_STRING_SERVICE
  from BASE;

You can look in the file

${ORACLEHOME}/Network/Admin/tnsnames.ora

of your local Oracle Home. If there is nothing in, you probably use LDAP. You check that in

${ORACLEHOME}/Network/Admin/sqlnet.ora

. There is DBMS_LDAP to access LDAP services from within PL/SQL. However, I do not know how to retrieve the data.

PL/SQL: get trace information

DBMS_OUTPUT.PUT_LINE('*** Error stack ***');
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
DBMS_OUTPUT.PUT_LINE('*** Error backtrace ***');
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
DBMS_OUTPUT.PUT_LINE('*** Call stack ***');
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_CALL_STACK);

Get information on locks

select C.OWNER
     , C.OBJECT_NAME
     , C.OBJECT_TYPE
     , B.SID
     , B.SERIAL#
     , B.STATUS
     , B.OSUSER
     , B.MACHINE
     , case A.LOCKED_MODE
          when 1 then 'No Lock'
          when 2 then 'Row Share'
          when 3 then 'Row Exclusive'
          when 4 then 'Shared Table'
          when 5 then 'Shared Row Exclusive'
          when 6 then 'Exclusive'
       end
          as LOCKED_MODE
     , case L.type
          when 'BL' then 'Buffer Cache Management (PCM lock)'
          when 'CF' then 'Controlfile Transaction'
          when 'CI' then 'Cross Instance Call'
          when 'CU' then 'Bind Enqueue'
          when 'DF' then 'Data File'
          when 'DL' then 'Direct Loader'
          when 'DM' then 'Database Mount'
          when 'DR' then 'Distributed Recovery'
          when 'DX' then 'Distributed Transaction'
          when 'FS' then 'File Set'
          when 'IN' then 'Instance Number'
          when 'IR' then 'Instance Recovery'
          when 'IS' then 'Instance State'
          when 'IV' then 'Library Cache Invalidation'
          when 'JQ' then 'Job Queue'
          when 'KK' then 'Redo Log Kick'
          when 'LA' then 'Library Cache Lock'
          when 'LB' then 'Library Cache Lock'
          when 'LC' then 'Library Cache Lock'
          when 'LD' then 'Library Cache Lock'
          when 'LE' then 'Library Cache Lock'
          when 'LF' then 'Library Cache Lock'
          when 'LG' then 'Library Cache Lock'
          when 'LH' then 'Library Cache Lock'
          when 'LI' then 'Library Cache Lock'
          when 'LJ' then 'Library Cache Lock'
          when 'LK' then 'Library Cache Lock'
          when 'LL' then 'Library Cache Lock'
          when 'LM' then 'Library Cache Lock'
          when 'LN' then 'Library Cache Lock'
          when 'LO' then 'Library Cache Lock'
          when 'LP' then 'Library Cache Lock'
          when 'MM' then 'Mount Definition'
          when 'MR' then 'Media Recovery'
          when 'NA' then 'Library Cache Pin'
          when 'NB' then 'Library Cache Pin'
          when 'NC' then 'Library Cache Pin'
          when 'ND' then 'Library Cache Pin'
          when 'NE' then 'Library Cache Pin'
          when 'NF' then 'Library Cache Pin'
          when 'NG' then 'Library Cache Pin'
          when 'NH' then 'Library Cache Pin'
          when 'NI' then 'Library Cache Pin'
          when 'NJ' then 'Library Cache Pin'
          when 'NK' then 'Library Cache Pin'
          when 'NL' then 'Library Cache Pin'
          when 'NM' then 'Library Cache Pin'
          when 'NN' then 'Library Cache Pin'
          when 'NO' then 'Library Cache Pin'
          when 'NP' then 'Library Cache Pin'
          when 'NQ' then 'Library Cache Pin'
          when 'NR' then 'Library Cache Pin'
          when 'NS' then 'Library Cache Pin'
          when 'NT' then 'Library Cache Pin'
          when 'NU' then 'Library Cache Pin'
          when 'NV' then 'Library Cache Pin'
          when 'NW' then 'Library Cache Pin'
          when 'NX' then 'Library Cache Pin'
          when 'NY' then 'Library Cache Pin'
          when 'NZ' then 'Library Cache Pin'
          when 'PF' then 'Password File'
          when 'PI' then 'Parallel Slaves'
          when 'PR' then 'Process Startup'
          when 'PS' then 'Parallel Slave Synchronization'
          when 'QA' then 'Row Cache Lock'
          when 'QB' then 'Row Cache Lock'
          when 'QC' then 'Row Cache Lock'
          when 'QD' then 'Row Cache Lock'
          when 'QE' then 'Row Cache Lock'
          when 'QF' then 'Row Cache Lock'
          when 'QG' then 'Row Cache Lock'
          when 'QH' then 'Row Cache Lock'
          when 'QI' then 'Row Cache Lock'
          when 'QJ' then 'Row Cache Lock'
          when 'QK' then 'Row Cache Lock'
          when 'QL' then 'Row Cache Lock'
          when 'QM' then 'Row Cache Lock'
          when 'QN' then 'Row Cache Lock'
          when 'QO' then 'Row Cache Lock'
          when 'QP' then 'Row Cache Lock'
          when 'QQ' then 'Row Cache Lock'
          when 'QR' then 'Row Cache Lock'
          when 'QS' then 'Row Cache Lock'
          when 'QT' then 'Row Cache Lock'
          when 'QU' then 'Row Cache Lock'
          when 'QV' then 'Row Cache Lock'
          when 'QW' then 'Row Cache Lock'
          when 'QX' then 'Row Cache Lock'
          when 'QY' then 'Row Cache Lock'
          when 'QZ' then 'Row Cache Lock'
          when 'RT' then 'Redo Thread'
          when 'SC' then 'System Commit number'
          when 'SM' then 'SMON synchronization'
          when 'SN' then 'Sequence Number'
          when 'SQ' then 'Sequence Enqueue'
          when 'SR' then 'Synchronous Replication'
          when 'SS' then 'Sort Segment'
          when 'ST' then 'Space Management Transaction'
          when 'SV' then 'Sequence Number Value'
          when 'TA' then 'Transaction Recovery'
          when 'TM' then 'DML Enqueue'
          when 'TS' then 'Table Space (or Temporary Segment)'
          when 'TT' then 'Temporary Table'
          when 'TX' then 'Transaction'
          when 'UL' then 'User-defined Locks'
          when 'UN' then 'User Name'
          when 'US' then 'Undo segment Serialization'
          when 'WL' then 'Writing redo Log'
          when 'XA' then 'Instance Attribute Lock'
          when 'XI' then 'Instance Registration Lock'
       end
          as type
  from V$LOCKED_OBJECT A
       inner join V$SESSION B on A.SESSION_ID = B.SID
       inner join DBA_OBJECTS C on A.OBJECT_ID = C.OBJECT_ID
       inner join GV$LOCK L on B.SID = L.SID;

Find indexes that are useless or not of the proper type

There is a rule of thumbs according to which a normal non-unique index should be quite selective to be taken by the CBO. Selective means rather to return only rather less than 5 % than 10 % of the data.
On the other hand, bitmaps are good on columns that are not selective at all but you have several of those on one table and queries restrict on a variety of combinations on bitmap indexed columns which in combination are selective. The more selective a bitmap indexed column is the more bloated gets the index.
Following query can help to find indexes that should be re-considered.

with IND_BASE
     as (select *
           from ALL_INDEXES
          where UNIQUENESS = 'NONUNIQUE' -- UNIQUE indexes are per definition
                                         -- very selective
            and OWNER not in ('SYS'
                            , 'OPS$DEZA'
                            , 'SYSTEM'
                            , 'XDB'))
   , COL_BASE
     as (select C.*     --  density = 1 / (Number of distinct NON null values)
           from ALL_TAB_COLS C
                inner join IND_BASE I
                   on C.OWNER = I.TABLE_OWNER
                  and C.TABLE_NAME = I.TABLE_NAME)
   , COLS_INVALID_DENS
     as (select *
           from COL_BASE
          /* we must rule out indexes with columns without or with invalid
             density information */
          where DENSITY is not null
             /* 0 or negative density is actually not defined but at least 0s
                occur for some obscure reason */
             or DENSITY > 0)
   , IND_COL_BASE
     as (select C.*
           from ALL_IND_COLUMNS C
                inner join IND_BASE I
                   on C.TABLE_OWNER = I.TABLE_OWNER
                  and C.TABLE_NAME = I.TABLE_NAME
                  and C.INDEX_OWNER = I.OWNER
                  and C.INDEX_NAME = I.INDEX_NAME)
   , IND_INVALID_DENS
     as (  select I.TABLE_OWNER
                , I.TABLE_NAME
                , I.INDEX_OWNER
                , I.INDEX_NAME
             from IND_COL_BASE I
                  inner join COLS_INVALID_DENS C
                     on I.TABLE_OWNER = C.OWNER
                    and I.TABLE_NAME = C.TABLE_NAME
                    and I.COLUMN_NAME = C.COLUMN_NAME
         group by I.TABLE_OWNER
                , I.TABLE_NAME
                , I.INDEX_OWNER
                , I.INDEX_NAME)
   , IND_VALID_BASE
     as (select I.*
           from IND_BASE I
                left outer join IND_INVALID_DENS IV
                   on I.TABLE_OWNER = IV.TABLE_OWNER
                  and I.TABLE_NAME = IV.TABLE_NAME
                  and I.OWNER = IV.INDEX_OWNER
                  and I.INDEX_NAME = IV.INDEX_NAME
          where IV.TABLE_OWNER is null)
   , IND_VALID_COL
     as (select C.*, I.INDEX_TYPE
           from IND_VALID_BASE I
                inner join IND_COL_BASE C
                   on I.TABLE_OWNER = C.TABLE_OWNER
                  and I.TABLE_NAME = C.TABLE_NAME
                  and I.OWNER = C.INDEX_OWNER
                  and I.INDEX_NAME = C.INDEX_NAME)
   , IND_COL_DENS
     as (select I.*, C.DENSITY as DENS
           from IND_VALID_COL I
                inner join COL_BASE C
                   on I.TABLE_OWNER = C.OWNER
                  and I.TABLE_NAME = C.TABLE_NAME
                  and I.COLUMN_NAME = C.COLUMN_NAME)
   , IND_DENS
     as (  select INDEX_OWNER
                , INDEX_NAME
                , TABLE_OWNER
                , TABLE_NAME
                , INDEX_TYPE
                , exp(sum(ln(DENS))) as DENS -- instead of multiplying numbers,
                                             -- their logarithms can be added, 
                                             -- and the result exponentiated: 
                                             -- only works if the density > 0
             from IND_COL_DENS
         group by INDEX_OWNER
                , INDEX_NAME
                , TABLE_OWNER
                , TABLE_NAME
                , INDEX_TYPE)
select /*+ parallel(4) */
      'Please consider converting the index into an normal index'
          as COMMENTS
     , I.*
  from IND_DENS I
 where I.INDEX_TYPE = 'BITMAP'
   and DENS <= 0.1
union all
select /*+ parallel(4) */
      'Please consider removing the index or converting it into an bitmap index, if it is a compound index into several bitmap indexes'
          as COMMENTS
     , I.*
  from IND_DENS I
 where I.INDEX_TYPE = 'NORMAL'
   and DENS >= 0.1;

Find indexes that possibly can be integrated into others

Following query returns all single column indexes (sci) of which the attribute is part of a multi column index (cmi) but not on first position. If the order of the cmi is changed such that the respective column is on the first position, the sci gets obsolete. This is only possible if the affected table is not queried with restrictions on the former first position column of the cmi.

with IND_COL_BASE
     as (select *
           from ALL_IND_COLUMNS
          where TABLE_OWNER not in ('SYS'
                                  , 'OPS$DEZA'
                                  , 'SYSTEM'
                                  , 'XDB'))
   , IND_COL_CNT
     as (  select TABLE_OWNER
                , TABLE_NAME
                , INDEX_OWNER
                , INDEX_NAME
                , count(*) as ANZ
             from IND_COL_BASE
         group by TABLE_OWNER
                , TABLE_NAME
                , INDEX_OWNER
                , INDEX_NAME)
   , IND_COL_1_ONLY
     as (select TABLE_OWNER
              , TABLE_NAME
              , INDEX_OWNER
              , INDEX_NAME
           from IND_COL_CNT
          where ANZ = 1)
   , IND_COL_P1
     as (select B.*
           from IND_COL_BASE B
                inner join IND_COL_1_ONLY C
                   on B.TABLE_OWNER = C.TABLE_OWNER
                  and B.TABLE_NAME = C.TABLE_NAME
                  and B.INDEX_OWNER = C.INDEX_OWNER
                  and B.INDEX_NAME = C.INDEX_NAME)
   , NON_UNIQUE_INDEXES
     as (select TABLE_OWNER
              , TABLE_NAME
              , OWNER
              , INDEX_NAME
           from ALL_INDEXES
          where UNIQUENESS = 'NONUNIQUE'
            and TABLE_OWNER not in ('SYS'
                                  , 'OPS$DEZA'
                                  , 'SYSTEM'
                                  , 'XDB'))
   , IND_COL_P1_NON_KEY
     as (select I.*
           from IND_COL_P1 I
                inner join NON_UNIQUE_INDEXES N
                   on I.TABLE_OWNER = N.TABLE_OWNER
                  and I.TABLE_NAME = N.TABLE_NAME
                  and I.INDEX_OWNER = N.OWNER
                  and I.INDEX_NAME = N.INDEX_NAME)
  select /*+ parallel(4) */
        *
    from IND_COL_P1_NON_KEY IC1
         inner join IND_COL_BASE IC
            on IC1.TABLE_OWNER = IC.TABLE_OWNER
           and IC1.TABLE_NAME = IC.TABLE_NAME
           and IC1.INDEX_OWNER = IC.INDEX_OWNER
           and IC1.COLUMN_NAME = IC.COLUMN_NAME
           and IC1.INDEX_NAME != IC.INDEX_NAME
           and IC1.COLUMN_POSITION != IC.COLUMN_POSITION
order by IC1.TABLE_OWNER asc
       , IC1.TABLE_NAME asc
       , IC1.INDEX_OWNER asc
       , IC1.INDEX_NAME asc
       , IC1.COLUMN_NAME asc;

Check whether "something" is of number type

There are in the net several proposals for this. One is to write a proper PL/SQL function that tries to assign a given value to a number type variable. It returns TRUE if no exception is raised and catches the exception for the data type incompatibility and returns false.
However, within SQL this is inefficient as it will make a context switch for every row the function is applied to. Maybe the use of regular expressions with regexp_like is more efficient. However, it means that notation conventions have to be clear and met. In the following I will show a solution for following conventions.

• A negative number is marked by a - as the first character
• No white space character are allowed
• No grouping separators allowed (for thousands and so on)
• The decimal separator is a .
• Fractions always need at least one digit after the decimal separator
• Before decimal separator digits are optional, e.g. -0.12 equals -.12

Regular expression:

^-?[0-9]*([.][0-9]+)?$

SQL example:

select *
  from DUAL
 where regexp_like(
            '-.3',
            '^-?[0-9]*([.][0-9]+)?$')