unable to resize a RBS tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: unable to resize a RBS tablespace

  1. #1
    Join Date
    Oct 2002
    Location
    Singapore
    Posts
    49

    unable to resize a RBS tablespace

    Hi Guys,

    My RBS tablespace has grow to 1.6Gb during some heavy transactions. I have check one the rollback segments. All of them
    OWNER SEGMENT_NAME TABLESPACE BYTES EXTENTS
    ------------------------------ ---------- ----------------- SYS RBS0 RBS 4194304 8
    SYS RBS1 RBS 4194304 8
    SYS RBS2 RBS 4194304 8
    SYS RBS3 RBS 4194304 8
    SYS RBS4 RBS 4194304 8
    SYS RBS5 RBS 4194304 8
    SYS RBS6 RBS 4194304 8



    File Name Size (M) Used (M) Free (M) HWM (M)
    ---------------------------------------- ----------
    /opt/oracle/oradata/irmsitdb/rbs01.dbf 1600 28 1069 1588

    From above, my RBS tablespace is using 28Mb only, and 1.5Gb at HWM.


    I have tried to coalesce and it doesn;t change anythign on the HWM. I did issue a resize on the datafile and it does not work :

    SQL> alter database datafile '/opt/oracle/oradata/irmsitdb/rbs01.dbf' resize 1500m;
    alter database datafile '/opt/oracle/oradata/irmsitdb/rbs01.dbf' resize 1500m
    *
    ERROR at line 1:
    ORA-03297: file contains used data beyond requested RESIZE value

    I have perform this on other tablespaces including TEMp and it works.

    What can be done to reduce the size of my RBS tablespace's datafile??
    Regards
    CF

  2. #2
    Join Date
    Feb 2003
    Posts
    40
    First shrink all the rollback segments and then coalesce the tablespace.

    alter rollback segment rbsname shrink to value;

    Then resize the rbs datafile.

    Might be that would help.
    K.Chithra
    Oracle DBA

  3. #3
    Join Date
    Oct 2002
    Location
    Singapore
    Posts
    49
    ALl the individual RBS are in their optimal size of 4M only. that is why from the extent usage of the RBS tablepsace, i have 1,5Gb free.

    At this point, coalescing the tablespace RBS does not changes anything, and reszie of datafile fails.
    Regards
    CF

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    If its ur PRO box dont try this....

    Recreate the datafiles with seting max size...for Datafiles....

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    Oct 2002
    Location
    Singapore
    Posts
    49
    I'm testing it out on my DEV database. But does this mean ORACLE does not have any mean to resize the RBS tablespace like any other tablespaces ?
    Regards
    CF

  6. #6
    Join Date
    Feb 2003
    Posts
    40
    Can you check the max free space available also,instead of just the total free sapce.

    By doing so,we can check if it is fragmented it or not isn't it?

    max free chunk just be near to the value to total free chunk.

    To resize the datafile the tail part of datafile should be free.
    In between if it is free,we can'd do much abt it ,I guess.
    K.Chithra
    Oracle DBA

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    It does, but u need to plan correctly before creating any Tablespace or so...

    i guess u might have spcified UNLIMITED option for the datafile with autoextend ON....and moreover rollback segment is more prone to fragmentation....and collace will not work in such cases...u also need to be use the parmeters correctly while creation of rollback segments....

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    372
    Feng
    Try this.
    ALTER ROLLBACK SEGMENT <> SHRINK TO size; -IF OPTIMAL SIZE is defined , or TO MINEXTENT SIZE if optimal size is not defined.....Additionally SMON will try once every 12 hours to shrink all rollback segments that have grown
    over the optimal

    To shrink all rollback segments do the following

    SET PAGES 0
    TRIMS ON
    ECHO OFF
    VERIFY OFF
    FEEDBACK OFF
    TTITLE OFF
    CLEAR COLUMN

    SPOOL shrink_rbs.tmp

    PROMPT SET ECHO ON
    PROMPT SPOOL shrink_rbs.log
    PROMPT

    SELECT 'ALTER ROLLBACK SEGMENT ' || segment_name
    || ' SHRINK;'
    FROM dba_rollback_segs
    WHERE status = 'ONLINE'
    /

    PROMPT ALTER TABLESPACE rbs COALESCE
    /
    PROMPT SPOOL OFF
    PROMPT SET ECHO OFF

    SPOOL OFF
    CLEAR COLUMN

    SET PAGES 24
    TRIMS ON
    ECHO OFF
    VERIFY ON
    FEEDBACK ON


    @shrink_rbs.tmp

    PROMPT
    PROMPT ******************************
    PROMPT
    PROMPT Output saved at shrink_rbs.log
    PROMPT
    Hope this help
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Hi,
    Run this:

    set lines 256
    col "File Name" for A40
    column file_name format a40;
    column highwater format 9999999999;
    SELECT Substr(df.file_name,1,40) "File Name",
    Round(df.bytes/1024/1024,2) "Size (M)",
    Round(e.used_bytes/1024/1024,2) "Used (M)",
    Round(f.free_bytes/1024/1024,2) "Free (M)",
    round((b.maximum+c.blocks-1)*d.db_block_size/(1024*1024)) "HWM (M)"
    FROM dba_data_files df,
    (SELECT file_id, Sum(Decode(bytes,NULL,0,bytes)) used_bytes FROM dba_extents GROUP by file_id) e,
    (SELECT Max(bytes) free_bytes, file_id FROM dba_free_space GROUP BY file_id) f,
    (SELECT file_id, max(block_id) maximum from dba_extents group by file_id) b,
    dba_extents c,
    (SELECT value db_block_size from v$parameter where name='db_block_size') d
    WHERE e.file_id (+) = df.file_id
    AND df.file_id = f.file_id (+)
    AND df.file_id = b.file_id and c.file_id = b.file_id and c.block_id = b.maximum
    ORDER BY
    df.tablespace_name, df.file_name
    /

    to see up to what size you can resize downwards. It is the value (in Megabytes) in the last column (HWM).

    Cheers,
    Julian
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  10. #10
    Join Date
    Oct 2002
    Location
    Singapore
    Posts
    49
    Hi Julian,

    I think i have a similar script and the values are as below for my RBS ( as from my 1st thread)

    File Name Size (M) Used (M) Free (M) HWM (M)
    ---------------------------------------- ---------------------------
    /opt/oracle/oradata/mydb/rbs01.dbf 1600 28 1069 1588

    From above, my RBS tablespace is using 28Mb only, and 1.5Gb at HWM.

    I should be able to downsize a free space of 1.5gb, but i can't.


    Could i do the following :
    1) create another seperate RBS tablepsace and create some public rollback segments in that tablepace.
    2) offline those current rollback segments in the oversize tablespace, and drop the tablespace.
    3) Create back the dropped RBS with a smaller datafile size, and recreate back those drop rollback segments.

    Will this work?
    Regards
    CF

Posting Permissions

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


Click Here to Expand Forum to Full Width