Code.TechInterviews.com

Oracle: eval function

checkthis — Thu, 15 Jan 2009 01:05:31 +0000

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

checkthis — Tue, 04 Nov 2008 05:07:55 +0000

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

checkthis — Sun, 12 Oct 2008 05:55:27 +0000

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

checkthis — Sat, 11 Oct 2008 00:21:46 +0000

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

checkthis — Tue, 07 Oct 2008 00:33:38 +0000

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 != 0;

Using the clipboard in WSH

checkthis — Fri, 25 Jul 2008 13:22:57 +0000

How to get the text from the clipboard

set objIE = CreateObject("InternetExplorer.Application") objIE.Navigate("about:blank") textFromClipboard = objIE.document.parentwindow.clipboardData.GetData("text") objIE.Quit WScript.Echo textFromClipboard

How to put the text into clipboard

textIntoClipboard = "Some text" & VbCrLf & "Some more text"


Set objIE = WScript.CreateObject("InternetExplorer.Application")

objIE.Navigate "about:blank"

Do Until objIE.ReadyState = 4

	WScript.Sleep 100

Loop

objIE.document.ParentWindow.ClipboardData.SetData "text", textIntoClipboard objIE.Quit

The detailed explanation could be found here.

Oracle: check the existance of logon/logoff triggers

checkthis — Fri, 25 Jul 2008 13:15:47 +0000

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

Unix shell: workaround for loop problem

checkthis — Fri, 11 Jul 2008 13:08:52 +0000

It's not possible to get the value of the loop variables in some versions of ksh.

Example:
#!/bin/ksh


num=0

cat $0 | while read line ; do

 let num=num+1

done

echo "Number=$num"

This script will return "Number=0" as the result.

Here is the workaround for the problem: You should change the redirection method for the input file.
#!/bin/ksh


l=0

while read line ; do

 let l=l+1

done < $0

echo "Number=$l"
The last script will return the correct result: "Number=9"

JavaScript: edit web page in browser

checkthis — Fri, 11 Jul 2008 13:00:43 +0000

Here is small bookmarklet, which allows to edit the web page for any site (ok, You could save the results on Your local machine only).

javascript:document.body.contentEditable='true'; document.designMode='on'; void 0

Oracle: redo log switches by date

checkthis — Thu, 08 May 2008 08:51:27 +0000

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

SET LINESIZE 150 COLUMN day FORMAT A3 COLUMN Total FORMAT 99G990 COLUMN h00 FORMAT 999 COLUMN h01 FORMAT 999 COLUMN h02 FORMAT 999 COLUMN h03 FORMAT 999 COLUMN h04 FORMAT 999 COLUMN h05 FORMAT 999 COLUMN h06 FORMAT 999 COLUMN h07 FORMAT 999 COLUMN h08 FORMAT 999 COLUMN h09 FORMAT 999 COLUMN h10 FORMAT 999 COLUMN h11 FORMAT 999 COLUMN h12 FORMAT 999 COLUMN h13 FORMAT 999 COLUMN h14 FORMAT 999 COLUMN h15 FORMAT 999 COLUMN h16 FORMAT 999 COLUMN h17 FORMAT 999 COLUMN h18 FORMAT 999 COLUMN h19 FORMAT 999 COLUMN h20 FORMAT 999 COLUMN h21 FORMAT 999 COLUMN h22 FORMAT 999 COLUMN h23 FORMAT 999 COLUMN h24 FORMAT 999 BREAK ON REPORT COMPUTE MAX OF "Total" ON REPORT COMPUTE MAX OF "h00" ON REPORT COMPUTE MAX OF "h01" ON REPORT COMPUTE MAX OF "h02" ON REPORT COMPUTE MAX OF "h03" ON REPORT COMPUTE MAX OF "h04" ON REPORT COMPUTE MAX OF "h05" ON REPORT COMPUTE MAX OF "h06" ON REPORT COMPUTE MAX OF "h07" ON REPORT COMPUTE MAX OF "h08" ON REPORT COMPUTE MAX OF "h09" ON REPORT COMPUTE MAX OF "h10" ON REPORT COMPUTE MAX OF "h11" ON REPORT COMPUTE MAX OF "h12" ON REPORT COMPUTE MAX OF "h13" ON REPORT COMPUTE MAX OF "h14" ON REPORT COMPUTE MAX OF "h15" ON REPORT COMPUTE MAX OF "h16" ON REPORT COMPUTE MAX OF "h17" ON REPORT COMPUTE MAX OF "h18" ON REPORT COMPUTE MAX OF "h19" ON REPORT COMPUTE MAX OF "h20" ON REPORT COMPUTE MAX OF "h21" ON REPORT COMPUTE MAX OF "h22" ON REPORT COMPUTE MAX OF "h23" ON REPORT

SELECT TRUNC(first_time) AS "Date", TO_CHAR(first_time, 'Dy') AS "Day", COUNT(1) AS "Total", SUM(DECODE(TO_CHAR(first_time, 'HH24'),'00',1,0)) AS "h00", SUM(DECODE(TO_CHAR(first_time, 'HH24'),'01',1,0)) AS "h01", SUM(DECODE(TO_CHAR(first_time, 'HH24'),'02',1,0)) AS "h02", SUM(DECODE(TO_CHAR(first_time, 'HH24'),'03',1,0)) AS "h03", SUM(DECODE(TO_CHAR(first_time, 'HH24'),'04',1,0)) AS "h04", SUM(DECODE(TO_CHAR(first_time, 'HH24'),'05',1,0)) AS "h05", SUM(DECODE(TO_CHAR(first_time, 'HH24'),'06',1,0)) AS "h06", SUM(DECODE(TO_CHAR(first_time, 'HH24'),'07',1,0)) AS "h07", SUM(DECODE(TO_CHAR(first_time, 'HH24'),'08',1,0)) AS "h08", SUM(DECODE(TO_CHAR(first_time, 'HH24'),'09',1,0)) AS "h09", SUM(DECODE(TO_CHAR(first_time, 'HH24'),'10',1,0)) AS "h10", SUM(DECODE(TO_CHAR(first_time, 'HH24'),'11',1,0)) AS "h11", SUM(DECODE(TO_CHAR(first_time, 'HH24'),'12',1,0)) AS "h12", SUM(DECODE(TO_CHAR(first_time, 'HH24'),'13',1,0)) AS "h13", SUM(DECODE(TO_CHAR(first_time, 'HH24'),'14',1,0)) AS "h14", SUM(DECODE(TO_CHAR(first_time, 'HH24'),'15',1,0)) AS "h15", SUM(DECODE(TO_CHAR(first_time, 'HH24'),'16',1,0)) AS "h16", SUM(DECODE(TO_CHAR(first_time, 'HH24'),'17',1,0)) AS "h17", SUM(DECODE(TO_CHAR(first_time, 'HH24'),'18',1,0)) AS "h18", SUM(DECODE(TO_CHAR(first_time, 'HH24'),'19',1,0)) AS "h19", SUM(DECODE(TO_CHAR(first_time, 'HH24'),'20',1,0)) AS "h20", SUM(DECODE(TO_CHAR(first_time, 'HH24'),'21',1,0)) AS "h21", SUM(DECODE(TO_CHAR(first_time, 'HH24'),'22',1,0)) AS "h22", SUM(DECODE(TO_CHAR(first_time, 'HH24'),'23',1,0)) AS "h23" FROM V$log_history GROUP BY TRUNC(first_time), TO_CHAR(first_time, 'Dy') ORDER BY 1; CLEAR BREAKS