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.Can you accommodate rows of distinct tables into a data BLOCK? #Oracle #Database Click To Tweet
System considerations – Oracle database 11gR2 on Oracle Enterprise Linux.
Let us quickly start with our case study, where we
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
SQL> select rowid, c1,c2 from tab1; ROWID C1 C2 ——————————————- ———- ———- AAASw1AAEAAAACXAAA 1 AB AAASw1AAEAAAACXAAB 2 BC AAASw1AAEAAAACXAAC 3 CD
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
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.True that data BLOCK in #Oracle #Database cannot accommodate rows of distinct tables. Click To Tweet
Of which, “Table directory” says that ‘This portion of the data block contains information about the table having rows in this block.’
- 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.
You agree or have a different opinion – I love to see your comments!
Feel free to give additional information in the comments, that I can validate and include in blog posts or update this as well.