Results 1 to 4 of 4
  1. #1
    Join Date
    May 2009
    Location
    US
    Posts
    29

    Unanswered: Reclaiming "white space" from tables

    Hi Guys

    We are running ORACLE 10g on LINUX, and many of our ORACLE databases are SAP databases.

    We are experiencing serious space issues as our SAP databases are growing at a much faster rate than was anticipated.

    Currently I am looking at trying to relieve this pressure for ever increasing space requirements by trying to get rid of the empty space inside some of the larger tables. This will also enhance performance. The way I identify which tables are good candidates is by firstly identifying what the largest data segments in the database are (select from dba_segments). This yields segments of the types 'table', 'lobsegment' or 'index' - i'm only considering tables at this point.

    I then check to see what the actual size of the data in this table is by looking at num_rows & avg_row_len in dba_tables. The difference between the size here (actual data size) and the size given in dba_segments represents the amount of empty space, and if this is significant then the table is a candidate for shrinking out the white space.

    The procedure is as follows for each table identified as a candidate:

    -- 1. place table to allow row movement:
    alter table <owner>.<table> enable row movement;

    -- 2. shrink the space
    alter table <owner>.<table> shrink space;

    -- 3. disable row movement
    alter table <owner>.<table> disable row movement;

    -- 4. Analyze the table to ensure statistics are correct
    exec sys.dbms_stats.gather_table_stats( <owner>, <table>, estimate_percent => 10);

    Now my understanding is that an ORACLE 10g database can reclaim space within data segments ONLINE without affecting the ability of end users to access their data. The only thing that must be ensured before using online segment reorganization capability is that the tablespaces have the Automatic Segment Space Management (ASSM) and row movement features enabled.

    Both these conditions are met in our case.

    I have had mixed success with this method though. On at least one occasion I managed to free about 6 Gb of space (table segment was about 9Gb, real data space taken was about 3Gb and after applying this method the table segment had been shrunk to around 3 Gb).

    On at least one other occasion the shrink space command completed in a relatively short period of time (less than 1 minute) and no space was freed at all.

    And on another occasion the shrink space statement generated the following error:
    ORA-30036: unable to extend segment by 8 in undo tablespace 'PSAPUNDO'

    The research i've done into this error points to the fact that the undo tablespace simply needs more space allocated to it (ours is currently 10Gb). When i pointed this out to our 'offsite' senior DBA, he tried to make it out to be an issue regarding locking and contention and not a lack of space in the undo tablespace.

    I would really very much appreciate comments and advice from you guys about this issue. Perhaps using the shrink space statement as i have is not the best way to reclaim white space in table segments?

    And also, surely if this is supposed to be a statment that can be run ONLINE, i.e. without affecting the ability of end users to access their data, then even if it is SAP (or any other application) accessing this table, this should logically mean that other users would not be affected by the shrink space statement and certainly it would not CAUSE locks and it certainly would not cause the ORA-30036 error mentioned above??

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    30036, 00000, "unable to extend segment by %s in undo tablespace '%s'"
    // *Cause:   the specified undo tablespace has no more space available.
    // *Action:  Add more space to the undo tablespace before retrying
    //           the operation. An alternative is to wait until active
    //           transactions to commit.
    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
    May 2009
    Location
    US
    Posts
    29
    thanks for that - that's pretty much what I could gather from researching the error.

    Any comments from anybody regarding whether this command is safe to run against any table which may have applications (including SAP) running transactions against it?

    My logic dictates that if ORACLE says the command can be run online then it should cause no issues (except perhaps slowing the system down) with users accessing that table. Certainly i would not expect data loss or table deadlocks...

  4. #4
    Join Date
    Feb 2009
    Posts
    62
    From the Administrators Guide
    Segment shrink is an online, in-place operation. DML operations and queries can be issued during the data movement phase of segment shrink. Concurrent DML operation are blocked for a short time at the end of the shrink operation, when the space is deallocated.
    So you should expect some other sessions to wait at the end of the process.

    The description for ORA-30036 is
    ORA-30036: unable to extend segment by string in undo tablespace "string"
    Cause: the specified undo tablespace has no more space available.
    Action: Add more space to the undo tablespace before retrying the operation. An alternative is to wait until active transactions to commit.
    - based on the information provided, I don't see any reason to assume that anything else happened other than your Shrink ran out of Undo space.

    Certainly i would not expect data loss or table deadlocks
    Based on the information you've provided, you haven't had any deadlocks or lost any data.

Posting Permissions

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