database fragmentation

[sreddy]

Jeff,

Let me understand this, after changing the parameters that control extent sizes in tablespaces there may be an error if the tablespace runs outta space, there shouldn't be any error having free space in the tablespace right ? It seems tablespace is running outta space.

I don't know how things were before 7.3. All my experiences are limited to only V7.3 thru 9i

[lgaorcl]

The error message is exactly the same:
ORA-01652: unable to extend temp segment by 794170 (I am still wondering: is this number block size or bytes?) in tablespace xyz.

[marist89]

Originally posted by sreddy
Jeff,

Let me understand this, after changing the parameters that control extent sizes in tablespaces there may be an error if the tablespace runs outta space, there shouldn't be any error having free space in the tablespace right ? It seems tablespace is running outta space.

I don't know how things were before 7.3. All my experiences are limited to only V7.3 thru 9i

Since the storage parameters are in the export file they could:
1. change the TS parameters
2. export the tables which carry the old storage parameters
3. drop objects
4. imp objects with old storage parameters regardless of hwo the TS was setup.

This scenario would yield the same results as before.

The fact that the temporary segment is trying to grab over 775K where they specified the NEXT extent as 16K tells me that the new parameters are not being used.

By pre-creating the table to hold the data you can control the size of your extents BEFORE you start loading data.

[lgaorcl]

Why it would make a difference if you create the table first with those parameters?

Thanks!

[marist89]

The export file "remembers" the previous storage parameters. If you export, drop, import, the tables will have the EXACT same storage definition as before.