Which three statements are true about table data storage in an Oracle Database? (Choose three.)
Which three statements are true about table data storage in an Oracle Database? (Choose three.)
Data block headers in an Oracle Database do contain their own Data Block Address (DBA) which uniquely identifies the block within the database. Rows in a table can be divided into multiple row pieces, especially when there are many columns or when the size of the row exceeds the block size. Multiple row pieces from the same row may indeed be stored in different database blocks due to chaining. Additionally, multiple row pieces from the same row may be stored in the same block, commonly occurring when a row with many columns is split into pieces even if it's not larger than the block size.
I would say A, B, C are correct!
agreed A, B, C are correct
E is wrong … Free space can be also Noncontiguous also called fragmented space. https://docs.oracle.com/cd/E25054_01/server.1111/e25789/logical.htm
Right answers: A,B,C E and F are false because the free space of a block gets fragmented over time as data is added and removed from it -> this is called fragmentation (meaning the free space is not contiguous anymore). If the free space of a block is fragmented to a point where no new rows can be inserted, the free space is coalesced by the oracle server so that it's contiguous again and can be used for inserts or updates. Coalescing free blocks doesn't change the actual size of free space in the block. For why answer D is wrong, I am not completely sure but I guess that you wouldn't store a row as multiple row pieces in one block. If you can fit the whole row into a single block than it would be stored there as a single row piece and not multiple - but that's just my guess
about D. --ora.doc-- Oracle Database can only store 255 columns in a row piece. if you insert a row into a table that has 1000 columns, then the database creates 4 row pieces, typically chained over multiple blocks. -- typically chained, but possible situation that it can be in one block. for example oracle block 4K(8K). row 1000 columns=4 row pieces, if column is small then at least 2 row pieces possible insert in one block. why not? for my point of view answer D possible also.
so i would say A-B-C-D
so what is the final answers for this?
A, C, D. B is not correct. A row can be chained, but not a row piece...if so, then is another piece
C is correct > https://antognini.ch/2016/10/whats-the-difference-between-row-migration-and-row-chaining/
Interestingly, this also says that D is true. I was actually in doubt on the B answer, which is then false cause it is a row that is chained in different row pieces, while the right ones are A-C-D
The Oracle block header contains the following: http://www.dba-oracle.com/t_data_block_format.htm#:~:text=The%20header%20of%20a%20data,and%20a%20%22variable%22%20area.&text=The%20data%20block%20address.,displacement%20into%20the%20data%20file). A fixed-size block header. A block directory. The interested transaction list (ITL). Space management information. The data block address.
I dont think B is correct - the most granular unit of row is a single row piece. row piece can not be chained. a row can be chained by multiple row pieces + pointers.
B is correct : Simply put, chained rows happen when a row is too big to fit into a single database block. Chained rows usually result from an insert. For example, if the blocksize for the database is 4 kilobytes and there is an 8 kilobyte row to be inserted, Oracle will break the data into pieces and store it in 3 different blocks that are chained together. There is forwarding information available to allow the database to collect all of the bits of chained row information from multiple blocks. http://www.dba-oracle.com/t_identify_chained_rows.htm
A. Data block headers contain their own Data Block Address (DBA) ==> Yes B. A table row piece can be chained across several database blocks ==> No, ROWS are chained not ROW PIECE C. Multiple row pieces from the same row may be stored in different database blocks ==> Yes (Row chaining) D. Multiple row pieces from the same row may be stored in the same block ==> Yes (Intra-block chaining e.g. table with more than 255 columns) E. Data block free space is always contiguous in the middle of the block ==> NO F. Index block free space is always contiguous in the middle of the block ==> NO
A. Data block headers contain their own Data Block Address (DBA) - True B. A table row piece can be chained across several database blocks - Wrong, a row may be stored in multiple row pieces which are then chained across several database blocks. The row is chained not the row piece. C. Multiple row pieces from the same row may be stored in different database blocks - True D. Multiple row pieces from the same row may be stored in the same block - True E. Data block free space is always contiguous in the middle of the block - Wrong F. Index block free space is always contiguous in the middle of the block - Wrong "As the database fills a data block from the bottom up, the amount of free space between the row data and the block header decreases." No mention of middle of the block anywhere in the Oracle Docs. https://docs.oracle.com/en/database/oracle/oracle-database/23/cncpt/tables-and-table-clusters.html#GUID-37546C88-24EE-47BE-8662-A9CED99BB90F
The header contains general block information, such as the block address and the type of segment (for example, data or index). https://docs.oracle.com/cd/B10501_01/server.920/a96524/c03block.htm#:~:text=The%20header%20contains%20general%20block,example%2C%20data%20or%20index).
A,B and C. https://antognini.ch/2016/10/whats-the-difference-between-row-migration-and-row-chaining/
D is false in 19c because there is no more limitation about the number of "intra-block" columns. This is an example for 400 columns declare l_txt long; begin l_txt := 'create table t (c0 varchar2(20)'; for i in 1 .. 400 loop l_txt := l_txt || ', c' || i || ' varchar2(20)'; end loop; l_txt := l_txt || ') ' ; execute immediate l_txt; end; / select count(dbms_rowid.ROWID_BLOCK_NUMBER(rowid)) BLOCK_NUMBER from t; BLOCK_NUMBER ------------ 0 declare v_txt varchar2(4000); begin v_txt := 'insert into t values ( 0'; for i in 1 .. 400 loop v_txt := v_txt || ', ' || i ; end loop; v_txt := v_txt || ') ' ; execute immediate v_txt; commit; end; / select count(dbms_rowid.ROWID_BLOCK_NUMBER(rowid)) BLOCK_NUMBER from t; BLOCK_NUMBER ------------ 1 so I think the right answers are a (for sure), b, c (for sure)
A, C, and D sounds better to me.
Please ignore this comment.
A. Data block headers contain their own Data Block Address (DBA) --> True B. A table row piece can be chained across several database blocks. --> False, it's not "database block", it's "data block" C. Multiple row pieces from the same row may be stored in different database blocks. --> False, it's not "database block", it's "data block" D. Multiple row pieces from the same row may be stored in the same block --> True E. Data block free space is always contiguous in the middle of the block --> True, in the oracle official guide (online) there many pictures showing the "data block" and the free space is always included between header and data stored. F. Index block free space is always contiguous in the middle of the block. --> False, "index block" does not exists, it is "data block with type table or index, or cluster".
After reviewing this https://antognini.ch/2016/10/whats-the-difference-between-row-migration-and-row-chaining/
B, C, and D sounds better to me.
The reason for excluding choice A, "Data block headers contain their own Data Block Address (DBA)," from the correct answers is that it is fundamentally true, but it doesn't directly relate to the scenario described in the question.
DBA (Data Block Address) is an address included in each data block header that uniquely identifies the block within the database. It is essential information used to locate the physical position of blocks in the database, crucial for organizing data and the structure of the block.
However, this information doesn't directly relate to the actions or functions regarding the storage of table data, which is the focus of "Question #81." The question concentrates on how rows are stored and how rows are placed within blocks. Therefore, choice A does not provide relevant information for the context of this question and is not an appropriate choice for the correct answers.
Instead, choices B, C, and D, which directly relate to how rows are stored and managed across blocks, are more appropriate selections.