DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: ORA-01422: exact fetch returns more than requested number of rows

  1. #1
    Join Date
    Nov 2002
    Location
    Dublin, Ireland
    Posts
    133

    ORA-01422: exact fetch returns more than requested number of rows

    Can someone help me out how to write the below query in PLSQL please.
    I want to get db_block_size dynamicaly to calucate in another query.
    if I run the query on a single column it worked fine but when I add
    more columns I get the error like :
    ORA-01422: exact fetch returns more than requested number of rows
    ORA-06512: at line 16.

    Thanks in advance for your help.

    SQL>declare
    2 a_max_ext number ;
    3 a_db_block_size number ;
    4 begin
    5 select value into a_db_block_size from v$parameter where name='db_block_size' ;
    6 select max(seg$.extsize)*a_db_block_size max_ext into a_max_ext from sys.seg$ ;
    7 dbms_output.put_line (a_max_ext) ;
    8 end ;
    9 /
    377524224

    PL/SQL procedure successfully completed.

    -----------------------

    declare
    a_ts_name number ;
    a_max_free number ;
    a_max_ext number ;
    a_ext_pct number ;
    a_max_next_ext number ;
    a_exts_left number ;
    a_db_block_size number ;

    begin

    SELECT value
    into a_db_block_size
    from v$parameter where name='db_block_size' ;

    select
    ts$.name,
    max(fet$.length)*a_db_block_size max_free,
    max(seg$.extsize)*a_db_block_size max_ext,
    seg$.extpct pct,
    ceil(max(seg$.extsize*((100+seg$.extpct)/100)))*a_db_block_size max_next_ext,
    trunc(max(fet$.length)/ceil(max(seg$.extsize*((100+seg$.extpct)/100))),2) exts_left
    into
    a_ts_name,
    a_max_free,
    a_max_ext,
    a_ext_pct,
    a_max_next_ext,
    a_exts_left
    from sys.seg$, sys.fet$, sys.ts$
    where ts$.ts# = fet$.ts#
    and seg$.ts# = fet$.ts#
    group by ts$.name,seg$.extpct
    having max(fet$.length)/ceil(max(seg$.extsize*((100+seg$.extpct)/100))) < 20
    order by 6 ;
    end;
    /
    -----------------------
    SQL>
    declare
    *
    ERROR at line 1:
    ORA-01422: exact fetch returns more than requested number of rows
    ORA-06512: at line 16
    Giani

  2. #2
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Look at your FROM, GROUP BY clause, it will definately return more than one row.. Query those individually and see how many records you get...

    sys.seg$ - Many segments
    sys.fet$ - Many tablespaces and datafiles
    sys.ts$ - Many tablespaces (SYSTEM, TEMP) atleast two
    Last edited by Sameer; 01-08-2003 at 06:46 AM.

  3. #3
    Join Date
    Nov 2002
    Location
    Dublin, Ireland
    Posts
    133
    Sameer, my query worked when I run it individually but I want columns
    MAX_FREE,MAX_EXT and MAX_NEXT_EXT result in bytes instead of blocks.
    Any idea how to write PLSQL code to get db_block_size dynamically and calculate it.

    Thanks.


    SQL>select
    2 ts$.name,
    3 max(fet$.length) max_free,
    4 max(seg$.extsize) max_ext,
    5 seg$.extpct pct,
    6 ceil(max(seg$.extsize*((100+seg$.extpct)/100))) max_next_ext,
    7 trunc(max(fet$.length)/ceil(max(seg$.extsize*((100+seg$.extpct)/100))),2) exts_left
    8 from sys.seg$, sys.fet$, sys.ts$
    9 where ts$.ts# = fet$.ts#
    10 and seg$.ts# = fet$.ts#
    11 group by ts$.name,seg$.extpct
    12 having max(fet$.length)/ceil(max(seg$.extsize*((100+seg$.extpct)/100))) < 20
    13 order by 6 ;

    NAME MAX_FREE MAX_EXT PCT MAX_NEXT_EXT EXTS_LEFT
    ------------------------------ ---------- ---------- ---------- ------------ ----------
    SMS 40569 92169 50 138254 .29
    TOOLS 1778 1280 0 1280 1.38

    2 rows selected.
    Giani

  4. #4
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    I am getting you. In all, are you trying to findout free/used space in tablespace? Finding a max extent without segment name doesn't make any use, this is what I feel. If you need to findout what I thought then use this query.
    Code:
    SELECT Substr(df.tablespace_name,1,20) "Tablespace_Name", 
           Substr(df.file_name,1,40) "File_Name", 
           Round(df.bytes/1024/1024,2) "Size_(M)", 
           Round(e.used_bytes/1024/1024,2) "Used_(M)", 
           Round(f.free_bytes/1024/1024,2) "Free_(M)" 
    FROM   DBA_DATA_FILES DF, 
           (SELECT file_id, 
                   Sum(Decode(bytes,NULL,0,bytes)) used_bytes 
            FROM dba_extents 
            GROUP by file_id) E, 
           (SELECT Max(bytes) free_bytes, 
                   file_id 
            FROM dba_free_space 
            GROUP BY file_id) f 
    WHERE  e.file_id (+) = df.file_id 
    AND    df.file_id  = f.file_id (+) 
    ORDER BY df.tablespace_name, 
             df.file_name

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Giani,

    When u have inbuilt DBA_Views why do u go for base tables to know used space, free space and so.......

    i think sammer's Query will fit ur req.......

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #6
    Join Date
    Nov 2002
    Location
    Dublin, Ireland
    Posts
    133
    Thanks Sameer for your help and to abhaysk as well.

    Regards,
    Giani

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width