Initial Extent sizes on tables

I have just taken over a 10GB database and on analyzing a few key tables, I find that the intial extent sizes range from 50-70MB. It is an OLTP application and I think that such big extent sizes are not good.

How do I go about fixing this problem? Export table and import it into a new table withh appropriate extent sizes? What happens to the associated indexes/views?

Thanks,
Nizar.

Big extents, but are the tables huge? EXP/IMP would do the trick. Rebuild the indexes afterwards. Don't worry about the view, only recompile them after the import of the tables.

what version is it?

Make sure that the privileges assigned on the table to various users are also taken care of while trying to import.

Cheers

Version 8.1.6

Nizar

then you dont need imp/exp, you can use

create a new tablespace aaaa

alter table xxx move tablespace aaaa storage(initial 4M next 4M pctincrease 0)

then mnove back to original tablespace, this is to avoid fragmentate your original tablespace, if it´s LMT then you can dmove the table in same tablespace

Pando,

Thanks for your reply. I will appreciate your help on these questions also. We are planning to bring this database down for 2-3 days for reorg maintenance.

First:

1. Will changing from dictionary managed to LMT's have any response in performance?

2. And can I do this by creating LMT's and importing all the tables from my current dump?

3. Any watchits or issues to be considered while doing this change?

Thanks,

Nizar

lmt is faster yes, the space management has almost zero overhead

you can exp, drop the tbsps and create new tbsps as lmt then import back yes

well i havent had a problem with lmt yet so..

Are LMTs more advantageous than DD based tablespaces?

If LMTs are used, are Dr.Codd's principles of RDBMS violated?

I understand that in LMT the tablespace parameters are contained in the tablespace itself.... ( ala FAT in DOS )

Any other points to be noted in this regard by me?

Quote:

---

Originally posted by sundaju
Are LMTs more advantageous than DD based tablespaces?

If LMTs are used, are Dr.Codd's principles of RDBMS violated?

I understand that in LMT the tablespace parameters are contained in the tablespace itself.... ( ala FAT in DOS )

Any other points to be noted in this regard by me?

---

LMT is faster and better than DDMT, period

There is nothing to do between tablespaces and Codd´s RULE!

Quote:

---

If LMTs are used, are Dr.Codd's principles of RDBMS violated?

---

:-)))))))

But tablespaces were part of DD

Dr. Codd's rule states that data dictionary should be the ultimate reference point for all database objects. I understand that tablespaces are also part of the database object definitions and hence my query about violation of Codd's rules.....

tablespace is transaparent, when Codd stated the rule I doubt tablespaces were invented?

Now that LMTs have the storage parameters as part of themselves, how do we query them?

Quote:

---

Originally posted by sundaju
But tablespaces were part of DD

Dr. Codd's rule states that data dictionary should be the ultimate reference point for all database objects. I understand that tablespaces are also part of the database object definitions and hence my query about violation of Codd's rules.....

---

Which Codd's rule are you refering to? No rule of Codd mentions tablespaces.

Quote:

---

Originally posted by sundaju
Now that LMTs have the storage parameters as part of themselves, how do we query them?

---

they are stored in datafile headers that is why querying the storage parameters in LMT has more overhead

Fellow DBA's,

I was testing the use of LMT's on a test database. From a 14MB dump file, when I import it into a dictionary managed tablespace, it fills up 24 MB. But when the same dump is imported into an LMT, it need 340MB of datafile space.

I know this sounds crazy, but does anybody have any explanations for this. Also when creating a LMT what is the difference between extent autoallocate and uniform size.

Thanks,
Nizar

In LMTs,

Uniform extents will cause Oracle to allocate extents of uniform size in the tablespace. If the initial extent size is greater than this size, then Oracle will allocate enough uniform extents to match the amount of space requested for the initial extent.

On the other hand, when AUTO ALLOCATE is used, Oracle uses an internal algorith to control the extent sizes.