If the segment does not yet have a second extent then the EXU7TNE query will
return nothing and export will pick up your intended next extent size. I suspect
that that is why some of your tests appeared to work.


-----Original Message-----
Sent: Wednesday, 6 September 2000 9:03


We found out some very weird stuff today.  We did some testing with this
next extent situation.  We altered the next extent of several database
objects and then we exported those objects (tables and indexes).

We noticed that the export file actually reflected the UPDATED next extent
sizes.  We started to think, because we examined the EXU7TNE view, that
sometimes the export will pick up the CHANGED next_extent size and sometimes
it won't, depending on the size of the table/index.

Can you tell me the process that the export views go through to determine
what size the next_extent should be?  We went back through log files, and it
seems like the next extents were changed for the smaller segments, but for
huge segments, like 2G tables, the next extent size did not change.

By the way, thanks for all the information.  This will be an on-going issue
for us going forward.


-----Original Message-----
Sent: Tuesday, September 05, 2000 3:36 PM


Sorry, it's been a long week/weekend.
Thanks so much!  I called Oracle support about this and they actually said
that the extent sizes should have been the ones we changed them to right
before the import.  They only tested this through doing an initial_extent
change, an export and then an import.  I don't think they understand the
normal export behavior.


-----Original Message-----
Sent: Tuesday, September 05, 2000 2:08 PM


No, that is exactly what I understood the first time. Please read my answer
again. It still applies.


-----Original Message-----
Sent: Wednesday, 6 September 2000 0:35


I think in my being tired, I didn't explain this correctly.
We:
1) Altered the next extents in a production database (ORCL)
2) Did an export of the database after the altering of next_extent
3) Removed ORCL; recreated ORCL
4) Did a full import into ORCL
5) When we looked at the segments within the database, the next extent size
was the same as the size BEFORE step 1.  This didn't make any sense.  The
altering of the next extent BEFORE the export should have resulted in the
new extent sizes imported into the newly created database...correct?


-----Original Message-----
Sent: Tuesday, September 05, 2000 2:54 AM


This is the expected behaviour. The export views actually try to determine what
the original next extent size was for each segment, and that is what is used in
the CREATE statement in the export file. Have a look at the view definition for
EXU8TNE (or EXU7TNE) in $ORACLE_HOME/rdbms/admin/catexp.sql to see how it is done.

Having segments with thousands of extents is bad for your data dictionary (see
the Ixora tip "Planning Extents" on that matter) so I would encourage you to try
again. Oracle recommend that you control the extent size on import by creating
the segments beforehand and then importing with IGNORE=Y. You can generate a
template for the create script with the SHOW=Y parameter of import. That then
has to be edited to get all the next extent sizes that you really wanted.

However, if your target database is at Oracle8, then putting a minimum extent
size on the target tablespaces will be equally effective, and much less effort.
Alternatively, for a really radical approach, you could temporarily hack EXU8TNE
(or EXU7TNE) on the source database before the export to make it look at the
next extent sizes you have so carefully established there!


-----Original Message-----
Sent: Monday, 4 September 2000 14:08


Recently, we wanted to defragment our database, so we did a full import/export
of it. Before we did the export, however, we changed many of the next_extent
sizes on many of our objects within the database.

The reason we changed those sizes was so that on import, the next_extent sizes
would be bigger, thus, there would be less extents to any given segment.
Originally, some of these tables/indexes had 8K next_extent sizes, and so the
table/index would end up with something on the order of 40,000 extents.

We altered the next_extent sizes...we have a log to prove this. Then we did the
import. When I was looking at extent sizes this evening, they seemed to have
reverted back to the original sizes (again - some were 8K), so the objects with
the most extents that we were trying to "clean up" before, still have a similar
# of extents.

I understand that decreasing the # of extents themselves won't drastically
improve performance, but I am puzzled as to why these next_extent values
reverted back to the original when they had been changed in the database BEFORE
the export.

What do you think?