1、在本地?cái)?shù)據(jù)庫中創(chuàng)建如下表：
-- Create table
create table TMP_CR_PERSONS
(
SPECIALTY VARCHAR2(50),
USER_GROUP VARCHAR2(50),
USER_NAME VARCHAR2(50),
USER_CATEGORY VARCHAR2(50)
)
2、創(chuàng)建DBLINK：
create database link LK2PMS connect to XXX identified by XX
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.5.99)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oradb9)
)
)'
3、創(chuàng)建視圖，查詢變更清單：
CREATE OR REPLACE VIEW V_TMP_CR_LIST AS
SELECT D.CHANGE_REQUEST_CODE,
D.PROJECT_NAME,
D.PRODUCT_NAME,
D.MODULE_NAME,
D.TYPE,
D.PRIVILEGE,
D.PROPOSE_TIME,
WF.TRANSACTOR,
WF.TASKNAME,
P.SPECIALTY,
P.USER_GROUP,
P.USER_CATEGORY
FROM PMS_CHANGE@lk2pms D,
(SELECT MAX(ID) ID
FROM WFT_FLOWCONTROL@lk2pms WF
WHERE WF.WORKFLOWID = 'ChangeRequest'
GROUP BY WF.WORKID) MX,
WFT_FLOWCONTROL@lk2pms WF,
TMP_CR_PERSONS P
WHERE WF.ID = MX.ID
AND WF.WORKID = D.CHANGE_REQUEST_ID
AND D.CHANGE_REQUEST_CODE LIKE 'CR%'
AND WF.TRANSACTOR = P.USER_NAME
AND D.STATE NOT IN ('結(jié)束','終止','拒絕');
4、創(chuàng)建統(tǒng)計(jì)視圖：
CREATE VIEW V_TMP_CR_STAT AS
SELECT T.USER_GROUP, T.USER_NAME, COUNT(V.CHANGE_REQUEST_CODE) TOTAL
FROM V_TMP_CR_LIST V, TMP_CR_PERSONS T
WHERE V.TRANSACTOR(+) = T.USER_NAME
GROUP BY CUBE(T.USER_GROUP, T.USER_NAME);
1、當(dāng)然是訪問老系統(tǒng)中的，應(yīng)該都采用兼容視圖
2、在訪問網(wǎng)公司系統(tǒng)的時候，出現(xiàn)內(nèi)存不能寫的錯誤提示，讓后直接頁面加載失敗，解決辦法是，找到用SOGOU瀏覽器加載頁面后，發(fā)現(xiàn)需要安裝JITDSign這個插件，所以手工找到JITDSign.cab，解壓后直接運(yùn)行“regsvr32 JITDSign.ocx”，再刷新頁面，OK了。
3、其他問題暫未發(fā)現(xiàn)。