4 Replies Latest reply: Sep 19, 2013 8:41 AM by Jeff Smith Sqldev Pm-Oracle RSS

    4.0 EA2 - AWR and ASH giving ora-01427

    markdba123

      I might have something not set correctly but when I try and generate a report (ASH or AWR), I get:

       

      An error was encountered performing the requested operation:

      ORA-01427: single-row subquery returns more than one row

      01427. 00000 -  "single-row subquery returns more than one row"

      *Cause:   

      *Action:

      Vendor code 1427

       

      I'm connecting to an 11.2.03 RAC configured database. I haven't tried a single instance yet but I thought I'd ask and see if anyone else is having a similar issue.

        • 1. Re: 4.0 EA2 - AWR and ASH giving ora-01427
          User286067

          I saw 'ASH Report Viewer' issuing following command, so obviously it will not work on RAC. This is probably a bug, it works well in single instance.

           

          SELECT output
            FROM TABLE (DBMS_WORKLOAD_REPOSITORY.ash_report_html ( (SELECT dbid FROM gv$database),
                                                                  (SELECT inst_id FROM gv$database),
                                                                  :BEGIN_TIME,
                                                                  :END_TIME,
                                                                  0,
                                                                  0,
                                                                  :SID,
                                                                  :SQL_ID,
                                                                  :WAIT_CLASS,
                                                                  :SERVICE_HASH,
                                                                  :MODULE,
                                                                  :ACTION,
                                                                  :CLIENT_ID))
          
          • 2. Re: 4.0 EA2 - AWR and ASH giving ora-01427
            markdba123

            Thanks for checking. I'll have to add this to the bug list.

            • 3. Re: 4.0 EA2 - AWR and ASH giving ora-01427
              User286067

              Thanks, I guess it is the same problem in most ASH/AWR error cases, they select from GV$

              • 4. Re: 4.0 EA2 - AWR and ASH giving ora-01427
                Jeff Smith Sqldev Pm-Oracle

                Bugged - thanks for the report!