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.
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
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".
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. 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
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
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.
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).
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.
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
A voting comment increases the vote count for the chosen answer by one.
Upvoting a comment with a selected answer will also increase the vote count towards that answer by one.
So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.
ama
Highly Voted 3 years, 11 months agobaeji
3 years, 6 months agoit6567306
Most Recent 2 months, 1 week agoit6567306
2 months, 1 week agoit6567306
2 months, 1 week agoit6567306
2 months, 1 week agoit6567306
2 months, 1 week agozouve
10 months, 3 weeks agomusafir
11 months, 2 weeks agoauwia
12 months agoauwia
1 year agoauwia
12 months agoRaNik69
1 year, 1 month agocalibre_04
1 year, 8 months agoemburria
2 years, 5 months agoflaviogcmelo
3 years, 2 months agoyukclam9
3 years, 2 months agosaad3577
1 year, 9 months agoPhat
3 years, 5 months agoMandar79
3 years, 10 months agoMandar79
3 years, 10 months agoadoptc94
3 years, 10 months agoKtNow
3 years, 6 months agoKtNow
3 years, 6 months agoama
3 years, 11 months agoSimoneF
3 years, 6 months agoama
3 years, 11 months ago