Oracle TABLE

CREATE

TODO

CTAS

To duplicate an existing table you can use CTAS (Create Table As Select)...

CREATE mytable2 AS SELECT * FROM mytable1;

The SELECT can be pretty much any valid SELECT statement. Any ambiguity will cause problems.. i.e. you should define the columns you are going to populate if the query isn't going to be able to derive them.

SAMPLE

If you only want a subset of the data, consider the SAMPLE clause...

CREATE mytable2 AS SELECT * FROM mytable1 SAMPLE(20);

Will create mytable2 based on 20% of the rows in mytable1

CREATE mytable2 AS SELECT * FROM mytable1 SAMPLE BLOCK(20);

Will create mytable2 based on 20% of the block in mytable1

CREATE_TABLE_COST

"This procedure is used in capacity planning to determine the size of the table given various attributes."

https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_space.htm#i1003180

MODIFY

For example, to change the size or nullability of a column...

ALTER TABLE myTable MODIFY myColumn VARCHAR2(30) NOT NULL; 

NOTE: If there are NULL values in this column in any existing rows you will get ORA-02296Also see: MOVE, later on this page.

DROP

To DROP a table...

DROP TABLE '&TABLE';

To the DROP the table and PURGE the table from the RECYCLEBIN in a single step...

DROP TABLE '&TABLE' PURGE;

To DROP the table and all referential integrity constraints that refer to primary and unique keys in the dropped table...

DROP TABLE '&TABLE' CASCADE CONSTRAINTS;

You could use this to address: ORA-02449: unique/primary keys in table referenced by foreign keys

To "un-drop" a table...

FLASHBACK TABLE '%TABLE' TO BEFORE DROP;

Note: this is NOT possible if you used the PURGE option for the DROP TABLE or if the table has been otherwise purged from the recycle bin.

TRUNCATE

TRUNCATE TABLE '&TABLE' REUSE STORAGE;      # Table retains all extents (but they are all empty)

TRUNCATE TABLE '&TABLE' DROP STORAGE;       # Table retains only number of extents equal to MINEXTENTS (all are empty) (DEFAULT)

TRUNCATE TABLE '&TABLE' DROP ALL STORAGE;   # Table retains only one extent (which is empty) 11.2.0.2+

Row Movement

Row Movement gives Oracle permission to change the rowid of a row so that you can perform operations like shrink space or an update/set that moves rows across table partitions.

Check

SELECT row_movement

FROM   dba_tables

WHERE  owner = '&OWNER'

AND    table_name = '&TABLE';

Enable

ALTER TABLE &TABLE ENABLE ROW MOVEMENT;

Table Size

To see the table size (not including indexes and LOB segments)...

SELECT SUM(e.blocks) AS blocks,

       COUNT(*) AS extents,

       SUM(e.blocks*p.value) AS bytes,

       SUM(e.blocks*p.value)/1024 AS KB,

       SUM(e.blocks*p.value)/1024/1024 AS MB,

       SUM(e.blocks*p.value)/1024/1024/1024 AS GB

  FROM dba_extents e,

       v$parameter p

 WHERE e.owner = '&table_owner'

   AND e.segment_name = '&table_name'

   AND p.name = 'db_block_size';

An alternate approach (not including indexes and LOB segments)...

SET TRIMSPOOL ON

SET LINESIZE 132 PAGESIZE 80

COLUMN owner FORMAT a20

COLUMN segment_name FORMAT a30

COLUMN segment_type FORMAT a10

COLUMN bytes FORMAT 999,999,999,999,999


SELECT owner, segment_name, segment_type, bytes

  FROM dba_segments

 WHERE segment_type = 'TABLE'

   AND owner = '&table_owner'

   AND segment_name = '&table_name'

 ORDER BY bytes, owner, segment_name, segment_type

/

Including LOB segments and Indexes...

SELECT owner, segment_name, segment_type, bytes

  FROM dba_segments

 WHERE segment_type = 'TABLE'

   AND owner = '&&table_owner'

   AND segment_name = '&&table_name'

UNION

SELECT owner, segment_name, segment_type, bytes

  FROM dba_segments

 WHERE segment_type = 'LOBSEGMENT'

   AND owner = '&&table_owner'

   AND segment_name IN (SELECT segment_name

                        FROM dba_lobs

                        WHERE owner = '&&table_owner'

                        AND table_name = '&&table_name')

UNION

SELECT owner, segment_name, segment_type, bytes

  FROM dba_segments

 WHERE segment_type = 'INDEX'

   AND owner = '&&table_owner'

   AND segment_name IN (SELECT index_name

                        FROM dba_indexes

                        WHERE owner = '&&table_owner'

                        AND table_name = '&&table_name')

 ORDER BY bytes, owner, segment_name, segment_type

/

TODO: Include partitions (table and index)

In versions prior to 12.1 the query below will only be accurate if table statistics have just been gathered....

SELECT num_rows, blocks

  FROM dba_tables

 WHERE table_name = '&TABLE';

The queries below will give you accurate results... but, are you really sure you need accurate results?

https://asktom.oracle.com/ords/f?p=100:11:0::NO::P11_QUESTION_ID:1660875645686

SPOOL /tmp/tmp.sql

SET HEADING OFF

SET PAGESIZE 0

SET FEEDBACK OFF

SELECT 'SELECT '''||table_name||''' AS TABLE_NAME, COUNT(*) AS ROW_COUNT FROM '||owner||'."'||table_name||'";'

  FROM dba_tables

 WHERE owner = UPPER('&table_owner')

/

SPOOL OFF

COLUMN TABLE_NAME FORMAT a30

@/tmp/tmp.sql

Save this to a script (e.g. /tmp/my.sql) and run it. Cut&Paste direct to SQL*Plus won't work.This script assumes you are a DBA needing to query from another schema. If you are logged in to the owning schema, make your life easier and use USER_TABLES.

ALTER SESSION SET CURRENT_SCHEMA=&table_owner;

SET PAGESIZE 20

COLUMN TABLE_NAME FORMAT a30

WITH

  FUNCTION rcount(tname IN VARCHAR2) RETURN INT IS

    c INT;

  BEGIN

    EXECUTE IMMEDIATE

      'SELECT COUNT(*) FROM "'||tname||'"'

      INTO c;

    RETURN c;

  END;

SELECT table_name, rcount(table_name) AS ROW_COUNT

  FROM dba_tables

 WHERE owner = UPPER('&&table_owner');

/

Save this to a script (e.g. /tmp/my.sql) and run it. Cut&Paste direct to SQL*Plus won't work.This script assumes you are a DBA needing to query from another schema. If you are logged in to the owning schema, make your life easier and use USER_TABLES.

Space Usage

DBMS_SPACE.SPACE_USAGE

@/u01/dba/blkspc.sql

SET SERVEROUTPUT ONDECLARE   l_unformatted_blocks number;   l_unformatted_bytes number;   l_fs1_blocks number; l_fs1_bytes number;   l_fs2_blocks number; l_fs2_bytes number;   l_fs3_blocks number; l_fs3_bytes number;   l_fs4_blocks number; l_fs4_bytes number;   l_full_blocks number; l_full_bytes number;BEGIN   DBMS_SPACE.SPACE_USAGE   ( '&OWNER', '&TABLE', 'TABLE', l_unformatted_blocks,     l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes,     l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes,     l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes );
   DBMS_OUTPUT.PUT_LINE( 'Unformatted Blocks '|| l_unformatted_blocks );   DBMS_OUTPUT.PUT_LINE( 'Full Blocks        '|| l_full_blocks );   DBMS_OUTPUT.PUT_LINE( 'FS1 Blocks (0-25)  '|| l_fs1_blocks );   DBMS_OUTPUT.PUT_LINE( 'FS2 Blocks (25-50) '|| l_fs2_blocks );   DBMS_OUTPUT.PUT_LINE( 'FS3 Blocks (50-75) '|| l_fs3_blocks );   DBMS_OUTPUT.PUT_LINE( 'FS4 Blocks (75-100)'|| l_fs4_blocks );   DBMS_OUTPUT.PUT_LINE( 'total              '||                         (l_unformatted_blocks+ l_fs1_blocks+ l_fs2_blocks+                          l_fs3_blocks+ l_fs4_blocks+ l_full_blocks) );END;/SET SERVEROUTPUT OFF

DBMS_SPACE.UNUSED_SPACE

@/u01/dba/blkunused.sql

variable total_blocks number;variable total_bytes number;variable unused_blocks number;variable unused_bytes number;variable lastextf number;variable last_extb number;variable lastusedblock number;BEGINdbms_space.unused_space(   segment_owner => '&OWNER',   segment_name => '&TABLE',   segment_type => 'TABLE',   :total_blocks,    :total_bytes,   :unused_blocks,   :unused_bytes,   :lastextf,     :last_extb,   :lastusedblock);END;/print total_blocks;print total_bytes;print unused_blocks;print unused_bytes;print lastextf;print last_extb;print lastusedblock;

Blocks

Not-Empty Blocks

SELECT COUNT(*)

  FROM ( SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)

           FROM &mytable

          GROUP BY DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID));

All Blocks

SELECT SUM(BLOCKS)

  FROM dba_segments

 WHERE owner = '&myschema'

   AND segment_name = '&mytable';

Generate DDL

Shrink

NOTE: Row Movement must be Enabled in order for Shrink operations to work.

To recover space and amend the high water mark...

ALTER TABLE &TABLE SHRINK SPACE;

To recover space for the table and all dependant objects...

ALTER TABLE &TABLE SHRINK SPACE CASCADE;

To recover space then amend the high water mark as a seperate step...

ALTER TABLE &TABLE SHRINK SPACE COMPACT;

ALTER TABLE &TABLE SHRINK SPACE;

Do this before you start your shrink operation...SELECT SYS_CONTEXT('USERENV','SID')  FROM DUAL;... because then you can track progress using...COLUMN sql_text FORMAT a50COLUMN event FORMAT a30SELECT a.event,       a.wait_time,       c.sql_text,       c.physical_read_bytes / 1024 / 1024 / 1024 "GB_READ",       c.physical_write_bytes/ 1024 / 1024 / 1024 "GB_WRITE"  FROM v$session_wait a ,       v$session b ,       v$sql c WHERE a.SID = &SID   AND a.sid = b.sid   AND b.SQL_ID = c.SQL_ID; 

Move

NOTE: Row Movement must be Enabled in order for Move operations to work.

ALTER TABLE &TABLE MOVE;

ALTER TABLE &TABLE MOVE TABLESPACE &TSPACE;

You will need to rebuild indexes to make them usable again

ALTER TABLE &TABLE MOVE TABLESPACE &TSPACE UPDATE INDEXES

ALTER TABLE &TABLE MOVE ONLINE;

It is recommended to always re-gather statistics at the end of the move

Rebuild in same tablespace 

Move to new tablespace


Move to new tablespace keeping indexes valid

ONLINE introduced in 12.2 (update indexes not required)

Note that a Move operation will lead to two copies of the table being in existence for the duration of the move. Be aware of the space implications of this.

ALTER TABLE &TABLE MOVE ONLINE TABLESPACE &TSPACE;

ALTER TABLE &TABLE MOVE ONLINE TABLESPACE &TSPACE COMPRESS;

ALTER TABLE &TABLE MOVE ONLINE TABLESPACE &TSPACE NOCOMPRESS;

ALTER TABLE &TABLE MOVE ONLINE STORAGE (PCTINCREASE 0)'

Estimating Benefit of Shrink or Move

Assuming a block overhead of 8% (1), and that table stats are current,  we can estimate how many blocks the table should theoretically use vs what it is actually using...

SELECT blocks,

       num_rows*avg_row_len/8192*100/(100-pct_free)*1.08 est_blocks

  FROM user_tables

 WHERE table_name = '&TABLE';

Test Data

Collected examples of tables containining randomly generated data for tesing purposes

CREATE TABLE &mytab AS

  SELECT level                       AS id,

         DBMS_RANDOM.string('x',10)  AS code,

         DBMS_RANDOM.string('a',50)  AS short_description,

         DBMS_RANDOM.string('a',200) AS long_description

    FROM dual

  CONNECT BY level <= 10000;

Generates a table called mytab with 10,000 rows, an auto-generated unique numeric id, a random 10 character uppercase code, a random mixed case short description and a random mixed case long description.

Potential issues with this dataset....

Table Blocks Deep Dive

CREATE USER &&user IDENTIFIED BY "&password"

DEFAULT TABLESPACE &&tspace

TEMPORARY TABLESPACE &temp

QUOTA UNLIMITED ON &&tspace

PROFILE &profile;

GRANT resource TO &&user;

Create an empty schema for this test.

CREATE TABLE &mytab AS

  SELECT level                       AS id,

         DBMS_RANDOM.string('x',10)  AS code,

         DBMS_RANDOM.string('a',50)  AS short_description,

         DBMS_RANDOM.string('a',200) AS long_description

    FROM dual

  CONNECT BY level <= 10000;

Run this 3 times to create:
 MYTAB1, MYTAB2 and MYTAB3.

SELECT table_name,

       num_rows,

       avg_row_len,

       blocks,

       pct_free,

       ROUND(num_rows*avg_row_len/8192*100/(100-pct_free)*1.08) AS est_blocks

  FROM user_tables;

Let's see how many blocks our tables are using and let's compare that to what we estimate it should be using (est_blocks).

Let's save this script as:

/tmp/blkchk.sql

Note that it's tempting to include EMPTY_BLOCKS in the query above but it is only populated by ANALYZE (which we should no longer use) and not by DBMS_STATS. (2)
TABLE_NAME                       NUM_ROWS AVG_ROW_LEN     BLOCKS   PCT_FREE EST_BLOCKS------------------------------ ---------- ----------- ---------- ---------- ----------MYTAB1                              10000         267        401         10        391MYTAB2                              10000         267        401         10        391MYTAB3                              10000         267        401         10        391

Our estimate calculation is close enough for our purposes (it is within 10 blocks of the number of blocks reported in USER_TABLES).

Note that the number of blocks reported in USER_TABLES will differ from other sources of what, at first glance, would seem to be the same information. Consider this alternate way of counting blocks...

SELECT COUNT(1)

FROM( SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)

 FROM mytab1

 GROUP BY DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))

This query is counting blocks containing row data only. What about that ~8% block overhead we take into account when coming up with our estimated figure?
  COUNT(1)----------       385

To further muddy the waters, USER_SEGMENTS returns a different figure...

SELECT blocks

  FROM user_segments

 WHERE segment_name = 'MYTAB1'

   AND segment_type='TABLE'

This is because Segments are allocated Extents and an Extent contains multiple Blocks. The Block may be unused but it's still allocated to the Extent, hence why the Blocks value tends to be higher (unless all Extent Blocks are used, in which case the values should match).
    BLOCKS----------       512

Let's dig into that a bit further...

SELECT COUNT(extent_id) AS Extents,

       blocks           AS Blocks_per_Extent

  FROM user_extents

 WHERE segment_name = 'MYTAB1'

   AND segment_type='TABLE'

 GROUP BY blocks;

This shows that our table is allocated 19 Extents. 3 of those extents contain 128 Blocks. The other 16 Extents contain 8 Blocks. (38*128)+(16*8) = 512.
   EXTENTS BLOCKS_PER_EXTENT---------- -----------------         3               128        16                 8

Let's reverse engineer one of our tables and create a new copy of it without any data...

SET LONG 20000

SET PAGESIZE 0

SELECT DBMS_METADATA.GET_DDL(object_type,object_name)

  FROM user_objects

 WHERE object_name = '&TABLE';

Change the table name to MYTAB4 and run this to create the empty table...
CREATE TABLE "MYSCHEMA"."MYTAB4"   (    "ID" NUMBER,        "CODE" VARCHAR2(4000),        "SHORT_DESCRIPTION" VARCHAR2(4000),        "LONG_DESCRIPTION" VARCHAR2(4000)   ) SEGMENT CREATION IMMEDIATE  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)  TABLESPACE "USERS"

Let's create some new scripts to make our experiment easier to run. 

This script will provide us with a breakdown of what the number of blocks looks like from the various different sources of this information just for our new table (mytab4).

Let's call this script...

/tmp/blkchk4.sql

SELECT COUNT(extent_id) AS Extents,       blocks           AS Blocks_per_Extent,       SUM(blocks)      AS blocks  FROM user_extents WHERE segment_name = 'MYTAB4'   AND segment_type='TABLE' GROUP BY blocks;
SELECT  table_name,        est_blocks,        rowid_blocks,        table_blocks,        extent_blocks,        segment_blocks,        empty_blocks,        table_rows  FROM  ( SELECT blocks AS segment_blocks            FROM user_segments           WHERE segment_name = 'MYTAB4'             AND segment_type='TABLE' ), ( SELECT table_name,                 num_rows AS table_rows,                 blocks AS table_blocks,                 empty_blocks,                 ROUND(num_rows*avg_row_len/8192*100/(100-pct_free)*1.08) AS est_blocks            FROM user_tables           WHERE table_name = 'MYTAB4' ), ( SELECT COUNT(1) AS rowid_blocks            FROM ( SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)                   FROM mytab4                   GROUP BY DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ) ), ( SELECT SUM(blocks) AS extent_blocks     FROM user_extents   WHERE segment_name = 'MYTAB4'     AND segment_type = 'TABLE')/

This script will prompt for a number of rows to insert, insert them, gather stats and provide us with a breakdown of what the number of blocks looks like from the various different sources of this information.

Let's call this script...

/tmp/addrow4.sql

INSERT INTO mytab4  SELECT level+5000                  AS id,         DBMS_RANDOM.string('x',10)  AS code,         DBMS_RANDOM.string('a',50)  AS short_description,         DBMS_RANDOM.string('a',200) AS long_description    FROM dual  CONNECT BY level <= &ROWS;COMMIT;
EXEC DBMS_STATS.gather_table_stats(NULL,'MYTAB4');
@@/tmp/blkchk4.sql
Let's see how many blocks our empty table is using...

@/tmp/blkchk4

   EXTENTS BLOCKS_PER_EXTENT     BLOCKS---------- ----------------- ----------         1                 8          8

TABLE_NAME                     EST_BLOCKS ROWID_BLOCKS TABLE_BLOCKS EXTENT_BLOCKS SEGMENT_BLOCKS EMPTY_BLOCKS TABLE_ROWS------------------------------ ---------- ------------ ------------ ------------- -------------- ------------ ----------MYTAB4                                               0                          8              8
Let's gather stats to fill in the blanks...

EXEC DBMS_STATS.gather_table_stats(NULL,'MYTAB4');

@/tmp/blkchk4

So, an empty table has an allocated 8 block extent but none of the blocks are used.
   EXTENTS BLOCKS_PER_EXTENT     BLOCKS---------- ----------------- ----------         1                 8          8

TABLE_NAME                     EST_BLOCKS ROWID_BLOCKS TABLE_BLOCKS EXTENT_BLOCKS SEGMENT_BLOCKS EMPTY_BLOCKS TABLE_ROWS------------------------------ ---------- ------------ ------------ ------------- -------------- ------------ ----------MYTAB4                                  0            0            0             8              8            0          0

The existence of the initial extent is not guaranteed. It exists for us because the "CREATE TABLE" statement we used earlier contained a "SEGMENT CREATION IMMEDIATE" clause. Let's recreate the table using "SEGMENT CREATION DEFERRED" instead...

DROP TABLE MYTAB4;

CREATE TABLE "MYTAB4"

   (    "ID" NUMBER,

        "CODE" VARCHAR2(4000),

        "SHORT_DESCRIPTION" VARCHAR2(4000),

        "LONG_DESCRIPTION" VARCHAR2(4000)

   ) SEGMENT CREATION DEFERRED

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

  NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS";

Let's see how many blocks our empty table is using...

@/tmp/blkchk4

no rows selected

no rows selected
We also get the same result after gathering stats

We have a couple of ways to materialize a deferred segment. We can add a row, or we can do this...

Note that you need to run this as a privileged user.

BEGIN

  DBMS_SPACE_ADMIN.materialize_deferred_segments (

    schema_name    => 'MYSCHEMA',

    table_name     => 'MYTAB4',

    partition_name => NULL);

END;

/

Let's see how many blocks our empty table is using...

@/tmp/blkchk4

   EXTENTS BLOCKS_PER_EXTENT     BLOCKS---------- ----------------- ----------         1                 8          8

TABLE_NAME                     EST_BLOCKS ROWID_BLOCKS TABLE_BLOCKS EXTENT_BLOCKS SEGMENT_BLOCKS EMPTY_BLOCKS TABLE_ROWS------------------------------ ---------- ------------ ------------ ------------- -------------- ------------ ----------MYTAB4                                  0            0            0             8              8            0          0

We can also "dematerialize" tables with no rows...

Note that you need to run this as a privileged user.

BEGIN

  DBMS_SPACE_ADMIN.drop_empty_segments (

    schema_name    => 'MYSCHEMA',

    table_name     => 'MYTAB4',

    partition_name => NULL);

END;

/

Let's see how many blocks our empty table is using...

@/tmp/blkchk4

no rows selected

no rows selected
We also get the same result after gathering stats
What about a table with a single row?...

@/tmp/addrow4

1 Row fits in 1 Block.1 Extent contains 8 Blocks.5 Blocks reported in USER_TABLES...
   EXTENTS BLOCKS_PER_EXTENT     BLOCKS---------- ----------------- ----------         1                 8          8

TABLE_NAME                     EST_BLOCKS ROWID_BLOCKS TABLE_BLOCKS EXTENT_BLOCKS SEGMENT_BLOCKS EMPTY_BLOCKS TABLE_ROWS------------------------------ ---------- ------------ ------------ ------------- -------------- ------------ ----------MYTAB4                                  0            1            5             8              8            0          1
What does DBMS_SPACE.SPACE_USAGE show us...

@/u01/dba/blkspc.sql

Unformatted Blocks 0Full Blocks        0FS1 Blocks (0-25)  0FS2 Blocks (25-50) 0FS3 Blocks (50-75) 0FS4 Blocks (75-100)5total              5
This shows 5 blocks all with more than 75% space free.
If we divide 10,000 rows by 400 blocks, we get ~25 rows per block. So, with 1 row, what is in those 5 blocks?

SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(mytab4.rowid) block_id, rowid

  FROM mytab4;

So, there's one block accounted for...
  BLOCK_ID ROWID---------- ------------------   2560527 AAF99iAAEAAJxIPAAA
We can get some more information from dba_extents. (You will need to run this from a provileged account).
Save as:/tmp/extchk.sql

COLUMN owner FORMAT a10

COLUMN segment_name FORMAT a15

COLUMN tablespace_name FORMAT a20

SELECT owner,

       segment_name,

       tablespace_name,

       extent_id,

       file_id,

       block_id AS start_block,

       blocks,

       block_id+blocks-1 AS end_block

  FROM dba_extents

 WHERE segment_name =  'MYTAB4';

Now we know the extent starts at block_id 2560521 and, as we already know, it's 8 blocks long...
OWNER      SEGMENT_NAME    TABLESPACE_NAME       EXTENT_ID    FILE_ID START_BLOCK     BLOCKS  END_BLOCK---------- --------------- -------------------- ---------- ---------- ----------- ---------- ----------MYSCHEMA   MYTAB4          USERS                         0          4     2560521          8    2560528
Let's dump some blocks and have a look...

ALTER SESSION SET TRACEFILE_IDENTIFIER='BLOCK_DUMP';

ALTER SYSTEM DUMP DATAFILE 4 BLOCK MIN 2560521 BLOCK MAX 2560528; 

Let's also check what our data looks like so that we can spot it in the block dump...

SELECT * FROM mytab4;

        ID----------CODE-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SHORT_DESCRIPTION-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------LONG_DESCRIPTION-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------      500147UQV5NIY8hCJuVaIWxcFgSSUVlNCHOmFlAIPVpPFxgzRrXBzjjqCKIagdAwQwNDxiLYQLpNUdcXYqUUEVEESXMmOAEfSCSCfrYiDNPTMMNaAcvlzEAiUZpMiQEiOIPzVAFbDjoRiRfTAHXNFFNmcIOOIcQRWfTiMSCEMjChWgBUhMaoWSVrQeOddWtaOIzvkhYMravcmPGKVoCeumJnJhLqovearCcHOZgZnVQIhbBlSRHYICNXqLlZdDnjcXjApnjI
The trace file will be in this location...

SELECT value FROM v$diag_info WHERE name = 'Diag Trace';

ls | grep BLOCK_DUMP

Start dump data blocks tsn: 4 file#:4 minblk 2560521 maxblk 2560528Block dump from cache:Dump of buffer cache at level 3 for pdb=0 tsn=4 rdba=19337737Block dump from disk:buffer tsn: 4 rdba: 0x01271209 (4/2560521)scn: 0xa24413e56fc seq: 0x02 flg: 0x04 tail: 0x56fc2002frmt: 0x02 chkval: 0xe4ab type: 0x20=FIRST LEVEL BITMAP BLOCKHex dump of block: st=0, typ_found=1Dump of memory from 0x00000001210AC000 to 0x00000001210AE0001210AC000 20A20000 01271209 413E56FC 0A240204  [ ....'..A>V..$..]1210AC010 E4AB0000 00000000 00000000 00000000  [................]1210AC020 00000000 00000000 00000000 00000000  [................]...Dump of First Level Bitmap Block --------------------------------   nbits : 4 nranges: 1         parent dba:  0x0127120a   poffset: 0   unformatted: 0       total: 8         first useful block: 3   owning instance : 1   instance ownership changed at 03/11/2024 10:53:16   Last successful Search 03/11/2024 10:53:16   Freeness Status:            nf1 0      nf2 0      nf3 0      nf4 0      nf5 5   Extent Map Block Offset: 4294967295   First free datablock : 3   Bitmap block lock opcode 0   Locker xid:     :  0x0000.000.00000000 Dealloc scn(ub4/ub4): (0x00000a24.413e4547) Format scn: 0x00000000413e56fc   Flag: 0x000002a1 (REJCTX/-/AUX/-/OBJD/-/-/-)   Inc #: 0 Objd: 1564514  HWM Flag: HWM Set      Highwater::  0x01271211  ext#: 0      blk#: 8      ext size: 8  #blocks in seg. hdr's freelists: 0  #blocks below: 5  mapblk  0x00000000  offset: 0  --------------------------------------------------------  DBA Ranges :  --------------------------------------------------------   0x01271209  Length: 8      Offset: 0
   0:Metadata   1:Metadata   2:Metadata   3:100% free   4:100% free   5:100% free   6:100% free   7:100% free  --------------------------------------------------------...
Block dump from cache:Dump of buffer cache at level 3 for pdb=0 tsn=4 rdba=19337738Block dump from disk:buffer tsn: 4 rdba: 0x0127120a (4/2560522)scn: 0xa24413e56fc seq: 0x01 flg: 0x04 tail: 0x56fc2101frmt: 0x02 chkval: 0x81e5 type: 0x21=SECOND LEVEL BITMAP BLOCKHex dump of block: st=0, typ_found=1Dump of memory from 0x00000001210AC000 to 0x00000001210AE0001210AC000 21A20000 0127120A 413E56FC 0A240104  [!....'..A>V..$..]1210AC010 81E50000 00000000 00000000 00000000  [................]1210AC020 00000000 00000000 00000000 00000000  [................]        Repeat 1 times1210AC040 00000000 00000000 00000000 0127120B  [.............'..]1210AC050 00000001 00000001 00000000 00000000  [................]1210AC060 00000000 00000000 0017DF62 00000003  [...........b....]1210AC070 00000000 01271209 07000001 00000000  [.....'..........]1210AC080 00000000 00000000 00000000 00000000  [................]        Repeat 502 times1210ADFF0 00000000 00000000 00000000 56FC2101  [............V.!.]Dump of Second Level Bitmap Block   number: 1       nfree: 1       ffree: 0      pdba:     0x0127120b   Inc #: 0 Objd: 1564514 Flag: 3  opcode:0 xid:  L1 Ranges :  --------------------------------------------------------   0x01271209  Free: 7 Inst: 1
  --------------------------------------------------------
Block dump from cache:Dump of buffer cache at level 3 for pdb=0 tsn=4 rdba=19337739BH (0x70001007ffef298) file#: 4 rdba: 0x0127120b (4/2560523) class: 4 ba: 0x70001007fe7a000  set: 6 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 1,25  dbwrid: 0 obj: 1564514 objn: 1564510 tsn: [0/4] afn: 4 hint: f  hash: [0x700010019801698,0x700010019801698] lru: [0x70001007df5c178,0x700010021f9a238]  ckptq: [NULL] fileq: [NULL]  objq: [0x700010036cfc518,0x700010021f9a260] objaq: [0x700010036cfc508,0x700010021f9a270]  st: XCURRENT md: NULL fpin: 'ktewh26: kteinpscan' fscn: 0xa244145c78b tch: 1  flags:  LRBA: [0x0.0.0] LSCN: [0x0] HSCN: [0x0] HSUB: [65535]  Printing buffer operation history (latest change first):  cnt: 5  01. sid:05 L145:zib:mk:EXCL         02. sid:05 L212:zib:bic:FSQ  03. sid:05 L122:zgb:set:st          04. sid:05 L830:olq1:clr:WRT+CKT  05. sid:05 L951:zgb:lnk:objq        06. sid:05 L372:zgb:set:MEXCL  07. sid:05 L123:zgb:no:FEN          08. sid:05 L896:z_mkfr:ulnk:objq  09. sid:05 L083:zgb:ent:fn          10. sid:09 L144:zibmlt:mk:EXCL  11. sid:09 L710:zibmlt:bis:FBP      12. sid:09 L085:zgm:ent:fn  13. sid:09 L122:zgb:set:st          14. sid:09 L830:olq1:clr:WRT+CKT  15. sid:09 L951:zgb:lnk:objq        16. sid:09 L372:zgb:set:MEXCL  buffer tsn: 4 rdba: 0x0127120b (4/2560523)  scn: 0xa24413e56fc seq: 0x03 flg: 0x04 tail: 0x56fc2303  frmt: 0x02 chkval: 0xb966 type: 0x23=PAGETABLE SEGMENT HEADERHex dump of block: st=0, typ_found=1Dump of memory from 0x070001007FE7A000 to 0x070001007FE7C00070001007FE7A000 23A20000 0127120B 413E56FC 0A240304  [#....'..A>V..$..]70001007FE7A010 B9660000 00000000 00000000 00000000  [.f..............]70001007FE7A020 00000000 00000001 00000008 0A9C0000  [................]...  Extent Control Header  -----------------------------------------------------------------  Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 8                  last map  0x00000000  #maps: 0      offset: 2716      Highwater::  0x01271211  ext#: 0      blk#: 8      ext size: 8  #blocks in seg. hdr's freelists: 0  #blocks below: 5  mapblk  0x00000000  offset: 0                   Unlocked  --------------------------------------------------------  Low HighWater Mark :      Highwater::  0x01271211  ext#: 0      blk#: 8      ext size: 8  #blocks in seg. hdr's freelists: 0  #blocks below: 5  mapblk  0x00000000  offset: 0  Level 1 BMB for High HWM block: 0x01271209  Level 1 BMB for Low HWM block: 0x01271209  --------------------------------------------------------  Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0  L2 Array start offset:  0x00001434  First Level 3 BMB:  0x00000000  L2 Hint for inserts:  0x0127120a  Last Level 1 BMB:  0x01271209  Last Level II BMB:  0x0127120a  Last Level III BMB:  0x00000000     Map Header:: next  0x00000000  #extents: 1    obj#: 1564514 flag: 0x10000000  Inc # 0  Extent Map  -----------------------------------------------------------------   0x01271209  length: 8
  Auxillary Map  --------------------------------------------------------   Extent 0     :  L1 dba:  0x01271209 Data dba:  0x0127120c  --------------------------------------------------------
   Second Level Bitmap block DBAs   --------------------------------------------------------   DBA 1:   0x0127120a
Block dump from disk:buffer tsn: 4 rdba: 0x0127120b (4/2560523)scn: 0xa24413e56fc seq: 0x03 flg: 0x04 tail: 0x56fc2303frmt: 0x02 chkval: 0xb966 type: 0x23=PAGETABLE SEGMENT HEADERHex dump of block: st=0, typ_found=1Dump of memory from 0x00000001210AC000 to 0x00000001210AE0001210AC000 23A20000 0127120B 413E56FC 0A240304  [#....'..A>V..$..]1210AC010 B9660000 00000000 00000000 00000000  [.f..............]1210AC020 00000000 00000001 00000008 0A9C0000  [................]...  Extent Control Header  -----------------------------------------------------------------  Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 8                  last map  0x00000000  #maps: 0      offset: 2716      Highwater::  0x01271211  ext#: 0      blk#: 8      ext size: 8  #blocks in seg. hdr's freelists: 0  #blocks below: 5  mapblk  0x00000000  offset: 0                   Unlocked  --------------------------------------------------------  Low HighWater Mark :      Highwater::  0x01271211  ext#: 0      blk#: 8      ext size: 8  #blocks in seg. hdr's freelists: 0  #blocks below: 5  mapblk  0x00000000  offset: 0  Level 1 BMB for High HWM block: 0x01271209  Level 1 BMB for Low HWM block: 0x01271209  --------------------------------------------------------  Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0  L2 Array start offset:  0x00001434  First Level 3 BMB:  0x00000000  L2 Hint for inserts:  0x0127120a  Last Level 1 BMB:  0x01271209  Last Level II BMB:  0x0127120a  Last Level III BMB:  0x00000000     Map Header:: next  0x00000000  #extents: 1    obj#: 1564514 flag: 0x10000000  Inc # 0  Extent Map  -----------------------------------------------------------------   0x01271209  length: 8
  Auxillary Map  --------------------------------------------------------   Extent 0     :  L1 dba:  0x01271209 Data dba:  0x0127120c  --------------------------------------------------------
   Second Level Bitmap block DBAs   --------------------------------------------------------   DBA 1:   0x0127120a
Block dump from cache:Dump of buffer cache at level 3 for pdb=0 tsn=4 rdba=19337740BH (0x700010022fe6918) file#: 4 rdba: 0x0127120c (4/2560524) class: 1 ba: 0x700010022db2000  set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,25  dbwrid: 0 obj: 1564514 objn: 1564510 tsn: [0/4] afn: 4 hint: f  hash: [0x7000100193c4048,0x7000100193c4048] lru: [0x700010090fa0538,0x700010077facf58]  ckptq: [NULL] fileq: [NULL]  objq: [0x70001000bd69880,0x700010077facf80] objaq: [0x70001000bd69870,0x700010077facf90]  st: XCURRENT md: NULL fpin: 'kdswh01: kdstgr' fscn: 0xa244145c78b tch: 1  flags: only_sequential_access  LRBA: [0x0.0.0] LSCN: [0x0] HSCN: [0x0] HSUB: [65535]  Printing buffer operation history (latest change first):  cnt: 6  01. sid:05 L338:zibmlt:set:MSHR     02. sid:05 L144:zibmlt:mk:EXCL  03. sid:05 L122:zgb:set:st          04. sid:05 L830:olq1:clr:WRT+CKT  05. sid:05 L951:zgb:lnk:objq        06. sid:05 L372:zgb:set:MEXCL  07. sid:05 L123:zgb:no:FEN          08. sid:05 L896:z_mkfr:ulnk:objq  09. sid:05 L083:zgb:ent:fn          10. sid:09 L144:zibmlt:mk:EXCL  11. sid:09 L710:zibmlt:bis:FBP      12. sid:09 L085:zgm:ent:fn  13. sid:09 L122:zgb:set:st          14. sid:09 L830:olq1:clr:WRT+CKT  15. sid:09 L951:zgb:lnk:objq        16. sid:09 L372:zgb:set:MEXCL  buffer tsn: 4 rdba: 0x0127120c (4/2560524)  scn: 0xa24413e56fc seq: 0x02 flg: 0x04 tail: 0x56fc0602  frmt: 0x02 chkval: 0x9438 type: 0x06=trans dataHex dump of block: st=0, typ_found=1Dump of memory from 0x0700010022DB2000 to 0x0700010022DB4000700010022DB2000 06A20000 0127120C 413E56FC 0A240204  [.....'..A>V..$..]700010022DB2010 94380000 01000000 0017DF62 413E56FC  [.8.........bA>V.]700010022DB2020 80000A24 00023200 01271209 00000000  [...$..2..'......]...Block header dump:  0x0127120c Object id on Block? Y seg/obj: 0x17df62  csc:  0x00000a24413e56fc  itc: 2  flg: E  typ: 1 - DATA     brn: 0  bdba: 0x1271209 ver: 0x01 opc: 0     inc: 0  exflg: 0
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.000000000x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000bdba: 0x0127120cdata_block_dump,data header at 0x700010022db2064===============tsiz: 0x1f98hsiz: 0xepbl: 0x700010022db2064     76543210flag=--------ntab=0nrow=0frre=-1fsbo=0xefseo=0x1f98avsp=0x1f8atosp=0x1f8ablock_row_dump:end_of_block_dump
Block dump from disk:buffer tsn: 4 rdba: 0x0127120c (4/2560524)scn: 0xa24413e56fc seq: 0x02 flg: 0x04 tail: 0x56fc0602frmt: 0x02 chkval: 0x9438 type: 0x06=trans dataHex dump of block: st=0, typ_found=1Dump of memory from 0x00000001210AC000 to 0x00000001210AE0001210AC000 06A20000 0127120C 413E56FC 0A240204  [.....'..A>V..$..]1210AC010 94380000 01000000 0017DF62 413E56FC  [.8.........bA>V.]1210AC020 80000A24 00023200 01271209 00000000  [...$..2..'......]Block header dump:  0x0127120c Object id on Block? Y seg/obj: 0x17df62  csc:  0x00000a24413e56fc  itc: 2  flg: E  typ: 1 - DATA     brn: 0  bdba: 0x1271209 ver: 0x01 opc: 0     inc: 0  exflg: 0
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.000000000x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000bdba: 0x0127120cdata_block_dump,data header at 0x1210ac064===============tsiz: 0x1f98hsiz: 0xepbl: 0x1210ac064     76543210flag=--------ntab=0nrow=0frre=-1fsbo=0xefseo=0x1f98avsp=0x1f8atosp=0x1f8ablock_row_dump:end_of_block_dump
NOTE: trans data blocks hold table data. If your database has been around for a while, it is likely that you will see data in the hex dump of memory that looks like it comes from other tables. I believe this to be deleted data. i.e. in Oracle when you delete data you are just removing the path to that data, not zeroing out the blocks (as that would negatively impact the performance of delete transactions).
Block dump from cache:Dump of buffer cache at level 3 for pdb=0 tsn=4 rdba=19337741BH (0x700010021f9a158) file#: 4 rdba: 0x0127120d (4/2560525) class: 1 ba: 0x7000100216be000  set: 6 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 1,25  dbwrid: 0 obj: 1564514 objn: 1564510 tsn: [0/4] afn: 4 hint: f  hash: [0x70001001bf66b78,0x70001001bf66b78] lru: [0x70001007ffef378,0x70001007ef81a58]  ckptq: [NULL] fileq: [NULL]  objq: [0x70001007ffef3a0,0x70001007ef81a80] objaq: [0x70001007ffef3b0,0x70001007ef81a90]  st: XCURRENT md: NULL fpin: 'kdswh01: kdstgr' fscn: 0xa244145c78b tch: 1  flags: only_sequential_access  LRBA: [0x0.0.0] LSCN: [0x0] HSCN: [0x0] HSUB: [65535]  Printing buffer operation history (latest change first):  cnt: 9  01. sid:05 L144:zibmlt:mk:EXCL      02. sid:05 L710:zibmlt:bis:FBP  03. sid:05 L085:zgm:ent:fn          04. sid:05 L122:zgb:set:st  05. sid:05 L830:olq1:clr:WRT+CKT    06. sid:05 L951:zgb:lnk:objq  07. sid:05 L372:zgb:set:MEXCL       08. sid:05 L123:zgb:no:FEN  09. sid:05 L896:z_mkfr:ulnk:objq    10. sid:05 L083:zgb:ent:fn  11. sid:09 L144:zibmlt:mk:EXCL      12. sid:09 L710:zibmlt:bis:FBP  13. sid:09 L085:zgm:ent:fn          14. sid:09 L122:zgb:set:st  15. sid:09 L830:olq1:clr:WRT+CKT    16. sid:09 L951:zgb:lnk:objq  buffer tsn: 4 rdba: 0x0127120d (4/2560525)  scn: 0xa24413e56fc seq: 0x02 flg: 0x04 tail: 0x56fc0602  frmt: 0x02 chkval: 0x1d14 type: 0x06=trans dataHex dump of block: st=0, typ_found=1Dump of memory from 0x07000100216BE000 to 0x07000100216C00007000100216BE000 06A20000 0127120D 413E56FC 0A240204  [.....'..A>V..$..]7000100216BE010 1D140000 01000000 0017DF62 413E56FC  [...........bA>V.]7000100216BE020 80000A24 00023200 01271209 00000000  [...$..2..'......]...Block header dump:  0x0127120d Object id on Block? Y seg/obj: 0x17df62  csc:  0x00000a24413e56fc  itc: 2  flg: E  typ: 1 - DATA     brn: 0  bdba: 0x1271209 ver: 0x01 opc: 0     inc: 0  exflg: 0
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.000000000x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000bdba: 0x0127120ddata_block_dump,data header at 0x7000100216be064===============tsiz: 0x1f98hsiz: 0xepbl: 0x7000100216be064     76543210flag=--------ntab=0nrow=0frre=-1fsbo=0xefseo=0x1f98avsp=0x1f8atosp=0x1f8ablock_row_dump:end_of_block_dump
Block dump from disk:buffer tsn: 4 rdba: 0x0127120d (4/2560525)scn: 0xa24413e56fc seq: 0x02 flg: 0x04 tail: 0x56fc0602frmt: 0x02 chkval: 0x1d14 type: 0x06=trans dataHex dump of block: st=0, typ_found=1Dump of memory from 0x00000001210AC000 to 0x00000001210AE0001210AC000 06A20000 0127120D 413E56FC 0A240204  [.....'..A>V..$..]1210AC010 1D140000 01000000 0017DF62 413E56FC  [...........bA>V.]1210AC020 80000A24 00023200 01271209 00000000  [...$..2..'......]...Block header dump:  0x0127120d Object id on Block? Y seg/obj: 0x17df62  csc:  0x00000a24413e56fc  itc: 2  flg: E  typ: 1 - DATA     brn: 0  bdba: 0x1271209 ver: 0x01 opc: 0     inc: 0  exflg: 0
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.000000000x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000bdba: 0x0127120ddata_block_dump,data header at 0x1210ac064===============tsiz: 0x1f98hsiz: 0xepbl: 0x1210ac064     76543210flag=--------ntab=0nrow=0frre=-1fsbo=0xefseo=0x1f98avsp=0x1f8atosp=0x1f8ablock_row_dump:end_of_block_dump
Block dump from cache:Dump of buffer cache at level 3 for pdb=0 tsn=4 rdba=19337742BH (0x700010077face78) file#: 4 rdba: 0x0127120e (4/2560526) class: 1 ba: 0x700010077874000  set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,25  dbwrid: 0 obj: 1564514 objn: 1564510 tsn: [0/4] afn: 4 hint: f  hash: [0x700010019662fc8,0x700010019662fc8] lru: [0x700010022fe69f8,0x700010082fcf6b8]  ckptq: [NULL] fileq: [NULL]  objq: [0x700010022fe6a20,0x700010082fcf6e0] objaq: [0x700010022fe6a30,0x700010082fcf6f0]  st: XCURRENT md: NULL fpin: 'kdswh01: kdstgr' fscn: 0xa244145c78b tch: 1  flags: only_sequential_access  LRBA: [0x0.0.0] LSCN: [0x0] HSCN: [0x0] HSUB: [65535]  Printing buffer operation history (latest change first):  cnt: 5  01. sid:05 L144:zibmlt:mk:EXCL      02. sid:05 L710:zibmlt:bis:FBP  03. sid:05 L085:zgm:ent:fn          04. sid:05 L122:zgb:set:st  05. sid:05 L830:olq1:clr:WRT+CKT    06. sid:05 L951:zgb:lnk:objq  07. sid:05 L372:zgb:set:MEXCL       08. sid:05 L123:zgb:no:FEN  09. sid:05 L896:z_mkfr:ulnk:objq    10. sid:05 L083:zgb:ent:fn  11. sid:09 L338:zibmlt:set:MSHR     12. sid:09 L144:zibmlt:mk:EXCL  13. sid:09 L122:zgb:set:st          14. sid:09 L830:olq1:clr:WRT+CKT  15. sid:09 L951:zgb:lnk:objq        16. sid:09 L372:zgb:set:MEXCL  buffer tsn: 4 rdba: 0x0127120e (4/2560526)  scn: 0xa24413e56fc seq: 0x02 flg: 0x04 tail: 0x56fc0602  frmt: 0x02 chkval: 0x4b26 type: 0x06=trans dataHex dump of block: st=0, typ_found=1Dump of memory from 0x0700010077874000 to 0x0700010077876000700010077874000 06A20000 0127120E 413E56FC 0A240204  [.....'..A>V..$..]700010077874010 4B260000 01000000 0017DF62 413E56FC  [K&.........bA>V.]700010077874020 80000A24 00023200 01271209 00000000  [...$..2..'......]...Block header dump:  0x0127120e Object id on Block? Y seg/obj: 0x17df62  csc:  0x00000a24413e56fc  itc: 2  flg: E  typ: 1 - DATA     brn: 0  bdba: 0x1271209 ver: 0x01 opc: 0     inc: 0  exflg: 0
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.000000000x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000bdba: 0x0127120edata_block_dump,data header at 0x700010077874064===============tsiz: 0x1f98hsiz: 0xepbl: 0x700010077874064     76543210flag=--------ntab=0nrow=0frre=-1fsbo=0xefseo=0x1f98avsp=0x1f8atosp=0x1f8ablock_row_dump:end_of_block_dump
Block dump from disk:buffer tsn: 4 rdba: 0x0127120e (4/2560526)scn: 0xa24413e56fc seq: 0x02 flg: 0x04 tail: 0x56fc0602frmt: 0x02 chkval: 0x4b26 type: 0x06=trans dataHex dump of block: st=0, typ_found=1Dump of memory from 0x00000001210AC000 to 0x00000001210AE0001210AC000 06A20000 0127120E 413E56FC 0A240204  [.....'..A>V..$..]1210AC010 4B260000 01000000 0017DF62 413E56FC  [K&.........bA>V.]1210AC020 80000A24 00023200 01271209 00000000  [...$..2..'......]...Block header dump:  0x0127120e Object id on Block? Y seg/obj: 0x17df62  csc:  0x00000a24413e56fc  itc: 2  flg: E  typ: 1 - DATA     brn: 0  bdba: 0x1271209 ver: 0x01 opc: 0     inc: 0  exflg: 0
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.000000000x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000bdba: 0x0127120edata_block_dump,data header at 0x1210ac064===============tsiz: 0x1f98hsiz: 0xepbl: 0x1210ac064     76543210flag=--------ntab=0nrow=0frre=-1fsbo=0xefseo=0x1f98avsp=0x1f8atosp=0x1f8ablock_row_dump:end_of_block_dump
Block dump from cache:Dump of buffer cache at level 3 for pdb=0 tsn=4 rdba=19337743BH (0x70001007ef81978) file#: 4 rdba: 0x0127120f (4/2560527) class: 1 ba: 0x70001007e484000  set: 6 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 1,25  dbwrid: 0 obj: 1564514 objn: 1564510 tsn: [0/4] afn: 4 hint: f  hash: [0x700010019222788,0x700010019222788] lru: [0x700010021f9a238,0x700010082f76fd8]  ckptq: [NULL] fileq: [NULL]  objq: [0x700010021f9a260,0x700010036cfc518] objaq: [0x700010021f9a270,0x700010036cfc508]  st: XCURRENT md: NULL fpin: 'kdswh01: kdstgr' fscn: 0xa244145c78b tch: 1  flags: only_sequential_access  LRBA: [0x0.0.0] LSCN: [0x0] HSCN: [0x0] HSUB: [65535]  Printing buffer operation history (latest change first):  cnt: 6  01. sid:05 L144:zibmlt:mk:EXCL      02. sid:05 L710:zibmlt:bis:FBP  03. sid:05 L085:zgm:ent:fn          04. sid:05 L122:zgb:set:st  05. sid:05 L830:olq1:clr:WRT+CKT    06. sid:05 L951:zgb:lnk:objq  07. sid:05 L372:zgb:set:MEXCL       08. sid:05 L123:zgb:no:FEN  09. sid:05 L896:z_mkfr:ulnk:objq    10. sid:05 L083:zgb:ent:fn  11. sid:09 L144:zibmlt:mk:EXCL      12. sid:09 L710:zibmlt:bis:FBP  13. sid:09 L085:zgm:ent:fn          14. sid:09 L122:zgb:set:st  15. sid:09 L830:olq1:clr:WRT+CKT    16. sid:09 L951:zgb:lnk:objq  buffer tsn: 4 rdba: 0x0127120f (4/2560527)  scn: 0xa24413e56fe seq: 0x01 flg: 0x06 tail: 0x56fe0601  frmt: 0x02 chkval: 0x4e3a type: 0x06=trans dataHex dump of block: st=0, typ_found=1Dump of memory from 0x070001007E484000 to 0x070001007E48600070001007E484000 06A20000 0127120F 413E56FE 0A240106  [.....'..A>V..$..]70001007E484010 4E3A0000 01000000 0017DF62 413E56FC  [N:.........bA>V.]70001007E484020 80000A24 00023200 01271209 000A000E  [...$..2..'......]...70001007E485EF0 0403C233 020A3437 55515635 4E495938  [...3..47UQV5NIY8]70001007E485F00 3268434A 75566149 57786346 67535355  [2hCJuVaIWxcFgSSU]70001007E485F10 566C4E43 484F6D46 6C414950 56705046  [VlNCHOmFlAIPVpPF]70001007E485F20 78677A52 7258427A 6A6A7143 4B496167  [xgzRrXBzjjqCKIag]70001007E485F30 644177C8 51774E44 78694C59 514C704E  [dAw.QwNDxiLYQLpN]70001007E485F40 55646358 59715555 45564545 53584D6D  [UdcXYqUUEVEESXMm]70001007E485F50 4F414566 53435343 66725969 444E5054  [OAEfSCSCfrYiDNPT]70001007E485F60 4D4D4E61 4163766C 7A454169 555A704D  [MMNaAcvlzEAiUZpM]70001007E485F70 69514569 4F49507A 56414662 446A6F52  [iQEiOIPzVAFbDjoR]70001007E485F80 69526654 4148584E 46464E6D 63494F4F  [iRfTAHXNFFNmcIOO]70001007E485F90 49635152 57665469 4D534345 4D6A4368  [IcQRWfTiMSCEMjCh]70001007E485FA0 57674255 684D616F 57535672 51654F64  [WgBUhMaoWSVrQeOd]70001007E485FB0 64577461 4F497A76 6B68594D 72617663  [dWtaOIzvkhYMravc]70001007E485FC0 6D50474B 566F4365 756D4A6E 4A684C71  [mPGKVoCeumJnJhLq]70001007E485FD0 6F766561 72436348 4F5A675A 6E565149  [ovearCcHOZgZnVQI]70001007E485FE0 6862426C 53524859 49434E58 714C6C5A  [hbBlSRHYICNXqLlZ]70001007E485FF0 64446E6A 63586A41 706E6A49 56FE0601  [dDnjcXjApnjIV...]Block header dump:  0x0127120f Object id on Block? Y seg/obj: 0x17df62  csc:  0x00000a24413e56fc  itc: 2  flg: E  typ: 1 - DATA     brn: 0  bdba: 0x1271209 ver: 0x01 opc: 0     inc: 0  exflg: 0
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc0x01   0x000a.00e.012ae506  0x0140082e.1ef2.06  --U-    1  fsc 0x0000.413e56fe0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000bdba: 0x0127120fdata_block_dump,data header at 0x70001007e484064===============tsiz: 0x1f98hsiz: 0x14pbl: 0x70001007e484064     76543210flag=--------ntab=1nrow=1frre=-1fsbo=0x14fseo=0x1e8aavsp=0x1e76tosp=0x1e760xe:pti[0]      nrow=1  offs=00x12:pri[0]     offs=0x1e8ablock_row_dump:tab 0, row 0, @0x1e8atl: 270 fb: --H-FL-- lb: 0x1  cc: 4col  0: [ 3]  c2 33 02col  1: [10]  34 37 55 51 56 35 4e 49 59 38col  2: [50] 68 43 4a 75 56 61 49 57 78 63 46 67 53 53 55 56 6c 4e 43 48 4f 6d 46 6c 41 49 50 56 70 50 46 78 67 7a 52 72 58 42 7a 6a 6a 71 43 4b 49 61 67 64 41 77col  3: [200] 51 77 4e 44 78 69 4c 59 51 4c 70 4e 55 64 63 58 59 71 55 55 45 56 45 45 53 58 4d 6d 4f 41 45 66 53 43 53 43 66 72 59 69 44 4e 50 54 4d 4d 4e 61 41 63 76 6c 7a 45 41 69 55 5a 70 4d 69 51 45 69 4f 49 50 7a 56 41 46 62 44 6a 6f 52 69 52 66 54 41 48 58 4e 46 46 4e 6d 63 49 4f 4f 49 63 51 52 57 66 54 69 4d 53 43 45 4d 6a 43 68 57 67 42 55 68 4d 61 6f 57 53 56 72 51 65 4f 64 64 57 74 61 4f 49 7a 76 6b 68 59 4d 72 61 76 63 6d 50 47 4b 56 6f 43 65 75 6d 4a 6e 4a 68 4c 71 6f 76 65 61 72 43 63 48 4f 5a 67 5a 6e 56 51 49 68 62 42 6c 53 52 48 59 49 43 4e 58 71 4c 6c 5a 64 44 6e 6a 63 58 6a 41 70 6e 6a 49end_of_block_dump
Block dump from disk:buffer tsn: 4 rdba: 0x0127120f (4/2560527)scn: 0xa24413e56fe seq: 0x01 flg: 0x06 tail: 0x56fe0601frmt: 0x02 chkval: 0x4e3a type: 0x06=trans dataHex dump of block: st=0, typ_found=1Dump of memory from 0x00000001210AC000 to 0x00000001210AE0001210AC000 06A20000 0127120F 413E56FE 0A240106  [.....'..A>V..$..]1210AC010 4E3A0000 01000000 0017DF62 413E56FC  [N:.........bA>V.]1210AC020 80000A24 00023200 01271209 000A000E  [...$..2..'......]...1210ADEF0 0403C233 020A3437 55515635 4E495938  [...3..47UQV5NIY8]1210ADF00 3268434A 75566149 57786346 67535355  [2hCJuVaIWxcFgSSU]1210ADF10 566C4E43 484F6D46 6C414950 56705046  [VlNCHOmFlAIPVpPF]1210ADF20 78677A52 7258427A 6A6A7143 4B496167  [xgzRrXBzjjqCKIag]1210ADF30 644177C8 51774E44 78694C59 514C704E  [dAw.QwNDxiLYQLpN]1210ADF40 55646358 59715555 45564545 53584D6D  [UdcXYqUUEVEESXMm]1210ADF50 4F414566 53435343 66725969 444E5054  [OAEfSCSCfrYiDNPT]1210ADF60 4D4D4E61 4163766C 7A454169 555A704D  [MMNaAcvlzEAiUZpM]1210ADF70 69514569 4F49507A 56414662 446A6F52  [iQEiOIPzVAFbDjoR]1210ADF80 69526654 4148584E 46464E6D 63494F4F  [iRfTAHXNFFNmcIOO]1210ADF90 49635152 57665469 4D534345 4D6A4368  [IcQRWfTiMSCEMjCh]1210ADFA0 57674255 684D616F 57535672 51654F64  [WgBUhMaoWSVrQeOd]1210ADFB0 64577461 4F497A76 6B68594D 72617663  [dWtaOIzvkhYMravc]1210ADFC0 6D50474B 566F4365 756D4A6E 4A684C71  [mPGKVoCeumJnJhLq]1210ADFD0 6F766561 72436348 4F5A675A 6E565149  [ovearCcHOZgZnVQI]1210ADFE0 6862426C 53524859 49434E58 714C6C5A  [hbBlSRHYICNXqLlZ]1210ADFF0 64446E6A 63586A41 706E6A49 56FE0601  [dDnjcXjApnjIV...]Block header dump:  0x0127120f Object id on Block? Y seg/obj: 0x17df62  csc:  0x00000a24413e56fc  itc: 2  flg: E  typ: 1 - DATA     brn: 0  bdba: 0x1271209 ver: 0x01 opc: 0     inc: 0  exflg: 0
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc0x01   0x000a.00e.012ae506  0x0140082e.1ef2.06  --U-    1  fsc 0x0000.413e56fe0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000bdba: 0x0127120fdata_block_dump,data header at 0x1210ac064===============tsiz: 0x1f98hsiz: 0x14pbl: 0x1210ac064     76543210flag=--------ntab=1nrow=1frre=-1fsbo=0x14fseo=0x1e8aavsp=0x1e76tosp=0x1e760xe:pti[0]      nrow=1  offs=00x12:pri[0]     offs=0x1e8ablock_row_dump:tab 0, row 0, @0x1e8atl: 270 fb: --H-FL-- lb: 0x1  cc: 4col  0: [ 3]  c2 33 02col  1: [10]  34 37 55 51 56 35 4e 49 59 38col  2: [50] 68 43 4a 75 56 61 49 57 78 63 46 67 53 53 55 56 6c 4e 43 48 4f 6d 46 6c 41 49 50 56 70 50 46 78 67 7a 52 72 58 42 7a 6a 6a 71 43 4b 49 61 67 64 41 77col  3: [200] 51 77 4e 44 78 69 4c 59 51 4c 70 4e 55 64 63 58 59 71 55 55 45 56 45 45 53 58 4d 6d 4f 41 45 66 53 43 53 43 66 72 59 69 44 4e 50 54 4d 4d 4e 61 41 63 76 6c 7a 45 41 69 55 5a 70 4d 69 51 45 69 4f 49 50 7a 56 41 46 62 44 6a 6f 52 69 52 66 54 41 48 58 4e 46 46 4e 6d 63 49 4f 4f 49 63 51 52 57 66 54 69 4d 53 43 45 4d 6a 43 68 57 67 42 55 68 4d 61 6f 57 53 56 72 51 65 4f 64 64 57 74 61 4f 49 7a 76 6b 68 59 4d 72 61 76 63 6d 50 47 4b 56 6f 43 65 75 6d 4a 6e 4a 68 4c 71 6f 76 65 61 72 43 63 48 4f 5a 67 5a 6e 56 51 49 68 62 42 6c 53 52 48 59 49 43 4e 58 71 4c 6c 5a 64 44 6e 6a 63 58 6a 41 70 6e 6a 49end_of_block_dump
SELECT UTL_RAW.CAST_TO_NUMBER(REPLACE('c2 33 02',' ')) AS col0,       UTL_RAW.CAST_TO_VARCHAR2(REPLACE('34 37 55 51 56 35 4e 49 59 38',' ')) AS col1,       UTL_RAW.CAST_TO_VARCHAR2(REPLACE('68 43 4a 75 56 61 49 57 78 63 46 67 53 53 55 56 6c 4e 43 48 4f 6d 46 6c 41',' ')) AS col2a,       UTL_RAW.CAST_TO_VARCHAR2(REPLACE('49 50 56 70 50 46 78 67 7a 52 72 58 42 7a 6a 6a 71 43 4b 49 61 67 64 41 77',' ')) AS col2b,       UTL_RAW.CAST_TO_VARCHAR2(REPLACE('51 77 4e 44 78 69 4c 59 51 4c 70 4e 55 64 63 58 59 71 55 55 45 56 45 45 53',' ')) AS col3a,       UTL_RAW.CAST_TO_VARCHAR2(REPLACE('58 4d 6d 4f 41 45 66 53 43 53 43 66 72 59 69 44 4e 50 54 4d 4d 4e 61 41 63',' ')) AS col3b,       UTL_RAW.CAST_TO_VARCHAR2(REPLACE('76 6c 7a 45 41 69 55 5a 70 4d 69 51 45 69 4f 49 50 7a 56 41 46 62 44 6a 6f',' ')) AS col3c,       UTL_RAW.CAST_TO_VARCHAR2(REPLACE('52 69 52 66 54 41 48 58 4e 46 46 4e 6d 63 49 4f 4f 49 63 51 52 57 66 54 69',' ')) AS col3d,       UTL_RAW.CAST_TO_VARCHAR2(REPLACE('4d 53 43 45 4d 6a 43 68 57 67 42 55 68 4d 61 6f 57 53 56 72 51 65 4f 64 64',' ')) AS col3e,       UTL_RAW.CAST_TO_VARCHAR2(REPLACE('57 74 61 4f 49 7a 76 6b 68 59 4d 72 61 76 63 6d 50 47 4b 56 6f 43 65 75 6d',' ')) AS col3f,       UTL_RAW.CAST_TO_VARCHAR2(REPLACE('4a 6e 4a 68 4c 71 6f 76 65 61 72 43 63 48 4f 5a 67 5a 6e 56 51 49 68 62 42',' ')) AS col3g,       UTL_RAW.CAST_TO_VARCHAR2(REPLACE('6c 53 52 48 59 49 43 4e 58 71 4c 6c 5a 64 44 6e 6a 63 58 6a 41 70 6e 6a 49',' ')) AS col3h  FROM dual;
      500147UQV5NIY8hCJuVaIWxcFgSSUVlNCHOmFlAIPVpPFxgzRrXBzjjqCKIagdAwQwNDxiLYQLpNUdcXYqUUEVEESXMmOAEfSCSCfrYiDNPTMMNaAcvlzEAiUZpMiQEiOIPzVAFbDjoRiRfTAHXNFFNmcIOOIcQRWfTiMSCEMjChWgBUhMaoWSVrQeOddWtaOIzvkhYMravcmPGKVoCeumJnJhLqovearCcHOZgZnVQIhbBlSRHYICNXqLlZdDnjcXjApnjI
Compare the orginal (above) to the output processed from the trace file (right)...
      500147UQV5NIY8hCJuVaIWxcFgSSUVlNCHOmFlAIPVpPFxgzRrXBzjjqCKIagdAwQwNDxiLYQLpNUdcXYqUUEVEESXMmOAEfSCSCfrYiDNPTMMNaAcvlzEAiUZpMiQEiOIPzVAFbDjoRiRfTAHXNFFNmcIOOIcQRWfTiMSCEMjChWgBUhMaoWSVrQeOddWtaOIzvkhYMravcmPGKVoCeumJnJhLqovearCcHOZgZnVQIhbBlSRHYICNXqLlZdDnjcXjApnjI
Block dump from cache:Dump of buffer cache at level 3 for pdb=0 tsn=4 rdba=19337744BH (0x700010082fcf5d8) file#: 4 rdba: 0x01271210 (4/2560528) class: 1 ba: 0x700010082b96000  set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,25  dbwrid: 0 obj: 1564514 objn: 1564510 tsn: [0/4] afn: 4 hint: f  hash: [0x70001001aeb1fd8,0x70001001aeb1fd8] lru: [0x700010077facf58,0x70001006df592b8]  ckptq: [NULL] fileq: [NULL]  objq: [0x700010077facf80,0x70001000bd69880] objaq: [0x700010077facf90,0x70001000bd69870]  st: XCURRENT md: NULL fpin: 'kdswh01: kdstgr' fscn: 0xa244145c78b tch: 1  flags: only_sequential_access  LRBA: [0x0.0.0] LSCN: [0x0] HSCN: [0x0] HSUB: [65535]  Printing buffer operation history (latest change first):  cnt: 5  01. sid:05 L144:zibmlt:mk:EXCL      02. sid:05 L710:zibmlt:bis:FBP  03. sid:05 L085:zgm:ent:fn          04. sid:05 L122:zgb:set:st  05. sid:05 L830:olq1:clr:WRT+CKT    06. sid:05 L951:zgb:lnk:objq  07. sid:05 L372:zgb:set:MEXCL       08. sid:05 L123:zgb:no:FEN  09. sid:05 L896:z_mkfr:ulnk:objq    10. sid:05 L083:zgb:ent:fn  11. sid:09 L144:zibmlt:mk:EXCL      12. sid:09 L710:zibmlt:bis:FBP  13. sid:09 L085:zgm:ent:fn          14. sid:09 L122:zgb:set:st  15. sid:09 L830:olq1:clr:WRT+CKT    16. sid:09 L951:zgb:lnk:objq  buffer tsn: 4 rdba: 0x01271210 (4/2560528)  scn: 0xa24413e56fc seq: 0x02 flg: 0x04 tail: 0x56fc0602  frmt: 0x02 chkval: 0x26c8 type: 0x06=trans dataHex dump of block: st=0, typ_found=1Dump of memory from 0x0700010082B96000 to 0x0700010082B98000700010082B96000 06A20000 01271210 413E56FC 0A240204  [.....'..A>V..$..]700010082B96010 26C80000 01000000 0017DF62 413E56FC  [&..........bA>V.]700010082B96020 80000A24 00023200 01271209 00000000  [...$..2..'......]...Block header dump:  0x01271210 Object id on Block? Y seg/obj: 0x17df62  csc:  0x00000a24413e56fc  itc: 2  flg: E  typ: 1 - DATA     brn: 0  bdba: 0x1271209 ver: 0x01 opc: 0     inc: 0  exflg: 0
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.000000000x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000bdba: 0x01271210data_block_dump,data header at 0x700010082b96064===============tsiz: 0x1f98hsiz: 0xepbl: 0x700010082b96064     76543210flag=--------ntab=0nrow=0frre=-1fsbo=0xefseo=0x1f98avsp=0x1f8atosp=0x1f8ablock_row_dump:end_of_block_dump
Block dump from disk:buffer tsn: 4 rdba: 0x01271210 (4/2560528)scn: 0xa24413e56fc seq: 0x02 flg: 0x04 tail: 0x56fc0602frmt: 0x02 chkval: 0x26c8 type: 0x06=trans dataHex dump of block: st=0, typ_found=1Dump of memory from 0x00000001210AC000 to 0x00000001210AE0001210AC000 06A20000 01271210 413E56FC 0A240204  [.....'..A>V..$..]1210AC010 26C80000 01000000 0017DF62 413E56FC  [&..........bA>V.]1210AC020 80000A24 00023200 01271209 00000000  [...$..2..'......]...Block header dump:  0x01271210 Object id on Block? Y seg/obj: 0x17df62  csc:  0x00000a24413e56fc  itc: 2  flg: E  typ: 1 - DATA     brn: 0  bdba: 0x1271209 ver: 0x01 opc: 0     inc: 0  exflg: 0
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.000000000x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000bdba: 0x01271210data_block_dump,data header at 0x1210ac064===============tsiz: 0x1f98hsiz: 0xepbl: 0x1210ac064     76543210flag=--------ntab=0nrow=0frre=-1fsbo=0xefseo=0x1f98avsp=0x1f8atosp=0x1f8ablock_row_dump:end_of_block_dumpEnd dump data blocks tsn: 4 file#: 4 minblk 2560521 maxblk 2560528

So, the 5 blocks shown in USER_TABLES reflects the number of "trans data" blocks in the initial 8 block extent.

We can get a breakdown of block types from our tracefile...

grep ^frmt tracefile.trc

frmt: 0x02 chkval: 0xe4ab type: 0x20=FIRST LEVEL BITMAP BLOCKfrmt: 0x02 chkval: 0x81e5 type: 0x21=SECOND LEVEL BITMAP BLOCKfrmt: 0x02 chkval: 0xb966 type: 0x23=PAGETABLE SEGMENT HEADERfrmt: 0x02 chkval: 0x9438 type: 0x06=trans datafrmt: 0x02 chkval: 0x1d14 type: 0x06=trans datafrmt: 0x02 chkval: 0x4b26 type: 0x06=trans datafrmt: 0x02 chkval: 0x4e3a type: 0x06=trans datafrmt: 0x02 chkval: 0x26c8 type: 0x06=trans data
Let's see what happens when we fill a block..
Let's add rows and watch. Remember we estimate 25 rows per block. I'll only show the interesting output below...

@/tmp/addrow4

Our est_blocks doesn't go to 1 (from 0) until we have inserted 13 rows.
Apparently our estimate of 25 rows per block was incorrect...
   EXTENTS BLOCKS_PER_EXTENT     BLOCKS---------- ----------------- ----------         1                 8          8

TABLE_NAME                     EST_BLOCKS ROWID_BLOCKS TABLE_BLOCKS EXTENT_BLOCKS SEGMENT_BLOCKS EMPTY_BLOCKS TABLE_ROWS------------------------------ ---------- ------------ ------------ ------------- -------------- ------------ ----------MYTAB4                                  1            1            5             8              8            0         25
At 27 rows, we begin using a second block, with table blocks remaining at 5. How high do we need to go for that to change? In theory 5 blocks fit 130 rows. Let's see what happens...
   EXTENTS BLOCKS_PER_EXTENT     BLOCKS---------- ----------------- ----------         1                 8          8

TABLE_NAME                     EST_BLOCKS ROWID_BLOCKS TABLE_BLOCKS EXTENT_BLOCKS SEGMENT_BLOCKS EMPTY_BLOCKS TABLE_ROWS------------------------------ ---------- ------------ ------------ ------------- -------------- ------------ ----------MYTAB4                                  1            2            5             8              8            0         27
Into a third block at 53 rows...
   EXTENTS BLOCKS_PER_EXTENT     BLOCKS---------- ----------------- ----------         1                 8          8

TABLE_NAME                     EST_BLOCKS ROWID_BLOCKS TABLE_BLOCKS EXTENT_BLOCKS SEGMENT_BLOCKS EMPTY_BLOCKS TABLE_ROWS------------------------------ ---------- ------------ ------------ ------------- -------------- ------------ ----------MYTAB4                                  2            3            5             8              8            0         53
Into a fourth block at 79 rows...
   EXTENTS BLOCKS_PER_EXTENT     BLOCKS---------- ----------------- ----------         1                 8          8

TABLE_NAME                     EST_BLOCKS ROWID_BLOCKS TABLE_BLOCKS EXTENT_BLOCKS SEGMENT_BLOCKS EMPTY_BLOCKS TABLE_ROWS------------------------------ ---------- ------------ ------------ ------------- -------------- ------------ ----------MYTAB4                                  3            4            5             8              8            0         79
Into the fifth block at 105 rows...
   EXTENTS BLOCKS_PER_EXTENT     BLOCKS---------- ----------------- ----------         1                 8          8

TABLE_NAME                     EST_BLOCKS ROWID_BLOCKS TABLE_BLOCKS EXTENT_BLOCKS SEGMENT_BLOCKS EMPTY_BLOCKS TABLE_ROWS------------------------------ ---------- ------------ ------------ ------------- -------------- ------------ ----------MYTAB4                                  4            5            5             8              8            0        105
Into the sixth block at 131 rows. But number of blocks has jumped to 13 an increase of 8. 
   EXTENTS BLOCKS_PER_EXTENT     BLOCKS---------- ----------------- ----------         2                 8         16

TABLE_NAME                     EST_BLOCKS ROWID_BLOCKS TABLE_BLOCKS EXTENT_BLOCKS SEGMENT_BLOCKS EMPTY_BLOCKS TABLE_ROWS------------------------------ ---------- ------------ ------------ ------------- -------------- ------------ ----------MYTAB4                                  5            6           13            16             16            0        131

@/tmp/extchk.sql

OWNER      SEGMENT_NAME    TABLESPACE_NAME       EXTENT_ID    FILE_ID START_BLOCK     BLOCKS  END_BLOCK---------- --------------- -------------------- ---------- ---------- ----------- ---------- ----------MYSCHEMA   MYTAB4          USERS                         0          4     2560521          8    2560528MYSCHEMA   MYTAB4          USERS                         1          4     2560529          8    2560536
Let's dump some blocks and have a look...

ALTER SESSION SET TRACEFILE_IDENTIFIER='BLOCK_DUMP';

ALTER SYSTEM DUMP DATAFILE 4 BLOCK MIN 2560521 BLOCK MAX 2560536; 

Compare the previous dump to the new dump. We can see the extra extent. We can see the 5 FULL blocks.
You should also be able to see the contents of your table in the extents.
  --------------------------------------------------------  DBA Ranges :  --------------------------------------------------------   0x01271209  Length: 8      Offset: 0
   0:Metadata   1:Metadata   2:Metadata   3:100% free   4:100% free   5:100% free   6:100% free   7:100% free  --------------------------------------------------------
  --------------------------------------------------------  DBA Ranges :  --------------------------------------------------------   0x01271209  Length: 8      Offset: 0   0x01271211  Length: 8      Offset: 8
   0:Metadata   1:Metadata   2:Metadata   3:FULL   4:FULL   5:FULL   6:FULL   7:FULL   8:100% free   9:100% free   10:100% free   11:100% free   12:100% free   13:100% free   14:100% free   15:100% free  --------------------------------------------------------

grep ^frmt tracefile.trc

frmt: 0x02 chkval: 0x7a57 type: 0x20=FIRST LEVEL BITMAP BLOCKfrmt: 0x02 chkval: 0x8907 type: 0x21=SECOND LEVEL BITMAP BLOCKfrmt: 0x02 chkval: 0xa2ba type: 0x23=PAGETABLE SEGMENT HEADERfrmt: 0x02 chkval: 0x9d5a type: 0x06=trans datafrmt: 0x02 chkval: 0x3e0b type: 0x06=trans datafrmt: 0x02 chkval: 0x6e1e type: 0x06=trans datafrmt: 0x02 chkval: 0x739c type: 0x06=trans datafrmt: 0x02 chkval: 0xf38a type: 0x06=trans datafrmt: 0x02 chkval: 0x3115 type: 0x06=trans datafrmt: 0x02 chkval: 0x3116 type: 0x06=trans datafrmt: 0x02 chkval: 0x26c6 type: 0x06=trans datafrmt: 0x02 chkval: 0xc11f type: 0x06=trans datafrmt: 0x02 chkval: 0xd05f type: 0x06=trans datafrmt: 0x02 chkval: 0x50f0 type: 0x06=trans datafrmt: 0x02 chkval: 0xb09a type: 0x06=trans datafrmt: 0x02 chkval: 0x08f6 type: 0x06=trans data
At 26 rows per block, we should fill the 13 blocks at 338 rows.
   EXTENTS BLOCKS_PER_EXTENT     BLOCKS---------- ----------------- ----------         2                 8         16

TABLE_NAME                     EST_BLOCKS ROWID_BLOCKS TABLE_BLOCKS EXTENT_BLOCKS SEGMENT_BLOCKS EMPTY_BLOCKS TABLE_ROWS------------------------------ ---------- ------------ ------------ ------------- -------------- ------------ ----------MYTAB4                                 13           13           13            16             16            0        338

@/u01/dba/blkspc.sql

Unformatted Blocks 0Full Blocks        12FS1 Blocks (0-25)  1FS2 Blocks (25-50) 0FS3 Blocks (50-75) 0FS4 Blocks (75-100)0total              13

@/tmp/addrow4

@/u01/dba/blkspc.sql

Unformatted Blocks 0Full Blocks        13FS1 Blocks (0-25)  0FS2 Blocks (25-50) 0FS3 Blocks (50-75) 0FS4 Blocks (75-100)7total              20
This time only 7 blocks have been added to the table yet the extent has increased by 8.
   EXTENTS BLOCKS_PER_EXTENT     BLOCKS---------- ----------------- ----------         3                 8         24

TABLE_NAME                     EST_BLOCKS ROWID_BLOCKS TABLE_BLOCKS EXTENT_BLOCKS SEGMENT_BLOCKS EMPTY_BLOCKS TABLE_ROWS------------------------------ ---------- ------------ ------------ ------------- -------------- ------------ ----------MYTAB4                                 13           14           20            24             24            0        339

@/tmp/extchk.sql

OWNER      SEGMENT_NAME    TABLESPACE_NAME       EXTENT_ID    FILE_ID START_BLOCK     BLOCKS  END_BLOCK---------- --------------- -------------------- ---------- ---------- ----------- ---------- ----------MYSCHEMA   MYTAB4          USERS                         0          4     2560521          8    2560528MYSCHEMA   MYTAB4          USERS                         1          4     2560529          8    2560536MYSCHEMA   MYTAB4          USERS                         2          4     2560537          8    2560544
Let's dump some blocks and have a look...

ALTER SESSION SET TRACEFILE_IDENTIFIER='BLOCK_DUMP';

ALTER SYSTEM DUMP DATAFILE 4 BLOCK MIN 2560521 BLOCK MAX 2560544; 

grep ^frmt tracefile.trc

We have an additional FIRST LEVEL BITMAP BLOCK. It won't be counted in USER_TABLES because, as we already established, that only counts "trans data" blocks.
frmt: 0x02 chkval: 0x6875 type: 0x20=FIRST LEVEL BITMAP BLOCKfrmt: 0x02 chkval: 0x983b type: 0x21=SECOND LEVEL BITMAP BLOCKfrmt: 0x02 chkval: 0xb195 type: 0x23=PAGETABLE SEGMENT HEADERfrmt: 0x02 chkval: 0x9d5a type: 0x06=trans datafrmt: 0x02 chkval: 0x3e0b type: 0x06=trans datafrmt: 0x02 chkval: 0x6e1e type: 0x06=trans datafrmt: 0x02 chkval: 0x739c type: 0x06=trans datafrmt: 0x02 chkval: 0xf38a type: 0x06=trans datafrmt: 0x02 chkval: 0x9033 type: 0x06=trans datafrmt: 0x02 chkval: 0x3bc0 type: 0x06=trans datafrmt: 0x02 chkval: 0x52ce type: 0x06=trans datafrmt: 0x02 chkval: 0x3119 type: 0x06=trans datafrmt: 0x02 chkval: 0xca04 type: 0x06=trans datafrmt: 0x02 chkval: 0xeecc type: 0x06=trans datafrmt: 0x02 chkval: 0xb089 type: 0x06=trans datafrmt: 0x02 chkval: 0x23f7 type: 0x06=trans datafrmt: 0x02 chkval: 0x7401 type: 0x20=FIRST LEVEL BITMAP BLOCKfrmt: 0x02 chkval: 0x4e52 type: 0x06=trans datafrmt: 0x02 chkval: 0x3508 type: 0x06=trans datafrmt: 0x02 chkval: 0xda9c type: 0x06=trans datafrmt: 0x02 chkval: 0xcb0a type: 0x06=trans datafrmt: 0x02 chkval: 0x4acf type: 0x06=trans datafrmt: 0x02 chkval: 0x3980 type: 0x06=trans datafrmt: 0x02 chkval: 0x7dd8 type: 0x06=trans data
20 blocks should fill up at around 520 rows. Let's keep going. (add 180 rows, then add 1 at a time until number of table blocks changes)

@/tmp/addrow4

   EXTENTS BLOCKS_PER_EXTENT     BLOCKS---------- ----------------- ----------         4                 8         32

TABLE_NAME                     EST_BLOCKS ROWID_BLOCKS TABLE_BLOCKS EXTENT_BLOCKS SEGMENT_BLOCKS EMPTY_BLOCKS TABLE_ROWS------------------------------ ---------- ------------ ------------ ------------- -------------- ------------ ----------MYTAB4                                 20           21           28            32             32            0        521
Next target 728 (add 205 rows, then add 1 at a time until number of table blocks changes)

@/tmp/addrow4

   EXTENTS BLOCKS_PER_EXTENT     BLOCKS---------- ----------------- ----------         5                 8         40

TABLE_NAME                     EST_BLOCKS ROWID_BLOCKS TABLE_BLOCKS EXTENT_BLOCKS SEGMENT_BLOCKS EMPTY_BLOCKS TABLE_ROWS------------------------------ ---------- ------------ ------------ ------------- -------------- ------------ ----------MYTAB4                                 29           29           35            40             40            0        729
Next target 910  (add 180 rows, then add 1 at a time until number of table blocks changes)

@/tmp/addrow4

   EXTENTS BLOCKS_PER_EXTENT     BLOCKS---------- ----------------- ----------         6                 8         48

TABLE_NAME                     EST_BLOCKS ROWID_BLOCKS TABLE_BLOCKS EXTENT_BLOCKS SEGMENT_BLOCKS EMPTY_BLOCKS TABLE_ROWS------------------------------ ---------- ------------ ------------ ------------- -------------- ------------ ----------MYTAB4                                 36           36           43            48             48            0        911

There seems to be a pattern. 8 blocks per extent. Every other extent gets an additional "first level bitmap block". But, as we know from our orginal tables, after 16 extents, Oracle seems to increase the extent size. Let's reproduce that. We should fill 16 extents at 3068 rows. 26 rows per block, with a pattern of 5,8,7,8,7,8,7,8,7,8,7,8,7,8,7,8 usable blocks across 16 extents.... ((8*8)+(7*7)+5)*26... (64+49+5)*26...  118*26 = 3068

@/tmp/addrow4

   EXTENTS BLOCKS_PER_EXTENT     BLOCKS---------- ----------------- ----------        16                 8        128

TABLE_NAME                     EST_BLOCKS ROWID_BLOCKS TABLE_BLOCKS EXTENT_BLOCKS SEGMENT_BLOCKS EMPTY_BLOCKS TABLE_ROWS------------------------------ ---------- ------------ ------------ ------------- -------------- ------------ ----------MYTAB4                                120          118          118           128            128            0       3068

@/tmp/addrow4

   EXTENTS BLOCKS_PER_EXTENT     BLOCKS---------- ----------------- ----------         1               128        128        16                 8        128

TABLE_NAME                     EST_BLOCKS ROWID_BLOCKS TABLE_BLOCKS EXTENT_BLOCKS SEGMENT_BLOCKS EMPTY_BLOCKS TABLE_ROWS------------------------------ ---------- ------------ ------------ ------------- -------------- ------------ ----------MYTAB4                                120          119          244           256            256            0       3069

 @/tmp/extchk.sql

OWNER      SEGMENT_NAME    TABLESPACE_NAME       EXTENT_ID    FILE_ID START_BLOCK     BLOCKS  END_BLOCK---------- --------------- -------------------- ---------- ---------- ----------- ---------- ----------MYSCHEMA   MYTAB4          USERS                         0          4     2560521          8    2560528MYSCHEMA   MYTAB4          USERS                         1          4     2560529          8    2560536MYSCHEMA   MYTAB4          USERS                         2          4     2560537          8    2560544MYSCHEMA   MYTAB4          USERS                         3          4     2560545          8    2560552MYSCHEMA   MYTAB4          USERS                         4          4     2560553          8    2560560MYSCHEMA   MYTAB4          USERS                         5          4     2560561          8    2560568MYSCHEMA   MYTAB4          USERS                         6          4     2560569          8    2560576MYSCHEMA   MYTAB4          USERS                         7          4     2560577          8    2560584MYSCHEMA   MYTAB4          USERS                         8          4     2560585          8    2560592MYSCHEMA   MYTAB4          USERS                         9          4     2560593          8    2560600MYSCHEMA   MYTAB4          USERS                        10          4     2560601          8    2560608MYSCHEMA   MYTAB4          USERS                        11          4     2560609          8    2560616MYSCHEMA   MYTAB4          USERS                        12          4     2560617          8    2560624MYSCHEMA   MYTAB4          USERS                        13          4     2560625          8    2560632MYSCHEMA   MYTAB4          USERS                        14          4     2560633          8    2560640MYSCHEMA   MYTAB4          USERS                        15          4     2560641          8    2560648MYSCHEMA   MYTAB4          USERS                        16          4     2560649        128    2560776
Let's add 6,931 rows so that MYTAB4 is the same as MYTAB1,2 and 3.

@/tmp/addrow4

   EXTENTS BLOCKS_PER_EXTENT     BLOCKS---------- ----------------- ----------         3               128        384        16                 8        128

TABLE_NAME                     EST_BLOCKS ROWID_BLOCKS TABLE_BLOCKS EXTENT_BLOCKS SEGMENT_BLOCKS EMPTY_BLOCKS TABLE_ROWS------------------------------ ---------- ------------ ------------ ------------- -------------- ------------ ----------MYTAB4                                391          385          496           512            512            0      10000
Let's run a final set of tests...

 @/tmp/extchk.sql


OWNER      SEGMENT_NAME    TABLESPACE_NAME       EXTENT_ID    FILE_ID START_BLOCK     BLOCKS  END_BLOCK---------- --------------- -------------------- ---------- ---------- ----------- ---------- ----------MYSCHEMA   MYTAB4          USERS                         0          4     2560521          8    2560528MYSCHEMA   MYTAB4          USERS                         1          4     2560529          8    2560536MYSCHEMA   MYTAB4          USERS                         2          4     2560537          8    2560544MYSCHEMA   MYTAB4          USERS                         3          4     2560545          8    2560552MYSCHEMA   MYTAB4          USERS                         4          4     2560553          8    2560560MYSCHEMA   MYTAB4          USERS                         5          4     2560561          8    2560568MYSCHEMA   MYTAB4          USERS                         6          4     2560569          8    2560576MYSCHEMA   MYTAB4          USERS                         7          4     2560577          8    2560584MYSCHEMA   MYTAB4          USERS                         8          4     2560585          8    2560592MYSCHEMA   MYTAB4          USERS                         9          4     2560593          8    2560600MYSCHEMA   MYTAB4          USERS                        10          4     2560601          8    2560608MYSCHEMA   MYTAB4          USERS                        11          4     2560609          8    2560616MYSCHEMA   MYTAB4          USERS                        12          4     2560617          8    2560624MYSCHEMA   MYTAB4          USERS                        13          4     2560625          8    2560632MYSCHEMA   MYTAB4          USERS                        14          4     2560633          8    2560640MYSCHEMA   MYTAB4          USERS                        15          4     2560641          8    2560648MYSCHEMA   MYTAB4          USERS                        16          4     2560649        128    2560776MYSCHEMA   MYTAB4          USERS                        17          4     2560777        128    2560904MYSCHEMA   MYTAB4          USERS                        18          4     2560905        128    2561032
Let's dump all the blocks and have a look...
I have had ocassions where the tracefile seems to contain duplicate rows which throws off the counts below. The checkpoint may be required to ensure consistent data.

ALTER SYSTEM CHECKPOINT;

ALTER SESSION SET TRACEFILE_IDENTIFIER='BLOCK_DUMP';

ALTER SYSTEM DUMP DATAFILE 4 BLOCK MIN 2560521 BLOCK MAX 2561032; 

Let's count the blocks. This should match EXTENT_BLOCKS and SEGMENT_BLOCKS...

grep ^buffer tracefile.trc | sort | uniq | wc -l

     512

 This should match TABLE_BLOCKS...

grep ^frmt tracefile.trc | grep "trans data" | wc -l

     496

Let's compare MYTAB4 to the other three tables...

@/tmp/blkchk.sql


Argh! Why is table_blocks different?
TABLE_NAME                       NUM_ROWS AVG_ROW_LEN     BLOCKS   PCT_FREE EMPTY_BLOCKS EST_BLOCKS------------------------------ ---------- ----------- ---------- ---------- ------------ ----------MYTAB1                              10000         267        401         10            0        391MYTAB2                              10000         267        401         10            0        391MYTAB3                              10000         267        401         10            0        391MYTAB4                              10000         267        496         10            0        391
The answer seems to be related to our insertion method. We populated MYTAB4 in multiple transactions. We populated the other tables in a single transactions.
Enter value for owner: MYSCHEMAEnter value for table: MYTAB1
Unformatted Blocks 0Full Blocks        384FS1 Blocks (0-25)  0FS2 Blocks (25-50) 1FS3 Blocks (50-75) 0FS4 Blocks (75-100)0total              385
Enter value for owner: MYSCHEMAEnter value for table: MYTAB4
Unformatted Blocks 62Full Blocks        384FS1 Blocks (0-25)  0FS2 Blocks (25-50) 1FS3 Blocks (50-75) 0FS4 Blocks (75-100)49total              496

Don't forget to delete the tracefiles before continuing.

ls *BLOCK_DUMP*

rm *BLOCK_DUMP*

*** UNDER CONSTRUCTION BELOW THIS POINT *** TODO

DELETE FROM mytab1 WHERE MOD(id, 2) = 0;

DELETE FROM mytab2 WHERE id < 5001;

DELETE FROM mytab3 WHERE id > 5000;

COMMIT;

EXEC DBMS_STATS.gather_table_stats(NULL,'MYTAB1');

EXEC DBMS_STATS.gather_table_stats(NULL,'MYTAB2');

EXEC DBMS_STATS.gather_table_stats(NULL,'MYTAB3');

Let's delete 50% of the rows from each table, but in different ways....

We should gather stats when we're done so that that the figures in user_tables are updated.

SELECT table_name,

       num_rows,

       avg_row_len,

       blocks,

       pct_free,

       empty_blocks,

       ROUND(num_rows*avg_row_len/8192*100/(100-pct_free)*1.08) AS est_blocks

  FROM user_tables;

Let's see how many blocks our tables are now using.

TABLE_NAME                       NUM_ROWS AVG_ROW_LEN     BLOCKS   PCT_FREE EST_BLOCKS------------------------------ ---------- ----------- ---------- ---------- ----------MYTAB1                               5000         267        401         10        196MYTAB2                               5000         267        401         10        196MYTAB3                               5000         267        401         10        196

The actual number of blocks remains the same but our estimate approximately halves.

INSERT INTO &mytab

  SELECT level+5000                  AS id,

         DBMS_RANDOM.string('x',10)  AS code,

         DBMS_RANDOM.string('a',50)  AS short_description,

         DBMS_RANDOM.string('a',200) AS long_description

    FROM dual

  CONNECT BY level <= 5000;

COMMIT;

EXEC DBMS_STATS.gather_table_stats(NULL,'MYTAB1');

EXEC DBMS_STATS.gather_table_stats(NULL,'MYTAB2');

EXEC DBMS_STATS.gather_table_stats(NULL,'MYTAB3');

Let's add 5000 more rows to each table.

SELECT table_name,

       num_rows,

       avg_row_len,

       blocks,

       pct_free,

       ROUND(num_rows*avg_row_len/8192*100/(100-pct_free)*1.08) AS est_blocks

  FROM user_tables;

Let's see how many blocks our tables are now using.

TABLE_NAME                       NUM_ROWS AVG_ROW_LEN     BLOCKS   PCT_FREE EST_BLOCKS------------------------------ ---------- ----------- ---------- ---------- ----------MYTAB1                              10000         267        401         10        391MYTAB2                              10000         267        401         10        391MYTAB3                              10000         267        401         10        391

This shows us that blocks get reused. i..e. before reclaiming space, be sure you're not just wasting time. Reclaim space when you delete data and don't expect to add new data.

DELETE FROM mytab1 WHERE MOD(id, 2) = 0;

DELETE FROM mytab2 WHERE id < 5001;

DELETE FROM mytab3 WHERE id > 5000;

COMMIT;

EXEC DBMS_STATS.gather_table_stats(NULL,'MYTAB1');

EXEC DBMS_STATS.gather_table_stats(NULL,'MYTAB2');

EXEC DBMS_STATS.gather_table_stats(NULL,'MYTAB3');

So, let's delete 50% again

DROP TABLE MYTAB1;

DROP TABLE MYTAB2;

DROP TABLE MYTAB3;

Cleanup

SELECT COUNT(1) 

FROM( SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)

 FROM mytab1

 GROUP BY DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))

SELECT SUM(BLOCKS) FROM user_segments WHERE segment_name = 'MYTAB1'


SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) AS Block, COUNT(1) AS ROWCNT

 FROM mytab1

 GROUP BY DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)

SELECT table_name, num_rows, avg_row_len, blocks, pct_free,

ROUND(num_rows*avg_row_len/8192*100/(100-pct_free)*1.08) est_blocks

  FROM user_tables;


SELECT table_name, num_rows, avg_row_len, blocks

  FROM user_tables;

Bibliography & References

https://dataedo.com/blog/useful-oracle-data-dictionary-queries-every-dba-should-have https://oracle-base.com/articles/9i/dbms_metadatahttps://svenweller.wordpress.com/2020/07/02/7-secret-oracle-db-features-you-might-not-know/(1) https://stackoverflow.com/questions/64756876/how-to-find-free-space-in-oracle-table (Connor McDonald's answer)
CREATE TABLEhttps://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_7002.htm (11.2)https://stackoverflow.com/questions/1008248/how-do-i-use-create-or-replacehttps://oracle-base.com/articles/11g/segment-creation-on-demand-11gr2
DROP TABLEhttps://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9003.htmhttps://dbtut.com/index.php/2018/12/11/how-to-recover-dropped-tables-with-oracle-recyclebin/
TRUNCATEhttps://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10007.htmhttps://docs.oracle.com/html/E25494_01/general003.htm 
MODIFYhttps://www.oracletutorial.com/oracle-basics/oracle-alter-table-modify-column/ 
ROW MOVEMENThttps://stackoverflow.com/questions/19430145/what-is-the-impact-of-enabling-disabling-row-movement-in-oracle-10g
SHRINK SPACEhttps://oracle-base.com/articles/misc/alter-table-shrink-space-online https://smarttechways.com/2019/11/08/check-progress-and-status-of-alter-shrink-space-command/https://asktom.oracle.com/pls/apex/f%3Fp%3D100:11:::::P11_QUESTION_ID:77579471985961https://www.dbi-services.com/blog/how-much-free-space-can-be-reclaimed-from-a-segment/
DBMS_SPACEhttps://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_space.htmhttps://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SPACE.html
DBMS_SPACE_ADMINhttps://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SPACE_ADMIN.html
Partitioninghttps://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/partition-concepts.htmlhttps://www.databasejournal.com/oracle/partition-pitfalls-in-oracle/https://stackoverflow.com/questions/41014959/oracle-table-and-index-partitioning-risk-and-disadvantageshttps://docs.oracle.com/cd/B10501_01/server.920/a96524/c12parti.htm
https://support.oracle.comBest Practices Documents to Avoid or Minimize Performance Issues on Partitioned Tables and Consolidated Known Issues (Doc ID 2635859.1)
Blocks/Extentshttps://asktom.oracle.com/ords/f?p=100:11:::::P11_QUESTION_ID:9531625700346446827 (blocks column in dba_tables)(2) https://asktom.oracle.com/ords/f?p=100:11:0::::P11_QUESTION_ID:492636200346818072 (HWM) (see answer at "April 05, 2010 - 9:45 am UTC" re: EMPTY_BLOCKS)https://asktom.oracle.com/ords/f?p=100:11:0::::P11_QUESTION_ID:5350053031470#1752788300346387205 (SHOW_SPACE)https://forums.oracle.com/ords/apexds/post/why-the-total-number-of-blocks-is-a-little-different-from-u-5274https://forums.oracle.com/ords/apexds/post/how-much-a-data-block-is-full-7219https://forums.oracle.com/ords/apexds/post/extent-block-id-and-parallel-processing-5780http://www.juliandyke.com/Diagnostics/Dumps/DatabaseBlocks.phphttp://www.juliandyke.com/Internals/BlockTypes.phphttps://www.oracledistilled.com/oracle-database/internals/formatted-data-block-dumps/https://iamdbablog.wordpress.com/2018/06/23/de-mystifying-the-oracle-data-block/https://renenyffenegger.ch/notes/development/databases/Oracle/SQL/statement/nouns/system/alter/dump/datafile/https://www.morganslibrary.org/reference/dump_ora.htmlhttps://www.peasland.net/2011/08/01/dumping-data-blocks/https://stackoverflow.com/questions/64284627/how-to-determine-the-empty-logical-data-block-for-a-tablehttps://stackoverflow.com/questions/26076377/segments-extents-and-data-blocks-in-the-oracle-database-architecture-how-tohttps://stackoverflow.com/questions/28884613/find-block-number-of-a-table-in-oracle-databasehttps://dba.stackexchange.com/questions/107465/oracle-table-and-number-of-blocks#107467