users parus SQL create Oracle
*import
set pagesize 0
set linesize 200
spool import_users.sql
SELECT 'CREATE USER '||u.username||' PROFILE "DEFAULT"'
||' IDENTIFIED BY VALUES '''||PASSWORD||''''
||' DEFAULT TABLESPACE "PARUS" TEMPORARY TABLESPACE "PRS_TMP" ACCOUNT '
||(case
when u.account_status='OPEN' then 'UNLOCK'
when u.account_status='LOCKED' then 'LOCK'
end)
||chr(13)||chr(10)||'/'||chr(13)||chr(10)
||'GRANT "CONNECT" TO "'||username||'"'
||chr(13)||chr(10)||'/'||chr(13)||chr(10)
||(case
when u.account_status='OPEN' then 'ALTER USER '||username||' ACCOUNT UNLOCK'
when u.account_status='LOCKED' then 'ALTER USER '||username||' ACCOUNT LOCK'
end)
||chr(13)||chr(10)||'/'||chr(13)||chr(10)
||'ALTER USER '||username
||' IDENTIFIED BY VALUES '''||PASSWORD||''''
||chr(13)||chr(10)||'/'||chr(13)||chr(10) "SQL"
FROM dba_users u
WHERE username NOT IN ('OUTLN','XDB','ANONYMOUS','DBSNMP','ORDPLUGINS','PARUS')
and username NOT LIKE '%SYS%'
union
select 'exit' "--"
from dual
order by 1
;
exit
