Sunday 2 February 2014

Execute os command on sql prompt

sys@> select comp_id, version from dba_registry;

COMP_ID VERSION

------------------------------ --------------------------

CATALOG 9.2.0.6.0

CATPROC 9.2.0.6.0

JAVAVM 9.2.0.6.0 (<< If not Available Then)

If you do not have java installed, your DBA can install it via initjvm.sql found in

$ORACLE_HOME/javavm/install

begin

dbms_java.grant_permission

('DILIP',

'java.io.FilePermission',

'/usr/bin/ps',

'execute');

dbms_java.grant_permission

('DILIP',

'java.lang.RuntimePermission',

'*',

'writeFileDescriptor' );

end;

begin

dbms_java.grant_permission

('DILIP',

'java.io.FilePermission',

'dir',

'execute');

dbms_java.grant_permission

('DILIP',

'java.lang.RuntimePermission',

'*',

'writeFileDescriptor' );

end;

@$ORACLE_HOME/javavm/install/initjvm

We need to start by granting some privs. I'm going to grant as little as I have to get

allow us to execute the program /usr/bin/ps. As SYS or some appropriately priveleged

user, we will execute:

begin

dbms_java.grant_permission

('DILIP',

'java.io.FilePermission',

'/bin/ls',

'execute');

dbms_java.grant_permission

('DILIP',

'java.lang.RuntimePermission',

'*',

'writeFileDescriptor' );

end;

/

PL/SQL procedure successfully completed.

ops$tkyte@ORA9I.WORLD> connect rt_test/rt_test

Connected.

ops$tkyte@ORA9I.WORLD> @login

create or replace and compile

java source named "Util"

as

import java.io.*;

import java.lang.*;



public class Util extends Object

{

public static int RunThis(String args)

{

Runtime rt = Runtime.getRuntime();

int rc = -1;



try

{

Process p = rt.exec(args);



int bufSize = 4096;

BufferedInputStream bis =

new BufferedInputStream(p.getInputStream(), bufSize);

int len;

byte buffer[] = new byte[bufSize];



// Echo back what the program spit out

while ((len = bis.read(buffer, 0, bufSize)) != -1)

System.out.write(buffer, 0, len);



rc = p.waitFor();

}

catch (Exception e)

{

e.printStackTrace();

rc = -1;

}

finally

{

return rc;

}

}

}

/

Java created.

create or replace

function RUN_CMD(p_cmd in varchar2) return number

as

language java

name 'Util.RunThis(java.lang.String) return integer';

/

Function created.

create or replace procedure RC(p_cmd in varchar2)

as

x number;

begin

x := run_cmd(p_cmd);

end;

/

Elapsed: 00:00:00.00

dilip@live > exec rc('/bin/ps -ef');

java.lang.ArrayIndexOutOfBoundsException

at Util.RunThis(Util.java:14)

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

dilip@live > create or replace and compile

2 java source named "Util"

3 as

4 import java.io.*;

5 import java.lang.*;

6

7 public class Util extends Object

8 {

9 public static int RunThis(String args)

10 {

11 Runtime rt = Runtime.getRuntime();

12 int rc = -1;

13

14 try

15 {

16 Process p = rt.exec(args);

17

18 int bufSize = 4096;

19 BufferedInputStream bis =

20 new BufferedInputStream(p.getInputStream(), bufSize);

21 int len;

22 byte buffer[] = new byte[bufSize];

23

24 // Echo back what the program spit out

25 while ((len = bis.read(buffer, 0, bufSize)) != -1)

26 System.out.write(buffer, 0, len);

27

28 rc = p.waitFor();

29 }

30 catch (Exception e)

31 {

32 e.printStackTrace();

33 rc = -1;

34 }

35 finally

36 {

37 return rc;

38 }

39 }

40 }

41 /

Java created.

Elapsed: 00:00:00.01

dilip@live > create or replace

2 function RUN_CMD(p_cmd in varchar2) return number

3 as

4 language java

5 name 'Util.RunThis(java.lang.String) return integer';

6 /

Function created.

Elapsed: 00:00:00.00

dilip@live >

dilip@live > Fnction created.

SP2-0734: unknown command beginning "Fnction cr..." - rest of line ignored.

dilip@live >

dilip@live > create or replace procedure RC(p_cmd in varchar2)

2 as

3 x number;

4 begin

5 x := run_cmd(p_cmd);

6 end;

7 /

Procedure created.

Elapsed: 00:00:00.00

dilip@live > variable x number;

dilip@live > set serveroutput on

dilip@live > exec dbms_java.set_output(100000);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

dilip@live > exec :x := RUN_CMD('/bin/ls');

BEGIN :x := RUN_CMD('/bin/ls'); END;

*

ERROR at line 1:

ORA-29549: class DILIP.Util has changed, Java session state cleared

ORA-06512: at "DILIP.RUN_CMD", line 0

ORA-06512: at line 1

Elapsed: 00:00:00.00

dilip@live > exec :x := RUN_CMD('/bin/ls');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

dilip@live > print x

X

----------

0

dilip@live > variable x number;

dilip@live > set serveroutput on

dilip@live > exec dbms_java.set_output(100000);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

dilip@live > exec :x := RUN_CMD('/bin/ls');

initdw.ora

init.ora

initorcl.ora

initORCL.ora.bak

lkORCL

orapw

spORCLinit.ora

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

dilip@live > exec :x := RUN_CMD('/bin/ls -lrt')

total 92

-rw-r--r-- 1 oralocal oinstall 12920 Mar 8 2002 initdw.ora

-rw-r--r-- 1 oralocal oinstall 8385 Mar 9 2002 init.ora

-rw-r--r-- 1 oralocal oinstall 8910 Jan 23 11:03 initORCL.ora.bak

-rw-r----- 1 oralocal oinstall 2560 Jan 23 11:56 spORCLinit.ora

-rwSr----- 1 oralocal oinstall 2560 Mar 29 10:00 orapw

-rw-r--r-- 1 oralocal oinstall 8998 Apr 12 17:15 initorcl.ora

-rw-rw---- 1 oralocal oinstall 24 Apr 26 15:58 lkORCL

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

dilip@live > exec :x := RUN_CMD('/bin/ps')

PID TTY TIME CMD

14010 ? 00:00:00 sh <defunct>

14019 ? 00:00:00 sh

14025 ? 00:00:00 sendmail <defunct>

14566 ? 00:07:17 tnslsnr

29639 ? 00:00:00 sshd

29799 ? 00:00:00 sshd

32625 ? 00:00:00 oracle

32627 ? 00:00:01 oracle

32629 ? 00:00:01 oracle

32631 ? 00:00:00 oracle

32633 ? 00:00:00 oracle

32635 ? 00:00:00 oracle

32637 ? 00:00:00 oracle

32639 ? 00:00:00 oracle

32641 ? 00:00:00 oracle

32643 ? 00:00:00 oracle

32732 ? 00:00:00 oracle

32740 ? 00:00:01 oracle

309 ? 00:00:00 oracle

347 ? 00:00:00 ps

348 ? 00:00:00 oracle

349 ? 00:00:00 oracle

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

dilip@live >

Few more useful SQL

---Remove all varchar on column
to_number(translate(mb.msisdn,'#+= <=<IDEAXXXXXXXX.?"-*%@!$%^&*$abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',' '),'999999999999')
between om.msisdn_start and om.msisdn_end

create materialized view dilip.DILIP_user_mv
tablespace DILIP
refresh complete
start with sysdate
next trunc(sysdate)+1+2/24+35/(24*60)
----enable query rewrite
as
SELECT USER_INFO_ID,POINTS_BALANCE,LOGIN_ID,rnk FROM
(
SELECT user_info_id, points_balance, login_id, RANK() OVER ( ORDER BY points_balance DESC) rnk
FROM dilip.DILIP_user, dilip.DILIP_user123
WHERE user_info_id=id
)
WHERE rownum<=10

CREATE MATERIALIZED VIEW DILIP_LOG_MV
TABLESPACE TEST BUILD IMMEDIATE REFRESH force
START WITH sysdate
NEXT trunc(sysdate)+1+2/24+35/(24*60)
AS
SELECT OPERATOR_ID,CIRCLE_ID,KEYWORD,COUNT(MSISDN)
TOTAL_DOWNLOAD_COUNT ,COUNT(DISTINCT MSISDN)
UNIQUE_USER_COUNT,trunc(RECVAT) recvat
FROM DILIP_LOG_report
WHERE trunc(RECVAT) between trunc(sysdate-60)
and trunc(sysdate-1) GROUP BY OPERATOR_ID,CIRCLE_ID,
KEYWORD,trunc(RECVAT)

----Duplicate rows calculation based on combination of columns
--- Good performance most of time if we have index created on ------ columns inside where clause. select count(*) from hw_content_master t1
where exists (select 'x' from dilip_master t2
where t1.cont_id = t2.cont_id
and t1.rowid > t2.rowid
-- and t1.active =1
)

----Rank Query
select mb.* from
(
SELECT user_info_id, points_balance, login_id, RANK() OVER ( ORDER BY points_test DESC) rnk
FROM dilip_user a, user_info b
WHERE a.user_info_id=b.id
) mb
mb.rownum < 11

---- All Foreign key reference on specific table

select
a.tt,
a.owner,
b.table_name,
a.constraint_name,
b.column_name,
b.position,
a.r_constraint_name,
c.column_name,
c.position,
c.table_name r_table_name,
a.r_owner
from
(select
owner,
constraint_name,
r_constraint_name,
r_owner,1 tt
from
dba_constraints
where
owner=upper('&&owner')
and table_name=upper('&&table_name')
and constraint_type!='C'
union
select
owner,
constraint_name,
r_constraint_name,
r_owner,2
from
dba_constraints
where
(r_constraint_name,r_owner) in
(select
constraint_name,
owner
from
dba_constraints
where
owner=upper('&owner')
and table_name=upper('&table_name'))
) a,
dba_cons_columns b,
dba_cons_columns c
where
b.constraint_name=a.constraint_name
and b.owner=a.owner
and c.constraint_name=a.r_constraint_name
and c.owner=a.r_owner
and b.position=c.position
order by 1,2,3,4,5

---Traverse from child through parent

select * from (
SELECT rpad('*',2*level,'*') || username ,sys_connect_by_path( userid, '/' )rownum1 ,level rank,userid,username,user_level_higher
from dilip_users --where user
start with userid=10001 ---10001
CONNECT BY userid= prior user_level_higher
) --where userid = 10002

where rank = 2

Manual Audit using logon Trigger

This is useful PL/SQL block to enable manual auditing and can customized on so many level.

Condition check can executed on:

USER matching
Table Matching
Host Matching etc.

CREATE OR REPLACE TRIGGER audit_ddl_changes
AFTER create OR drop OR alter
ON DILIP.SCHEMA -- Change SCOTT to your schema name!!!
-- ON DATABASE
BEGIN
INSERT INTO dll_audit_log VALUES
(SYSDATE,
SYS_CONTEXT('USERENV', 'SESSION_USER'),
SYS_CONTEXT('USERENV', 'OS_USER'),
SYS_CONTEXT('USERENV', 'HOST'),
SYS_CONTEXT('USERENV', 'TERMINAL'),
ORA_SYSEVENT,
ORA_DICT_OBJ_TYPE,
ORA_DICT_OBJ_NAME
);
END;

Create Or Replace Trigger CheckDILIP_Logon
After logon on database
Begin
if ( user='DILIP') then

-- execute immediate 'alter session set sql_trace=true';

---execute immediate 'alter session set cursor_sharing = FORCE';
End if;
end;

exec perfstat.statspack.snap

PL/SQL procedure successfully completed.

SQL> @?/rdbms/admin/spreport

CREATE or replace TRIGGER audit_DILIP
After logon on database
Begin
if ( user='DILIP') then
INSERT INTO sms_log VALUES
(SYSDATE,
SYS_CONTEXT('USERENV', 'SESSION_USER'),
SYS_CONTEXT('USERENV', 'OS_USER'),
SYS_CONTEXT('USERENV', 'HOST'),
SYS_CONTEXT('USERENV', 'TERMINAL'),
ORA_SYSEVENT,
ORA_DICT_OBJ_NAME
);
end if;
END;

CREATE OR REPLACE TRIGGER block_tools_from_prod
AFTER LOGON ON DATABASE
DECLARE
v_prog sys.v_$session.program%TYPE;
BEGIN
SELECT program INTO v_prog
FROM sys.v_$session
WHERE audsid = USERENV('SESSIONID')
AND audsid != 0 -- Don't Check SYS Connections
AND rownum = 1; -- Parallel processes will have the same AUDSID's

IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR -- Toad
UPPER(v_prog) LIKE '%SQLNAV%' OR -- SQL Navigator
UPPER(v_prog) LIKE '%PLSQLDEV%' OR -- PLSQL Developer
UPPER(v_prog) LIKE '%BUSOBJ%' OR -- Business Objects
UPPER(v_prog) LIKE '%EXCEL%' -- MS-Excel plug-in
THEN
RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed on PROD DB!');
END IF;
END;

/

Explore more functionaly of SYS_CONTEXT

https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions184.htm#SQLRF06117

Ftp using valid user name and Password shell script

[oracle@localhost visitor_log]$ cat cmd12.sh
export COMMAND=/data1/oracle/dilip/log/cmd11.sh
YESTERDAY=$(date --date "1 days ago" +%d%m%y)
FILE=visitlog_$YESTERDAY.txt
REMOTE_ADD=/usr/local/apache/htdocs/mycmp/Log/
export LOCALDIR=/data1/oracle/dilip/log
cd /data1/oracle/dilip/log
echo "#!/usr/bin/expect --" > $COMMAND
echo >> $COMMAND
echo >> $COMMAND
echo >> $COMMAND
echo >> $COMMAND
echo "set timeout 60" >> $COMMAND
echo "log_file $LOCALDIR/ftp.log" >> $COMMAND
echo "spawn ftp XXX.XXX.XXX.XXX" >> $COMMAND
echo "expect \"Name:\"" >> $COMMAND
echo "send \"www1\\r\"" >> $COMMAND
echo "expect \"Password:\"" >> $COMMAND
#echo "sleep 40" >> $COMMAND
echo "send \"passphase\\r\"" >> $COMMAND
echo "expect \"ftp>\"" >> $COMMAND
echo "send \"lcd $LOCALDIR\\r\"" >> $COMMAND
echo "expect \"ftp>\"" >> $COMMAND
echo "send \"cd $REMOTE_ADD\\r\"" >> $COMMAND
echo "expect \"ftp>\"" >> $COMMAND
echo "send \"get $DATAFILE\\r\"" >> $COMMAND
#echo "sleep 500" >> $COMMAND
echo "set timeout -1" >> $COMMAND
echo "expect \"ftp>\"" >> $COMMAND
echo "send \"bye\\r\"" >> $COMMAND
#echo "sleep 120" >> $COMMAND
echo exit >> $COMMAND
chmod 755 $COMMAND
$COMMAND

ENDTIME=`date '+%s'`
echo "Total Time taken `expr $ENDTIME - $STARTTIME` Seconds"

#!/usr/bin/expect --
cat $FILE> VISIT_LOG_BIG

[oracle@localhost visitor_log]$

47 01 * * * sh /data1/oracle/dilip/visit_log/cmd12.sh

Return Comma Separated value as Integer and Varchar

--Return integer as result
create type myScalarType as object
( dp int)
/

create type myTableType as table of myScalarType
/

create or replace function parse( p_dp in varchar2 )
return myTAbleType
PIPELINED
as
l_dp long := p_dp || ',';
n number;
l_rec myScalarType := myScalarType(null);
begin
loop
n := instr( l_dp, ',' );
if ( nvl(n,0) > 0 )
then
l_rec.dp := substr( l_dp, 1, n-1 );
l_dp := substr( l_dp, n+1 );
else
l_rec.dp := null;
end if;
exit when l_rec.dp is null ;
pipe row( l_rec );
end loop;
return;
end;
/

Test

SQL> select * from TABLE( parse('1,1,1,1,2,2,3,3,3,1,1,4,4'))

---- Return Varchar2 values as result
CREATE TYPE MYSCALARTYPE_V AS OBJECT
( DP VARCHAR2(200))
/

CREATE OR REPLACE TYPE MYTABLETYPE_V AS TABLE OF MYSCALARTYPE_V
/

create function Get_var_to_var( p_dp in long )
return myTableType_v
PIPELINED
as
l_dp long := p_dp || ',';
n number;
l_rec MYSCALARTYPE_V := MYSCALARTYPE_V(null);
begin
loop
n := instr( l_dp, ',' );
if ( nvl(n,0) > 0 )
then
l_rec.dp := substr( l_dp, 1, n-1 );
l_dp := substr( l_dp, n+1 );
else
l_rec.dp := null;
end if;
exit when l_rec.dp is null ;
---dbms_output.put_line(l_rec.dp);
pipe row( l_rec );
end loop;
return;
end;

TestSQL> select * from TABLE(Get_var_to_var('dilip,sagar,dil'));

Send mail In PL/SQL Code

Send mail using PLSQL code:

Prerequisite are:

smpt configuration needed.

declare
mailhost varchar2(50) :='XXX.XXX.XXX.XXX';
port number := 25;
mail_conn utl_smtp.connection;
sender varchar2(20) := 'dilip';
recepient varchar2(40) := 'XXXXXXX@XXXXX.com';
crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
message1 varchar2(1000);
message2 varchar2(1000);
--cursor c1 is select * from tab where rownum<=5;
begin
mail_conn:=utl_smtp.open_connection(mailhost,port);
message1:='Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' )|| crlf ||
'From:'||Sender|| crlf ||
'Subject: First test mail' || crlf ||'';
message2:='Hi using utl_smtp for the first time in my life'||crlf;
for i in (select * from tab where rownum<=5) loop
message2:=message2||i.tname||crlf;
end loop;
utl_smtp.helo(mail_conn,mailhost);
utl_smtp.mail(mail_conn,sender);
utl_smtp.rcpt(mail_conn,recepient);
utl_smtp.data(mail_conn,message1||crlf||message2);
utl_smtp.quit(mail_conn);
end;

Tracing Using Session level Trigger

Use Case: We need to tracker number of users Or wanted to load CONTEXT, this is good way to achieve it.

May times issue we just wanted load some parameter for User or Login level, this is Handy trigger to do. Many level this trigger ca be used, Audit etc..

CREATE OR REPLACE TRIGGER trace_trigger
AFTER LOGON
ON DATABASE
DECLARE
sqlstr VARCHAR2(2000) := 'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 4''';
BEGIN
IF (USER = 'SCOTT') THEN
execute immediate sqlstr;
--Direct immediate statements
EXECUTE IMMEDIATE 'ALTER SESSION SET TRACEFILE_IDENTIFIER = ''ENABLE_TRACE_DILIP''';
EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
END IF;
END trace_trigger;
/

Dilip's Oracle DBA Blog

Menu