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

[Giani]

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

[Sameer]

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

[Giani]

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.

[Sameer]

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.

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

[abhaysk]

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.

[Giani]

Thanks Sameer for your help and to abhaysk as well.

Regards,