Thursday, 26 September 2019

Oracle: Default users

The set of users that get created if db is created using dbca:

Note : Specifically I needed when I needed to cleanup after  impdp

11.2.0.4:
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
SCOTT
SI_INFORMTN_SCHEMA
SPATIAL_CSW_ADMIN_USR
SPATIAL_WFS_ADMIN_USR
SYS
SYSMAN
SYSTEM
WMSYS
XDB
XS$NULL


To drop schema's excluding above:
select 'drop user '||username||' cascade;' from dba_users where username not in ('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','SCOTT',
'SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR',
'SYS','SYSMAN','SYSTEM','WMSYS','XDB','XS$NULL');

Oracle: Privileges / Roles need to use expdp

DATAPUMP_EXP_FULL_DATABASE  & DATAPUMP_IMP_FULL_DATABASE 

plus directory

create directory proj as '/somepath/';

grant read,write on proj to <schema>   ;  ---not req is expdp is executed via sys/system