Re: Oracle DB_BLOCK_SIZE and it's relationship to TS/table initial extent

In article <397E1C73.E2C426F7_at_yahoo.com>,   bopeep <bopeep_at_yahoo.com> wrote:
> We are running Oracle 8.0.6 on Solaris 2.6
>
> We're in the process of moving our databases to a new server, and
> the professional service team building our initial test databases
 built
> them
> with 2K db_block_size. However, the actual block size will need to be
> recreated eventaully as at least 8K.
> They want to test failover (Oracle HA), and have asked for an export
 of
>
> the data on the existing machine, and want me to import on the new
> machine db
> My question is, with 2K block size, what will be max, if there one, of
> the Initial extents within tablespaces/tables? What is the
> relationship between db block size and initial extent (multiple of?).
> We have a table that through export wants to create an initial extent
 of
> nearly 10GB, (the tablespace itself is built to 25GB), and when
> attempting to import it is obviously not able to create an initial
> extent of the size that it wants to.
> Will the Database have to be recreated to even allow this import?
>
> TIA
>
>

Having read the litany of posts on this it occurs to me that at least one datafile for the tablespace in question needs to be larger than the initial extent indicated, approx. 8 gig, and currently this is not the case. I usually set up 20 to 30 gig tablespaces for large instances to allow for such "inevitabilities". I suggest that you add a datafile to the necessary tablespace that is large enough to accommodate the extent size you require.

The size of initial extents (and of next extents) is not directly related to nor governed by the DB_BLOCK_SIZE; I DO agree that for large tables the initial and next extent values should be set to multiples of the DB_BLOCK_SIZE. Extent sizes are NOT tied to the block size value.

Without MAXEXTENTS UNLIMITED the maximum extents for a given block size are as follows:

2K (Oracle default block size)       121 extents
4K                                   249 extents
8K                                   505 extents
16K                                  1017 extents
32K                                  2041 extents

    (This works out to a formula of extents at new block size =
     2*(extents at current block size) + 7 when the block sizes are
     powers of 2, i.e., 2,4,8,16,32, etc., and only works between
     adjacent values in the list.  As an example:

     extents at 4K = 2(extents at 2K) + 7 = 2(121) + 7 = 249
     extents at 8K = 2(extents at 4K) + 7 = 2(249) + 7 = 505

     If you are wanting to calculate the extents at 8K vs. the extents
     at 2K you'll need to calculate the extents at 4K first.)

No, the database will not need to be recreated to accept this import; all that is required is to add a datafile to the affected tablespace (or tablespaces) sufficient to allow the creation of an 8 gigabyte initial extent:

alter tablespace <...> add datafile <***> size 10000M;

This is, of course, presuming that there is enough available disk resource to accomplish this task. The worst-case scenario (barring instance failure) would be to add and configure additional disk drives, re-boot the server (if necessary), then proceed with the tablespace alterations.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.