Skip to content
July 8, 2015 / doganay

GENERATE USER CREATE SCRIPT (COMPLETE)

added quota part:
select ‘alter user ‘||username||’ quota ‘||decode(max_bytes,-1,’UNLIMITED’,max_bytes)||’ on ‘||tablespace_name||’;’ from DBA_ts_quotas
where username=upper(trim(‘&&USERNAME’))

Mustafa DOGANAY's Blog

undefine USERNAME prompt prompt COMPLETE USER CREATE GENERATION SCRIPT by prompt MUSTAFA DOGANAY prompt http://doganay.worpress.com prompt accept USERNAME prompt 'USERNAME......: ' set linesize 1000 pages 1000 feedback off select chr(10)||'CREATE USER '||u1.name||chr(10)|| 'IDENTIFIED BY VALUES '''||u1.password||''''||chr(10)|| 'DEFAULT TABLESPACE '||t1.name||chr(10)|| 'TEMPORARY TABLESPACE '||t2.name||chr(10)|| 'PROFILE '||u2.profile||chr(10)|| decode(astatus,0,'ACCOUNT UNLOCK',1,'ACCOUNT UNLOCK PASSWORD EXPIRE',8,'ACCOUNT LOCK',9,'ACCOUNT LOCK PASSWORD EXPIRE','')||';'||chr(10) from sys.user$ u1, dba_users u2, sys.ts$ t1, sys.ts$ t2 where u1.user#=u2.user_id and u1.DATATS#=t1.ts# and u1.TEMPTS#=t2.ts# and u1.name=upper(trim('&&USERNAME')) -- -- union all -- -- select 'grant sysdba to '||username||';' from v$pwfile_users where username=upper(trim('&&USERNAME')) and sysdba='TRUE' union all select 'grant sysoper to '||username||';' from v$pwfile_users where username=upper(trim('&&USERNAME')) and sysoper='TRUE' union all select 'grant sysasm to '||username||';' from v$pwfile_users where username=upper(trim('&&USERNAME')) and sysasm='TRUE' union all select '' from dual union all -- -- select 'grant '||granted_role||' to '||grantee||decode(admin_option,'YES',' with admin option','')||';'||chr(10) from dba_role_privs where grantee=upper(trim('&&USERNAME')) -- -- union all -- -- SELECT 'ALTER USER ' || name || ' DEFAULT…

View original post 259 more words

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: