Re: Default/Temp tablespaces

"Sid_James" <sid.james_at_virgin.net> wrote in message news:3972287F.1CFF_at_virgin.net...
> Hi.
>
> About a 100 of our users have been given SYSTEM as their default user
> and temporary tablespaces! Is their an automated way that can change
> these to the USER and TEMP tablespaces instead?
>
> Thanks.
>
> Ed.

Use the following script

set heading off feedback off pagesize 0 newpage 0 spool temp.sql
select 'alter user ||username||' default tablespace users '|| 'temporary tablespace temp;'
from dba_users
where default_tablespace = 'SYSTEM'
and temporary_tablespace = 'SYSTEM'
/
spool off
set feedback on
spool temp.log
@temp.sql
exit

Hth,

Sybrand Bakker, Oracle DBA Received on Mon Jul 17 2000 - 00:00:00 CDT