Cool Scripts

Cool script to know the details of a Particular user.

+++++++++++++++++++++++++++++++++++++++++++++++
-- user_conf.sql

set lines 100 pages 999
set verify off
set feedback off

undefine user

accept userid prompt 'Enter username:'

select username
, default_tablespace
, temporary_tablespace
from dba_users
where username = '&userid'
/

select tablespace_name
, decode(max_bytes, -1, 'unlimited'
, ceil(max_bytes / 1024 / 1024) || 'M' ) "QUOTA"
from dba_ts_quotas
where username = upper('&&userid')
/

select granted_role || ' ' || decode(admin_option, 'NO', '', 'YES', 'with admin option')

"ROLE"
from dba_role_privs
where grantee = upper('&&userid')
/

select privilege || ' ' || decode(admin_option, 'NO', '', 'YES', 'with admin option')

"PRIV"
from dba_sys_privs
where grantee = upper('&&userid')
/

undefine user
set verify on
set feedback on

+++++++++++++++++++++++++++++++++++++++++++++++

Cool script to CLONE a Particular user.

+++++++++++++++++++++++++++++++++++++++++++++++
-- user_clone.sql

set lines 999 pages 999
set verify off
set feedback off
set heading off

select username
from dba_users
order by username
/

undefine user

accept userid prompt 'Enter user to clone: '
accept newuser prompt 'Enter new username: '
accept passwd prompt 'Enter new password: '

select username
, created
from dba_users
where lower(username) = lower('&newuser')
/

accept poo prompt 'Continue? (ctrl-c to exit)'

--spool /tmp/user_clone_tmp.sql
spool D:\temp\testing\user_clone_tmp.sql

select 'create user ' || '&newuser' ||
' identified by ' || '&passwd' ||
' default tablespace ' || default_tablespace ||
' temporary tablespace ' || temporary_tablespace || ';' "user"
from dba_users
where username = '&userid'
/

select 'alter user &newuser quota '||
decode(max_bytes, -1, 'unlimited'
, ceil(max_bytes / 1024 / 1024) || 'M') ||
' on ' || tablespace_name || ';'
from dba_ts_quotas
where username = '&&userid'
/

select 'grant ' ||granted_role || ' to &newuser' ||
decode(admin_option, 'NO', ';', 'YES', ' with admin option;') "ROLE"
from dba_role_privs
where grantee = '&&userid'
/

select 'grant ' || privilege || ' to &newuser' ||
decode(admin_option, 'NO', ';', 'YES', ' with admin option;') "PRIV"
from dba_sys_privs
where grantee = '&&userid'
/

spool off

undefine user

set verify on
set feedback on
set heading on

@D:\temp\testing\user_clone_tmp.sql

host del D:\temp\testing\user_clone_tmp.sql

--@/tmp/user_clone_tmp.sql

--!rm /tmp/user_clone_tmp.sql

NOTE: Username should be Entered in Captitals.

+++++++++++++++++++++++++++++++++++++++++++++++

Save the scripts as .sql and run at the command prompt.