web analytics

Oracle: eval function

The function takes some expression as the argument and executes it, returning output in the varchar string

CREATE OR REPLACE FUNCTION eval (expr VARCHAR2) RETURN VARCHAR2 AS   ret VARCHAR2(4000); BEGIN   EXECUTE IMMEDIATE ‘begin :result := ‘ || expr || ‘; end;’ USING OUT ret;   RETURN ret; END; /

The discussion and examples […]

Oracle: list of the system events

This piece of code show the list of events, set in the Oracle database

SET serveroutput ON  DECLARE   event_level NUMBER;  BEGIN   dbms_output.enable(20000) ;   FOR i IN 10000..33999 LOOP      sys.dbms_system.read_ev(i,event_level);      IF (event_level > 0) THEN         dbms_output.put_line(’Event ‘||TO_CHAR(i)||’ set at level ‘||                              TO_CHAR(event_level));
     END IF;
  END LOOP;
END;

Oracle: list of the running transactions

This query shows SID, username and start time of the running transaction. In addition, number of the used blocks is shown.

SELECT a.sid, a.status, a.username, b.xidusn, b.used_urec, b.used_ublk, b.START_TIME FROM v$session a, v$transaction b WHERE a.saddr = b.ses_addr ORDER BY START_TIME DESC;

Oracle: internal sql types

Here is the list of the Oracle SQL types and their internal codes:

SELECT t.typecode,o.name FROM sys.TYPE$ t, sys.obj$ o WHERE BITAND (t.properties, 16) = 16 AND t.toid = o.oid$ ORDER BY t.typecode

Oracle: wait statisics by block class

This query helps to get information about the wait statistics (in fact, this query is contained in GV_$WAITSTAT)

SELECT inst_id, DECODE (indx, 1,’data block’, 2,’sort block’, 3,’save undo block’, 4,’segment header’, 5,’save undo header’, 6,’free list’, 7,’extent map’, 8,’1st level bmb’, 9,’2nd level bmb’, 10,’3rd level bmb’, 11,’bitmap block’, 12,’bitmap index block’, 13,’file header block’, […]

Reading password in Unix shell

print -n "Enter Your password:" stty_orig=`stty -g` trap "stty ${stty_orig}; exit" 1 2 3 15 stty -echo >&- 2>&- read PASS stty ${stty_orig} >&- 2>&- trap 1 2 3 15 print

trap :catches interruptions. I.e. if the user presses Ctrl+C, the normal stty mode is set before stopping the program stty -echo :switches off the […]