Saturday, October 8, 2011

Homework (Week-14) Exercise on Segments, Extents and Blocks --Moid

17 comments:

  1. In a hard drive there are data blocks, each of 512 bytes, if we want to install the Oracle software in the hard drive than the linux file system combine 16 blocks together to make 1 oracle block

    ReplyDelete
  2. The smallest Linux block is 512 bytes and it is EXT3 format, oracle considers this too small for it so it combines 16 blocks of the Linux blocks together to make one Oracle block. When a table is created and Extent is created, each Extent contains of 8 Oracle blocks and has the size of 64Kb. The table may contains multiple Extents so it is called a segment.

    ReplyDelete
  3. The OS blocks depends on what type of operating system you are using and what type of file system you are using as well. For Windows there are FAT32 and NTFS file systems while for RHEL there are EXT3, EXT4, and so on.
    Since the o/s blocks are not enough for Oracle, it will take x amount of blocks depending on the o/s. For Linux, 16 Linux blocks equal 1 Oracle block.
    As for extents, from extent number 0-15 there are 8 Oracle blocks for each extent.
    Whenever a table is created a segment is also created. Each segment has to have at least 1 extent which has 8 blocks.
    When a table is truncated or dropped, the space is released back to wherever it belongs and the segment shrinks down to having 1 extent.
    On the other hand, when rows are deleted the size of the segment is not altered. In technical terms, the high watermark is not released. This is due to the rows in the DML area being altered. When it comes down to efficiency, it is better to truncate a table because it releases the high watermark and does not keep data in the undo tablespace.

    ReplyDelete
    Replies
    1. I am so impressed Kachiya.be ready to explode your head with my questions...lol

      Delete
  4. the default size for the oracle is 8KB or 8192 bytes

    ReplyDelete
  5. 8 bits = 1 byte, 512 bytes = 1 linux block(ext 3 file system), 16 linux blocks= 1 oracle block(8kb or 8192 bytes)

    ReplyDelete
    Replies
    1. If we want to create a a table, than oracle says one block is very small for a table, than it combines 8 blocks to make one extent, that extent has a header, the first extent always start with 0,
      1 segment is nothing but bunch of extent for a table .
      Table 1 = segment 1 = bunch of extents(0,1,2,….)
      A bunch of segments = Tablespace
      5 tablespaces(system,sysaux,temp,undo,users) =1 Database
      A bunch of databases = Database Server

      Delete
  6. if we want to create a a table, than oracle says one block is very small for a table, than it combines 8 blocks to make one extent, that extent has a header, the first extent always start wth 0, 1 segment is nothing but bunch of extent for a table, Table 1 = segment 1 = bunch of extents(0,1,2,….), A bunch of segments = Tablespace.
    5 tablespaces(system,sysaux,temp,undo,users) =1 Database.
    A bunch of databases = Database Server

    ReplyDelete
  7. if we want to create a a table, than oracle says one block is very small for a table, than it combines 8 blocks to make one extent, that extent has a header, the first extent always start with 0, 1 segment is nothing but bunch of extent for a table.
    Table 1 = segment 1 = bunch of extents(0,1,2,….)
    A bunch of segments = Tablespace
    5 tablespaces(system,sysaux,temp,undo,users) =1 Database
    A bunch of databases = Database Server

    ReplyDelete
  8. 1 bit
    8 bit = 1 byte
    512 byte = 1 linex block(ext-3 RHEL 4)
    16 liex block = 1 oracle block

    2 truncate is the delete every thing other then ddl part
    Drop meane delete the table
    delete mean delete dml part

    the deleted part of the table size is called highwater mark

    ReplyDelete
  9. With the drop everything is removed and the extent size is released to the tablespace; with truncate only the header is available the rows are deleted and the extents size is also released but only the header will be available for that one extent is also available; with delete when we first allocate the space from 8 blocks to 128, and then we deleted half of the data than the second extent will not be deleted the extent are still part of the segment, that free space is called high water mark. In delete as it is a part of the DML then it has to record somewhere if the user want to rollback, and we have to commit every time when you make changes.

    ReplyDelete
  10. OS BLOCK is for Windows while DB BLOCK is for Oracle.
    8 Bit=1 Byte
    512 Bytes=1 Linux Block (Ext 3,RHEL 4)
    16 Linux Blocks=1 Oracle Blocks (8192 Bytes or 8 KB)

    EXTENTS-A group of 8 blocks is called an Extent.
    The smallest size of an extent is 64 KB.
    SEGMENTS-A group of Extents creates a segment.

    DROP-When the Drop command is used it completely
    deletes the table.

    TRUNCATE-When we truncate the table it deletes all the
    information and reduces the size of the table.
    When we truncate it automatically commits to the change.

    DELETE-When we delete a table it deletes the entire information
    but the size of the file still remains the same as it was
    while it had the info.
    When we delete the info it is copied to the undo tablespace.
    Delete needs the commit command to save changes.


    High Water Mark-The space that gets used in a table to store
    the info and later if we delete it the space used by the
    information still remains.
    This empty space is called the High Water Mark.

    ReplyDelete
  11. OS BLOCK is for Windows while DB BLOCK is for Oracle.
    8 Bit=1 Byte
    512 Bytes=1 Linux Block (Ext 3,RHEL 4)
    16 Linux Blocks=1 Oracle Blocks (8192 Bytes or 8 KB)

    EXTENTS-A group of 8 blocks is called an Extent.
    The smallest size of an extent is 64 KB.
    SEGMENTS-A group of Extents creates a segment.

    DROP-When the Drop command is used it completely
    deletes the table.

    TRUNCATE-When we truncate the table it deletes all the
    information and reduces the size of the table.
    When we truncate it automatically commits to the change.

    DELETE-When we delete a table it deletes the entire information
    but the size of the file still remains the same as it was
    while it had the info.
    When we delete the info it is copied to the undo tablespace.
    Delete needs the commit command to save changes.


    High Water Mark-The space that gets used in a table to store
    the info and later if we delete it the space used by the
    information still remains.
    This empty space is called the High Water Mark.

    ReplyDelete
  12. Drop : drops everything in the table including the table and free all the allocated space back to the server. The blocks than advertise themselves free so it can be used by others.

    Truncate: leaving the structure of the table it cleans/ deletes everything inside the table. It shrinks the default 8 blocks 1 segment. It is much faster as it doesn't save anything for a user to rollback or undo. It removes the "High Water Mark".

    Delete: Delete can be used for specific rows or entire table. It is much slower and expensive because it records everything in undo table space for specific time frame. It will delete data from block but size will be same. By using delete 1 row or 100 it doesn't mark the blocks as free. It uses "High Water Mark".

    High Water Mark: is the space that was used by a table. It may or may not have data, requiring to keep the blocks occupied for that table even if there is no data inserted anymore.

    OS Block :is for windows

    DB Block : is for Oracle

    Segment: A segment is a set of extents, each of which has been allocated for a specific data structure and all of which are stored in the same table space.

    Extent: is collection of contiguous data blocks. One or more extents make up a segment.

    ReplyDelete
  13. 8k as OS block size and 4k is the Data Base block size.
    An extent is a logical unit of database storage space allocation made up of a number of contiguous data blocks.
    One or more extents in turn make up a segment.A segment is a set of extents that contains all the data for a specific storage
    structure within a table space,For example,for each table oracle database allocates one or more extend to form that table's
    data segment,and for each index,oracle Database allocates one or more extents to form its index segment.
    Truncate Table VS Delete Table, when we truncate table the DML part of the table get delete and we have DDL part of
    the table left means we have table without a data.Exactly same way the blocks works they get realize back to the
    system.When we delete the table blocks are not realize to the system wright away they advertize there self a free
    blocks but they still under used blocks.
    High Water Mark is the space between used blocks and unused blocks. lets say we have a table with one extents or
    64 blocks now we enter a 50 rows means we used 50 blocks Now,there are only 14 blocks they are in high water mark.
    If we delete those 50 rows we still have 14 rows in high water mark even the blocks are free but they are still
    advertize there selves used blocks VS when we truncate the table all the blocks get realized wright away so there is
    no high water mark rows there.( Moid Bahi if i am missing anything or did explain wright plz let me know so i can
    correct my self THANK YOU).

    ReplyDelete
  14. Assalam-0-alukom Moid Bahi,
    8k as OS block size and 4k is the Data Base block size.
    An extent is a logical unit of database storage space allocation made up of a number of contiguous data blocks.
    One or more extents in turn make up a segment.A segment is a set of extents that contains all the data for a specific storage
    structure within a table space,For example,for each table oracle database allocates one or more extents to form that table's
    data segment,and for each index,oracle Database allocates one or more extents to form its index segment.
    Truncate Table VS Delete Table, when we truncate table the DML part of the table get delete and we have DDL part of
    the table left means we have table without a data.Exactly same way the blocks works they get realize back to the
    system.When we delete the tables blocks are not realize to the system wright away they advertize there self a free
    blocks but they still under used blocks.
    High Water Mark is the space between used blocks and unused blocks. lets say we have a table with one extents or
    64 blocks now we enter a 50 rows means we used 50 blocks Now,there are only 14 blocks they are in high water mark.
    If we delete those 50 rows we still have 14 rows in high water mark even the blocks are free but they are still
    advertize there selves used blocks VS when we truncate the table all the blocks get realized wright away so there is
    no high water mark rows there.( Moid Bahi if i am missing anything or did explain wright plz let me know so i can
    correct my self THANK YOU).

    ReplyDelete
  15. There are various types of file systems. The smallest Linux block is 512 bytes, but it's too small for Oracle. So when you install Oracle, it creates 16 blocks of 512 bytes for Linux = 8k

    8k is the default block size

    Each block represents 8k and the blocks are free - F, meaning nothing is written to it. The smallest unit we understand is a block. The machine combines blocks and creates an 'extent' of at least 8 blocks and the space on the machine for the table is called a 'segment'. The segment contains at least 1 extent, and each extent contains 8 blocks. So 8k (1 block) * 8 = 64k (1 extent). So the first is Extent 0.

    Segment is space for a table. The size of table space usage will be the total space available. So you may have a 2 extent space (128 k), only filled with 64k of data, but the size of the table will be given as total space available, not the used portion. Once the blocks are given to a segment for your table, even if you are not using all the blocks, the machine will not give them to another segment. If you need to release empty blocks back to Free, you truncate or drop.

    Chunks - term to define amount of Oracle blocks in use.
    Drop - every extent is released.
    Truncate - (this is fastest method) every extent except the first one is released, that is Extent_ID 0.
    Delete - the data will be deleted but the high water mark will be kept, so the extent is not released.
    The segment will grow as you add data, and when you delete the space remains. That space is known as "the high water mark" and will remain available. Oracle has to keep track of this info so it can be available in case of Rollback.

    Truncate is part of DDL - so automatically commits
    Delete is part of DML - so user must enter commit; to complete the delete.

    ReplyDelete

Followers