select username from dba_users order by 1; USERNAME ------------------------------ ANONYMOUS APEX_030200 APEX_PUBLIC_USER APPQOSSYS CTXSYS DBSNMP DIP EXFSYS FLOWS_FILES MDSYS MGMT_VIEW ORACLE_OCM ORDDATA ORDPLUGINS ORDSYS OUTLN OWBSYS OWBSYS_AUDIT SI_INFORMTN_SCHEMA SYS SYSMAN SYSTEM WMSYS XDB XS$NULL 25 rows selected.
select username from dba_users order by 1; USERNAME ------------------------------ ANONYMOUS APEX_030200 APEX_PUBLIC_USER APPQOSSYS CTXSYS DBSNMP DIP EXFSYS FLOWS_FILES MDDATA MDSYS MGMT_VIEW OLAPSYS ORACLE_OCM ORDDATA ORDPLUGINS ORDSYS OUTLN OWBSYS OWBSYS_AUDIT SI_INFORMTN_SCHEMA SPATIAL_CSW_ADMIN_USR SPATIAL_WFS_ADMIN_USR SYS SYSMAN SYSTEM WMSYS XDB XS$NULL 29 rows selected.
select
lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
(
/* THE USERS */
select
null grantee,
username granted_role
from
dba_users
/* THE ROLES TO ROLES RELATIONS */
union
select
grantee,
granted_role
from
dba_role_privs
/* THE ROLES TO PRIVILEGE RELATIONS */
union
select
grantee,
privilege
from
dba_sys_privs
)
start with grantee is null
connect by grantee = prior granted_role;
If the user is very large, it can take a very long time to use the standard way expdp / impdp.
It can be done more efficiently using transportable tablespaces technique to remap the schema.
Basic steps
Essentially performs a network datapump from PL/SQL using a database link back to the same database so no intermediate dump file is necessary.
An unsupported workaround to rename a schema is to simply update the username in sys.user$ and restart the database.
select user# from sys.user$ where name = 'EPK';
USER_ID
----------
172
update sys.user$ set name = 'KPK' where user# = 172;
1 row updated.
SQL> show parameter authent NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ os_authent_prefix string ops$ SQL>
create user ops$oracle identified externally; grant create session to ops$oracle;
Watch out on Windows - the domain name needs to be included!
create user "ops$domainname.com\\oracle" identified externally; grant create session to "ops$domainname.com\\oracle";
Additionally, the following option must be set in “%ORACLE_HOME%\
etwork\\admin\\sqlnet.ora”
If this is not present, you will not be able to connect as sysdba
SQLNET.AUTHENTICATION_SERVICES=(NTS)