> -----Original Message-----
> From: Browett, Darren [mailto:[EMAIL PROTECTED]]
>
> I have a small datawarehouse that I would like to re-design
> and re-build.
> All the scripts are in place to recreate the
> snapshots, tables and associated indexes. But before I
> delete the existing
> one, and create the new one, I need to
> retrieve all the users and their associated grants and roles.
>
> Can this be done using export ?? I have looked through the
> help, tried a couple of things, but with no success.
>
> Oh yeah, this is oracle 8.0.5.1.1
I think with export/import you would have to do a full database export and import (you could use ROWS=N to prevent data from being imported.) But then you would have to go drop all the objects that were created.
The script included with this e-mail will recreate all the grants for a user. You could add a SQL statement to recreate the user with the correct default tablespace, temporary tablespace, and password, something like
set pagesize 0
column user_pass fold_after
select 'create user ' || username || ' identified by values ''' ||
password || '''' as user_pass,
' default tablespace ' || default_tablespace ||
' temporary tablespace ' || temporary_tablespace || ' ;' as tbsp
from dba_users ;
------
Jacques R. Kilchoer
(949) 754-8816
Quest Software, Inc.
8001 Irvine Center Drive
Irvine, California 92618
U.S.A.
http://www.quest.com
showpriv.sql