set linesize 80;
set pagesize 10000;
set long 50;
set echo on;

/* ****************************************************** */
/*                                                        */
/* Name:    alter_extents.sql                             */
/* Purpose: Script to spool a isting of alter table/index */
/*          command to properly put users in the correct  */
/*          maxextent                                     */
/* Creator: R.Kupcunas                                    */
/* Date:    01-Jan-98                                     */
/*                                                        */
/* ****************************************************** */


set termout on;
set echo on;
set feedback on;

col dummy     	noprint format a1;
col dummy2    	noprint format a1;
col table_name  noprint format a1;
col command   	format a70;

/* ********************************* */
/* 1. Generate drop table commands   */
/* 2. Generate create table commands */
/* ********************************* */

select 'alter table '||OWNER||'.'||SEGMENT_NAME||' storage(maxextents 500);' 
from dba_segments
where SEGMENT_TYPE = 'TABLE' and
      MAX_EXTENTS < 500     and
      OWNER not in ('SYS', 'SYSTEM')
UNION
select 'alter index '||OWNER||'.'||SEGMENT_NAME||' storage(maxextents 500);' 
from dba_segments
where SEGMENT_TYPE = 'INDEX' and
      MAX_EXTENTS < 500     and
      OWNER not in ('SYS', 'SYSTEM')
order by 1; 
       
set termout off;
set heading off;
set echo off;
set feedback off;
       
spool alter_max_extents.sql
/
spool off;

set echo on;
set feedback on;

@alter_max_extents.sql

set heading on;
set pagesize 100;
set termout on;

commit;










