oracle

AutoTrace UserTrigger Oracle (appunti grezzi)

SQL> CREATE OR REPLACE TRIGGER trace_logon
2 after logon on database
3 begin
4 if ( sys_context(‘USERENV’, ‘SESSION_USER’) in (‘N00b1′, ‘Noob2′))
5 then
6 execute immediate ‘alter session set timed_statistics=true’;
7 execute immediate ‘alter session set max_dump_file_size=10000′;
8 execute immediate ‘alter session set tracefile_identifier=”Noobs”;
9 execute immediate ‘alter session set events ”10046 trace name context forever, level 12”’;
10 end if;
11 end;
12 /

SQL> CREATE OR REPLACE TRIGGER trace_logoff before logoff on database
2 begin
3 if ( sys_context(‘USERENV’, ‘SESSION_USER’) in (‘N00b1′,’Noob2′))
4 then
5 execute immediate ‘alter session set events ”10046 trace name context off”’;
6 end if;
7 end;
8

PS:
tkprof $ORACLE_BASE/diag/rdbms/prod/prod/trace/*Noobs….* $ORACLE_HOME/*Noob….*.out sys=no print=10 sort=fchela

Rebuild index oracle (appunti grezzi)

#!/bin/sh
#rebuilder script
#trova indici e ricostruisce
#Tempesta Piergiorgio 06/05/2010
. /home/oracle/.env11g
SCRIPT_DIR=/home/oracle/Tempesta
/home/oracle/app/product/11.2.0/bin/sqlplus -s /nolog < set feed off pages 1000 lines 120 echo off verify off
connect system/"password"
spool ${SCRIPT_DIR}/allIndex.sql
select 'ALTER INDEX "' || table_owner ||'"."'|| index_name || '" REBUILD;' from all_indexes;
spool off
EOF
/bin/sed /TABLE_OWNER/d allIndex.sql > allIndex2.sql
/bin/sed /———–/d allIndex2.sql > allIndex3.sql
/home/oracle/app/product/11.2.0/bin/sqlplus -s /nolog< connect SYSTEM/"password"
spool ${SCRIPT_DIR}/risultato.out select to_char(sysdate,'DD/MM/YYYY HH24:MI:SS') from dual;
@${SCRIPT_DIR}/allIndex3.sql
select to_char(sysdate,'DD/MM/YYYY HH24:MI:SS') from dual;
spool off
EOF
exit 0