In den Systemtabellen des Daten-Schemas und des TBSYS/MAPSYS Users sind häufig fehlerhafte bzw. veraltete Einträge enthalten.
Z.B. werden beim Löschen von Projekten die Einträge in der zugehörigen Systemtabelle nicht bereinigt.
Weiterhin werden beim Löschen eines Benutzers oder einer Benutzergruppe im Admin sämtliche Einträge mit dieser IDs in allen Systemtabellen beibehalten.
In TB_SETTINGS stehen zum Teil noch Einträge mit dem Namespace TOPOBASE. Gleichzeitig gibt es die neuen Einträge mit dem Namespace MAP.
Meistens verursachen diese alten Einträge keine Probleme. Wir hatten aber auch schon Fälle, wo das Löschen dieser Einträge notwendig war.
Die Programmierung einer 1-Klick-Wartung für Systemtabellen wurde von Autodesk abgelehnt und die Umfrage zu einer AGU-Eigenlösung fand nicht genügend Resonanz. Aus diesem Grunde stelle ich hier meine SQL-Statements zur Verfügung, mit denen ich die Systemtabellen bereinige.
Ein Hinweis vorab: Ich übernehme keinerlei Gewähr für die Richtigkeit der Angaben und dafür, dass nach Absetzen der Statements noch alles korrekt läuft. Bei mir hat es funktioniert, in anderen Umgebungen kann es sich anders verhalten. Ich rate dringend dazu diese Statements zuerst auf einer Testdatenbank abzusetzen und danach ausführliche Tests durchzuführen, bevor die Operationen auf der Produktionsdatenbank ausgeführt werden!
Löschen von nicht mehr existierenden Benutzergruppen:
im Daten-Schema:
delete from TB_GN_MENU_FILTER a where not exists (select 1 from mapsys.tb_usergroup b where b.id = a.USERGROUP_ID);
delete from TB_GN_MENU_NODE_FILTER a where not exists (select 1 from mapsys.tb_usergroup b where b.id = a.USERGROUP_ID);
delete from TB_GN_REPORT_FILTER a where not exists (select 1 from mapsys.tb_usergroup b where b.id = a.USERGROUP_ID);
delete from TB_GN_USERGROUP_DIALOG a where not exists (select 1 from mapsys.tb_usergroup b where b.id = a.USERGROUP_ID);
delete from TB_GN_DOCUMENT_BAR_FILTER a where not exists (select 1 from mapsys.tb_usergroup b where b.id = a.USERGROUP_ID);
delete from TB_GN_FLYIN_FILTER a where not exists (select 1 from mapsys.tb_usergroup b where b.id = a.USERGROUP_ID);
delete from TB_GN_DIALOG_BAR_FILTER a where not exists (select 1 from mapsys.tb_usergroup b where b.id = a.USERGROUP_ID);
delete from TB_FCONT_MENU_ITEM_UG_RESTR a where not exists (select 1 from mapsys.tb_usergroup b where b.id = a.USERGROUP_ID);
delete from TB_FCONT_PROFILE_UG_RESTR a where not exists (select 1 from mapsys.tb_usergroup b where b.id = a.USERGROUP_ID);
delete from TB_FUNCTIONALITEM_FILTER a where not exists (select 1 from mapsys.tb_usergroup b where b.id = a.USERGROUP_ID);
delete from TB_EXPLORER_GROUP_UG_RESTR a where not exists (select 1 from mapsys.tb_usergroup b where b.id = a.USERGROUP_ID);
im System-Schema (TBSYS/MAPSYS):
delete from TB_USERGROUP_WORKSPACE a where not exists (select 1 from tb_usergroup b where b.id = a.USERGROUP_ID);
delete from TB_USER_USERGROUP a where not exists (select 1 from tb_usergroup b where b.id = a.USERGROUP_ID);
delete from TB_FUNCTIONALITEM_FILTER a where not exists (select 1 from tb_usergroup b where b.id = a.USERGROUP_ID);
delete from TB_GN_FLYIN_FILTER a where not exists (select 1 from tb_usergroup b where b.id = a.USERGROUP_ID);
delete from TB_GN_MENU_FILTER a where not exists (select 1 from tb_usergroup b where b.id = a.USERGROUP_ID);
delete from TB_GN_TOOLBAR_FILTER a where not exists (select 1 from tb_usergroup b where b.id = a.USERGROUP_ID);
Diese Benutzergruppen können noch in den Formulardefinitionen enthalten sein, wenn man von der Möglichkeit Gebrauch macht, Attribute für einzelne Gruppen zu sperren oder auszublenden. Dazu habe ich noch kein direktes Update-Statement, aber einen manuellen Weg:
im Daten-Schema:
select distinct groupsuspend from tb_gn_control c;
select distinct grouplocked from tb_gn_control c;
Wenn man hier Gruppen entdeckt, die es nicht mehr gibt, dann kann man folgende (entsprechend angepasste) Statements im Daten-Schema absetzen:
update tb_gn_control set groupsuspend = replace(groupsuspend,‘
update tb_gn_control set grouplocked = replace(groupsuspend,‘
Nicht mehr vorhandene Benutzer entfernt man so:
im Daten-Schema:
delete from TB_CRYSTAL_REPORTS a where not exists (select 1 from tbsys.tb_user b where b.id = a.USER_ID) and USER_ID != ‚ALL‘;
delete from TB_EXPLORER_GROUP_VIEW a where not exists (select 1 from tbsys.tb_user b where b.id = a.USER_ID) and USER_ID != ‚ALL‘;
delete from TB_EXPLORER_ITEM a where not exists (select 1 from tbsys.tb_user b where b.id = a.USER_ID) and USER_ID != ‚ALL‘;
delete from TB_FORM_LOCATION a where not exists (select 1 from tbsys.tb_user b where b.id = a.USER_ID) and USER_ID != ‚ALL‘;
delete from TB_GN_DIALOG_FILTER a where not exists (select 1 from tbsys.tb_user b where b.id = a.USER_ID) and USER_ID != ‚ALL‘;
delete from TB_GN_DIALOG_MACHINE_USER a where not exists (select 1 from tbsys.tb_user b where b.id = a.USER_ID) and USER_ID != ‚ALL‘;
delete from TB_GN_DIALOG_USER a where not exists (select 1 from tbsys.tb_user b where b.id = a.USER_ID) and USER_ID != ‚ALL‘;
delete from TB_GN_FLYIN_USER a where not exists (select 1 from tbsys.tb_user b where b.id = a.USER_ID) and USER_ID != ‚ALL‘;
delete from TB_GN_LIST_CONFIG a where not exists (select 1 from tbsys.tb_user b where b.id = a.USER_ID) and USER_ID != ‚ALL‘;
delete from TB_GN_REF a where not exists (select 1 from tbsys.tb_user b where b.id = a.USER_ID) and USER_ID != ‚ALL‘;
delete from TB_GN_SELECTION a where not exists (select 1 from tbsys.tb_user b where b.id = a.USER_ID) and USER_ID != ‚ALL‘;
delete from TB_REF a where not exists (select 1 from tbsys.tb_user b where b.id = a.USER_ID) and USER_ID != ‚ALL‘;
delete from TB_REF_SELECT a where not exists (select 1 from tbsys.tb_user b where b.id = a.USER_ID) and USER_ID != ‚ALL‘;
delete from TB_REPORT_KEY a where not exists (select 1 from tbsys.tb_user b where b.id = a.USER_ID) and USER_ID != ‚ALL‘;
delete from TB_SETTINGS a where not exists (select 1 from tbsys.tb_user b where b.id = a.USER_ID) and USER_ID != ‚ALL‘;
delete from TB_VARIABLE a where not exists (select 1 from tbsys.tb_user b where b.id = a.USER_ID) and USER_ID != ‚ALL‘;
delete from TB_VIEWPORT a where not exists (select 1 from tbsys.tb_user b where b.id = a.USER_ID) and USER_ID != ‚ALL‘;
delete from TB_VIEWPORT_VIEW a where not exists (select 1 from tbsys.tb_user b where b.id = a.USER_ID) and USER_ID != ‚ALL‘;
im System-Schema:
delete from TB_DOCUMENT_VIEW_EXPL_GROUP a where not exists (select 1 from tbsys.tb_user b where b.id = a.USER_ID) and USER_ID != ‚ALL‘;
delete from TB_DOCUMENT_VIEW_VIEWPORT a where not exists (select 1 from tbsys.tb_user b where b.id = a.USER_ID) and USER_ID != ‚ALL‘;
delete from TB_FORM_LOCATION a where not exists (select 1 from tbsys.tb_user b where b.id = a.USER_ID) and USER_ID != ‚ALL‘;
delete from TB_GN_FLYIN_USER a where not exists (select 1 from tbsys.tb_user b where b.id = a.USER_ID) and USER_ID != ‚ALL‘;
delete from TB_SETTINGS a where not exists (select 1 from tbsys.tb_user b where b.id = a.USER_ID) and USER_ID != ‚ALL‘;
delete from TB_USER_USERGROUP a where not exists (select 1 from tbsys.tb_user b where b.id = a.USER_ID) and USER_ID != ‚ALL‘;
delete from TB_VIEWPORT a where not exists (select 1 from tbsys.tb_user b where b.id = a.USER_ID) and USER_ID != ‚ALL‘;
delete from TB_WORKSPACE a where not exists (select 1 from tbsys.tb_user b where b.id = a.USER_ID) and USER_ID != ‚ALL‘;
Hier ist es wichtig den Benutzer ‚ALL‘ beizubehalten, denn der sorgt für die Defaults.
Hin und wieder hatten wir schon einmal identische Einträge in TB_SETTINGS im Daten- und System-Schema, die man so herausfindet:
select USER_ID,ITEMTHEMA,ITEMKEY
from tb_settings
group by USER_ID,ITEMTHEMA,ITEMKEY
having count(*) > 1;
..und so löschen kann:
delete from tb_settings s where exists (
select 1 from tb_settings s1
where s1.USER_ID = s.USER_ID
and s1.ITEMTHEMA = s.ITEMTHEMA
and s1.ITEMKEY = s.ITEMKEY
group by USER_ID,ITEMTHEMA,ITEMKEY
having count(*) > 1)
and id = (select max(id) from tb_settings s2
where s2.USER_ID = s.USER_ID
and s2.ITEMTHEMA = s.ITEMTHEMA
and s2.ITEMKEY = s.ITEMKEY);
Gelöschte Tabellen, Topics und Labeldefinitionen werden im Explorer nicht nachgeführt und haben bei uns schon mehrfach zu Problemen geführt. Diese Leichen kann man mit folgenden Statements entfernen (wurden von der Entwicklung bereitgestellt):
delete from TB_EXPLORER_ITEM where id in (select id from tb_explorer_item e where (e.nodetypes like ‚1|%‘ or e.nodetypes like ‚%|1|%‘) and (e.stmt_id = 3 or e.stmt_id = 4) and e.key not in (select f_class_id from tb_dictionary));
delete from TB_EXPLORER_ITEM where id in (select id from tb_explorer_item e, tb_dictionary d where (e.nodetypes like ‚3|%‘ or e.nodetypes like ‚%|3|%‘) and e.key = d.f_class_id and d.f_class_type <> ‚P‘);
delete from TB_EXPLORER_ITEM where id in (select id from tb_explorer_item e, tb_dictionary d where (e.nodetypes like ‚4|%‘ or e.nodetypes like ‚%|4|%‘) and e.key = d.f_class_id and d.f_class_type <> ‚E‘);
delete from TB_EXPLORER_ITEM where id in (select id from tb_explorer_item e, tb_dictionary d where (e.nodetypes like ‚5|%‘ or e.nodetypes like ‚%|5|%‘) and e.key = d.f_class_id and d.f_class_type <> ‚A‘);
delete from TB_EXPLORER_ITEM where id in (select id from tb_explorer_item e, tb_dictionary d where (e.nodetypes like ‚6|%‘ or e.nodetypes like ‚%|6|%‘) and e.key = d.f_class_id and d.f_class_type <> ‚L‘);
delete from TB_EXPLORER_ITEM where id in (select id from tb_explorer_item e, tb_dictionary d where (e.nodetypes like ‚7|%‘ or e.nodetypes like ‚%|7|%‘) and e.key = d.f_class_id and d.f_class_type <> ‚I‘);
delete from TB_EXPLORER_ITEM where id in (select id from tb_explorer_item e, tb_dictionary d where (e.nodetypes like ‚8|%‘ or e.nodetypes like ‚%|8|%‘) and e.key = d.f_class_id and d.f_class_type <> ‚O‘);
delete from TB_EXPLORER_ITEM where id in (select id from tb_explorer_item e, tb_dictionary d where (e.nodetypes like ‚9|%‘ or e.nodetypes like ‚%|9|%‘) and e.key = d.f_class_id and d.f_class_type <> ‚R‘);
delete from TB_EXPLORER_ITEM where id in (select id from tb_explorer_item e, tb_dictionary d where (e.nodetypes like ’10|%‘ or e.nodetypes like ‚%|10|%‘) and e.key = d.f_class_id and d.f_class_type <> ‚X‘);
delete from TB_EXPLORER_ITEM where id in (select id from tb_explorer_item e, tb_dictionary d where (e.nodetypes like ’11|%‘ or e.nodetypes like ‚%|11|%‘) and e.key = d.f_class_id and d.f_class_type <> ‚W‘);
delete from TB_EXPLORER_ITEM where id in (select id from tb_explorer_item e, tb_dictionary d where (e.nodetypes like ’12|%‘ or e.nodetypes like ‚%|12|%‘) and e.key = d.f_class_id and d.f_class_type <> ‚S‘);
delete from TB_EXPLORER_ITEM where id in (select id from tb_explorer_item e, tb_dictionary d where (e.nodetypes like ’13|%‘ or e.nodetypes like ‚%|13|%‘) and e.key = d.f_class_id and d.f_class_type <> ‚C‘);
delete from TB_EXPLORER_ITEM where id in (select id from tb_explorer_item e, tb_dictionary d where (e.nodetypes like ’14|%‘ or e.nodetypes like ‚%|14|%‘) and e.key = d.f_class_id and d.f_class_type <> ‚D‘);
delete from TB_EXPLORER_ITEM where id in (select id from tb_explorer_item e, tb_dictionary d where (e.nodetypes like ’15|%‘ or e.nodetypes like ‚%|15|%‘) and e.key = d.f_class_id and d.f_class_type <> ‚M‘);
delete from TB_EXPLORER_ITEM where id in (select id from tb_explorer_item e, tb_dictionary d where (e.nodetypes like ’16|%‘ or e.nodetypes like ‚%|16|%‘) and e.key = d.f_class_id and d.f_class_type <> ‚T‘);
delete from TB_EXPLORER_ITEM where id in (select id from tb_explorer_item e, tb_dictionary d where e.stmt_id = 3 and e.key = d.f_class_id and d.PARENT_F_CLASS_ID is not null);
delete from TB_EXPLORER_ITEM where id in (select id from tb_explorer_item e, tb_dictionary d where e.stmt_id = 4 and e.key = d.f_class_id and d.PARENT_F_CLASS_ID is null);
delete from TB_EXPLORER_ITEM where id in (select id from tb_explorer_item e where (e.nodetypes like ’18|%‘ or e.nodetypes like ‚%|18|%‘) and (e.stmt_id = 1 or e.stmt_id = 2) and e.key not in (select id from tb_topic));
delete from TB_EXPLORER_ITEM where id in (select e.id from tb_explorer_item e, tb_topic t where e.stmt_id = 1 and e.key = t.id and t.TOPIC_ID is not null);
delete from TB_EXPLORER_ITEM where id in (select e.id from tb_explorer_item e, tb_topic t where e.stmt_id = 2 and e.key = t.id and t.TOPIC_ID is null);
delete from TB_EXPLORER_ITEM where id in (select e.id from tb_explorer_item e where (e.nodetypes like ’17|%‘ or e.nodetypes like ‚%|17|%‘) and e.stmt_id = 5 and e.key not in (select id from tb_label_def));
delete from tb_explorer_item e where (e.nodetypes like ’20|%‘ or e.nodetypes like ‚%|20|%‘) and e.stmt_id = 6 and e.key not in (select id from tb_domain);
Zuletzt noch ein paar Hinweise zum Löschen von Tabellen:
Das Löschen im Admin führt nicht zum Löschen der Dialogdefinitionen in den Systemtabellen. Da kann man auch von Zeit zu Zeit mal mit der bestehenden 1-Klick-Wartung aufräumen.
Allerdings werden zusätzlich eingerichtete Highlights nicht mit gelöscht. Das kann man so nachholen:
delete from tb_gn_dialog_highlight h where not exists (select 1 from tb_gn_dialog d where d.id = h.DIALOG_ID);
Wenn Ihr noch weitere Statements habt, würde ich mich freuen, wenn Ihr sie auch hier posten könntet.
Hinterlasse einen Kommentar
Du musst angemeldet sein, um einen Kommentar schreiben zu können.