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: internal sql types
Reply
Author Archives: bigspoon
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 != 0;
Reading password in Unix shell
print -n "Enter Your password:"
stty_orig=`stty -g`
trap "stty ${stty_orig}; exit" 1 2 3 15
stty -echo >&- 2>&-
read PASS
stty ${stty_orig} >&- 2>&-
trap 1 2 3 15
print
trap :catches interruptions. I.e. if the user presses Ctrl+C, the normal stty mode is set before stopping the program
stty -echo :switches off the display echo
>&- 2>&- :helps to avoid “stty: Not a typewriter” message for non-interactive scripts.
Using the clipboard in WSH
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 existence 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! Very clever…
Unix shell: workaround for loop problem
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 on some Linux machines.
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"
Another possibility is to use the named pipe:
#!/bin/ksh
TMPPIPE=/tmp/thepipe.$$
mkfifo $TMPPIPE
cat $0 > $TMPPIPE &
num=0
while read line ; do
let num=num+1
done < $TMPPIPE
echo "Number=$num"
JavaScript: edit web page in browser
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
The following script help to find, how often the redo logs were switched.
It calculates the number by date and by hour.
JavaScript: Soundex implementation
There is a special algorithm for comparision strings, which sound similar (Soundex).
Here is JavaScript Soundex implementation:
function soundex ( s_src )
{
var s_rez = "0000" ;
var new_code, prev, idx
a_codes = { "bfpv": 1, "cgjkqsxz":2, "dt": 3, "l": 4, "mn": 5, "r": 6 };
s_src = s_src.toLowerCase().replace(/ /g,"")
if ( s_src.length < 1) {
return(s_rez);
}
s_rez = s_src.substr(0,1);
prev = "0";
for ( idx = 1 ; idx < s_src.length ; idx++) {
new_code = "0";
cur_char = s_src.substr(idx,1)
for (s_code in a_codes)
if (s_code.indexOf(cur_char) >= 0)
{ new_code = a_codes[ s_code ] ; break ; }
if (new_code != prev && new_code != "0" ) {
s_rez += new_code;
}
prev = new_code;
}
s_rez = s_rez + "0000"
return s_rez.substr(0,4);
}
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,
replace(c.status,' ','_') status
FROM
( select tablespace_name,
sum(bytes) bytes
from dba_free_space
group by tablespace_name
) a ,
( select tablespace_name,
sum(bytes) bytes
from dba_data_files
group by tablespace_name
) b ,
dba_tablespaces c
WHERE b.tablespace_name = a.tablespace_name (+)
AND b.tablespace_name = c.tablespace_name
union
select f.TABLESPACE_NAME,
f.TOTAL_MB/1024 sizegb,
nvl( (u.USED_MB/f.TOTAL_MB)*100, 0 ) usedproc,
'TEMPORARY' status
from
(
select f1.TABLESPACE_NAME,sum( f1.BYTES/1024/1024 ) TOTAL_MB
from (
select TABLESPACE_NAME,BYTES
from dba_temp_files
union all
select TABLESPACE_NAME,BYTES
from dba_data_files
where TABLESPACE_NAME in (
select TABLESPACE_NAME
from dba_tablespaces
where CONTENTS='TEMPORARY'
)
) f1
group by f1.TABLESPACE_NAME
) f,
(
select u1.TABLESPACE,
sum(u1.blocks) * max((select value from v$parameter where name='db_block_size')/1024/1024) USED_MB
from v$sort_usage u1
group by u1.TABLESPACE
) u
where f.TABLESPACE_NAME = u.tablespace (+)
ORDER BY 1;