一个drop operation 持续2天没有出来!!

接到T3的请求说一个Drop materialied view preserve table 持续2天没有出来。

原来是被一个大的SQL block住了。

看来是一个大的循环。

SQL> select xidusn, object_id, session_id, locked_mode from v$locked_object;

    XIDUSN  OBJECT_ID SESSION_ID LOCKED_MODE
---------- ---------- ---------- -----------
         9        212        242           3
         9        224        242           3
         9        228        242           3
         9        226        242           3
         9        220        242           3
SQL> SELECT PID FROM V$PROCESS WHERE ADDR=
  2          (SELECT PADDR FROM V$SESSION WHERE SID=242);

       PID
----------
       136
       SQL> select blocking_session,event from v$session where sid=242;

BLOCKING_SESSION
----------------
EVENT
--------------------------------------------------------------------------------
             264
library cache lock

SQL> select sql_id,sql_hash_value,sql_address
  2   from v$session where sid=264;

SQL_ID                     SQL_HASH_VALUE SQL_ADDRESS
-------------------------- -------------- ----------------
2h78kmrvf333d                  4141976685 0000000389313F10
SQL> select  SQL_FULLTEXT from v$sql where SQL_ID='2h78kmrvf333d';
The  follow SQL block the drop operation.
/* Formatted on 2009/08/04 13:34 (Formatter Plus v4.8.8) */
SELECT   devs.device_name, al.nbocc, al.nbalert, al.nbticket, al.nbmast, t999,
        t998, t997, t996, t995, t994, t993, t992, t991, t990, t989, t988,
        t987, t986, t985, t984, t983, t982, t981, t980, t979, t978, t977,
        t976, t975, t974, t973, t972, t971, t970, t969, t968, t967, t966,
        t965, t964, t963, t962, t961, t960, t959, t958, t957, t956, t955,
        t954, t953, t952, t951, t950, t949, t948, t947, t946, t945, t944,
        t943, t942, t941, t940, t939, t938, t937, t936, t935, t934, t933,
        t932, t931, t930, t929, t928, t927, t926, t925, t924, t923, t922,
        t921, t920, t919, t918, t917, t916, t915, t914, t913, t912, t911,
        t910, t909, t908, t907, t906, t905, t904, t903, t902, t901, t900,
        t899, t898, t897, t896, t895, t894, t893, t892, t891, t890, t889,
        t888, t887, t886, t885, t884, t883, t882, t881, t880, t879, t878,
        t877, t876, t875, t874, t873, t872, t871, t870, t869, t868, t867,
        t866, t865, t864, t863, t862, t861, t860, t859, t858, t857, t856,
        t855, t854, t853, t852, t851, t850, t849, t848, t847, t846, t845,
        t844, t843, t842, t841, t840, t839, t838, t837, t836, t835, t834,
        t833, t832, t831, t830, t829, t828, t827, t826, t825, t824, t823,
        t822, t821, t820, t819, t818, t817, t816, t815, t814, t813, t812,
        t811, t810, t809, t808, t807, t806, t805, t804, t803, t802, t801,
        t800, t799, t798, t797, t796, t795, t794, t793, t792, t791, t790,
        t789, t788, t787, t786, t785, t784, t783, t782, t781, t780, t779,
        t778, t777, t776, t775, t774, t773, t772, t771, t770, t769, t768,
        t767, t766, t765, t764, t763, t762, t761, t760, t759, t758, t757,
        t756, t755, t754, t753, t752, t751, t750, t749, t748, t747, t746,
        t745, t744, t743, t742, t741, t740, t739, t738, t737, t736, t735,
        t734, t733, t732, t731, t730, t729, t728, t727, t726, t725, t724,
        t723, t722, t721, t720, t719, t718, t717, t716, t715, t714, t713,
        t712, t711, t710, t709, t708, t707, t706, t705, t704, t703, t702,
        t701, t700, t699, t698, t697, t696, t695, t694, t693, t692, t691,
        t690, t689, t688, t687, t686, t685, t684, t683, t682, t681, t680,
        t679, t678, t677, t676, t675, t674, t673, t672, t671, t670, t669,
        t668, t667, t666, t665, t664, t663, t662, t661, t660, t659, t658,
        t657, t656, t655, t654, t653, t652, t651, t650, t649, t648, t647,
        t646, t645, t644, t643, t642, t641, t640, t639, t638, t637, t636,
        t635, t634, t633, t632, t631, t630, t629, t628, t627, t626, t625,
        t624, t623, t622, t621, t620, t619, t618, t617, t616, t615, t614,
        t613, t612, t611, t610, t609, t608, t607, t606, t605, t604, t603,
        t602, t601, t600, t599, t598, t597, t596, t595, t594, t593, t592,
        t591, t590, t589, t588, t587, t586, t585, t584, t583, t582, t581,
        t580, t579, t578, t577, t576, t575, t574, t573, t572, t571, t570,
        t569, t568, t567, t566, t565, t564, t563, t562, t561, t560, t559,
        t558, t557, t556, t555, t554, t553, t552, t551, t550, t549, t548,
        t547, t546, t545, t544, t543, t542, t541, t540, t539, t538, t537,
        t536, t535, t534, t533, t532, t531, t530, t529, t528, t527, t526,
        t525, t524, t523, t522, t521, t520, t519, t518, t517, t516, t515,
        t514, t513, t512, t511, t510, t509, t508, t507, t506, t505, t504,
        t503, t502, t501, t500, t499, t498, t497, t496, t495, t494, t493,
        t492, t491, t490, t489, t488, t487, t486, t485, t484, t483, t482,
        t481, t480, t479, t478, t477, t476, t475, t474, t473, t472, t471,
        t470, t469, t468, t467, t466, t465, t464, t463, t462, t461, t460,
        t459, t458, t457, t456, t455, t454, t453, t452, t451, t450, t449,
        t448, t447, t446, t445, t444, t443, t442, t441, t440, t439, t438,
        t437, t436, t435, t434, t433, t432, t431, t430, t429, t428, t427,
        t426, t425, t424, t423, t422, t421, t420, t419, t418, t417, t416,
        t415, t414, t413, t412, t411, t410, t409, t408, t407, t406, t405,
        t404, t403, t402, t401, t400, t399, t398, t397, t396, t395, t394,
        t393, t392, t391, t390, t389, t388, t387, t386, t385, t384, t383,
        t382, t381, t380, t379, t378, t377, t376, t375, t374, t373, t372,
        t371, t370, t369, t368, t367, t366, t365, t364, t363, t362, t361,
        t360, t359, t358, t357, t356, t355, t354, t353, t352, t351, t350,
        t349, t348, t347, t346, t345, t344, t343, t342, t341, t340, t339,
        t338, t337, t336, t335, t334, t333, t332, t331, t330, t329, t328,
        t327, t326, t325, t324, t323, t322, t321, t320, t319, t318, t317,
        t316, t315, t314, t313, t312, t311, t310, t309, t308, t307, t306,
        t305, t304, t303, t302, t301, t300, t299, t298, t297, t296, t295,
        t294, t293, t292, t291, t290, t289, t288, t287, t286, t285, t284,
        t283, t282, t281, t280, t279, t278, t277, t276, t275, t274, t273,
        t272, t271, t270, t269, t268, t267, t266, t265, t264, t263, t262,
        t261, t260, t259, t258, t257, t256, t255, t254, t253, t252, t251,
        t250, t249, t248, t247, t246, t245, t244, t243, t242, t241, t240,
        t239, t238, t237, t236, t235, t234, t233, t232, t231, t230, t229,
        t228, t227, t226, t225, t224, t223, t222, t221, t220, t219, t218,
        t217, t216, t215, t214, t213, t212, t211, t210, t209, t208, t207,
        t206, t205, t204, t203, t202, t201, t200, t199, t198, t197, t196,
        t195, t194, t193, t192, t191, t190, t189, t188, t187, t186, t185,
        t184, t183, t182, t181, t180, t179, t178, t177, t176, t175, t174,
        t173, t172, t171, t170, t169, t168, t167, t166, t165, t164, t163,
        t162, t161, t160, t159, t158, t157, t156, t155, t154, t153, t152,
        t151, t150, t149, t148, t147, t146, t145, t144, t143, t142, t141,
        t140, t139, t138, t137, t136, t135, t134, t133, t132, t131, t130,
        t129, t128, t127, t126, t125, t124, t123, t122, t121, t120, t119,
        t118, t117, t116, t115, t114, t113, t112, t111, t110, t109, t108,
        t107, t106, t105, t104, t103, t102, t101, t100, t99, t98, t97, t96,
        t95, t94, t93, t92, t91, t90, t89, t88, t87, t86, t85, t84, t83, t82,
        t81, t80, t79, t78, t77, t76, t75, t74, t73, t72, t71, t70, t69, t68,
        t67, t66, t65, t64, t63, t62, t61, t60, t59, t58, t57, t56, t55, t54,
        t53, t52, t51, t50, t49, t48, t47, t46, t45, t44, t43, t42, t41, t40,
        t39, t38, t37, t36, t35, t34, t33, t32, t31, t30, t29, t28, t27, t26,
        t25, t24, t23, t22, t21, t20, t19, t18, t17, t16, t15, t14, t13, t12,
        t11, t10, t9, t8, t7, t6, t5, t4, t3, t2, t1
   FROM (SELECT inv.device_name
           FROM cp_inventory.inventory inv, ACCOUNT.customerinfo ci
          WHERE ci.customername = :"SYS_B_00000"
            AND ci.customerid = inv.customer_id
            AND inv.inventory_status_id IN (:"SYS_B_00001", :"SYS_B_00002")) devs,
        (SELECT   node, SUM (tally) nbocc, COUNT (tally) nbalert,
                  SUM (DECODE (grade, :"SYS_B_00003", :"SYS_B_00004", NULL)
                      ) nbticket,
                  COUNT (mast) nbmast
             FROM reporter.reporter_status
            WHERE customername = :"SYS_B_00005"
              AND devstatus IN (:"SYS_B_00006", :"SYS_B_00007")
              AND maintflag = :"SYS_B_00008"
              AND firstoccurrence BETWEEN (  TRUNC (SYSDATE)
                                           - :"SYS_B_00009"
                                           - :"SYS_B_00010"
                                          )
                                      AND (TRUNC (SYSDATE) - :"SYS_B_00011")
              AND originalseverity IN (:"SYS_B_00012", :"SYS_B_00013")
              AND REGEXP_INSTR (summary,
                                :"SYS_B_00014",
                                :"SYS_B_00015",
                                :"SYS_B_00016",
                                :"SYS_B_00017",
                                :"SYS_B_00018"
                               ) > :"SYS_B_00019"
         GROUP BY node) al,
        (SELECT   node, SUM (tally) t999
             FROM reporter.reporter_status
            WHERE customername = :"SYS_B_00020"
              AND devstatus IN (:"SYS_B_00021", :"SYS_B_00022")
              AND maintflag = :"SYS_B_00023"
              AND originalseverity IN (:"SYS_B_00024", :"SYS_B_00025")
              AND REGEXP_INSTR (summary,
                                :"SYS_B_00026",
                                :"SYS_B_00027",
                                :"SYS_B_00028",
                                :"SYS_B_00029",
                                :"SYS_B_00030"
                               ) > :"SYS_B_00031"
              AND (firstoccurrence BETWEEN (  TRUNC (SYSDATE)
                                            - :"SYS_B_00032"
                                            - :"SYS_B_00033"
                                           )
                                       AND (  TRUNC (SYSDATE)
                                            - (:"SYS_B_00034"
                                               - :"SYS_B_00035"
                                              )
                                            - :"SYS_B_00036"
                                           )
                  )
         GROUP BY node) d999,
        (SELECT   node, SUM (tally) t998
             FROM reporter.reporter_status
            WHERE customername = :"SYS_B_00037"
              AND devstatus IN (:"SYS_B_00038", :"SYS_B_00039")
              AND maintflag = :"SYS_B_00040"
              AND originalseverity IN (:"SYS_B_00041", :"SYS_B_00042")
              AND REGEXP_INSTR (summary,
                                :"SYS_B_00043",
                                :"SYS_B_00044",
                                :"SYS_B_00045",
                                :"SYS_B_00046",
                                :"SYS_B_00047"
                               ) > :"SYS_B_00048"
              AND (firstoccurrence BETWEEN (  TRUNC (SYSDATE)
                                            - :"SYS_B_00049"
                                            - :"SYS_B_00050"
                                           )
                                       AND (  TRUNC (SYSDATE)
                                            - (:"SYS_B_00051"
                                               - :"SYS_B_00052"
                                              )
                                            - :"SYS_B_00053"
                                           )
                  )
         GROUP BY node) d998,
        (SELECT   node, SUM (tally) t997
             FROM reporter.reporter_status
            WHERE customername = :"SYS_B_00054"
              AND devstatus IN (:"SYS_B_00055", :"SYS_B_00056")
              AND maintflag = :"SYS_B_00057"
              AND originalseverity IN (:"SYS_B_00058", :"SYS_B_00059")
              AND REGEXP_INSTR (summary,
                                :"SYS_B_00060",
                                :"SYS_B_00061",
                                :"SYS_B_00062",
                                :"SYS_B_00063",
                                :"SYS_B_00064"
                               ) > :"SYS_B_00065"
              AND (firstoccurrence BETWEEN (  TRUNC (SYSDATE)
                                            - :"SYS_B_00066"
                                            - :"SYS_B_00067"
                                           )
                                       AND (  TRUNC (SYSDATE)
                                            - (:"SYS_B_00068"
                                               - :"SYS_B_00069"
                                              )
                                            - :"SYS_B_00070"
                                           )
                  )
         GROUP BY node) d997,
        (SELECT   node, SUM (tally) t996
             FROM reporter.reporter_status
            WHERE customername = :"SYS_B_00071"
              AND devstatus IN (:"SYS_B_00072", :"SYS_B_00073")
              AND maintflag = :"SYS_B_00074"
              AND originalseverity IN (:"SYS_B_00075", :"SYS_B_00076")
              AND REGEXP_INSTR (summary,
                                :"SYS_B_00077",
                                :"SYS_B_00078",
                                :"SYS_B_00079",
                                :"SYS_B_00080",
                                :"SYS_B_00081"
                               ) > :"SYS_B_00082"
              AND (firstoccurrence BETWEEN (  TRUNC (SYSDATE)
                                            - :"SYS_B_00083"
                                            - :"SYS_B_00084"
                                           )
                                       AND (  TRUNC (SYSDATE)
                                            - (:"SYS_B_00085"
                                               - :"SYS_B_00086"
                                              )
                                            - :"SYS_B_00087"
                                           )
                  )
         GROUP BY node) d996,
        (SELECT   node, SUM (tally) t995.......................直到t3,t2,t1

以下省略