Oracle TABLE

Databases

Oracle Database

Schema

Oracle Tablespaces/Datafiles

Oracle Materialized Views

Oracle External Tables

Oracle INDEX

Oracle LOBs

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.

Oracle Recycle Bin

TRUNCATE

A TRUNCATE statement does not generate any Undo information and it Commits immediately.
It is a DDL statement and cannot be rolled back.
Constraints and Triggers are retained (but Triggers do not fire during TRUNCATE)
A single Audit record is generated for the TRUNCATE statement being issued (if auditing is turned on)
When a table or cluster is truncated, all associated indexes are also truncated.

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.

WITH

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.

Oracle ASSM

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.

Oracle ASSM

Oracle INDEX

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';

Oracle ASSM

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....

All values are fixed length rather than variable length

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....

The first option should leave all the blocks 50% utilised.
The second option should leave empty blocks at the start of the table.
The third option should leave empty blocks at the end of the table.

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

Oracle TABLE

CREATE

CTAS

SAMPLE

CREATE_TABLE_COST

MODIFY

DROP

TRUNCATE

Row Movement

Check

Enable

Table Size

Space Usage

DBMS_SPACE.SPACE_USAGE

DBMS_SPACE.UNUSED_SPACE

Blocks

Not-Empty Blocks

All Blocks

Generate DDL

Shrink

Move

Estimating Benefit of Shrink or Move

Test Data

Table Blocks Deep Dive

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

Bibliography & References

* UNDER CONSTRUCTION BELOW THIS POINT * TODO