08-12-09, 23:25 #1Registered User
- Join Date
- May 2009
Unanswered: Reclaiming "white space" from tables
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??
08-12-09, 23:57 #2Registered User
Provided Answers: 1Code:
- Join Date
- Aug 2003
- Where the Surf Meets the Turf @Del Mar, CA
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.
08-14-09, 00:47 #3Registered User
- Join Date
- May 2009
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...
08-14-09, 04:04 #4Registered User
- Join Date
- Feb 2009
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.
The description for ORA-30036 isORA-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.
Certainly i would not expect data loss or table deadlocks