Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2003
    Location
    SJ, Costa Rica
    Posts
    48

    Unanswered: Resizing sysaux tablespace

    Hi everybody,
    I'm having a little trouble with my sysaux tablespace. It has a size of 12Gb, I already move the occupants, and now I have a 12Gb tablespce using only like 3Gb

    I'm using the following query to obtain my free space in sysaux
    Code:
    SELECT
    	B.tablespace_name, 
    	tbs_size SizeMb, 
    	A.free_space FreeMb
    FROM  
          (SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024 ,2) as free_space
               FROM dba_free_space
               GROUP BY tablespace_name) A,
          (SELECT tablespace_name, SUM(bytes)/1024/1024 as tbs_size
               FROM dba_data_files
               GROUP BY tablespace_name) B
    WHERE A.tablespace_name(+)=B.tablespace_name;
    
    TABLESPACE_NAME 		   SIZEMB     FREEMB
    ------------------------------       ---------    ----------
    SYSAUX				    12160       9726.94
    SYSTEM				      750        246.25
    TOOLS				       50	   1.81
    As I have free space available in my sysaux tablespace, I want to resize it, so I tried this:

    Code:
    SQL> alter database datafile '/u02/oradata/numarcr/sysaux01.dbf' resize 5000M
      2  ;
    alter database datafile '/u02/oradata/numarcr/sysaux01.dbf' resize 5000M
    *
    ERROR at line 1:
    ORA-03297: file contains used data beyond requested RESIZE value
    I understand that I have data at the end of the tablespace. Is there something I can do about this?

    best regards!,
    -eduardo s.m.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I understand that I have data at the end of the tablespace. Is there something I can do about this?
    move those objects which reside near the HWM into a different tablespace.

    Then SHRINK SYSAUX, & then move objects back to SYSAUX
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2003
    Location
    SJ, Costa Rica
    Posts
    48
    Quote Originally Posted by anacedent View Post
    ....
    move those objects which reside near the HWM into a different tablespace.
    ...
    Hi!, thanks for your answer, seems I'm getting closer.
    How do I know which occupants/objects are near the High Water Mark?
    I know the size of each of the occupants:

    SELECT OCCUPANT_NAME, SPACE_USAGE_KBYTES
    FROM V$SYSAUX_OCCUPANTS; 2

    OCCUPANT_NAME SPACE_USAGE_KBYTES
    ---------------------------------------------------------------- ------------------
    LOGMNR 7488
    LOGSTDBY 0
    STREAMS 192
    XDB 47360
    AO 14144
    XSOQHIST 14144
    XSAMD 15552
    SM/AWR 742848
    SM/ADVISOR 24448
    SM/OPTSTAT 1476992
    SM/OTHER 15104

    OCCUPANT_NAME SPACE_USAGE_KBYTES
    ---------------------------------------------------------------- ------------------
    STATSPACK 0
    ODM 5504
    SDO 6080
    WM 6656
    ORDIM 512
    ORDIM/PLUGINS 0
    ORDIM/SQLMM 0
    EM 68672
    TEXT 4736
    ULTRASEARCH 7552
    JOB_SCHEDULER 256
    but how do I know where are they located?

    best regards,
    -eduardo s.m.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    select de1.owner, de2.segment_name, max(de1.block_id )
    from dba_extents de1, dba_extents de2
    where de1.block_id >  de2.block_id
     and  de1.owner = de2.owner
     and  de1.segment_name = de2.segment_name
    AND de1.TABLESPACE_NAME = 'SYSAUX'
    group by de1.owner, de2.segment_name
    order by 3
    Last edited by anacedent; 02-18-10 at 21:58.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Mar 2003
    Location
    SJ, Costa Rica
    Posts
    48
    well, moving occupants didn't work.
    the thing is that statspack and awr data had no move procedure from sysaux. I move other objects but this ones(statspack/awr) doesn't have a move procedure.

    I shrink tables, and rebuild indexes, but keep getting the error:

    Code:
    ERROR at line 1:
    ORA-03297: file contains used data beyond requested RESIZE value
    Can I just export/import the sysaux tablespace? could that work?

    best regards,
    -eduardo s.m.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I move other objects but this ones(statspack/awr) doesn't have a move procedure.
    turn them off so it/they stop collecting until after problem resolution
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Mar 2003
    Location
    SJ, Costa Rica
    Posts
    48
    and what about if I export the sysaux tablespace, drop the old tablespace, create a new one and then import? would that work?

    best regards,
    -eduardo s.m.

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    I say just leave it. Do you REALLY need the 5-7G for something that desperately?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Mar 2003
    Location
    SJ, Costa Rica
    Posts
    48
    Quote Originally Posted by The_Duck View Post
    I say just leave it. Do you REALLY need the 5-7G for something that desperately?
    not really, the thing is that I don't like to have a tablespace of 12.5Gb with just 2.5Gb of use. Seems there is no easy way to resize this tablespace, so I should backup and recreate again the whole database.

    thanks to all for your help.
    best regards,
    -eduardo s.m.

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Statpack and everything else stores information in tables or external flat files. Use the move command of the alter table to put it elsewhere.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •