Generate the DDL of users in oracle 8i and 9i

select 'alter user '||username||' profile default;'||'

alter user '||username||' identified by values '''||b. PASSWORD||''';'||'

alter user '||username||' profile '||PROFILE||';'

from dba_users,sys.user$ b

where username in (select username from dba_users)

and username=b.name;

----

set pagesize 0

set escape on

select 'create user ' || U.username || ' identified ' ||

DECODE(password,

      NULL, 'EXTERNALLY',

      ' by values ' || '''' || password || ''''

      )

|| chr(10) ||

'default tablespace ' || default_tablespace || chr(10) ||

'temporary tablespace ' || temporary_Tablespace || chr(10) ||

' profile ' || profile || chr(10) ||

'quota ' ||

decode ( Q.max_bytes, -1, 'UNLIMITED', NULL, 'UNLIMITED', Q.max_bytes) ||

' on ' || default_tablespace ||

decode (account_status,'LOCKED', ' account lock',

                                                'EXPIRED', ' password expire',

                                                'EXPIRED \& LOCKED', ' account lock password expire',

                                                null)

||

';'

from dba_users U, dba_ts_quotas Q

-- Comment this clause out to include system & default users

where U.username not in ('SYS','SYSTEM',

'SCOTT','DBSNMP','OUTLN','WKPROXY','WMSYS','ORDSYS','ORDPLUGINS','MDSYS','CTXSYS','XDB','ANONYMOUS','OWNER','WKSYS','ODM_MTR','ODM','OLAPSYS','HR','OE','PM','SH','QS_ADM','QS','QS_WS','QS_ES','QS_OS','QS_CBADM','QS_CB','QS_CS','PERFSTAT') and U.username=Q.username(+) and U.default_tablespace=Q.tablespace_name(+);

set pagesize 100

set escape off