A라는 오라클서버에서 expdp로 덤프한 후 B라는 오라클서버에 impdp 합니다..
그럼 모든 테이블이 똑같이 복사가되는데요..
여기서 똑같은 쿼리를 날리는데 이러네요
SELECT
DISTINCT A.LOTID ,
CASE
WHEN A.DETAILLOTTYPE = 'Shipping' THEN 'OUT'
ELSE 'IN'
END AS DIVISION ,
TO_CHAR(A.PLANSTARTDATE,
'YYYY-MM-DD') AS PLANSTARTDATE ,
TO_CHAR(A.PLANDUEDATE,
'YYYY-MM-DD') AS PLANDUEDATE ,
A.RECIPEID ,
A.RETICLEID ,
A.LOTTYPE ,
B.DESCRIPTION AS LOTTYPEDESC ,
A.DETAILLOTTYPE ,
A.PROCESSFLOWID ,
A.PROCESSSPECID ,
J.DESCRIPTION AS PROCESSSPECDESC ,
A.VERSION ,
A.QUANTITY ,
A.LOTSTATE ,
A.PROCESSSTATE ,
A.REWORKSTATE ,
A.HOLDSTATE ,
C.PRODUCTIONTYPE ,
A.PRODUCTID ,
C.DESCRIPTION AS PRODUCTDESC ,
A.PRIORITY ,
D.DESCRIPTION AS PRIORITYDESC ,
A.EQUIPMENTID ,
E.AREAID AS BAYID ,
F.DESCRIPTION AS BAYDESC ,
G.AREAID ,
G.DESCRIPTION AS AREADESC ,
A.EVENTCOMMENT ,
A.PROCESSOPERATIONID ,
A.PROCESSOPERATIONID AS OPERATIONID ,
I.DESCRIPTION AS OPERDESC ,
A.PORTID ,
K.PROCESSFLOWTYPE ,
A.REWORKPROCESSSPECID ,
A.REWORKPROCESSVERSION ,
A.RETURNPROCESSFLOWID ,
A.RETURNPROCESSOPERATIONID ,
L.DESCRIPTION AS RETURNOPERDESC ,
NVL((SELECT
PERSONNAME
FROM
PERSON
WHERE
PERSONID = A.EVENTUSER),
A.EVENTUSER) AS EVENTUSER ,
I.DETAILOPERATIONTYPE ,
(SELECT
DESCRIPTION
FROM
VENDOR
WHERE
VENDORID = C.VENDORID) AS VENDORNAME ,
C.DESCRIPTION AS PRODUCTNAME ,
M.WORKORDERID ,
C.VENDORID ,
N.CONSUMABLELOTID ,
TO_CHAR(N.RECEIVETIME,
'YY-MM-DD') AS RECEIVETIME ,
(SELECT
DESCRIPTION
FROM
VENDOR
WHERE
VENDORID = N.RECEIVEVENDORID) AS RECEIVEVENDORID
FROM
LOT A
LEFT JOIN
ENUMVALUE B
ON A.LOTTYPE = B.ENUMVALUE
AND B.ENUMNAME = 'LotType'
LEFT JOIN
PRODUCTDEF C
ON A.FACTORYID = C.FACTORYID
AND A.PRODUCTID = C.PRODUCTID
LEFT JOIN
ENUMVALUE D
ON A.PRIORITY = D.ENUMVALUE
AND D.ENUMNAME = 'Priority'
LEFT JOIN
EQUIPMENTDEF E
ON A.FACTORYID = E.FACTORYID
AND A.EQUIPMENTID = E.EQUIPMENTID
LEFT JOIN
AREA F
ON A.FACTORYID = F.FACTORYID
AND E.AREAID = F.AREAID
AND F.DETAILAREATYPE = 'Bay'
LEFT JOIN
AREA G
ON F.FACTORYID = G.FACTORYID
AND F.PARENTAREAID = G.AREAID
AND G.DETAILAREATYPE = 'Area'
LEFT JOIN
PROCESSOPERATION I
ON A.FACTORYID = I.FACTORYID
AND A.PROCESSOPERATIONID = I.PROCESSOPERATIONID
LEFT JOIN
PROCESSSPEC J
ON (
(
A.REWORKSTATE = 'NotInRework'
AND A.PROCESSSPECID = J.PROCESSSPECID
AND A.VERSION = J.VERSION
)
OR (
A.REWORKSTATE = 'InRework'
AND A.REWORKPROCESSSPECID = J.PROCESSSPECID
AND A.REWORKPROCESSVERSION = J.VERSION
)
)
LEFT JOIN
PROCESSFLOW K
ON A.PROCESSFLOWID = K.PROCESSFLOWID
AND A.FACTORYID = K.FACTORYID
LEFT JOIN
PROCESSOPERATION L
ON A.FACTORYID = L.FACTORYID
AND A.RETURNPROCESSOPERATIONID = L.PROCESSOPERATIONID
LEFT JOIN
SUBLOT N
ON A.LOTID = N.LOTID
LEFT JOIN
WORKORDER M
ON A.FACTORYID = M.FACTORYID
AND A.WORKORDERID = M.WORKORDERID
LEFT JOIN
CONSUMABLELOT N
ON A.CONSUMABLELOTID = N.CONSUMABLELOTID
WHERE
A.FACTORYID = 'REGEN_FAB01' /**P*/
AND (
A.LOTID = 'SH16A2219' /**P*/
OR A.LOTID IN (
(
SELECT
DISTINCT PORT2LOTID
FROM
LOTDESTINATION
WHERE
PORT1LOTID = 'SH16A2219' /**P*/
)
UNION
(
SELECT
DISTINCT PORT1LOTID
FROM
LOTDESTINATION
WHERE
PORT2LOTID = 'SH16A2219' /**P*/
)
UNION
(
SELECT
DISTINCT TARGETLOTID
FROM
LOTGENEALOGYHISTORY
WHERE
LOTID = 'SH16A2219' /**P*/
AND GENDIRECTION = 'To'
)
)
)
ORDER BY
A.LOTID
쿼리는 이렇구요 LEFT JOIN
CONSUMABLELOT N
ON A.CONSUMABLELOTID = N.CONSUMABLELOTID 이부분을 제거해주니 정상 실행됩니다..