Script to get the USER CREATION SCRIPT in the database:
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)
||';' "Create user scripts"
from dba_users U, dba_ts_quotas Q
where U.username = '<USERNAME>'
and U.username=Q.username(+) and U.default_tablespace=Q.tablespace_name(+);
All you need to do is to replace the <USERNAME> with the user want to get the creation script.
This is handy especially when you want to do migration to a new environment. It will tell you the default tablespace the user is using so that you will recreate the tablespace in the new environment if not already existing. Also, it displays the profile the user is using and TEMP tablespace.
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)
||';' "Create user scripts"
from dba_users U, dba_ts_quotas Q
where U.username = '<USERNAME>'
and U.username=Q.username(+) and U.default_tablespace=Q.tablespace_name(+);
All you need to do is to replace the <USERNAME> with the user want to get the creation script.
This is handy especially when you want to do migration to a new environment. It will tell you the default tablespace the user is using so that you will recreate the tablespace in the new environment if not already existing. Also, it displays the profile the user is using and TEMP tablespace.
Comments
Post a Comment