Search the Site


bottom corner

Export Oracle users to text file

This article provides an example for Oracle DBAs to export users to a flat file, which may be useful as an alternative to their regular system backups as well as a means to quickly create some users in a testing database.

This page is filed under keyword(s): oracle.

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;

Did you find this page useful? Please consider browsing other articles or subscribing to the RSS feed to keep up with latest.

This page is filed under keyword(s): oracle.
Author: C. Peter Chen
Last updated: 26 Aug 2008

bottom corner