SELECT T1.EVENTTIMESTAMP,
T1.OBJECTID,
T1.UIA_AREA_ID,
T1.UIA_LOCATION_ID,
T1.RELATED_NE6,
T1.RELATED_NE5,
T1.VENDOR_NAME,
T1.CELLLABEL,
T1.TAC,
T1.CGI,
T1.EARFCN,
T1.PCI,
T1.LONGITUDE,
T1.LATITUDE,
T1.ADJ_GSM_NUM,
T1.NOBJECTID,
T1.NCELLLABEL,
T1.NLAC,
T1.NCGI,
T1.NBCCH,
T1.NBSIC,
T1.NLONGITUDE,
T1.NLATITUDE,
T1.DISTANCE,
T1.MR_LTETOGSMRSRP,
CASE
WHEN T2.OBJECTID IS NULL THEN
'否' ELSE
'是'
END AS ISNEIGHBOR,
CASE
WHEN T3.OBJECTID IS NULL THEN
'否' ELSE
'是'
END AS ISRNEIGHBOR,
T1.ISMSCPOOL,
1 AS EVA,
CASE
WHEN T4.OBJECTID IS NULL THEN
'否'
ELSE
'是'
END AS ISADDBCCH
FROM (SELECT T1.EVENTTIMESTAMP,
T1.SEARCHCODE AS OBJECTID,
T3.UIA_AREA_ID,
T3.UIA_LOCATION_ID,
T3.RELATED_NE6,
T3.RELATED_NE5,
T3.VENDOR_NAME,
T3.CELLLABEL,
T3.TAC,
T3.CGI,
T3.EARFCN,
T3.PCI,
T3.LONGITUDE,
T3.LATITUDE,
T3.ADJ_GSM_NUM,
T1.NSEARCHCODE AS NOBJECTID,
T2.CELLLABEL AS NCELLLABEL,
T2.LAC AS NLAC,
T2.CGI AS NCGI,
T2.BCCH AS NBCCH,
T2.BSIC AS NBSIC,
T2.LONGITUDE AS NLONGITUDE,
T2.LATITUDE AS NLATITUDE,
T1.DISTANCE,
T1.MR_LTETOGSMEFFNUM AS MR_LTETOGSMRSRP,
CASE
WHEN T2.LAC = T3.TAC THEN
'否' ELSE
'是'
END AS ISMSCPOOL
FROM (SELECT EVENTTIMESTAMP,
SEARCHCODE,
ENBID,
CELLID,
VENDOR,
NSEARCHCODE,
MR_GSMNCELLBSIC,
MR_GSMNCELLBCCH,
MR_LTETOGSMEFFNUM,
MR_LTETOGSMALLNUM,
DISTANCE
FROM APP.TMRO4GCELL_2GNEIGHBER_DAY@ossdb137 T
WHERE T.EVENTTIMESTAMP =
TRUNC(TO_DATE('#STARTTIME',
'YYYY-MM-DD HH24:MI:SS') - 1)
AND T.DISTANCE <= 2000) T1
LEFT JOIN (SELECT T.SEARCHCODE,
CELLLABEL,
LAC,
CGI,
BCCH,
BSIC,
LONGITUDE,
LATITUDE
FROM UIA.UIA_DATE_CELL@ossdb137 T
WHERE T.UIA_TIME_ID =
TO_CHAR(TRUNC(TO_DATE('#STARTTIME',
'YYYY-MM-DD HH24:MI:SS') - 1),
'YYYYMMDD')) T2
ON T1.NSEARCHCODE = T2.SEARCHCODE
LEFT JOIN (SELECT T.SEARCHCODE,
UIA_AREA_ID,
UIA_LOCATION_ID,
RELATED_NE6,
RELATED_NE5,
VENDOR_NAME,
CELLLABEL,
TAC,
CGI,
EARFCN,
PCI,
LONGITUDE,
LATITUDE,
ADJ_GSM_NUM
FROM UIA.UIA_DATE_EUTRANCELL@ossdb137 T
WHERE T.UIA_TIME_ID =
TO_CHAR(TRUNC(TO_DATE('#STARTTIME',
'YYYY-MM-DD HH24:MI:SS') - 1),
'YYYYMMDD')
AND UIA_FACTORY_ID IN(1,7,8,9)) T3
ON T1.SEARCHCODE = T3.SEARCHCODE
WHERE T2.SEARCHCODE IS NOT NULL
AND T3.SEARCHCODE IS NOT NULL) T1
LEFT JOIN (SELECT T.OBJECTID, T.NOBJECTID
FROM APP.INTPERTY_HCDION_NB@ossdb137 T
WHERE T.EVENTTIMESTAMP =
TRUNC(TO_DATE('#STARTTIME',
'YYYY-MM-DD HH24:MI:SS') - 1)
AND T.EVA = 2) T2
ON T1.OBJECTID = T2.OBJECTID
AND T1.NOBJECTID = T2.NOBJECTID
LEFT JOIN (SELECT T.OBJECTID, T.NOBJECTID
FROM APP.INTPERTY_HCDION_NB@ossdb137 T
WHERE T.EVENTTIMESTAMP =
TRUNC(TO_DATE('#STARTTIME',
'YYYY-MM-DD HH24:MI:SS') - 1)
AND T.EVA = 4) T3
ON T1.OBJECTID = T3.NOBJECTID
AND T1.NOBJECTID = T3.OBJECTID
LEFT JOIN (SELECT DISTINCT T.OBJECTID, T.UARFCN
FROM APP.INTPERTY_HCDION_NB@ossdb137 T
WHERE T.EVENTTIMESTAMP =
TRUNC(TO_DATE('#STARTTIME', 'YYYY-MM-DD HH24:MI:SS') - 1)
AND T.EVA = 2)T4
ON T1.OBJECTID = T4.OBJECTID
AND T1.NBCCH = T4.UARFCN
T1.OBJECTID,
T1.UIA_AREA_ID,
T1.UIA_LOCATION_ID,
T1.RELATED_NE6,
T1.RELATED_NE5,
T1.VENDOR_NAME,
T1.CELLLABEL,
T1.TAC,
T1.CGI,
T1.EARFCN,
T1.PCI,
T1.LONGITUDE,
T1.LATITUDE,
T1.ADJ_GSM_NUM,
T1.NOBJECTID,
T1.NCELLLABEL,
T1.NLAC,
T1.NCGI,
T1.NBCCH,
T1.NBSIC,
T1.NLONGITUDE,
T1.NLATITUDE,
T1.DISTANCE,
T1.MR_LTETOGSMRSRP,
CASE
WHEN T2.OBJECTID IS NULL THEN
'否' ELSE
'是'
END AS ISNEIGHBOR,
CASE
WHEN T3.OBJECTID IS NULL THEN
'否' ELSE
'是'
END AS ISRNEIGHBOR,
T1.ISMSCPOOL,
1 AS EVA,
CASE
WHEN T4.OBJECTID IS NULL THEN
'否'
ELSE
'是'
END AS ISADDBCCH
FROM (SELECT T1.EVENTTIMESTAMP,
T1.SEARCHCODE AS OBJECTID,
T3.UIA_AREA_ID,
T3.UIA_LOCATION_ID,
T3.RELATED_NE6,
T3.RELATED_NE5,
T3.VENDOR_NAME,
T3.CELLLABEL,
T3.TAC,
T3.CGI,
T3.EARFCN,
T3.PCI,
T3.LONGITUDE,
T3.LATITUDE,
T3.ADJ_GSM_NUM,
T1.NSEARCHCODE AS NOBJECTID,
T2.CELLLABEL AS NCELLLABEL,
T2.LAC AS NLAC,
T2.CGI AS NCGI,
T2.BCCH AS NBCCH,
T2.BSIC AS NBSIC,
T2.LONGITUDE AS NLONGITUDE,
T2.LATITUDE AS NLATITUDE,
T1.DISTANCE,
T1.MR_LTETOGSMEFFNUM AS MR_LTETOGSMRSRP,
CASE
WHEN T2.LAC = T3.TAC THEN
'否' ELSE
'是'
END AS ISMSCPOOL
FROM (SELECT EVENTTIMESTAMP,
SEARCHCODE,
ENBID,
CELLID,
VENDOR,
NSEARCHCODE,
MR_GSMNCELLBSIC,
MR_GSMNCELLBCCH,
MR_LTETOGSMEFFNUM,
MR_LTETOGSMALLNUM,
DISTANCE
FROM APP.TMRO4GCELL_2GNEIGHBER_DAY@ossdb137 T
WHERE T.EVENTTIMESTAMP =
TRUNC(TO_DATE('#STARTTIME',
'YYYY-MM-DD HH24:MI:SS') - 1)
AND T.DISTANCE <= 2000) T1
LEFT JOIN (SELECT T.SEARCHCODE,
CELLLABEL,
LAC,
CGI,
BCCH,
BSIC,
LONGITUDE,
LATITUDE
FROM UIA.UIA_DATE_CELL@ossdb137 T
WHERE T.UIA_TIME_ID =
TO_CHAR(TRUNC(TO_DATE('#STARTTIME',
'YYYY-MM-DD HH24:MI:SS') - 1),
'YYYYMMDD')) T2
ON T1.NSEARCHCODE = T2.SEARCHCODE
LEFT JOIN (SELECT T.SEARCHCODE,
UIA_AREA_ID,
UIA_LOCATION_ID,
RELATED_NE6,
RELATED_NE5,
VENDOR_NAME,
CELLLABEL,
TAC,
CGI,
EARFCN,
PCI,
LONGITUDE,
LATITUDE,
ADJ_GSM_NUM
FROM UIA.UIA_DATE_EUTRANCELL@ossdb137 T
WHERE T.UIA_TIME_ID =
TO_CHAR(TRUNC(TO_DATE('#STARTTIME',
'YYYY-MM-DD HH24:MI:SS') - 1),
'YYYYMMDD')
AND UIA_FACTORY_ID IN(1,7,8,9)) T3
ON T1.SEARCHCODE = T3.SEARCHCODE
WHERE T2.SEARCHCODE IS NOT NULL
AND T3.SEARCHCODE IS NOT NULL) T1
LEFT JOIN (SELECT T.OBJECTID, T.NOBJECTID
FROM APP.INTPERTY_HCDION_NB@ossdb137 T
WHERE T.EVENTTIMESTAMP =
TRUNC(TO_DATE('#STARTTIME',
'YYYY-MM-DD HH24:MI:SS') - 1)
AND T.EVA = 2) T2
ON T1.OBJECTID = T2.OBJECTID
AND T1.NOBJECTID = T2.NOBJECTID
LEFT JOIN (SELECT T.OBJECTID, T.NOBJECTID
FROM APP.INTPERTY_HCDION_NB@ossdb137 T
WHERE T.EVENTTIMESTAMP =
TRUNC(TO_DATE('#STARTTIME',
'YYYY-MM-DD HH24:MI:SS') - 1)
AND T.EVA = 4) T3
ON T1.OBJECTID = T3.NOBJECTID
AND T1.NOBJECTID = T3.OBJECTID
LEFT JOIN (SELECT DISTINCT T.OBJECTID, T.UARFCN
FROM APP.INTPERTY_HCDION_NB@ossdb137 T
WHERE T.EVENTTIMESTAMP =
TRUNC(TO_DATE('#STARTTIME', 'YYYY-MM-DD HH24:MI:SS') - 1)
AND T.EVA = 2)T4
ON T1.OBJECTID = T4.OBJECTID
AND T1.NBCCH = T4.UARFCN
WHERE T2.OBJECTID IS NULL
ORA-01652:unable to extend temp segment by num in tablespace name
是由于 WHERE T2.SEARCHCODE IS NOT NULL AND T3.SEARCHCODE IS NOT NULL 造成的。而不是因为oracle temp空间少造成的。