c1 number 10 c2 varchar2 25 c3 dateI think that per day, I will have 3000-4000 records inserted and as many updates. How do we calculate the initial extents? My db_block_size is 4K.
An easy way to do the calculation is to take the daily number of records (4,000 records), multiply times the max row length (2+25+7=34 bytes) and then multiply by the number of days you want to be in the first extent (say 6 months which is approximately 180 days). Then under these calculations, your initial extent needs to be 4000*34*180 = 24,480,000 bytes or 5,997 blocks (with your 4K block size).
But I have to tell you that most Oracle DBAs don't go through these type of calculations anymore. The Locally Managed Tablespace (LMT) was introduced in Oracle 8i. If you use AUTOALLOCATE for your LMT, then the INITIAL extent is typically 64K. If you use UNIFORM sizing for your LMT, then your INITIAL extent can be pretty much anything.
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in January 2003