Many people got bitten in the 8i versions by the "java_pool_size" parameter defaulting to 20M, and thus, somewhat secretly chewing up more RAM than was expected.
The resolution was to drop the size of the java pool down to an appropriate value (typically 1m on 8.1.5 and 32k on 8.1.6+). A lot of databases had the Java option loaded (since that is the default configuration), but if it was not being used, simply shrinking the pool down limited any impact.
Interestingly with 9i, once you loadup the Java option, its a lot more conspicuous (or is "intrusive" the right word?). With a very small Java pool (after all, I wasn't using it - so I thought), I tried this:
SQL> create table demotab ( x number ) ; create table demotab ( x number ) * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-04031: unable to allocate 4032 bytes of shared memory ("shared pool","oracle/CDC/ChangeTableSYS" ehe","ioc_allocate_pal") ORA-06512: at "SYS.DBMS_CDC_PUBLISH", line 0 ORA-06512: at line 4
A quick search around the data dictionary shows that there are some database level triggers in place in 9i. In fact, for every CREATE or ALTER command that you run, the following code is executed:
IF dictionary_obj_type = 'TABLE' THEN sys.dbms_cdc_publish.change_table_trigger(dictionary_obj_owner,dictionary_obj_name,sysevent); END IF;
The DBMS_CDC_PUBLISH is part of the Oracle 9i data warehousing enhancements for tracking database modifications (for processing of deltas). So once you've loaded up the Java option, be aware that you are going to need at least a moderately sized java pool to actually CREATE or ALTER tables. The bottom line is - if you are not going to use Java in your database, you should probably fully remove it using the appropriate scripts rather than just reduce the java_pool_size parameter