for more information about blocks : https://docs.oracle.com/en/database/oracle/oracle-database/21/cncpt/logical-storage-structures.html#GUID-DC561B64-67D9-43CC-A9BA-BE5B92A7B869

SELECT   (SELECT COUNT (DISTINCT at.TABLE_NAME)
            FROM all_tables  at
                 JOIN dba_segments seg ON seg.BLOCKS = at.BLOCKS
           WHERE at.NUM_ROWS > 0)
       - (SELECT COUNT (DISTINCT at.TABLE_NAME)
            FROM all_tables at
           WHERE at.NUM_ROWS > 0)
  FROM DUAL;

-564

I've calculated the difference between all table having a block and with nur_rows>0 with all the table with num_row >0.

I find that a negative number. It means that not the table have block. Why.

I can't provide an exemple on dbfiddle:

dba_segments doesn't not exist

CodePudding user response：

There are many problems with the join in the first subquery.

First up, you should join on the segment_name to avoid spurious matches when many tables have the same number of blocks.

But more importantly the blocks columns of these views store different information:

• *_tables.blocks is the number of blocks used in the table
• *_segments.blocks is the number of blocks allocated to the table

Tables can have blocks allocated to them that are not used. There's likely to be at least a few tables like this in most systems.

Here's an example from a database I'm currently using:

select count (*) num_tables,
       count ( us.segment_name ) num_segments,
       count ( case when us.blocks = ut.blocks then 1 end ) equal,
       count ( case when us.blocks <> ut.blocks then 1 end ) different
from   user_tables ut
left join user_segments us
on     table_name = segment_name
and    us.segment_type = 'TABLE';

NUM_TABLES NUM_SEGMENTS      EQUAL  DIFFERENT
---------- ------------ ---------- ----------
       457          406         51        351

Notice that most there's a mismatch for most of the tables.

There are also many tables with no segment. This could be because they haven't been allocated yet. But could also be because there are some table types that never have segments allocated to them.

External tables are an obvious example - the data exists on a file outside the database.

Another example is index-organized tables. Here the primary key index and table are combined into one structure. All the data are stored in the index; none is allocated to the table.

create table t (
  c1 int 
    constraint t_pk primary key
) organization index;

insert into t 
with rws as (
  select level x from dual
  connect by level <= 100
)
  select * from rws;

commit;

exec dbms_stats.gather_table_stats ( user, 't' ) ;

select blocks from user_tables
where  table_name = 'T';

    BLOCKS
----------
<null>  

select segment_name, segment_type, blocks 
from   user_segments
where  segment_name in ( 'T',  'T_PK' );

SEGMENT_NAME         SEGMENT_TYPE           BLOCKS
-------------------- ------------------ ----------
T_PK                 INDEX                       8