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
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.
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.
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
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
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
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.
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.
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.
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.
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).
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).
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.
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
ReplyDeleteThe 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.
ReplyDeleteThe 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.
ReplyDeleteSince 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.
I am so impressed Kachiya.be ready to explode your head with my questions...lol
Deletethe default size for the oracle is 8KB or 8192 bytes
ReplyDelete8 bits = 1 byte, 512 bytes = 1 linux block(ext 3 file system), 16 linux blocks= 1 oracle block(8kb or 8192 bytes)
ReplyDeleteIf 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,
Delete1 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
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.
ReplyDelete5 tablespaces(system,sysaux,temp,undo,users) =1 Database.
A bunch of databases = Database Server
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.
ReplyDeleteTable 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
1 bit
ReplyDelete8 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
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.
ReplyDeleteOS BLOCK is for Windows while DB BLOCK is for Oracle.
ReplyDelete8 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.
OS BLOCK is for Windows while DB BLOCK is for Oracle.
ReplyDelete8 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.
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.
ReplyDeleteTruncate: 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.
8k as OS block size and 4k is the Data Base block size.
ReplyDeleteAn 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).
Assalam-0-alukom Moid Bahi,
ReplyDelete8k 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).
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
ReplyDelete8k 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.