Code Generation Example sql

SET FEEDBACK OFF

spool gen_gen.sql
SELECT
       'SPOOL '||owner||'_REBUILD.sql'||CHR(10)||
       'PROMPT PROMPT START WORKING ON '||owner||CHR(10)||
       'PROMPT ALTER TABLE '||owner||'.SGA_W_PSMS_SUBSCRIBER ENABLE ROW MOVEMENT ;;' ||CHR(10)||
       'PROMPT ALTER TABLE '||owner||'.SGA_W_PSMS_SUBSCRIBER MOVE;;' ||CHR(10)||
       'PROMPT PROMPT Finished Rebuild Table'||CHR(10)||
       'PROMPT ALTER INDEX '||owner||'.SSU_PSMS_MSISDN_IDX REBUILD ONLINE;;'||CHR(10)||
       'PROMPT ALTER INDEX '||owner||'.SSU_PSMS_IMSI_IDX REBUILD ONLINE;;'||CHR(10)||
       'PROMPT ALTER INDEX '||owner||'.CURRENT_COUNTRY_INX REBUILD ONLINE;;'||CHR(10)||
       'PROMPT PROMPT Finished Rebuild Indexes'||CHR(10)||
       'PROMPT BEGIN'||CHR(10)||
       'PROMPT   DBMS_STATS.unlock_table_stats(ownname => '''||owner||''', tabname => ''SGA_W_PSMS_SUBSCRIBER'');;'||CHR(10)||
       'PROMPT   DBMS_STATS.gather_table_stats(ownname => '''||owner||''', tabname => ''SGA_W_PSMS_SUBSCRIBER'');;'||CHR(10)||
       'PROMPT   DBMS_STATS.gather_index_stats(ownname => '''||owner||''', indname => ''SSU_PSMS_IMSI_IDX'');;'||CHR(10)||
       'PROMPT   DBMS_STATS.gather_index_stats(ownname => '''||owner||''', indname => ''SSU_PSMS_MSISDN_IDX'');;'||CHR(10)||
       'PROMPT   DBMS_STATS.gather_index_stats(ownname => '''||owner||''', indname => ''CURRENT_COUNTRY_INX'');;'||CHR(10)||
       'PROMPT   DBMS_STATS.lock_table_stats(ownname => '''||owner||''', tabname => ''SGA_W_PSMS_SUBSCRIBER'');;'||CHR(10)||
       'PROMPT END;;'||CHR(10)||
       'PROMPT /'||CHR(10)||
       'PROMPT PROMPT Finished Gather Table Stats'||CHR(10)||
       'PROMPT PROMPT SUCCESS!!! Finished Working on '||owner||CHR(10)||
       'PROMPT EXIT;;'
FROM DBA_SEGMENTS
WHERE segment_type = 'TABLE'
  AND segment_name = 'SGA_W_PSMS_SUBSCRIBER'
  AND owner LIKE '%USER_A%'
  AND ROUND(bytes/1024/1024) > 0
ORDER BY ROUND(bytes/1024/1024) ASC;

PROMPT  EXIT;;
spool off

@gen_gen.sql

gen_gen.sql
SPOOL USER_A_REBUILD.sql
PROMPT PROMPT START WORKING ON USER_A
PROMPT ALTER TABLE USER_A.SGA_W_PSMS_SUBSCRIBER ENABLE ROW MOVEMENT ;;
PROMPT ALTER TABLE USER_A.SGA_W_PSMS_SUBSCRIBER MOVE;;
PROMPT PROMPT Finished Rebuild Table
PROMPT ALTER INDEX USER_A.SSU_PSMS_MSISDN_IDX REBUILD ONLINE;;
PROMPT ALTER INDEX USER_A.SSU_PSMS_IMSI_IDX REBUILD ONLINE;;
PROMPT ALTER INDEX USER_A.CURRENT_COUNTRY_INX REBUILD ONLINE;;
PROMPT PROMPT Finished Rebuild Indexes
PROMPT BEGIN
PROMPT   DBMS_STATS.unlock_table_stats(ownname => 'USER_A', tabname => 'SGA_W_PSMS_SUBSCRIBER');;
PROMPT   DBMS_STATS.gather_table_stats(ownname => 'USER_A', tabname => 'SGA_W_PSMS_SUBSCRIBER');;
PROMPT   DBMS_STATS.gather_index_stats(ownname => 'USER_A', indname => 'SSU_PSMS_IMSI_IDX');;
PROMPT   DBMS_STATS.gather_index_stats(ownname => 'USER_A', indname => 'SSU_PSMS_MSISDN_IDX');;
PROMPT   DBMS_STATS.gather_index_stats(ownname => 'USER_A', indname => 'CURRENT_COUNTRY_INX');;
PROMPT   DBMS_STATS.lock_table_stats(ownname => 'USER_A', tabname => 'SGA_W_PSMS_SUBSCRIBER');;
PROMPT END;;
PROMPT /
PROMPT PROMPT Finished Gather Table Stats
PROMPT PROMPT SUCCESS!!! Finished Working on USER_A
PROMPT EXIT;;

USER_A_REBUILD.sql

PROMPT START WORKING ON USER_A

ALTER TABLE USER_A.SGA_W_PSMS_SUBSCRIBER ENABLE ROW MOVEMENT ;

ALTER TABLE USER_A.SGA_W_PSMS_SUBSCRIBER MOVE;

PROMPT Finished Rebuild Table

ALTER INDEX USER_A.SSU_PSMS_MSISDN_IDX REBUILD ONLINE;

ALTER INDEX USER_A.SSU_PSMS_IMSI_IDX REBUILD ONLINE;

ALTER INDEX USER_A.CURRENT_COUNTRY_INX REBUILD ONLINE;

PROMPT Finished Rebuild Indexes

BEGIN

DBMS_STATS.unlock_table_stats(ownname => 'USER_A', tabname => 'SGA_W_PSMS_SUBSCRIBER');

DBMS_STATS.gather_table_stats(ownname => 'USER_A', tabname => 'SGA_W_PSMS_SUBSCRIBER');

DBMS_STATS.gather_index_stats(ownname => 'USER_A', indname => 'SSU_PSMS_IMSI_IDX');

DBMS_STATS.gather_index_stats(ownname => 'USER_A', indname => 'SSU_PSMS_MSISDN_IDX');

DBMS_STATS.gather_index_stats(ownname => 'USER_A', indname => 'CURRENT_COUNTRY_INX');

DBMS_STATS.lock_table_stats(ownname => 'USER_A', tabname => 'SGA_W_PSMS_SUBSCRIBER');

END;

/

PROMPT Finished Gather Table Stats

PROMPT SUCCESS!!! Finished Working on USER_A

EXIT;