本文是系统上线前,实施过程中产生疑惑,面临的问题,进行的思考和讨论的日常工作记录,供日后参考。
1.备份数据库;
2.删除 AD_ORGINFO 下的 两个外键约束 cbank_adorginfo ,ccashbook_adorginfo。(运行删除程序时提示此两个约束没法禁用);
3.
以下步骤不是必须的,也有可能需要增加,视具体情况而定(根据运行删除程序错误Log调整,谨慎 勿删核心数据)。
--delete FROM AD_changelog
--WHERE AD_SESSION_ID NOT IN
--(SELECT AD_SESSION_ID FROM AD_SESSION);COMMIT;
--DELETE FROM AD_PREFERENCE
--WHERE AD_USER_ID NOT IN
--(SELECT AD_USER_ID FROM AD_USER);COMMIT;
--DELETE FROM AD_WF_NODE_TRL
--WHERE AD_WF_NODE_ID NOT IN
--(SELECT AD_WF_NODE_ID FROM AD_WF_NODE );COMMIT;
--DELETE FROM T_REPORTSTATEMENT
--WHERE AD_PINSTANCE_ID NOT IN
--(SELECT AD_PINSTANCE_ID FROM AD_PINSTANCE);COMMIT;
--DELETE FROM AD_PINSTANCE_LOG
--WHERE AD_PINSTANCE_ID NOT IN
--(SELECT AD_PINSTANCE_ID FROM AD_PINSTANCE);COMMIT;
--DELETE FROM T_TRIALBALANCE
--WHERE AD_PINSTANCE_ID NOT IN
--(SELECT AD_PINSTANCE_ID FROM AD_PINSTANCE);COMMIT;
4. 运行RUN_DELETECLIENT.bat,执行Oracle目录下相应的 DELETECLIENT.sql
5. 报错转第3步
6.更新AD_SEQUENCE
以下代码根据实际情况调整(慎用)
============================================================
DECLARE
v_AD_SEQUENCE_ID NUMBER;
v_NAME VARCHAR2(60);
v_sql1 VARCHAR2(100);
v_sql2 VARCHAR2(100);
CURSOR cur_ad_sequence
IS
SELECT AD_SEQUENCE_ID,NAME
FROM AD_SEQUENCE
WHERE NAME NOT LIKE 'DocumentNo%'
AND AD_CLIENT_ID =0
AND NAME NOTIN('T_BOMLine_Costs','AD_Document_Action_Access')
AND CURRENTNEXT >1000000;
BEGIN
FOR p IN cur_ad_sequence
LOOP
v_sql1 := 'SELECTMAX('||p.NAME||'_ID) FROM '||p.NAME;
--DBMS_OUTPUT.put_line(v_sql1);
EXECUTE IMMEDIATEv_sql1 INTO V_AD_SEQUENCE_ID;
--DBMS_OUTPUT.put_line(v_AD_SEQUENCE_ID);
IF V_AD_SEQUENCE_IDIS NULL THEN
V_AD_SEQUENCE_ID:=0;
END IF;
IF V_AD_SEQUENCE_ID< 1000000 THEN
V_AD_SEQUENCE_ID:=1000000;
ELSE
V_AD_SEQUENCE_ID:=V_AD_SEQUENCE_ID+1;
END IF;
v_sql2 :='UPDATEAD_SEQUENCE SET CURRENTNEXT='||V_AD_SEQUENCE_ID||' WHEREAD_SEQUENCE_ID='||p.AD_SEQUENCE_ID;
EXECUTE IMMEDIATEv_sql2;
--DBMS_OUTPUT.put_line(v_sql2);
END LOOP;
COMMIT;
END;
/
EXIT
可能存在的问题:
DELETECLIENT.sql 执行时可能出现字符集不匹配的情况,
修改部分SQL如下
========================================================================================
SELECT N'delete from '
|| tablename
|| ' where AD_Client_ID='
|| v_client_id AS v_sql
, a.TableName
FROM AD_TABLE a
WHERE a.isview = 'N'
AND EXISTS (
SELECT ad_column_id
FROM AD_COLUMN c
WHERE a.ad_table_id = c.ad_table_id
AND UPPER (c.columnname) = 'AD_CLIENT_ID')
-- Assure that the table is really a table in database
AND EXISTS (SELECT 1 FROM user_objects dbo WHERE UPPER(dbo.object_name)=UPPER(a.TableName) AND dbo.object_type='TABLE')
UNION
SELECT N'delete from '
|| t.tablename
|| ' where '
|| columnname
|| '='
|| v_client_id AS v_sql
, t.TableName
FROM AD_COLUMN c, AD_TABLE t
===========================================================================================