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