Hi,
I executed below query on my database and found given output:
select a.table_name, a.NUM_ROWS, a.AVG_ROW_LEN, a.LAST_ANALYZED, a.SAMPLE_SIZE, a.blocks
from user_tables a
where num_rows <10;
Output:
TABLE_NAME NUM_ROWS AVG_ROW_LEN LAST_ANALYZED SAMPLE_SIZE BLOCKS
EMPLOYEE_STATUS 1 32 05-May-18 1 4
EMPLOYEE_CITY 6 36 05-May-18 6 4
Considering each block size to be of 8K bytes, what could be the possible reason(apart from fragmentation) for multiple block allocation when we have very small amount of data in the table. . Also how can I reduce this block allocation to minimal.
Thanks in advance.
When allocating space to a table, Oracle Database doesn't do it one block at a time. Instead it allocates an extent. This is a set of continuous data blocks.
The size of the first extent is determined by the initial size for the tablespace:
select initial_extent from dba_tablespaces
where tablespace_name = 'USERS';
INITIAL_EXTENT
65536
create table t (
x int, stuff varchar2(100) default lpad('x', 100, 'x')
) tablespace users ;
insert into t values (1, default);
commit;
select blocks, bytes from user_extents
where segment_name = 'T';
BLOCKS BYTES
8 65536 Now, whether or not these blocks are used is a different matter. This depends on the DML and DDL statements you've run on it in the past.
For example, if you insert 1,000 rows to the table. Then deleted all but one. The high water mark remains where it is. So a full table scan will read them all.
The docs explain this in more detail in the Logical Storage Structures section:
https://docs.oracle.com/en/database/oracle/oracle-database/18/cncpt/logical-storage-structures.html#GUID-13CE5EDA-8C66-4CA0-87B5-4069215A368D If you're really concerned you can shrink or move the table to ensure the high water mark is as low as possible.
But!
If you're worried about saving three blocks, you're looking at the wrong thing!
There's almost always more bigger gains to be made elsewhere.