<- Scripts
-- users.drop.sql
set linesize 300
set pagesize 9999
set verify off
prompt +--------------------------+
prompt | Create Drop Users Script |
prompt +--------------------------+
prompt
ACCEPT v_confirm prompt 'Press Enter to continue:'
CLEAR SCREEN
-- Create DROP USER
set heading off
set feedback off
spool c:\temp\users.drop.dropCmds.spool
SELECT 'DROP USER ' || username || ' CASCADE;'
FROM dba_users
WHERE username not in (
'ANONYMOUS','APEX_040200','APEX_PUBLIC_USER','APPQOSSYS','AUDSYS','CTXSYS','DBSNMP','DIP','FLOWS_FILES',
'GGSYS','GSMADMIN_INTERNAL','GSMUSER','GSMCATUSER','LBACSYS','MDDATA','MDSYS','MGMT_VIEW',
'ODM','ODM_MTR','OJVMSYS','OLAPSYS','ORACLE_OCM','ORDDATA','ORDSYS','ORDPLUGINS','OUTLN','OWNER','OWBSYS','OWBSYS_AUDIT',
'QS_ADM','QS','QS_WS','QS_ES','QS_OS','QS_CBADM','REMOTE_SCHEDULER_AGENT',
'SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR',
'SYS','SYSDG','SYSKM','SYSMAN','SYSBACKUP','SYSRAC','SYS$UMF','SYSTEM',
'QS_CB','QS_CS','PERFSTAT','WKSYS','WKPROXY','WMSYS','XDB','XS$NULL',
'SCOTT','HR','IX','OC','OE','PM','SH'
)
ORDER BY username;
spool off
-- Create DataPump SCHEMAS Entry for Import
spool c:\temp\users.drop.toImport.spool
SELECT LISTAGG(username,',') WITHIN GROUP (ORDER BY username)
FROM dba_users
WHERE username not in (
'ANONYMOUS','APEX_040200','APEX_PUBLIC_USER','APPQOSSYS','AUDSYS','CTXSYS','DBSNMP','DIP','FLOWS_FILES',
'GGSYS','GSMADMIN_INTERNAL','GSMUSER','GSMCATUSER','LBACSYS','MDDATA','MDSYS','MGMT_VIEW',
'ODM','ODM_MTR','OJVMSYS','OLAPSYS','ORACLE_OCM','ORDDATA','ORDSYS','ORDPLUGINS','OUTLN','OWNER','OWBSYS','OWBSYS_AUDIT',
'QS_ADM','QS','QS_WS','QS_ES','QS_OS','QS_CBADM','REMOTE_SCHEDULER_AGENT',
'SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR',
'SYS','SYSDG','SYSKM','SYSMAN','SYSBACKUP','SYSRAC','SYS$UMF','SYSTEM',
'QS_CB','QS_CS','PERFSTAT','WKSYS','WKPROXY','WMSYS','XDB','XS$NULL',
'SCOTT','HR','IX','OC','OE','PM','SH'
);
-- End
spool off
set heading on
set feedback on
prompt
prompt Scripts created:
prompt c:\temp\users.drop.dropCmds.spool
prompt c:\temp\users.drop.toImport.spool
prompt
