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 could be [...]

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));
  [...]

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 [...]

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 [...]

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’,
14,’unused’,
15,’system undo header’,
16,’system undo block’,
17,’undo header’,
18,’undo block’
),  COUNT,  TIME
FROM x$kcbwait
WHERE indx [...]

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.

Continue reading Oracle: redo log switches by date

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 ,
       b.bytes/1024/1024/1024 AS sizegb ,
       NVL(100-((a.bytes/b.bytes)*100), 100) usedproc,
    [...]

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 [...]

Oracle: using substitution variables in sqlplus

Get the value:

ACCEPT my_password CHAR PROMPT ‘Password:  ’ HIDE
ACCEPT birthday DATE FORMAT ‘dd/mm/yyyy’ DEFAULT ’01/01/1950′ PROMPT ‘Enter birthday date:  ’

Declaring the variable

DEFINE the_answer = 42

Undefine the variable

UNDEFINE the_answer

How to remember the result of the query

COLUMN the_date new_value the_rundate noprint;
SELECT TO_CHAR(SYSDATE, ‘DDMMYYYY_HH24MI’) the_date FROM dual;

SELECT ‘&the_rundate’ FROM dual ;

Save the variables to the file

store SET myvars.txt CREATE
store [...]