Proactively Mitigate ORA-01654 Error

[BenPerry]

ORA-01654: unable to extend index PDMADMIN.HOLDERTOCONTENT$COMPOSITE4 by 512 in tablespace INDX

I've gotten this error message a few times now this year. Today, there was a 2 hour window between the time that the hard-working Europe team found the error, and us lazy administrators sleeping in the US were able to do something about it. In our company, we must forward the message to the DBA team for them to fix it.

But I would like to get proactive with this message if possible. We're just now upgrading from 9.1 to 10.2. Is there some OOTB functionality in 10.2 that will send a message before hitting the limit? Or perhaps something within PTC System Monitor?

With my current knowledge (9.1-based), the only thing I can think of is to periodically check the tablespace sizes. Perhaps create a query that only returns tables that are over 90% or 95% full? I don't know if that is a good solution or not.

What are your thoughts about trying to prevent this error from happening in the first place?

[MikeLockwood]

See attached - examples of looking at Oracle info using standard Oracle tools (don't know SQL Server but must be equiv).

Oracle E/M (enterprise manager - if you Oracle Enterprise installed) also has similar along with preventive tools.

The DB guys can and should give the Windchill admin's read-only access to the Windchill database.

[BenPerry]

Mike,

I could look in those Oracle reports via SQL Developer or similar. But that would be a manual thing. Do you know of a way we could automatically get notified when running out of space? That way something could be done before users start seeing ORA-01654 in Event Manager.

[MikeLockwood]

Windchill reporting can't reach into the database so it has to be an Oracle functionality. Exception - gatther_info_script.sql can be run from a WC shell - but you have to dig deep to find the issues. Steve Vaillancourt (spelling?) may want to comment on this.

Oracle Enterprise Manager has extensive "warning" tools for this type of thing I believe but I'm not familar enough with them.