While I appreciate that this isn't a solution to the underlying problem, I believe that you can limit the maximum trace file size by issuing a command similar to the following to set a maximum file size for dump files: alter system set max_dump_file_size=<N> While this will not fix the problem it might take the pressure off allowing for a greater amount of time to be spent on investigating and testing potential solutions for the root cause. Dave On Tue, Aug 24, 2010 at 9:05 PM, Evan Pettrey <jepettrey@xxxxxxxxx> wrote: > Thanks everybody, you've been immensely helpful. I'm going to create a > clone of the VM and bring it up in our lab environment to see if it works > this evening. I have scheduled an hour downtime tomorrow morning to perform > the fix assuming all goes well in the lab environment. > > My plan for tomorrow is as follows: > > 1) Stop the application > 2) Stop the database > 3) Create a clone of the VM the DB resides on > 4) Backup and delete the huge trace files that have been created > 5) Drop and recreate the indexes > 6) Start the database > 7) Start the application > 8) Pray it all works properly! > > > > > > On Tue, Aug 24, 2010 at 3:34 PM, Ilmar Kerm <ilmar.kerm@xxxxxxxxx> wrote: > >> From XE: >> >> SQL> explain plan for alter index SMON_SCN_TIME_TIM_IDX rebuild; >> >> Explained. >> >> SQL> select >> substr (lpad(' ', level-1) || operation || ' (' || options || >> ')',1,30 ) "Operation", >> object_name >> "Object" >> from >> plan_table >> start with id = 0 >> connect by prior id=parent_id; >> >> Operation >> Object >> >> -------------------------------------------------------------------------------------------------------------- >> ------------------------------ >> ALTER INDEX STATEMENT () >> INDEX BUILD (UNIQUE) >> SMON_SCN_TIME_TIM_IDX >> SORT (CREATE INDEX) >> INDEX (FAST FULL SCAN) >> SMON_SCN_TIME_TIM_IDX >> >> >> >> On Tue, Aug 24, 2010 at 9:52 PM, Andrew Kerber <andrew.kerber@xxxxxxxxx> >> wrote: >> > Not sure if that is true, as I recall alter index rebuild... will fix an >> > index that is in an invalid state. >> > >> > On Tue, Aug 24, 2010 at 1:43 PM, Ilmar Kerm <ilmar.kerm@xxxxxxxxx> >> wrote: >> >> >> >> Hi >> >> >> >> Doesn't rebuilding actually use the existing index to read the data >> >> from, so rebuild will actually hit the corruption? >> >> From documentation: >> >> >> >> >> http://www.filibeto.org/sun/lib/nonsun/oracle/10.2.0.1.0/B19306_01/server.102/b14231/indexes.htm#i1006864 >> >> "When you rebuild an index, you use an existing index as the data >> source." >> >> >> >> So drop and create the index. >> >> >> >> 1. Get the index DDL using DBMS_METADATA.GET_DDL >> >> select dbms_metadata.get_ddl('INDEX','SMON_SCN_TIME_TIM_IDX') txt from >> >> dual; >> >> >> >> 2. Drop old index: drop index SMON_SCN_TIME_TIM_IDX; >> >> >> >> 3. Run create index statement returned from dbms_metadata.get_ddl >> >> >> -- >> Ilmar Kerm >> -- >> //www.freelists.org/webpage/oracle-l >> >> >> >