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