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’)
and U.username=Q.username(+) and U.default_tablespace=Q.tablespace_name(+)
;
set pagesize 100
set escape off
Archive for April 3, 2013
Script to extract create users script from the database for all the users
Posted: April 3, 2013 in DBA Useful ScriptsTags: Create user, oracle, script for create user, script to extract create user
0
Query to count failed log in attempts
Posted: April 3, 2013 in DBA Useful ScriptsTags: failed login, failed login attempts, FAILED_LOGIN_ATTEMPTS, lcount, oracle, profile
— to count failed log in attempts:
SELECT name, lcount FROM user$ WHERE lcount <> 0;