Monday, June 17, 2013

How to export import Oracle users only





How to export / import users only.

You can use a script similiar to the one below to extract the users and privileges and the run in the target database.
set pages 9999
set lines 180
set hea off
spool create_users_to_move.sql
select ‘create user ‘||username||’ identified by ‘||password||
‘ default tablespace ‘||default_tablespace||
‘ temporary tablespace ‘||temporary_tablespace||’;’
from dba_users
where username not in (‘DBSNMP’,'OUTLN’,'SYS’,'SYSTEM’)
order by username;
spool off
–end of script
set pages 9999
set lines 180
set hea off
spool grants_for_users_to_move.sql
select ‘grant ‘||granted_role||’ to ‘||grantee||’;’
from dba_role_privs
where grantee not in (‘DBSNMP’,'OUTLN’,'SYS’,'SYSTEM’)
order by grantee;
/*
select ‘grant ‘||privilege||’ on ‘||owner||’.'||table_name||
‘ to ‘||grantee||’;’
from dba_tab_privs
where grantee not in (‘DBSNMP’,'OUTLN’,'SYS’,'SYSTEM’)
and owner not in (‘SYS’,'SYSTEM’,'DBSNMP’,'OUTLN’)
order by grantee;
*/
spool off
–end of script

No comments:

Post a Comment