How to generate dbms_application_info settings together with DDL in a sql script

Many DBAs forget to incoporate usage of dbms_application_info into their own scipts. This is particulary important if you start long-running maintenance jobs that could last for days or weeks.

By sending MODULE and ACTION to the databaser server, you allow for more granular searching using v$session, end-to-end tracing using trcsess. In addtion, more information is collected by any ongoing auditing so it's easier to see who did what later.

Here is a sniplet that can be used when generating a DDL script for multiple objects.

accept table_owner prompt 'Table owner: '
accept table_name prompt 'Table name: '
accept new_tabspc prompt 'New tablespace: '
accept old_tabspc prompt 'Old tablespace: '

set lines 300
set heading off
set feedback off
set verify off
set echo off
set pages 0
set trimspool on
spool exec_move_lob_&&table_owner..&&table_name..sql
select 'alter session set nls_language=''american'';' from dual;
select 'alter session force parallel ddl;' from dual;
select 'set timing on' from dual;
select 'set lines 200' from dual;
select 'set trimspool on' from dual;
select 'spool exec_move_lob_&&table_owner..&&table_name..log' from dual;
select 'exec DBMS_APPLICATION_INFO.SET_MODULE(module_name=>''Move'', action_name=>''MoveLOB_&&table_name'');' from dual;
select 'exec DBMS_APPLICATION_INFO.SET_CLIENT_INFO (client_info=>''MoveLOB_&&table_name'');' from dual;
select 'exec DBMS_SESSION.SET_IDENTIFIER (client_id=>''MoveLOB_&&table_name''); ' from dual;

-- your DDL statements generated here, for example:
select 'alter table ' || owner || '.' || table_name || ' move tablespace data2 online;'
from dba_tables
where owner='&&table_owner';

select 'exit' from dual;
exit