Skip to Content
0

After reorg the segments count was reduced !! How ? Why ?

Mar 27, 2017 at 08:08 PM

82

avatar image
Former Member

Hi all , I have done oracle database tables reor on one of the big sized tablespace in my environment , before reorg the count of segments was 513 and after reorg ithe segment count was reduced and count is 207 . ? Where I did mistaken while doing reorg .. ??? But the table count is showing same before and. After reorg ..

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
James Zhang
Mar 29, 2017 at 12:29 AM
1

Hi Sudhakar,

Now I understand your concern, this could be caused by Deferred Segment Creation as mentioned in SAP note 1583303.
It means if you create a empty table, dba_segments won't record, only if you insert data into the table, then dba_segments will have record, that means the segment is allocated.

Best regards,
James

Show 3 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Thanks for the update James , i was verified and we do have few tables are empty , does it mean the segments doesn't create due to emptiness of tables ? if the users inserts any data then automatically the segments will be created ?

0

yes, this is the design of Deferred Segment Creation. You can verify this:

1. create a table empty
2. check dba_segments, dba_tables
3. insert data
4. check again Deferred Segment Creation

0
Former Member

Thanks again James . really appreciate the help you did in it .

0
James Zhang
Mar 28, 2017 at 04:34 AM
0

Hi Sudhakar,

I am not sure how you count segment count, but this looks correct, reorg is supposed to reduce the segment space.

As long as the table row count is the same, the reorg is fine.

Best regards,
James

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Mar 28, 2017 at 11:15 AM
0

before re-org , using dba_segments i check the count of segments resides under the tablespace and after re-org the count was not matching ...

it should be have same .. as you said the space can be reduced after re-org , why the segments count reduced ? in the logs i could not find any dropped segments as well .

Thanks

Sudhakar

Share
10 |10000 characters needed characters left characters exceeded