Export Oracle users to text file

This is achieved by using the sys.dba_users table, which means these queries must be done using an account with proper privileges. Beyond creation of users, the second SQL statement in the example below also generates account-locking commands for those users that are currently marked as locked. Finally, note the output file has the extension .sql, which suggests that it can be run directly from SQL*Plus as part of an automated script, if necessary.

set linesize 256;
spool c:oracleUsers.sql;

select 'create user '||username||' identified by values '||''''||password||''''||' 
default tablespace '||default_tablespace||' 
temporary tablespace '||temporary_tablespace||' 
quota unlimited on '||default_tablespace||' 
quota unlimited on '||temporary_tablespace||';' as "-- create_users"
from sys.dba_users 
where default_tablespace not in('SYSAUX','SYSTEM')
order by username;

select concat(concat('alter user ',du.username),' account lock;') as "--lock_users"
from sys.dba_users du
where du.account_status<>'OPEN'
and du.default_tablespace not in('SYSAUX','SYSTEM')
order by du.username;

spool off;

Leave a Reply

Your email address will not be published. Required fields are marked *