-
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
-
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.
-
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
-
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
-
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"
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|