ORA-04031: unable to allocate 8388608 bytes of shared memory

I have a query returning the following error message:

ORA-04031: unable to allocate 8388608 bytes of shared memory ("shared pool","unknown object","hash-join subh","QERHJ Hash Table Entries")

What I really would like to know, is if an attempt to allocate "8388608 bytes" is "normal"?

Should a query be attempting to allocate this much memory? The database is not what I would call big (i.e. 300000 rows and 600000 rows in the main tables).

TIA Mark

---

Here are some details:

The query is:

SELECT "Units"."UnitKey", "Units"."UnitName", "Units"."StreetName",


"Units"."StreetNmbr", "Units"."PostalZone", "Units"."Town",

"Units"."AnimalMovementDBIDs" FROM "Units" WHERE  1=1

 AND "Units"."UnitKey" IN
(SELECT "UnitKey" FROM "UnitCategories" WHERE
("CategoryKey"=HEXTORAW('8EFF2E9DBEE54A97B25303D4F50DB655') OR


"CategoryKey"=HEXTORAW('0BF64F42B37446E1BB828E1889CE4929')) AND

("Units"."RVOKey" = HEXTORAW('E41B25063E744543B26419F190CF62B2'))   )
AND "Units"."UnitKey" IN
(SELECT "Units"."UnitKey" FROM "Units" WHERE  1=1  AND
(((lower("Units"."UnitName") LIKE '%l%')  OR
(lower("Units"."StreetName") LIKE '%l%')  OR (lower("Units"."Town")
LIKE '%l%')  OR (lower("Units"."AnimalMovementDBIDs") LIKE '%l%'))   ))

Excuse the messy SQL, it comes from an ad-hoc quesry builder. There is
actually a bug in the above SQL, it runs, but the second sub-query is
wrong.

Database: 9.2i
Operating System: Solaris (not sure which version)