Re: max extents reached on system table

It is generally bad news when you read max extents on any table. You have to export it, drop it, and import it. The is not a good thing to do to a data dictionary table.

We have dealt with this problem by recreating the database.

The steps would go something like this.

1. Do a full database export
2. Determine the current sizes of the offending data dictionary tables.
3. shutdown the original database
4. take a cold backup.
5. Locate and save a copy of sql.bsq. This is the file that Oracle uses to create the data dictionary tables when you issue a CREATE DATABASE command. On UNIX, it is in $ORACLE_HOME/dbs. On VMS, it is in ORA_RDBMS.
6. Modify sql.bsq, setting the storage parameters for the data dictionary tables you identified in #2.
7. Recreate the database
8. Do a full database import.

Go slowly, and carefully.

p.s. I just remembered something that might apply to you. A

      question that you want it ask is why is OBJAUTH$ so 
      large?  This is the table that stores GRANT information.
      Do you really have that many grants?  

      We had a problem on a V6 database that was converted to
      V7 where the unique index on objauth$ was not created.
      The result of this was that we ended up getting duplicate
      rows in OBJAUTH$, and it got huge.  Performance also
      went down the toilet, particularly on drops of users.

      If you look in sql.bsq, you will see that there are 
      supposed to be 2 indexes on sys.objauth$.  The unique
      index is i_objauth1.  Check to make sure that these
      indexes exist on your database.  If they don't, it 
      probably explains why your objauth$ table is so big. 

      I think that I dealt with this problem by writing a 
      script that would delete duplicate rows from objauth$.
      That was a scary thing to do, but it seemed better 
      than rebuilding the database. If this is your problem,
      I would suggest that you review your options with 
      Oracle technical support.

-- 
Bob Swisshelm
Eli Lilly and Company
swisshelm_at_lilly.com
(317) 276-5472