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;

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’, […]

Oracle: check the existance of logon/logoff triggers

SELECT  DECODE((COUNT(trigger_name)),0,’LOGON trigger missing’,  ‘Number of logon triggers: ‘ || COUNT(trigger_name) ) "INFO" FROM sys.dba_triggers WHERE  TRIGGERING_EVENT LIKE ‘LOGON%’  AND status=’ENABLED’  AND owner=’SYS’ UNION SELECT  DECODE((COUNT(trigger_name)),0,’LOGOFF trigger missing’,”,  ‘Number of logoff triggers:’ || COUNT(trigger_name)) "INFO" FROM sys.dba_triggers WHERE  TRIGGERING_EVENT LIKE ‘LOGOFF%’  AND status=’ENABLED’  AND owner=’SYS’

The field TRIGGERING_EVENT could have the spaces at the end! […]

Oracle: redo log switches by date

The following script help to find, how often the redo logs were switched. It calculates the number by date and by hour.


Oracle: usage of the tablespaces

SET pagesize 10000 SET COLSEP ‘|’ SET VERIFY off SET serveroutput ON SIZE 1000000 BREAK ON report COLUMN tablespace_name format a30 heading ‘TABLESPACE’ COLUMN sizegb   format 9999999999D9 heading ‘SIZE-Gb’ COLUMN usedproc format 999D99 heading ‘USED-%’ COLUMN status format a10 heading ‘STATUS’ COMPUTE SUM LABEL ‘Total size:’ OF sizegb ON report SELECT b.tablespace_name ,   […]

Oracle: plan of the running query

col object_name FOR a40 SELECT operation,        options,        object_name,    partition_id FROM   v$sql_plan WHERE address IN  ( SELECT sql_address FROM v$session WHERE sid = &sid.) ORDER BY id;