Q

Calculating the initial extents of a table

I need to calculate the initial extent of a table.

 ACCOUNT_NO  NUMBER (10)   
 BILL_REF_NO  NUMBER (10)   
 BILL_REF_RESETS   NUMBER (3)  
 TRACKING_ID       NUMBER (10)
 TRACKING_ID_SERV  NUMBER (3)
 SUBSCR_NO         NUMBER (10) 
 SUBSCR_NO_RESETS  NUMBER (6)  
 DISCOUNT_ID       NUMBER (10) 
 FROM_DATE         DATE   
 TO_DATE           DATE   
 STATUS            NUMBER (3)  
 
The DB_BLOCK_SIZE is 8 KB. There will be approximately four lacs records in the table. Also, if I make my tablespace LM what is the advantage?

With locally managed tablespaces, you have two options. One, you can make all extents for all tablespace objects the same, uniform size. This accounts for almost all LMTs in use today. Two, you can let Oracle decide the best extent size for you by autoallocating them. In either case, there are two big benefits, the data dictionary is not a bottleneck for allocating and deallocating extents, and fragmentation of free space is no longer a problem. I use LMTs for all of my tablespaces now and I'd suggest it for anyone.

So make your tablespace a LMT with a UNIFORM extent size. Many DBAs put large objects in one tablespace, small objects in another tablespace and medium sized objects in a third tablespace. Large objects get a large, uniform extent size, for example 8 MB. Small objects get a small uniform extent size, like 64 KB. Medium objects get a different extent size, like 1 MB. No longer do you figure out initial extent sizes for each and every table with this method.


This was first published in February 2003
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close