Last active
March 20, 2018 23:13
-
-
Save kbaesler/b1b4091d96f52629f78c to your computer and use it in GitHub Desktop.
Oracle 11g: The post scripts that should be run after refreshing a geodatabase instance.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; | |
/ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; | |
/ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
@@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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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.