3 Replies Latest reply: Jun 29, 2011 4:39 PM by Udo RSS

    sysaux size

    Alain
      I imported a db into 11g without indexes (db is ~10Gb size).
      I am in noarchivelog mode. Could import all, activate constraints, all is fine.
      Then I deleted 50% of data (using merge and delete).
      Commited, till here works fine!
      ---
      Then, to claim free space, I used
      alter table ... enable row movement;
      alter table ... shrink space compact;
      =>here I get a disk full.

      I inspected and see my sysaux tablespace size has raised to 5Gb and temp to 3Gb.
      Also, the size of datafile holding objects was not reduced of event 1 byte.

      1. Is it possible to shrink sysaux tablespace?
      2. To claim space, I suppose my only option is to do an export, drop objects and tablespace (or reinstall xe re-creating database), then import?
        • 1. Re: sysaux size
          Udo
          Hi Alain,

          see this thread for information on how to resize SYSAUX: {thread:id=762720}
          An interesting aspect would be to know if you actually have wasted space in SYSAUX. Could it be you used it for some other data, e.g. do you use it for APEX? In that case, it will be hard to shrink it.
          You can shrink TEMP easily by creating an intermediate TEMP tablespace, assign that to be used as system TEMP tablespace and then drop the original TEMP, recreate it with new size, assign it to be used as default again and drop the intermediate file.
          CREATE TEMPORARY TABLESPACE TEMP2
          TEMPFILE <location> 
          SIZE 16M REUSE
          AUTOEXTEND ON NEXT 4M
          MAXSIZE <yourlimit>
          EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
          
          ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;
          
          DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
          
          CREATE TEMPORARY TABLESPACE TEMP
          TEMPFILE <location> 
          SIZE 16M REUSE
          AUTOEXTEND ON NEXT 4M
          MAXSIZE <yourlimit>
          EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
          
          ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
          
          DROP TABLESPACE TEMP2 INCLUDING CONTENTS AND DATAFILES;
          Feel free to adjust parameters as they fit to your system.
          But I guess it will grow again some day you run big merges or similar temp-consuming operations.
          I'd try to free some space on your disk elsewhere or move some of your tablespaces to other partitions where there's still room, e.g. you could create the new temp tablespace on a different disk, if you have one.

          -Udo
          • 2. Re: sysaux size
            Alain
            Problem is that sysaux.dbf was related to undo tablespace, and undotbs1.dbf related to sysaux.dbf.
            • 3. Re: sysaux size
              Udo
              Okay, so your first analysis was based on wrong assumptions. As I can see in your [url http://forums.oracle.com/forums/thread.jspa?threadID=2245739&tstart=0]recent post, neither UNDO nor SYSAUX are your problem any longer.
              Undo, by the way, is not counted into your 11G limit, because like TEMP, it's not actually accounted to be user data.
              So, if this is not a problem any longer, please mark this thread as answered.

              -Udo