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

Thread: Space usage

  1. #1
    Join Date
    Oct 2003
    Posts
    65

    Space usage

    Hi,

    Can someone clarify me.In present shop another DBA suggest to increase/Add datafiles by adding a block size to it.DB_block_size =8k.

    Suppose if i want to add 100MB space to tablespace XX.

    for eg:

    Alter tablespace XX add datafile '/local/dat00/oradata/SID/xx_2.dbf' size 102408 MB

    Why cant we just add like this:

    Alter tablespace XX add datafile '/local/dat00/oradata/SID/xx_2.dbf' size 100 MB

    Is there any block efficiency usage???

    Thanks in advace.

  2. #2
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Don't understand...Hummm

    first one addding a datafile of 102408MB and second one 100MB. Only size differs. But, what is the role of DB_block_size while adding datafile. I appreciate if explain more...
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  3. #3
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    I think that what the DBA may mean is that when sizing datafiles you should account for DB_BLOCK_SIZE to ensure all the space is usable. Bear in mind that each datafile has an area reserved for header information that is DB_BLOCK_SIZE in size. I would say this is only useful if you are using uniform extent sizes.

    A simple example:

    Tablespace USERS is in a database with an 8K block size and is using a uniform extent size of 128K. It contains 10 extents. It is exactly 100% full, no space is wasted. Therefore, this datafile must be 1288K ((10 extents * 128K) + DB_BLOCK_SIZE). Now we want to add enough space for a further 10 extents. This is 10 * 128K = 1280K of usable space. So we must resize the datafile as follows:

    1288K + 1280K = 2568K

    So resize to 2568K:

    2568 - 8 = 2560

    2560 / 128 = 20 extents.

    It fits exactly.

    What we don't want to do is say "I want double the space", and then:

    1288 * 2 = 2576K

    So resize to 2576K. We would be wasting a little bit of space if we did that.

    1288 * 2 = 2576
    2576 - 8 = 2568
    2568 / 128 = 20.0625

    So we're wasting .0625 KB of space in the tablespace!

    Hope I've not totally misinterpreted what your asking and that it helps.

    Austin

  4. #4
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Suppose if i want to add 100MB space to tablespace XX.

    for eg:

    Alter tablespace XX add datafile '/local/dat00/oradata/SID/xx_2.dbf' size 102408 MB

    Why cant we just add like this:

    Alter tablespace XX add datafile '/local/dat00/oradata/SID/xx_2.dbf' size 100 MB
    yes.. I understand what he meant... But he made a mistake.. Instead of 102408 MB he supposed to put 102408 KB. Then its ok.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    this is useless j0nx, at most you are gonna lose some mb

    do we need this kind of purity now days?

  6. #6
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Just hanging on the theories.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

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