Oracle « Previous Entries

Oracle: eval function

Wednesday, January 14th, 2009

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 found there

Oracle: list of the system events

Monday, November 3rd, 2008

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

Oracle: list of the running transactions

Saturday, October 11th, 2008

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: sql types

Friday, October 10th, 2008

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

Monday, October 6th, 2008

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

Oracle: check the existance of logon/logoff triggers

Friday, July 25th, 2008

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! Very clever…

Oracle: redo log switches by date

Thursday, May 8th, 2008

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 (permanent and temporary)

Friday, March 28th, 2008

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

Oracle: plan of the running query

Friday, March 28th, 2008

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;

Oracle: using DBMS_METADATA for getting table structure

Monday, March 17th, 2008

set long 5000
select dbms_metadata.get_ddl(’TABLE’,'EMP’,'SCOTT’);

« Previous Entries