DBMS_SHARED_POOL => list objects on shared pool

The DBMS_SHARED_POOL package provides access to the shared pool.
To create DBMS_SHARED_POOL, run the DBMSPOOL.SQL script:

$ cd $ORACLE_HOME
$ cd rdbms/admin/
$ sqlplus / as sysdba @dbmspool.sql


SIZES Procedure: Shows objects in the shared pool that are larger than the specified size

List all objects with size 100M:

SQL> SET SERVEROUT ON
SQL> EXECUTE dbms_shared_pool.sizes (102400);

SIZE(K) KEPT NAME
------- ------ ---------------------------------------------------------------
424764 XX.AAAA (SUMMARY)
139582 XX.BBBB (SUMMARY)
139582 XX.CCCC (SUMMARY)
104687 XX.DDDD (SUMMARY)



Enjoy!