I have gathered the following information for all tables: table size (sum bytes of the segment), number or rows, blocks, empty blocks, average space and average row length. Table sizes vary from 3 gig, 2 gig, 1 gig . . . 40960. Under 9i, I would like to know if I should have tablespaces created with different uniform size. Once I pre-create the table, I also have the opportunity to adjust the table extent (by dropping and recreating the table prior to the import). Should I specify initial and next or default to uniform size for all the tables?
As far as sizing, you have to remember that an extent can only hold one object. If you create large extents and place indexes in them, there could be a lot of wasted space. On the other hand, locally managed tablespaces do not hit the data dictionary for extent information and having many extents will not impact performance.
There is no need to try and import a table into one extent. My recommendation is to create one tablespace for indexes and another for tables. Make the table tablespace extent size equal to the smallest table you are going to place in it (if you have some large tables with very small tables you might need to make larger extents and have some wasted space with the small tables). Make the index tablespace extents smaller since the objects are smaller. Don't worry about a table that ends up with 100 extents; it will not impact performance.
Another alternative is to create small, medium and large tablespaces and divide up your objects into these tablespaces depending on object size.
Dig Deeper on Oracle Application Server
Related Q&A from John Garmany
I am facing the ORA-04076 error. I googled the error but was unable to understand it. What exactly do I need to do to solve this? I am using Toad. Continue Reading
We are using Oracle Database. In our client site, we have 500 users using the database at one time. Sometimes the database objects become invalid ... Continue Reading
I get the ORA-12541 error message when I try to connect through Forms6i and Reports6i Builder. I've started the listener. I have no problems ... Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.