Generate script for create user on Oracle Databases 10g onward
Prerequisite
|
— CREATE USER WITH DEFAULT TABLE
— AND TEMPORARY TABLESPACE
— AND PROFILE
SELECT 'CREATE USER '||U.USERNAME||' IDENTIFIED BY VALUES '''||S.PASSWORD||''' DEFAULT TABLESPACE '||U.DEFAULT_TABLESPACE||' TEMPORARY TABLESPACE '||U.TEMPORARY_TABLESPACE||' PROFILE '||U.PROFILE||';'
FROM DBA_USERS U,SYS.USER$ S
WHERE U.USERNAME=S.NAME
AND U.USERNAME NOT IN ('DBSNMP',
'SYSMAN',
'MGMT_VIEW',
'SYS',
'SYSTEM',
'FLOWS_FILES',
'MDSYS',
'ORDSYS',
'EXFSYS',
'SCOTT',
'WMSYS',
'ORACLE_OCM',
'APPQOSSYS',
'XS$NULL',
'APEX_030200',
'OWBSYS_AUDIT',
'MDDATA',
'ORDDATA',
'CTXSYS',
'ANONYMOUS',
'OUTLN',
'DIP',
'APEX_PUBLIC_USER',
'XDB',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'ORDPLUGINS',
'OWBSYS',
'SI_INFORMTN_SCHEMA',
'OLAPSYS',
'PUBLIC','TSMSYS')
AND U.USERNAME NOT LIKE 'MGMT%';