Global environment variables

To view the global environment variables, use the printenv command:
$ printenv
HOSTNAME=testbox.localdomain
TERM=xterm
SHELL=/bin/bash
HISTSIZE=1000
SSH CLIENT=192.168.1.2 1358 22
OLDPWD=/home/rich/test/test1
SSH TTY=/dev/pts/0
USER=rich
LS COLORS=no=00:fi=00:di=00;34:ln=00;36:pi=40;33:so=00;35:
bd=40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=00;32:
*.cmd=00;32:*.exe=00;32:*.com=00;32:*.btm=00;32:*.bat=00;32:
*.sh=00;32:*.csh=00;32:*.tar=00;31:*.tgz=00;31:*.arj=00;31:
*.taz=00;31:*.lzh=00;31:*.zip=00;31:*.z=00;31:*.Z=00;31:
*.gz=00;31:*.bz2=00;31:*.bz=00;31:*.tz=00;31:*.rpm=00;31:
*.cpio=00;31:*.jpg=00;35:*.gif=00;35:*.bmp=00;35:*.xbm=00;35:
*.xpm=00;35:*.png=00;35:*.tif=00;35:
MAIL=/var/spool/mail/rich
PATH=/usr/kerberos/bin:/usr/lib/ccache:/usr/local/bin:/bin:/usr/bin:
/home/rich/bin
INPUTRC=/etc/inputrc
PWD=/home/rich
LANG=en US.UTF-8
SSH ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass
SHLVL=1
HOME=/home/rich
LOGNAME=rich
CVS RSH=ssh

SSH CONNECTION=192.168.1.2 1358 192.168.1.4 22
LESSOPEN=|/usr/bin/lesspipe.sh %s
G BROKEN FILENAMES=1
=/usr/bin/printenv

Function Digit To Word

Following the function for convert from digit to Word :

Create or Replace Function Terbilang(Digit varchar2) Return Varchar2 is
Tampung99 varchar2(100);

# (Author : Ida Bagus Enderajana)
kel_1    varchar2(30);
kel_2    varchar2(30);
kel_3    varchar2(30);
kel_4    varchar2(30);
kel_5    varchar2(30);
hasil   varchar2(3000);
cekdigit number;
sisabagi number;
Begin
tampung99 :=”;
kel_1:=”;
kel_2:=”;
kel_3:=”;
kel_4:=”;
kel_5:=”;
cekdigit :=length(digit);
sisabagi :=mod(cekdigit,3);

if sisabagi=0 then
tampung99:=digit;
elsif sisabagi=1 then
tampung99:=’00′||digit;
elsif sisabagi=2 then
tampung99:=’0′||digit;
end if;

if length(tampung99)=3 then
kel_1:=substr(tampung99,1,3);
hasil:=basecurr_triple_number_words(kel_1);

elsif length(tampung99)=6 then
kel_1:=substr(tampung99,1,3);
kel_2:=substr(tampung99,4,3);
if kel_1=’001′ and kel_2=’000′ then
hasil:=’seribu’;
elsif kel_1=(‘001′) and kel_2 <>(‘000′) then
hasil:=’seribu ‘||basecurr_triple_number_words(to_number(kel_2));
elsif kel_1 <>(‘001′) and kel_2 <>(‘000′) then
hasil:=basecurr_triple_number_words(to_number(kel_1))||’ ribu ‘||basecurr_triple_number_words(to_number(kel_2));
elsif kel_1 <>’001′ and kel_2=’000′ then
hasil:=basecurr_triple_number_words(to_number(kel_1))||’ ribu ‘;
end if;

elsif length(tampung99)=9 then
begin
kel_1:=substr(tampung99,1,3);
kel_2:=substr(tampung99,4,3);
kel_3:=substr(tampung99,7,3);
if kel_1 <>’000′ then
begin
if kel_2 <>’000′ and kel_2 <>’001′ and kel_3 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ juta ‘||basecurr_triple_number_words(kel_2)||’ ribu ‘||basecurr_triple_number_words(kel_3);
elsif kel_2 =’000′ and kel_3=’000′  then
hasil:=basecurr_triple_number_words(kel_1)||’ juta ‘||basecurr_triple_number_words(kel_3);
elsif kel_2=’000′ and kel_3 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ juta ‘||basecurr_triple_number_words(kel_3);
elsif kel_2=’001′ and kel_3=’000′ then
hasil:=basecurr_triple_number_words(to_number(kel_1))||’ juta ‘||’ seribu’;
elsif kel_2=’001′ and kel_3 <>’000′ then
hasil:=basecurr_triple_number_words(to_number(kel_1))||’ juta ‘||’ seribu ‘||basecurr_triple_number_words(to_number(kel_3));
elsif kel_2 =’001′ and kel_3 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ juta ‘||’ seribu ‘||basecurr_triple_number_words(kel_3);
elsif kel_2 <>’000′ and kel_2 <>’001′ and kel_3 <>’000′ or kel_3=’000′   then
hasil:=basecurr_triple_number_words(kel_1)||’ juta ‘||basecurr_triple_number_words(kel_2)||’ ribu ‘||basecurr_triple_number_words(kel_3);
end if;
end;
end if;
end;

elsif length(tampung99)=12 then
begin
kel_1:=substr(tampung99,1,3);
kel_2:=substr(tampung99,4,3);
kel_3:=substr(tampung99,7,3);
kel_4:=substr(tampung99,10,3);
if kel_1 <>’000′ and kel_2 <>’000′ then
begin
if kel_3 <>’000′ and kel_4 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ milyar ‘||basecurr_triple_number_words(kel_2)||’ juta ‘||basecurr_triple_number_words(kel_3)||’ ribu ‘||basecurr_triple_number_words(kel_4);
elsif kel_3 =’000′ and kel_4 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ milyar ‘||basecurr_triple_number_words(kel_2)||’ juta ‘||basecurr_triple_number_words(kel_4);
elsif kel_3 =’000′ and kel_4=’000′  then
hasil:=basecurr_triple_number_words(kel_1)||’ milyar ‘||basecurr_triple_number_words(kel_2)||’ juta ‘||basecurr_triple_number_words(kel_4);
elsif kel_3=’001′ and kel_4=’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ milyar ‘||basecurr_triple_number_words(kel_2)||’ juta ‘||’ seribu ‘;
elsif kel_3=’001′ and kel_4 <>’000′ then
hasil :=basecurr_triple_number_words(kel_1)||’ milyar ‘||basecurr_triple_number_words(kel_2)||’ juta ‘||’ seribu ‘||basecurr_triple_number_words(kel_4);
elsif kel_3 <>’000′ and kel_3 =’001′ and kel_4 <>’000′ or kel_4=’000′   then
hasil :=basecurr_triple_number_words(kel_1)||’ milyar ‘||basecurr_triple_number_words(kel_2)||’ juta ‘||basecurr_triple_number_words(kel_3)||’ ribu ‘||basecurr_triple_number_words(kel_4);
end if;
end;
elsif kel_1 <>’000′ and kel_2=’000′ then
begin
if kel_3 =’000′ and kel_4=’000′ or kel_4 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ milyar ‘||basecurr_triple_number_words(kel_4);
elsif kel_3 <>’001′ or kel_3 <>’000′ and kel_4 =’000′ or kel_4 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ milyar ‘||basecurr_triple_number_words(kel_3)||’ ribu ‘||basecurr_triple_number_words(kel_4);
end if;
end;
end if;
end;
elsif length(tampung99)=15 then
begin
kel_1:=substr(tampung99,1,3);
kel_2:=substr(tampung99,4,3);
kel_3:=substr(tampung99,7,3);
kel_4:=substr(tampung99,10,3);
kel_5:=substr(tampung99,13,3);
if kel_1 <>’000′ and kel_2 <>’000′ and kel_3 <>’000′ then
begin
if kel_4 <>’000′ and kel_5 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_2)||’ milyar ‘||basecurr_triple_number_words(kel_3)||’ juta ‘||basecurr_triple_number_words(kel_4)||’ ribu ‘||basecurr_triple_number_words(kel_5);
elsif kel_4 =’000′ and kel_5 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_2)||’ milyar ‘||basecurr_triple_number_words(kel_3)||’ juta ‘||basecurr_triple_number_words(kel_5);
elsif kel_4 =’000′ and kel_5=’000′  then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_2)||’ milyar ‘||basecurr_triple_number_words(kel_3)||’ juta ‘||basecurr_triple_number_words(kel_5);
elsif kel_4=’001′ and kel_5=’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_2)||’ milyar ‘||basecurr_triple_number_words(kel_3)||’ juta ‘||’ seribu ‘;
elsif kel_4=’001′ and kel_5 <>’000′ then
hasil :=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_2)||’ milyar ‘||basecurr_triple_number_words(kel_3)||’ juta ‘||’ seribu ‘||basecurr_triple_number_words(kel_5);
elsif kel_4 <>’000′ and kel_4 =’001′ and kel_5 <>’000′ or kel_5=’000′   then
hasil :=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_2)||’ milyar ‘||basecurr_triple_number_words(kel_3)||’ juta ‘||basecurr_triple_number_words(kel_4)||’ ribu ‘||basecurr_triple_number_words(kel_5);
end if;
end;
elsif kel_1 <>’000′ and kel_2 <>’000′ and kel_3 =’000′ then
begin
if kel_4 <>’000′ and kel_5 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_2)||’ milyar ‘||basecurr_triple_number_words(kel_4)||’ ribu ‘||basecurr_triple_number_words(kel_5);
elsif kel_4 =’000′ and kel_5 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_2)||’ milyar ‘||basecurr_triple_number_words(kel_5);
elsif kel_4 =’000′ and kel_5=’000′  then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_2)||’ milyar ‘||basecurr_triple_number_words(kel_5);
elsif kel_4=’001′ and kel_5=’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_2)||’ milyar ‘||’ seribu ‘;
elsif kel_4=’001′ and kel_5 <>’000′ then
hasil :=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_2)||’ milyar ‘||’ seribu ‘||basecurr_triple_number_words(kel_5);
elsif kel_4 <>’000′ and kel_4 =’001′ and kel_5 <>’000′ or kel_5=’000′   then
hasil :=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_2)||’ milyar ‘||basecurr_triple_number_words(kel_4)||’ ribu ‘||basecurr_triple_number_words(kel_5);
end if;
end;
elsif kel_1 <>’000′ and kel_2 =’000′ and kel_3 <>’000′ then
begin
if kel_4 <>’000′ and kel_5 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_3)||’ juta ‘||basecurr_triple_number_words(kel_4)||’ ribu ‘||basecurr_triple_number_words(kel_5);
elsif kel_4 =’000′ and kel_5 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_3)||’ juta ‘||basecurr_triple_number_words(kel_5);
elsif kel_4 =’000′ and kel_5=’000′  then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_3)||’ juta ‘||basecurr_triple_number_words(kel_5);
elsif kel_4=’001′ and kel_5=’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_3)||’ juta ‘||’ seribu ‘;
elsif kel_4=’001′ and kel_5 <>’000′ then
hasil :=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_3)||’ juta ‘||’ seribu ‘||basecurr_triple_number_words(kel_5);
elsif kel_4 <>’000′ and kel_4 =’001′ and kel_5 <>’000′ or kel_5=’000′   then
hasil :=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_3)||’ juta ‘||basecurr_triple_number_words(kel_4)||’ ribu ‘||basecurr_triple_number_words(kel_5);
end if;
end;
elsif kel_1 <>’000′ and kel_2=’000′ and kel_3=’000′ then
begin
if kel_3 =’000′ and kel_4=’000′ or kel_4 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_5);
elsif kel_3 <>’001′ or kel_3 <>’000′ and kel_4 =’000′ or kel_4 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_4)||’ ribu ‘||basecurr_triple_number_words(kel_5);
end if;
end;
end if;
end;
end if;
return(hasil);
end;
/

Show Users with High CPU Processing since Instance Startup

set feed off;
set pagesize 10000;
set wrap off;
set linesize 80;
set heading on;
set tab on;
set scan on;
set verify off;
–
spool show_users_with_high_cpu_processing.lst

ttitle ‘Show Users with High CPU Processing’ -
skip 2

column user_process format a10 heading “UserProcess(SID)”
column value format 999,999,999.99

select ss.username||’(‘||se.sid||’)’ user_process, value
from v$session ss, v$sesstat se, v$statname sn
where  se.statistic# = sn.statistic#
and  name  like ‘%CPU used by this session%’
and  se.sid = ss.sid
and  ss.username is not null
order  by substr(name,1,25), value desc
/
spool off;
set feed on echo off termout on pages 24 verify on
ttitle off

Show Installation Database Version and Option

Use the following script to view database version and components that already Install :

set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
set termout on;
set serveroutput on;

ttitle left ‘Oracle Version:’ skip 2

select banner
from   sys.v$version;

ttitle left ‘Installed Options:’ skip 2

select parameter
from   sys.v$option
where  value = ‘TRUE’;

ttitle left ‘Not Installed Options:’ skip 2

select parameter
from   sys.v$option
where  value <> ‘TRUE’;

prompt
begin
dbms_output.put_line(‘Specific Port Information: ‘||dbms_utility.port_string);
end;
/
prompt

set head on feed on

Converting Columns to Rows

Suppose you want to convert an Oracle table:

(id, sum1, sum2, sum3)

into another table:

(id, ‘1′, sum1)
(id, ‘2′, sum2)
(id, ‘3′, sum3)

That means converting 1 row from the first table into 3 rows in the other table. Of course, this can be done by scanning the source table 3 times, one for each «sum» column, but if the first table is pretty large (~50 million rows), we need another, faster approach.

Solution

Using an Inline View with the UNION ALL operator, all can be done in one single step.

CREATE TABLE t1 (
id     NUMBER PRIMARY KEY,
sum1   NUMBER,
sum2   NUMBER,
sum3   NUMBER
);

INSERT INTO t1 VALUES (1,20,40,50);
INSERT INTO t1 VALUES (2,30,20,25);
INSERT INTO t1 VALUES (3,15,60,55);
COMMIT;

select * from t1;

ID       SUM1       SUM2       SUM3
———- ———- ———- ———-
1         20         40         50
2         30         20         25
3         15         60         55

CREATE TABLE t2 AS
SELECT id, num, DECODE(num,’1′,sum1,’2′,sum2,’3′,sum3) data
from t1, (SELECT ‘1′ num FROM dual UNION ALL
SELECT ‘2′ num FROM dual UNION ALL
SELECT ‘3′ num FROM dual)
/

select * from t2 order by id;

ID N       DATA
———- – ———-
1 1         20
1 2         40
1 3         50
2 1         30
2 3         25
2 2         20
3 1         15
3 3         55
3 2         60

(Source : Internet)