/* Query written by Edward Stoever 
   Copyright March 23, 2004 */

/* This query will create the necessary DDL to change tables, indexes 
   and LOBs so that the max_extents is 10 extents greater than extents, 
   to ensure controlled growth on an Oracle Database. */
   
SET pagesize 200
COL owner format a15
COL ddl_command format a100
COL segment_name format a30
BREAK on owner skip 2 on segment_type
SPOOL v:\extents.log

SELECT '--',  owner, segment_name, segment_type, max_extents, extents, difference, chr(10),
         ddl_command
    FROM (SELECT owner, segment_name, segment_type, max_extents, extents,
                 max_extents - extents AS difference,
                 DECODE (SIGN ((max_extents - extents) - 10),
                         -1, 'alter '
                          || LOWER (segment_type)
                          || ' '
                          || owner
                          || '.'
                          || segment_name
                          || ' storage (maxextents '
                          || TO_CHAR (  max_extents
                                      + (10 - (max_extents - extents))
                                     )
                          || ');',
                         NULL
                        ) AS ddl_command
            FROM dba_segments
           WHERE max_extents > 10
             AND max_extents != 2147483645
             AND extents > 50
             AND segment_type != 'LOBSEGMENT'
             AND segment_type != 'ROLLBACK'
             AND owner NOT LIKE 'OPS%'
          UNION ALL
          SELECT owner, segment_name, segment_type, max_extents, extents,
                 max_extents - extents AS difference,
                 DECODE (SIGN ((max_extents - extents) - 10),
                         -1, 'alter table '
                          || (SELECT owner || '.' || table_name
                                FROM dba_lobs
                               WHERE segment_name = dba_segments.segment_name)
                          || ' modify lob ('
                          || (SELECT column_name
                                FROM dba_lobs
                               WHERE segment_name = dba_segments.segment_name)
                          || ')(storage (maxextents '
                          || TO_CHAR (  max_extents
                                      + (10 - (max_extents - extents))
                                     )
                          || '));',
                         NULL
                        ) AS ddl_command
            FROM dba_segments
           WHERE max_extents > 10
             AND max_extents != 2147483645
             AND extents > 50
             AND segment_type = 'LOBSEGMENT'
             AND owner NOT LIKE 'OPS%')
ORDER BY difference ASC;

EXIT