Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When randomly deleting ~5GB from a ~10GB Oracle table, I suspect that disk space is left in fragments. Is there a way to know about the disk state and any query to defragment or shrink the space?

share|improve this question

2 Answers 2

Yes, deleting 50% of data from a table leaves it fragmented, and this make two problems:

1) The High Water Mark (which show the max usage of a table) is still at maximum size of table and make worst performance on Full table Scan

2) Space (extents) are not freed.

If you are using Oracle 11g you can run the Segment Advisor (read this) and run ALTER TABLE SHRINK SPACE CASCADE;

Read warnings for the ALTER command like this one:

"As with other DDL operations, segment shrink causes subsequent SQL statements to be reparsed because of invalidation of cursors unless you specify the COMPACT clause."

share|improve this answer
    
thanks. Is Segment advisor some kind of external tool? Is there any way i can query the table to get this segment information –  Javafreak Aug 23 '12 at 13:43

Can you explain exactly what problem you are concerned with? People have come up with lots of different definitions of "fragmentation" some of which are impossible, some of which are possible but don't create any problems, and a few of which might be worth thinking about.

If you delete at random 1 out of every 2 rows in a table, that will generally mean that you'll have lots of half-empty blocks in the table. That space will be reused by subsequent inserts into the table, though, so it is generally not create any problems unless you are stating that you are permanently reducing the size of the table (i.e. it will never again grow past 5 GB in size) or you have queries that rely on doing full table scans of the table that you want to tune. Depending on the Oracle version, you can probably do a SHRINK SPACE on the table to reduce the size of the segment in that case.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.