Category Archives: Procedures

Trigger_send_mail_on_delete

REM ################################################
REM # Creator: Vincent Fenoll
REM # Created: 2010/02/15
REM # Name: Trigger_send_mail.sql
REM ################################################
REM #
REM # Compatible: Oracle 10g 11g
REM#
REM ################################################
REM #
REM # Configure and send an email after deleting a
REM # row in a table: use an AFTER DELETE trigger
REM #
REM # change: 	myschema, mytrigger, mytable
REM #		myrecipient1@company.com
REM #		myrecipient2@company.com
REM #		mysender@company.com
REM ################################################


sqlplus / as sysdba

@?/rdbms/admin/utlsmtp.sql
@?/rdbms/admin/prvtsmtp.plb
@?/rdbms/admin/utlmail.sql
@?/rdbms/admin/prvtmail.plb
GRANT EXECUTE ON UTL_MAIL TO PUBLIC;

ALTER SESSION SET CURRENT SCHEMA=myschema;


CREATE OR REPLACE TRIGGER myschema.mytrigger
AFTER DELETE ON myschema.mytable REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
begin

        EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''127.0.0.1''';
          UTL_MAIL.send(sender => 'mysender@company.com',
                 recipients => 'myrecipient1@company.com',
                 cc => 'myrecipient2@company.com',
                        subject => 'Myapplication: A row has been deleted in the table mytable',
                        message => 'A row has been deleted in the table mytable.'       ||  utl_tcp.CRLF ||  utl_tcp.CRLF ||
                '   Customer informations: ' ||  utl_tcp.CRLF ||
                '       Name: ' || :old.Name                                ||  utl_tcp.CRLF ||
                '       Country  : ' || :old.COUNTRY                                ||  utl_tcp.CRLF || utl_tcp.CRLF ||
                '   Technical informations: ' ||  utl_tcp.CRLF ||
                '       DB Name: ' || SYS_CONTEXT('USERENV','DB_NAME')                ||  utl_tcp.CRLF ||
                '       OS USER: ' || SYS_CONTEXT('USERENV','OS_USER')                 ||  utl_tcp.CRLF ||
                '       Session ID: ' || SYS_CONTEXT('USERENV','SESSIONID')                ||  utl_tcp.CRLF ||
                '       Session User: ' || SYS_CONTEXT('USERENV','SESSION_USER')        ||  utl_tcp.CRLF ||
                '       IP Address: ' ||  SYS_CONTEXT('USERENV','IP_ADDRESS')        ||  utl_tcp.CRLF ||
                '       Terminal: ' || SYS_CONTEXT('USERENV','TERMINAL')                 ||  utl_tcp.CRLF ||
                '       Is DBA: ' || SYS_CONTEXT('USERENV','ISDBA') ,
                      mime_type => 'text; charset=us-ascii');
end;
/

Moving tables and indexes

REM ################################################
REM # Creator: Vincent Fenoll
REM # Created: 2010/01/22
REM # Name: Procedure Move tables and indexes
REM  ################################################
REM #
REM # Compatible: Oracle 9i 10g 11g
REM#
REM ################################################
REM #
REM # Move Tables & Indexes from “current_tablespace” to “new_tablespace” for the “myschema” user
REM # Change/Replace: myschema, new_tablespace, current_tablespace
REM #
REM ################################################

sqlplus “/ as sysdba”

REM *********************************************************************
REM MOVES
REM *********************************************************************

REM Moving tables and indexes
set head off feed off echo off pages 2000 lines 150
spool move.sql

REM Tables
select ‘ALTER TABLE myschema.’ || table_name || ‘ move tablespace new_tablespace;’ from dba_tables where owner=’myschema’ and tablespace_name=’current_tablespace’;

REM Indexes
select ‘ALTER INDEX myschema.’ || index_name || ‘ rebuild tablespace new_tablespace online;’ from dba_indexes where owner=’myschema’ and index_type<>’LOB’ and tablespace_name=’current_tablespace’;

REM LOB Data & indexes
select ‘ALTER TABLE myschema.’ || table_name || ‘ MOVE LOB(‘ || column_name || ‘)
STORE AS ‘ || segment_name || ‘ (TABLESPACE new_tablespace);’from dba_lobs where index_name like ‘SYS_IL%$$’ and owner=’myschema’ and tablespace_name=’current_tablespace’;

REM  Rebuild of UNUSABLE indexes
select ‘ALTER INDEX myschema.’ || index_name || ‘ rebuild online;’ from dba_indexes where owner=’myschema’ and status=’UNUSABLE’;

spool off
exit

————————————————————————-
— $vi move.sql
— Delete all extra lines
————————————————————————-

sqlplus “/ as sysdba”
SQL>@move.sql

REM *********************************************************************
REM VERIFICATIONS
REM *********************************************************************

— I verify that all the tables and indexes have been moved to the new tablespace
select distinct TABLESPACE_NAME from dba_tables where owner=’myschema’;
select distinct TABLESPACE_NAME from dba_indexes where owner=’myschema’;

— I verify that all indexes are VALID
select distinct status from dba_indexes where owner=’myschema’;

REM *********************************************************************
REM UPDATE STATS
REM *********************************************************************

exec sys.dbms_stats.gather_schema_stats(‘myschema’, NULL, FALSE, ‘FOR ALL INDEXED COLUMNS’, NULL, ‘DEFAULT’, TRUE);

REM *********************************************************************
REM Grants for future objets
REM *********************************************************************

alter user myschema default tablespace new_tablespace;
alter user myschema quota unlimited on new_tablespace;