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

/* ****************************************************** */
/*                                                        */
/* Name:    alter_users_ps.sql                            */
/* Purpose: Script to spool a isting of alter user        */
/*          command to properly put users in the correct  */
/*          tablespace                                    */
/* 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 USER '||username||' DEFAULT TABLESPACE PSUSER TEMPORARY TABLESPACE PSTEMP;' 
from dba_users
where (DEFAULT_TABLESPACE = 'SYSTEM' or
      TEMPORARY_TABLESPACE = 'SYSTEM') and
username not in ('PS', 'QDBA', 'SYS', 'SYSADM', 'SYSTEM')
order by 1; 
       
set termout off;
set heading off;
set echo off;
set feedback off;
       
spool alter_users_ps.rslt
/
spool off;
set heading on;
set pagesize 100;
set termout on;

commit;










