DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: storage parameters and import/export

  1. #1
    Join Date
    Apr 2001
    Posts
    257
    Hi,

    If I have
    initial extent = 128K
    next extent = 256K

    when I create the very first table, it allocates the size of the initial extent to the table (128K) even though there is no rows yet. What happen when I create second table right after? Does Oracle allocate 128K or 256K to the second table? That is, "initial" is for the tablespace or object?

    Is it true that once an extent is allocated to a table, that extent is reserved for the table only? If so, it'll be waste of space to have large extent sizes if table sizes are expected to be small.

    Finally, I took over two databases and one has initial extent = next extent = 10MB while the other one has initial = next = 128KB. I am supposed to do export/import from the first one to the second. I notice the import took quite long and the size on the second one after import is 9GB vs the first's 4GB. On the second DB, the actual used size is about 4GB but it expanded to 9GB during the import. I suspect extent sizes have alot to do with it. Is my assumption correct?

    Thanks,

  2. #2
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Hi a123..

    Okay...

    This is what dba's do, packing the data as tight as possible without wasting space and affecting performance.

    Remember, sizing starts at the blocks level.

    From block to segment to datafile to tablespace...

    INITAL and NEXT extents are specified at the SEGMENT level (or TABLESPACE level..defaults for segments) (a segment is an object that is made up of extents.. i.e. TABLE, INDEX etc). The INTITIAL and NEXT are only for the current segment. for example.

    Create table 1 (initial 128K next 256k)

    (Table creates with INITIAL extents size of 128K)

    Create table 2 (initial 128k next 256k) straig after

    And it with have an INITIAL size of 128K also.

    It's probably better practice to keep you initial and next the same size. i.e. 128k and 128k. It reduces fragmentation. You should also aim to keep different size segments in different tablespaces.

    i.e. small objects in one table space (all have same INITIAL NEXT ) and large objects in another (all with same INITIAL NEXT also)

    Cheers,
    OCP 8i, 9i DBA
    Brisbane Australia

  3. #3
    Join Date
    Mar 2001
    Posts
    131
    Hi grjohnson,
    Here in this, I would also like to add one question.

    Suppose i am not defining the storage parameters at segment level, instead i am defining it at Tablespace level so it will flow-down to all segments in that tablespace then What will be the effect of it ?

    UpeshP

  4. #4
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    Nothing, as long as all of your segments in that tablespace behave indifferently with respect to growth.
    By allowing tables to use tablespace level defaults, you may lose finer level of control over your segment's space allocation.
    -nagarjuna

  5. #5
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    however even after setting default storage parameters to tablespaces they can be overwritten by table storage parameters .
    siva prakash
    DBA

  6. #6
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Hi,

    I believe the others have answered the question you asked me.

    Cheers,
    OCP 8i, 9i DBA
    Brisbane Australia

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width