Oracle 11g: The post scripts that should be run after refreshing a geodatabase instance.

associate_sde_stats.sql

EXECUTE sys.utl_recomp.recomp_serial('SDE');

ASSOCIATE STATISTICS WITH PACKAGES sde.st_domain_operators, sde.st_relation_operators USING sde.st_domain_stats;
ASSOCIATE STATISTICS WITH INDEXTYPES sde.st_spatial_index  USING sde.st_domain_stats;
ASSOCIATE STATISTICS WITH TYPES sde.st_geometry  USING sde.st_domain_stats;

delete_orphaned_locks.sql

set SERVEROUTPUT ON 

DECLARE 

CURSOR process_list IS 
SELECT sde_id, owner, nodename FROM sde.process_information; 

lock_name VARCHAR2(30); 
lock_handle VARCHAR2(128); 
lock_status INTEGER; 
cnt INTEGER DEFAULT 0; 

BEGIN 

FOR check_locks IN process_list LOOP 

lock_name := 'SDE_Connection_ID#' || TO_CHAR (check_locks.sde_id); 
DBMS_LOCK.ALLOCATE_UNIQUE (lock_name,lock_handle); 
lock_status := DBMS_LOCK.REQUEST (lock_handle,DBMS_LOCK.X_MODE,0,TRUE); 

IF lock_status = 0 THEN 
DELETE FROM sde.process_information WHERE sde_id = check_locks.sde_id; 
DELETE FROM sde.state_locks WHERE sde_id = check_locks.sde_id; 
DELETE FROM sde.table_locks WHERE sde_id = check_locks.sde_id; 
DELETE FROM sde.object_locks WHERE sde_id = check_locks.sde_id; 
DELETE FROM sde.layer_locks WHERE sde_id = check_locks.sde_id; 
cnt := cnt + 1; 
dbms_output.put_line('Removed entry ('||check_locks.sde_id||'): '||check_locks.owner||'/'||check_locks.nodename||''); 
END IF; 

END LOOP; 

DELETE FROM sde.state_locks WHERE sde_id NOT IN (SELECT sde_id FROM sde.process_information); 
DELETE FROM sde.table_locks WHERE sde_id NOT IN (SELECT sde_id FROM sde.process_information); 
DELETE FROM sde.object_locks WHERE sde_id NOT IN (SELECT sde_id FROM sde.process_information); 
DELETE FROM sde.layer_locks WHERE sde_id NOT IN (SELECT sde_id FROM sde.process_information); 

COMMIT; 

dbms_output.put_line('Removed '||cnt||' entries.'); 

END; 
/ 

drop_orphaned_syn.sql

set feedback off
set heading off
set lines 200
set pages 999

spool XXX_drop_orphaned_syn.sql

select 'drop '||decode(owner,'PUBLIC','PUBLIC SYNONYM ','SYNONYM '||owner||'.')||synonym_name||';'  
from dba_synonyms 
where table_owner not in (select username from dba_users)
or (owner <> 'PUBLIC' and owner not in (select username from dba_users));

spool off

set echo on
set feedback on
@XXX_drop_orphaned_syn.sql

set heading on
set lines 80
set pages 25
set echo off

kill_sessions.sql

set serveroutput on

prompt killing sessions
/*Kill any non critical (non system) sessions that are connected*/
declare
cursor user_cursor is
select username,sid,serial# 
from v$session 
where username not in (
'SYS'
,'SYSTEM'
,'SYS'
,'DBSNMP'
,'TMSYS'
,'WMSYS'
,'OUTLN'
,'DIP'
,'DMSYS'
,'EXFSYS'
,'MDDATA'
,'MDSYS'
,'ORDSYS'
,'ORDPLUGINS'
,'PUBLIC'
,'SI_INFORMTN_SCHEMA'
,'CTXSYS'
,'DSSYS'
,'PERFSTAT'
,'WKPROXY'
,'WKSYS'
,'WMSYS'
,'XDB'
,'ANONYMOUS'
,'ODM'
,'ODM_MTR'
,'OLAPSYS'
,'TRACESVR'
,'REPADMIN'
,'AURORA$ORB$UNAUTHENTICATED'
,'AURORA$JIS$UTILITY$'
,'OSE$HTTP$ADMIN'
,'TSMSYS'
,'SYSMAN'
,'MONITOR_USAGE'
)
and username not like 'OPS%';

begin
for u in user_cursor loop
dbms_output.put_line('alter system kill session '''||u.sid||','||u.serial#||''' immediate;');
execute immediate 'alter system kill session '''||u.sid||','||u.serial#||''' immediate';
end loop;
end;
/

mastercleanup_leave_tablespaces.sql

set serveroutput on

/*murder sessions */
prompt murdering sessions

prompt putting the db in restricted mode
/* so responder or other users cant keep trying to reconnect
and thwart the dropusers script */

alter system enable restricted session;

@@kill_sessions.sql

/* drop queue tables */
--prompt dropping queue tables so we can drop all users
--@@drop_queue_tables.sql

/*drop users */
prompt dropping users
@@usercleanup.sql

prompt removing levtover arcsde oracle pipes
@@remove_pipes.sql

prompt putting the db in unrestricted mode

alter system disable restricted session;


/*drop roles */

prompt dropping non essential roles
@@role_cleanup.sql

/* drop orphaned synonyms */

prompt dropping orphaned synonyms
@@drop_orphaned_syn.sql

post_refresh.sql

@@delete_orphaned_locks.sql


exec DBMS_STATS.gather_database_stats(estimate_percent=>100,cascade=>TRUE);


alter system flush shared_pool;

@@associate_sde_stats.sql


@@username_reset.sql
@@remove_pipes.sql

@@11g_grant_create_session.sql

remove_pipes.sql

/* removes old pipes from last gdb */

spool XXX_remove_pipes.sql

select 'select dbms_pipe.remove_pipe('''||name||''') from dual;' from v$db_pipes;

spool off

@XXX_remove_pipes.sql

role_cleanup.sql

set pages 999
set lines 200
spool XXX_role_cleanup.sql

select 'drop role '||'"'||role||'";' from dba_roles where role not in (
'AQ_ADMINISTRATOR_ROLE',
'AQ_USER_ROLE',
'AUTHENTICATEDUSER',
'CONNECT',
'CSW_USR_ROLE',
'CTXAPP',
'CWM_USER',
'DATAPUMP_EXP_FULL_DATABASE',	
'DATAPUMP_IMP_FULL_DATABASE',
'DBA',
'DELETE_CATALOG_ROLE',
'DMUSER_ROLE',  	
'DM_CATALOG_ROLE',
'EJBCLIENT',
'EXECUTE_CATALOG_ROLE',
'EXP_FULL_DATABASE',
'GATHER_SYSTEM_STATISTICS',
'GLOBAL_AQ_USER_ROLE',
'HS_ADMIN_ROLE',
'IMP_FULL_DATABASE',
'JAVADEBUGPRIV',
'JAVAIDPRIV',
'JAVASYSPRIV',
'JAVAUSERPRIV',
'JAVA_ADMIN',
'JAVA_DEPLOY',
'JMXSERVER',
'LOGSTDBY_ADMINISTRATOR',
'MGMT_USER',
'OEM_ADVISOR',
'OEM_MONITOR',
'OLAPI_TRACE_USER',
'OLAP_DBA',
'OLAP_USER',
'OLAP_XS_ADMIN',
'ORDADMIN',
'OWB$CLIENT',
'OWB_DESIGNCENTER_VIEW',
'OWB_USER',
'PLUSTRACE',
'RECOVERY_CATALOG_OWNER',
'RESOURCE',
'SCHEDULER_ADMIN',
'SELECT_CATALOG_ROLE',
'SPATIAL_CSW_ADMIN',
'SPATIAL_WFS_ADMIN',
'WFS_USR_ROLE',
'WKUSER',
'WM_ADMIN_ROLE',
'XDBADMIN',
'XDB_SET_INVOKER',
'XDBWEBSERVICES',
'XDB_WEBSERVICES',
'XDB_WEBSERVICES_OVER_HTTP',
'XDB_WEBSERVICES_WITH_PUBLIC')
/

spool off
@XXX_role_cleanup.sql

usercleanup.sql

spool XXX_drop_users.sql
select 'drop user '||CHR(34)||username||CHR(34)||' cascade;' from dba_users where username not in('SYS'
,'SYSTEM'
,'SYS'
,'DBSNMP'
,'TMSYS'
,'WMSYS'
,'OUTLN'
,'DIP'
,'DMSYS'
,'EXFSYS'
,'MDDATA'
,'MDSYS'
,'ORDSYS'
,'ORDPLUGINS'
,'PUBLIC'
,'SI_INFORMTN_SCHEMA'
,'CTXSYS'
,'DSSYS'
,'PERFSTAT'
,'WKPROXY'
,'WKSYS'
,'WMSYS'
,'XDB'
,'ANONYMOUS'
,'ODM'
,'ODM_MTR'
,'OLAPSYS'
,'TRACESVR'
,'REPADMIN'
,'AURORA$ORB$UNAUTHENTICATED'
,'AURORA$JIS$UTILITY$'
,'OSE$HTTP$ADMIN'
,'TSMSYS'
,'SYSMAN'
,'MONITOR_USAGE'
,'APEX_030200'                                                                     
,'APEX_PUBLIC_USER'                                                                
,'FLOWS_FILES'
,'MGMT_VIEW'
,'ORACLE_OCM'
,'OWBSYS'                                                                          
,'OWBSYS_AUDIT'                                                                    
,'SCOTT'
,'SPATIAL_CSW_ADMIN_USR'                                                           
,'SPATIAL_WFS_ADMIN_USR'
,'XS$NULL',
'APPQOSSYS',
'LBACSYS'
,'SDE'
)
and username not like 'OPS%';
select 'drop user '||CHR(34)||username||CHR(34)||' cascade;' from dba_users where username = 'SDE';
spool off;
@XXX_drop_users.sql

username_reset.sql

spool passwords.sql

set heading off
select 'alter user '||username||' identified by '||lower(username)||';' from dba_users where username not in ('SYS','SYSTEM');

select 'revoke DBA from '||grantee||';' 
from dba_role_privs 
where grantee not in ('SYS','SYSTEM','SYSMAN')
and granted_role='DBA';


spool off;
set heading on;
@passwords.sql

Thanks for those scripts. They're really valuable when trying to bring SDE to live after dumping it into an oracle instance. Searched a lot on google but you script made it.