inspect Oracle active sessions

Here come queries useful to see the current running sessions, together with the sql being executed and the associated events

select u.username, ash.session_id, ash.session_serial#, ash.event, count(*), ash.SQL_ID, s.sql_text
      from gv$active_session_history ash, v$sql s, V$SESSION u
      where ash.sql_id=s.sql_id and u.user# = ash.user_id
    group by ash.session_id, ash.session_serial#, u.username, ash.SQL_ID, ash.event, sql_text
    order by 1;

select se.username, se.sid, se.serial#, sq.sql_fulltext
      from V$SESSION se, v$sql sq
      where se.sql_id = sq.sql_id
order by 1

gathering statistics on Oracle DB

Here comes a script to gather statistics on Oracle DB

begin

  dbms_stats.unlock_schema_stats('SCHEMA');
  dbms_stats.delete_schema_stats('SCHEMA');
  dbms_stats.gather_schema_stats(ownname       => 'SCHEMA',
                                estimate_percent => 40.0,
                                granularity      => 'GLOBAL',
                                block_sample     => TRUE,
                                cascade          => TRUE,
                                degree           => DBMS_STATS.DEFAULT_DEGREE,
                                method_opt       => 'for all columns size 1');

  dbms_stats.lock_schema_stats('SCHEMA');

end;



select t.owner, t.table_name, t.last_analyzed from dba_all_tables t
where lower(t.owner) = 'your_schema';

foreign keys without indexes

In Oracle database a foreign keys without an index defined on it could lead to contention problems (enq: TM – contention wait event). The following script retrieves foreign keys not having indexes

--the script finds the missing indexes on FK

SELECT * FROM (
   SELECT c.table_name, cc.column_name, cc.position column_position
   FROM   user_constraints c, user_cons_columns cc
   WHERE  c.constraint_name = cc.constraint_name
   AND    c.constraint_type = 'R'
   MINUS
   SELECT i.table_name, ic.column_name, ic.column_position
   FROM   user_indexes i, user_ind_columns ic
   WHERE  i.index_name = ic.index_name
   )
ORDER BY table_name, column_position;

Oracle roles and privileges

The following very useful scripts are taken out from http://www.adp-gmbh.ch/ora/misc/recursively_list_privilege.html (thanks to René Nyffenegger) Users to roles and system privileges This is a script that shows the hierarchical relationship between system privileges, roles and users.

select
  lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
  (
  /* THE USERS */
    select 
      null     grantee, 
      username granted_role
    from 
      dba_users
    where
      username like upper('%&enter_username%')
  /* THE ROLES TO ROLES RELATIONS */ 
  union
    select 
      grantee,
      granted_role
    from
      dba_role_privs
  /* THE ROLES TO PRIVILEGE RELATIONS */ 
  union
    select
      grantee,
      privilege
    from
      dba_sys_privs
  )
start with grantee is null
connect by grantee = prior granted_role;

System privileges to roles and users This is also possible the other way round: showing the system privileges in relation to roles that have been granted this privilege and users that have been granted either this privilege or a role:

select
  lpad(' ', 2*level) || c "Privilege, Roles and Users"
from
  (
  /* THE PRIVILEGES */
    select 
      null   p, 
      name   c
    from 
      system_privilege_map
    where
      name like upper('%&enter_privliege%')
  /* THE ROLES TO ROLES RELATIONS */ 
  union
    select 
      granted_role  p,
      grantee       c
    from
      dba_role_privs
  /* THE ROLES TO PRIVILEGE RELATIONS */ 
  union
    select
      privilege     p,
      grantee       c
    from
      dba_sys_privs
  )
start with p is null
connect by p = prior c;

search and replace in an Oracle CLOB

Here comes a very useful function which replaces a string in a CLOB with another string the function has been taken out from http://www.astral-consultancy.co.uk/cgi-bin/hunbug/doco.cgi?11080

--      The function accepts three parameters, the same as the standard REPLACE function.
       --1.    p_clob: The CLOB data
       --2.    p_what: The search string
       --3.    p_with: The replacement string
       FUNCTION dfn_clobReplace
       ( p_clob          IN CLOB,
         p_what          IN VARCHAR2,
         p_with          IN VARCHAR2 ) RETURN CLOB IS
 
         c_whatLen       CONSTANT PLS_INTEGER := LENGTH(p_what);
         c_withLen       CONSTANT PLS_INTEGER := LENGTH(p_with);
 
         l_return        CLOB;
         l_segment       CLOB;
         l_pos           PLS_INTEGER := 1-c_withLen;
         l_offset        PLS_INTEGER := 1;
 
       BEGIN
 
         --It first checks that the p_what string is not null, to save time checking the CLOB.
         IF p_what IS NOT NULL THEN
           --The function then begins the first of two loops. The outer loop splits the main CLOB into segments of 32K, so that the inner loop can process the data.
           WHILE l_offset < DBMS_LOB.GETLENGTH(p_clob) LOOP
             l_segment := DBMS_LOB.SUBSTR(p_clob,32767,l_offset);
             LOOP --The inner loop then checks for the search string, p_what, in the segment of CLOB that is current being processed.
               l_pos := DBMS_LOB.INSTR(l_segment,p_what,l_pos+c_withLen);
               EXIT WHEN (NVL(l_pos,0) = 0) OR (l_pos = 32767-c_withLen);
               --If the search string is not found it exits the loop to process the next segment. 
               --If the search string is found at the very end of the string, it moves on to the next segment to be processed, 
               --as the **** segments overlap by the length of the search string ****, so a search string occurring over a segment break does not get missed.
               l_segment := TO_CLOB( DBMS_LOB.SUBSTR(l_segment,l_pos-1)
                                   ||p_with
                                   ||DBMS_LOB.SUBSTR(l_segment,32767-c_whatLen-l_pos-c_whatLen+1,l_pos+c_whatLen));
             END LOOP;
             --The segment is then rebuilt, from the data before the search string, the replacement string, then the data after the search string.
             l_return := l_return||l_segment;
             l_offset := l_offset + 32767 - c_whatLen;
             --The processed segment is then added to the return CLOB and the offset for the segment is increased by 32K minus the search string length.
           END LOOP;
         END IF;
 
         RETURN(l_return);
 
       END;

Multi-criteria search query in Oracle PL/SQL with dynamic SQL

How to implement a multi-criteria search query in PL/SQL with dynamic SQL.

It is very frequent to implement a search form with multiple criteria, where each criteria can be independently enabled by the user, according to her needs.
The application will then need to perform a query on the database, taking into account all the criteria choosen by the user, that is perform a dynamic query. Here comes an example of how to implement a dynamic query in PL/SQL, using dynamic SQL.

The query could be implemented also with a static query, which takes into account all the possible criteria, and switch them on according to the value of some input parameters.
This static query would sadly perform very bad, since it makes the optimizer choose access paths for unnecessary criteria.

For example, given the following table

CREATE TABLE SUBJECT
  (
    SUBJECTID   NUMBER(38,0) NOT NULL ENABLE,
    SURNAME NVARCHAR2(400),
    NAME NVARCHAR2(400)
    DATEOFBIRTH TIMESTAMP (6),
    CONSTRAINT PK_SUBJECT PRIMARY KEY (SUBJECTID) );

the following procedure performs a static search for an user taking into account her surname, name and birthdate and putting results into ResCursor1.
Each criteria is switching on in the query according to whether the corresponding input parameter is not null.

create or replace
PROCEDURE SEARCHPHYSICALPERSONS (Surname    in nvarchar2 default null,
                                                       Name       in nvarchar2 default null,
                                                       BirthDate  in date default null
                                                       ResCursor1 out sys_refcursor) is

  pSurname    Subject.Surname%TYPE := Surname;
  pName       Subject.Name%TYPE := Name;
  pBirthDate  Subject.Dateofbirth%TYPE := BirthDate;

begin

  open ResCursor1 for
    select distinct
           a.subjectid,
           surname,
           name,
      from Subject a
     where (pSurname is null or TRIM(upper(a.surname)) like TRIM(upper(pSurname||'%')))
       and (pName is null or TRIM(upper(a.name)) = TRIM(upper(pName)))
       and (pBirthDate is null or a.Dateofbirth = pBirthDate);

end SearchPhysicalPersons;

The following approach will instead optimize the search taking into account only the necessary criteria, building a dynamic sql query which is being parsed at runtime by the DB engine (the query will use binding variables, since they are necessary for optimal performances).

create or replace
TYPE physicalPersonsType AS OBJECT
    ( subjectid NUMBER(38,0),
      surname VARCHAR2(400 CHAR),
      name VARCHAR2(400 CHAR),
      dateofbirth TIMESTAMP(6),
    );

 
CREATE OR REPLACE TYPE physicalPersonsTableType AS TABLE OF physicalPersonsType;

create or replace
FUNCTION readSearchPhysicalPersons(cur_hdl int) RETURN physicalPersonsTableType
PIPELINED IS
  subjectid SUBJECT.SUBJECTID%type;
  surname SUBJECT.SURNAME%type;
  name SUBJECT.NAME%type;
  dateofbirth SUBJECT.DATEOFBIRTH%type;
BEGIN

      dbms_sql.define_column(cur_hdl, 1, subjectid);
      dbms_sql.define_column(cur_hdl, 2, surname, 400);
      dbms_sql.define_column(cur_hdl, 3, name, 400);
      dbms_sql.define_column(cur_hdl, 4, dateofbirth);

  LOOP
  -- fetch a row
  IF dbms_sql.fetch_rows(cur_hdl) > 0 then

      -- fetch columns from the row
      dbms_sql.column_value(cur_hdl, 1, subjectid);
      dbms_sql.column_value(cur_hdl, 2, surname);
      dbms_sql.column_value(cur_hdl, 3, name);
      dbms_sql.column_value(cur_hdl, 4, dateofbirth);
   
   PIPE ROW (physicalPersonsType(subjectid, surname, name, dateofbirth));
  ELSE
        EXIT; 
  END IF;
  END LOOP;
  
  RETURN;
END;

create or replace
PROCEDURE SEARCHPHYSICALPERSONS (Surname    in nvarchar2 default null,
                                                       Name       in nvarchar2 default null,
                                                       BirthDate  in date default null,
                                                       ResCursor1 out sys_refcursor) is

                                                       pSurname    Subject.Surname%TYPE := Surname;
                                                       pName       Subject.Name%TYPE := Name;
                                                       pBirthDate  Subject.Dateofbirth%TYPE := BirthDate;

  stmt_str varchar2(4000);
  cur_hdl int;
  rows_processed int;
  commaFlag boolean := false;
begin

cur_hdl := dbms_sql.open_cursor; -- open cursor
stmt_str := 'SELECT DISTINCT A.SUBJECTID, SURNAME, NAME, DATEOFBIRTH'
   ||' FROM SUBJECT A'
   ||' WHERE 1=1';

if pSurname is not null then
  stmt_str := stmt_str || ' AND (TRIM(UPPER(A.SURNAME)) LIKE TRIM(UPPER(:pSurname ||''%'')))';
end if;

if pName is not null then
  stmt_str := stmt_str || ' AND (TRIM(UPPER(A.NAME)) = TRIM(UPPER(:pName)))';
end if;

if pBirthDate is not null then
  stmt_str := stmt_str || ' AND (A.DATEOFBIRTH = :pBirthDate)';
end if;

--uncomment this line to see the generated sql
--dbms_output.put_line(stmt_str);

dbms_sql.parse(cur_hdl, stmt_str, dbms_sql.native);

-- supply binds (bind by name)
if pSurname is not null then
  dbms_sql.bind_variable(cur_hdl, ':pSurname', pSurname);
end if;

if pName is not null then
  dbms_sql.bind_variable(cur_hdl, ':pName', pName);
end if;

if pBirthDate is not null then
  dbms_sql.bind_variable(cur_hdl, ':pBirthDate', pBirthDate);
end if;

rows_processed := dbms_sql.execute(cur_hdl); -- execute

open ResCursor1 for
select * from table(readSearchPhysicalPersons(cur_hdl));

END;

Oracle, get the current DB instance

--this is a useful query to get the DB instance on which the current session is running, useful especially in RAC mode
SELECT sys_context('USERENV', 'INSTANCE') AS instance#, sys_context('USERENV', 'INSTANCE_NAME') AS instance_name FROM dual

Oracle export/import

here comes an example of data export:

the export must be performed on the source db server, the import on the target db server, since a local directory is required for the Oracle db server to export/import data

-- Create datapump directory
CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS 'D:\Backup\Oracle\DataPumpDir';
GRANT READ,WRITE ON DIRECTORY DATA_PUMP_DIR TO myorauser;

-- Check datapump directory
SELECT * FROM ALL_DIRECTORIES;

expdp myorauser/password@SID directory=DATA_PUMP_DIR DUMPFILE=myorauserDump.dmp SCHEMAS=myorauser

impdp DIRECTORY=DATA_PUMP_DIR DUMPFILE=myorauserDump.dmp SCHEMAS=myorauser

when you are asked credentials, don't use the SYS users, use another user with powerful privileges (as SYSTEM)
-always check that indexes have been correctly created in the target db
-the option CONTENT=DATA_ONLY imports only data
-in the target db must be created a tablespace with the same name of the tablespace containing the Oracle schema in the source db

ssh tunnel set up

server:
edit the file /etc/ssh/sshd_config

check that port-forwarding has not been disabled server-wide in /etc/ssh/sshd_config verify that
AllowTcpForwarding is set to yes (default setting is yes)

client:
use the following syntax
ssh -L localPort:remoteHost:RemoteHostPort sshServerHost

JVM monitoring

this is an example of how to monitoring the execution of a JVM running JBoss 4.2.3 GA under Solaris OS:

1. set the following Java options in run.conf (if you are on Windows, set them in run.bat)
JAVA_OPTS="....... -XX:+HeapDumpOnOutOfMemoryError -Dcom.sun.management.jmxremote.port=9999 -Dcom.sun.management.jmxremote.authenticate=false -Dcom.sun.management.jmxremote.ssl=false -Djboss.platform.mbeanserver -Djavax.management.builder.initial=org.jboss.system.server.jmx.MBeanServerBuilderImpl -Djava.rmi.server.hostname= ............"
2. use jvisualvm.exe in your JDK to attach to the remote JBoss: Remote -> Add Remote Host -> add JMX connection -> :9999
3. run the following script on the server, to get memory utilization percentages:

pid=`/bin/jps -l | grep Main | awk {'print $1'
}`
/bin/jstat -gcutil $pid 2000

extract Java stack traces from log4j logs

here is described a solution to retrieve from a Windows machine any errors and stack traces in log4j log files saved on a Solaris machine.
Results are filtered out by a given date, in this example 2011-06-16

1. download plink.exe and put in a new folder
2. in the same folder create a batch file with the following content

@echo off
plink.exe -v -ssh 127.0.0.1 -P 24 -l username -pw password -m cmd.txt > output.txt

3. in the same folder create the file cmd.txt with the following content

for file in `find logFolder -name "logfile.log*" -print`
do
echo $file
awk '{ if(($1 == "2011-06-16") && ($4 == "ERROR")){print $0; gl = getline row; while(gl > 0 && row !~ /^[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]/){print row; gl = getline row;}} }' $file
done


Oracle, table size

The following query (to be run as sys user) is useful to calculate the space used by all tables


SELECT OWNER,
OBJECT_NAME,
OBJECT_TYPE,
TABLESPACE_NAME,
NUM_ROWS,
SUM(KBYTES) KBYTES,
TO_CHAR(DECODE(NUM_ROWS, 0, 0, SUM(KBYTES) / (NUM_ROWS)), '9999.99') KB_ROW
FROM (SELECT LO.OWNER,
LO.TABLE_NAME AS OBJECT_NAME,
EX.SEGMENT_TYPE AS OBJECT_TYPE,
EX.TABLESPACE_NAME,
TS.NUM_ROWS AS NUM_ROWS,
ROUND((SUM(EX.BYTES) / 1024)) AS KBYTES
FROM DBA_EXTENTS EX
JOIN DBA_LOBS LO ON (EX.OWNER = LO.OWNER AND
EX.SEGMENT_NAME = LO.SEGMENT_NAME)
JOIN DBA_TAB_STATISTICS TS ON (LO.OWNER = TS.OWNER AND
LO.TABLE_NAME = TS.TABLE_NAME)
GROUP BY LO.OWNER,
LO.TABLE_NAME,
LO.COLUMN_NAME,
EX.SEGMENT_NAME,
EX.SEGMENT_TYPE,
EX.TABLESPACE_NAME,
TS.NUM_ROWS
UNION ALL
SELECT EX.OWNER,
EX.SEGMENT_NAME AS OBJECT_NAME,
EX.SEGMENT_TYPE AS OBJECT_TYPE,
EX.TABLESPACE_NAME,
TS.NUM_ROWS,
ROUND((SUM(EX.BYTES) / 1024)) AS KBYTES
FROM DBA_EXTENTS EX
LEFT JOIN DBA_LOBS LO ON EX.OWNER = LO.OWNER
AND EX.SEGMENT_NAME = LO.SEGMENT_NAME
JOIN DBA_TAB_STATISTICS TS ON EX.OWNER = TS.OWNER
AND EX.SEGMENT_NAME = TS.TABLE_NAME
GROUP BY EX.OWNER,
EX.SEGMENT_NAME,
EX.SEGMENT_TYPE,
EX.TABLESPACE_NAME,
TS.NUM_ROWS)
WHERE OWNER LIKE 'CF%'
GROUP BY OWNER, OBJECT_NAME, OBJECT_TYPE, TABLESPACE_NAME, NUM_ROWS
ORDER BY KBYTES DESC;

Windows, vb script to get the process data

this script prints the process start time

it can be run in this way
cscript <scriptname>.vbs <processname>
for example, if this script has been saved as the file named procInfo.vbs, in order to inspect all running instances of notepad.exe, it can be run as
cscript procInfo.vbs notepad.exe

procName = WScript.Arguments.Item(0)
strComputer = "."
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colProcesses = objWMIService.ExecQuery("Select * from Win32_Process Where Name = '" & procName & "'")
For Each item In colProcesses
  WScript.Echo(WMIDateStringToDate(item.CreationDate) & ", cmdLine " & item.CommandLine)
Next

Function WMIDateStringToDate(dtmDate)
  WMIDateStringToDate = CDate(Mid(dtmDate, 5, 2) & "/" & _
  Mid(dtmDate, 7, 2) & "/" & Left(dtmDate, 4) _
    & " " & Mid (dtmDate, 9, 2) & ":" & Mid(dtmDate, 11, 2) & ":" & Mid(dtmDate,13, 2))
End Function