Can a data BLOCK accommodate rows of distinct tables?

In Oracle database, data BLOCK is defined as the smallest storage unit in the data files. But, there are many more concepts run around the BLOCK architecture. One of them is to understand if a BLOCK can accommodate rows from distinct tables. In this article, we are going to arrive at the justifiable answer with a simple case study. It could be pretty easy to find the answer if we are able to trace out the BLOCK_ID of every row that we insert into tables.

Having said that, we are not going to deal in detail with PCT_FREE, PCT_USED and much other space-related concepts of a data BLOCK. In an earlier Orskl blog, we addressed “How to find block sizes of all Oracle Database files?” and this article will add other concepts related to data BLOCK.

Case Study:

System considerations – Oracle database 11gR2 on Oracle Enterprise Linux.

Let us quickly start with our case study, where we

1. Create a table in USERS tablespace -> insert few rows -> identify the BLOCK ID of these rows.

2. Create another table into same USERS tablespace -> insert few rows -> identify the BLOCK ID of these rows.

Exercise:

1. Creating table TAB1 in USERS tablespace

SQL> create table tab1 (c1 number,c2 varchar2(10)) tablespace users;
Table created.

SQL> insert into tab1 values(&c1,’&c2′); — When prompted dump some values and repeat to insert few rows.

/

SQL> commit;

Commit complete.

SQL> select * from tab1;

      C1 C2

————– ————-

      1  AB

      2  BC

      3  CD

2. The way to retrieve the BLOCK ID of the rows is from the ROW ID of each row in the table.

SQL> select rowid, c1,c2 from tab1;

ROWID                       C1 C2

——————————————- ———- ———-

AAASw1AAEAAAACXAAA          1  AB

AAASw1AAEAAAACXAAB          2  BC

AAASw1AAEAAAACXAAC          3  CD

3. ROW ID’s can be decrypted to the BLOCK ID’s using the Oracle defined package “DBMS_ROWID”.

SQL> select dbms_rowid.rowid_block_number(rowid) “Block No”,c1,c2 from tab1;

Block No     C1 C2

—————- ———- ———-

151          1  AB

151          2  BC

151          3  CD

As we note, all the rows are into the same BLOCK 151 as the size of each row is not more than default block size 8KB.

Take away point: A block accommodates multiple rows of a table.

4. Now create a second table TAB2 in the same tablespace and insert few rows.

SQL> create table tab2 (c1 number,c2 varchar2(10)) tablespace users;

Table created.

SQL> insert into tab2 values(&c1,’&c2′);

SQL> commit;

Commit complete.

SQL> select * from tab2;

      C1 C2

————– ————-

      1  GB

      5  TH

      6  UY

5. Check the ROW ID’s and the BLOCK ID of the rows in the table TAB2

SQL> select dbms_rowid.rowid_block_number(rowid) “Block No”,c1,c2 from tab2;

Block No     C1 C2

——————– ——- ——-

159          1  GB

159          5  TH

159          6  UY

Take away point: Clearly shows that a new block (159) has been allocated and not the block (151) for the rows in the table TAB2.

Related references:

Oracle documentation explains storage BLOCK architecture with the help of below diagram.

Of which, “Table directory” says that ‘This portion of the data block contains information about the table having rows in this block.’

This is often misunderstood by many professionals that a “Table Directory” of a data BLOCK will have details of all the tables of rows that BLOCK accommodates.

Conclusion:

• Oracle data BLOCK can accommodate rows of only one table at any given point.
• What if I create multiple tables (1 billion) with one row of small size? It leads to lot of free space in each block allocated to each table.
• So the actual space occupied is not the same as the sum of the data blocks allocated to the system.
• You will have to always difference the FREE space in each data block to get the actual storage utilization
  .