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