REM
REM scrub_db.sql
REM ============
REM
REM This script is provided by Database Specialists, Inc. 
REM (http://www.dbspecialists.com) for individual use and not for sale. 
REM Database Specialists, Inc. does not warrant the script in any way 
REM and will not be responsible for any loss arising out of its use.
REM
REM Your feedback is welcome! Please send your comments about this script
REM to scriptfeedback@dbspecialists.com
REM
REM This script will create a script to drop all database users except for 
REM those created in a default Oracle 8.1.6 install. The generated script 
REM will also drop all public database links, and all roles other than roles 
REM created in a default install. The generated script will also drop all
REM public synonyms that point to objects in schemas other than those that
REM are part of a default install. Finally, the generated script will coalesce 
REM all non-SYSTEM tablespaces.
REM
REM Run this script to scrub a database before importing another database's
REM contents or otherwise loading with data. 
REM
REM USE WITH CAUTION! You can't roll this stuff back!
REM
REM Run this script as any DBA user. The generated script must be run by
REM the SYS or SYSTEM user.
REM
REM Version 05-23-2000
REM

SELECT * 
FROM   global_name;

SET TERMOUT   OFF
SET FEEDBACK  OFF
SET HEADING   OFF
SET PAGESIZE  0
SET TRIMSPOOL ON

SPOOL drop_things.sql

SELECT   'DROP USER "' || username || '" CASCADE;'
FROM     SYS.dba_users
WHERE    username NOT IN 
         (
         'SYS', 'SYSTEM', 'DBSNMP', 'TRACESVR', 'OUTLN', 'ORDSYS',
         'CTXSYS', 'ORDPLUGINS', 'MDSYS', 'AURORA$ORB$UNAUTHENTICATED',
         'MTSSYS'
         )
ORDER BY username;

PROMPT

SELECT   'DROP PUBLIC DATABASE LINK "' || object_name || '";'
FROM     SYS.dba_objects
WHERE    owner = 'PUBLIC'
AND      object_type = 'DATABASE LINK'
ORDER BY object_name;

PROMPT

SELECT   'DROP ROLE "' || role || '";'
FROM     SYS.dba_roles
WHERE    role NOT IN ('CONNECT', 'RESOURCE', 'DBA', 'SELECT_CATALOG_ROLE',
                      'EXECUTE_CATALOG_ROLE', 'DELETE_CATALOG_ROLE',
                      'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE',
                      'RECOVERY_CATALOG_OWNER', 'AQ_ADMINISTRATOR_ROLE',
                      'AQ_USER_ROLE', 'SNMPAGENT', 'HS_ADMIN_ROLE',
                      'CTXAPP', 'JAVADEBUGPRIV', 'JAVAIDPRIV',
                      'JAVASYSPRIV', 'JAVAUSERPRIV', 'JAVA_ADMIN',
                      'TIMESERIES_DBA', 'TIMESERIES_DEVELOPER', 
                      'OEM_MONITOR')
ORDER BY role;

PROMPT

SELECT   'DROP PUBLIC SYNONYM "' || synonym_name || '";'
FROM     SYS.dba_synonyms
WHERE    owner = 'PUBLIC'
AND      (
         table_owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'TRACESVR', 
                             'OUTLN', 'ORDSYS', 'CTXSYS', 'ORDPLUGINS', 
                             'MDSYS', 'AURORA$ORB$UNAUTHENTICATED',
                             'MTSSYS')
      OR db_link IS NOT NULL
         )
ORDER BY synonym_name;

PROMPT

SELECT   'ALTER TABLESPACE "' || tablespace_name || '" COALESCE;'
FROM     SYS.dba_tablespaces
WHERE    status = 'ONLINE'
AND      tablespace_name != 'SYSTEM'
ORDER BY tablespace_name;

SPOOL OFF

SET TERMOUT   ON
SET FEEDBACK  6
SET HEADING   ON
SET PAGESIZE  20

PROMPT A script called drop_things.sql has been generated.
PROMPT Review it VERY CAREFULLY before using.
PROMPT
PROMPT If you do choose to run drop_things.sql, you must do so from the
PROMPT SYS or SYSTEM user.