Thanks Tom!

Hi Tom,

Even though the limit for a particular table is block and
row size dependent, is there still a theoretical maximum
(e.g. based on the width of the portion of the rowid devoted
to encoding the row number)?

This is of particular interest when creating rowids. You
use, in other examples, dbms_rowid.rowid_create to create
rowid ranges. There is typically a query snippet like this:


select dbms_rowid.rowid_create
( 1, data_object_id, lo_fno, lo_block, 0 ) min_rid,
dbms_rowid.rowid_create
( 1, data_object_id, hi_fno, hi_block, 10000 ) max_rid


The 10000 in the last line implies that you assume there are
never more than 10000 rows in a block. Experimenting on my
Oracle 9.2 (terminal release) dev database, I can make this
number 32767 (2^15 - 1), but beyond that I get
ORA-01410: invalid ROWID, suggesting a limit of 32767.

But this might also simply be the block size (32K) and
therefore the limit is the number of bytes in the block,
and is just block size dependent as you said first.

So I guess the question then becomes: is your 10000 a
reflection of a known limit, or is it just unlikely that
there are more than 10000 rows in a block? And what is a
good value to use when creating rowids as an upper bound
for the row number?

I tried to work this out with empirical testing, and found
that on the production databases I support I can quite
happily increase this number to 4294967295 (2^32 - 1) when
calling dbms_rowid.rowid_create without triggering ORA-01410
errors. That makes me think that, even if there is a limit
based on block size, dbms_rowid.rowid_create will take any
overflow for that limit and treat it as a block offset or
something similar. In production that doesn't matter
because the overflowed block offset still produces a valid
rowid. But an alternative is that it means there is some
other limit in the number of rows/block. Can you shed any
light on this?

where dt between to_char( '01-jan-2008','dd-mon-yyyy')
  and to_char( '01-jan-2008 23:59:59', 'dd-mon-yyyyy hh24:mi:ss')

where dt >= to_char('01-jan-2008','dd-mon-yyyy')
  and dt <  to_char('02-jan-2008','dd-mon-yyyy')

Thanks for that!

I had tried using the next highest block number (e.g. query
with LEAD(), or PL/SQL loop that remembers last value), and
this sidesteps the problem of calculating the upper bound
for rowids in an extent very nicely, at the expense of a less
friendly condition like:

...
AND rowid >= this_extent_start_rowid
AND (rowid < next_extent_start_rowid OR next_extent_start_rowid IS NULL)

vs.
AND rowid BETWEEN start_rowid AND end_rowid

In case it's useful to future generations, I note in the 11g
database documentation that the maximum block size appears to
be 32K, as it is in 9i.

http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/create005.htm#i1014092

This would explain the upper limit I saw in my empirical
testing, as > 32767 rows means rows < 1 byte, even in the
maxiumum block size, and thus is impossible. So if creating
rowids for the upper limit I guess 32767 is as close as it
gets to an "upper limit" on row numbers.

Tom...I have touble in generating correct rowid from v$session. Please see below. I have generated rowid for a sessoin existing for DUAL. and when I use the same rowid it to select from dual its throwing invalid rowid error.

I see the problem with ROW_WAIT_BLOCK#. In sessoin details its showing as 0, where the actual rowid has it as 389. Can you help me please?

SQL> select ROW_WAIT_OBJ#, 
  2          ROW_WAIT_FILE#, 
  3          ROW_WAIT_BLOCK#, 
  4          ROW_WAIT_ROW#,
  5          dbms_rowid.rowid_create(1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW
#) "RID"
  6  from v$session a, dba_objects b
  7  where a.ROW_WAIT_OBJ# = b.object_id
  8  and b.object_name = 'DUAL';

ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# RID
------------- -------------- --------------- ------------- ------------------
          195              1               0             0 AAAADDAABAAAAAAAAA

SQL> select * from dual where rowid = 'AAAADDAABAAAAAAAAA';
select * from dual where rowid = 'AAAADDAABAAAAAAAAA'
              *
ERROR at line 1:
ORA-01410: invalid ROWID


SQL> select rowid from dual;

ROWID
------------------
AAAADDAABAAAAGFAAA

SQL> select dbms_rowid.ROWID_RELATIVE_FNO('AAAADDAABAAAAGFAAA')  object_id,
  2   dbms_rowid.ROWID_BLOCK_NUMBER('AAAADDAABAAAAGFAAA') lo_fno, 
  3   dbms_rowid.ROWID_ROW_NUMBER('AAAADDAABAAAAGFAAA') lo_block from dual;

 OBJECT_ID     LO_FNO   LO_BLOCK
---------- ---------- ----------
         1        389          0

Thanks!

Tom,

I also wondered when I noticed that in our production yesterday. We are still trying to figure out what causing a lock on DUAL.

In the mean while, I have again taken created one rowid from v$session which is showing as invalid. Please see below.

SQL> select
  2  'select * from ' || b.owner||'.'||b.object_name || ' where rowid = ''' || dbms_rowid.rowid_crea
te(1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#) || '''' "Query"
  3  from v$session a, dba_objects b
  4   where a.ROW_WAIT_OBJ# = b.object_id
  5   and b.owner <> 'SYS'
  6   and rownum < 2;

Query
--------------------------------------------------------------------------------
select * from EDW.HOUSE_DIM where rowid = 'AAACvLAAIAAAAAAAAA'

SQL> select * from EDW.HOUSE_DIM where rowid = 'AAACvLAAIAAAAAAAAA';
select * from EDW.HOUSE_DIM where rowid = 'AAACvLAAIAAAAAAAAA'
                  *
ERROR at line 1:
ORA-01410: invalid ROWID


SQL> 

Thanks!

Thanks Tom, I got the point. I ignoed your point (BLOCKED) in yesterday's reply. In fact other session is not blocked, it was a shared lock. Actually all I was trying to do was to find a row in a table (by usgin rowid) that is locked by a session.

So, from your words what I have understood is shared locks cann't give you a way to find correct rowid (using createrowid proc). I'm not sure..could you please explain me. And any reason to support why they don't give correct rowid...

Thanks!

I have created this statement to find which tables/records have locks of any type. May be I am wrong at the joins. Please correct me, and make me understand. I am wondering if ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# is v$session doesn't locate a correct rowid.

To answer your question on lock type iwas basing on v$locked_object.lock_type. Please share your suggestions Tom.

select 
 b.sid,
 b.serial#,
 b.process,
 d.ctime "LOCK TIME (sec)",
 c.object_name,
 decode(a.locked_mode, 1, 'NULL', 2, 'ROW SHARED', 3,'ROW EXCLUSIVE', 4, 'SHARED', 5,'S/ROW EXCLUSIVE', 6,'EXCLUSIVE' ) "LOCK MODE",
 b.last_call_et "INACTIVE FOR(sec)",
 decode(d.block, 1, 'Yes', 0, 'No') "BLOCKING",
 dbms_rowid.rowid_create(1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#) "LOCKed  ROWID"
from v$locked_object a,
 v$session b,
 dba_objects c,
 v$lock d,
where b.sid = a.session_id
 and c.object_id = a.object_id
 and d.sid = a.session_id
 and d.id1 = a.object_id

Thanks!

Hi Tom
What does the following statement show?Can you explain what it is used for?

dbms_rowid.rowid_block_number(rowid)

Hello sir,

dbms_rowid.rowid_block_number(rowid) is used to find out where row "lives". is it possible to know where index entry lives? i.e. Is it possible to know that one particular index entry stored in so and so block id?

I am trying to understand Index internals. I read few articles/blogs but still not clear. How index stored in database for e.g. suppose in my table TEST I have a column NO and I am creating index on that column. (let's assume in one index Block it stores 7 entry. I have 8192 block size). Now If I store no. in a sequence 1,2,.. 7 then all nos will fit in first block and there is no branch block. the moment I add one more no it will store in second block and it also create one branch block. that way If I store 35 no in a sequence then it use total 7 leaf block and 1 branch block. but If I store different 35 nos. then it use more leaf block and more branch block too. and here it also possible my index size is bigger than my table size. so how Oracle store index entry internaly? I am thinking 2 possibilities but not sure which one is correct.

1. When first record insert oracle will put it one first block (X). second row will go in same block(X) if it is nearest no. third row will go in same block(X) if it is nearest no. (still there is a space to write another 4 entry) but fourth records is some different no. (lets say) 100 then oracle won't write it in first block(X) but will write it in second block (X1). (I am sure It won't skip 100/7 blocks but just skip that first block). By any chance now if new nos are greater than 100 then it never use first block even though there is space available.
2. first record will go in first block, second record will go in same block even if it is not in sequence (i.e first record is no. 1, second record is no. 100) third record is 101, 102, 103, 104, 105 all will go in first block and now there is no room and No. 2 came then it will make room in first block my transfering 100 no to block 2 and no. 2 will go in block 1.

Is there any one possibility correct? Or Oracle is doing something different internaly?

Regards
Sonu

Thank you sir for the explanation but somehow I am still not clear and so I have few more questions. 

1. We'll fill it up with data in sorted order - so you will store data in sorted order. i.e. if I enter 1, 3, 5, 7, 2, 4, 6 then will it store like 1,2,3,4,5,6,7 (Assuming we have that 7 rows in 1 block).

2. we'll typically do a 60/40 split - so even if 8th record is in a sequence still oracle will keep first 4(60% of 7 rows) record in first block and 3 record move to second block? is there any logic which rows will stay in first block and which will go in next block? 

now answer of your question "how did you arrive at that conclusion?"  and my original assumption no. 1, I did following test in my oracle database. 

21:59:37 SQL> create table test (name varchar2(1000));

Table created.

21:59:42 SQL> create index ind_test on test(name);

Index created.

21:59:57 SQL> ed
Wrote file afiedt.buf

  1  insert into test
  2  select name from
  3  (select substr(rpad('X', 1000, 'X') || level, -1000) name, level l from dual  connect by level<=5 )  a
  4  where --mod(l,10)=0 and
  5* not exists (select 1 from test b where a.name = b.name)
22:00:05 SQL> /

5 rows created.

22:00:06 SQL> analyze index ind_test validate structure;

Index analyzed.

22:00:14 SQL> analyze index ind_test compute STATISTICS;

Index analyzed.

22:00:14 SQL> SELECT * from index_stats where name = 'IND_TEST';

    HEIGHT     BLOCKS NAME                           PARTITION_NAME                    LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_ROWS    BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS   PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ---------- ------------------------------ ------------------------------ ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- --------------- ------------- ----------------- ----------- ---------- ---------- ------------ -------------------- ---------- ------------ -------------- ----------------
         1          8 IND_TEST                                                               5          1        5065       8000          0          0           0          0           0               0             5                 1        8000       5065         64            1                    2          0            0              0                0

Elapsed: 00:00:00.00
22:00:46 SQL> insert into test
22:00:57   2  select name from
22:00:57   3  (select substr(rpad('X', 1000, 'X') || level, -1000) name, level l from dual  connect by level<=60 )  a
22:00:57   4  where mod(l,10)=0 and
22:00:57   5  not exists (select 1 from test b where a.name = b.name)
22:01:00   6  /

6 rows created.

22:01:01 SQL> analyze index ind_test validate structure;

Index analyzed.

22:01:06 SQL> analyze index ind_test compute STATISTICS;

Index analyzed.

22:01:09 SQL> SELECT * from index_stats where name = 'IND_TEST';

    HEIGHT     BLOCKS NAME                           PARTITION_NAME                    LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_ROWS    BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS   PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ---------- ------------------------------ ------------------------------ ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- --------------- ------------- ----------------- ----------- ---------- ---------- ------------ -------------------- ---------- ------------ -------------- ----------------
         2          8 IND_TEST                                                              11          3       11143       8000          2          1        2017       8032           0               0            11                 1       32032      13160         42            1                    3          0            0              1                7

22:01:15 SQL> select * from test;   -- to fit output here I kept just last 5 characters of this table. 

NAME
-----
XXXX1
XXXX2
XXXX3
XXXX4
XXXX5
XXX10
XXX20
XXX30
XXX40
XXX50
XXX60

11 rows selected.

Elapsed: 00:00:00.00
22:01:35 SQL> 

If you see here it took 3 leaf block to store 11 rows which suppose to fit in 2 blocks only. I do not understand why it is taking 3 leaf block??

I did few more insert.. 

22:14:22 SQL> insert into test
22:14:48   2  select name from
22:14:48   3  (select substr(rpad('X', 1000, 'X') || to_char(level), -1000) name, level l from dual  connect by level<=120 )  a
22:14:48   4  where mod(l,10)=0 and
22:14:48   5  not exists (select 1 from test b where a.name = b.name)
22:14:48   6  /

6 rows created.


22:15:52 SQL> insert into test
22:15:52   2  select name from
22:15:52   3  (select substr(rpad('X', 1000, 'X') || to_char(level), -1000) name, level l from dual  connect by level<=200 )  a
22:15:52   4  where mod(l,10)=0 and
22:15:52   5  not exists (select 1 from test b where a.name = b.name)
22:15:52   6  /

8 rows created.

22:15:53 SQL> analyze index ind_test validate structure;

Index analyzed.

22:16:00 SQL> analyze index ind_test compute STATISTICS;

Index analyzed.

22:37:44 SQL> select HEIGHT,  BLOCKS, NAME, LF_ROWS, LF_BLKS, LF_ROWS_LEN, BR_ROWS, BR_BLKS from 
22:38:25   2  index_stats where name = 'IND_TEST';

    HEIGHT     BLOCKS NAME                              LF_ROWS    LF_BLKS LF_ROWS_LEN    BR_ROWS    BR_BLKS
---------- ---------- ------------------------------ ---------- ---------- ----------- ---------- ----------
         2         16 IND_TEST                               25          7       25325          6          1

22:38:30 SQL> select SEGMENT_NAME, SUM(BYTES)/1024 FROM dba_segments
22:38:34   2  where segment_name in ('TEST', 'IND_TEST')
22:38:34   3  group by segment_name
22:38:34   4  ;

SEGMENT_NAME                                                                      SUM(BYTES)/1024
--------------------------------------------------------------------------------- ---------------
IND_TEST                                                                                      128
TEST                                                                                           64

Now here if we see to store 25 index entry it took 7 blocks which suppose to fit in 4 blocks. (25/7=3.5) and as per dba_Segments, index size is bigger than table size. 

I do not know what I am missing here or what I misunderstood  but based of this test I thought possibility no. 1 and to verify that I was looking something which shows me which block contains which index entry? Pl. help me to understand this concept.

Thanks
sonu

Thank you sir for the clarification.. and I am clear about the size related stuff.. can you pl. give me answer of my index's question.

1. We'll fill it up with data in sorted order - so you will store data in sorted order. i.e. if I
enter 1, 3, 5, 7, 2, 4, 6 then will it store like 1,2,3,4,5,6,7 (Assuming we have that 7 rows in 1
block). pl. confirm. (YES/No would be fine)

2. we'll typically do a 60/40 split - so even if 8th record is in a sequence still oracle will keep
first 4(60% of 7 rows) record in first block and 3 record move to second block? is there any logic
which rows will stay in first block and which will go in next block?

I try to find out something related to this but could not find, if you have such link handy then pl. give it here..

thanks again
sonu

Hi Tom,

Into a 10.2.0.4 database I create a cluster and a table as following:

CREATE CLUSTER CU_CLU(ACCOUNT_NO NUMBER(10))PCTUSED 0 PCTFREE 3 SIZE 8K INDEX;

CREATE INDEX CU_CLU_IDX ON CLUSTER CU_CLU;

CREATE TABLE CU(f1 DATE,f2 NUMBER(10),f3 NUMBER(3), ACCOUNT_NO NUMBER(10)) CLUSTER CU_CLU(ACCOUNT_NO);

If my understanding is right, considering the cluster size of 8K I would expect to get at least one block for every distinct account_no. The cluster index will point to this first block. If the records for an account will no fit into one block then Oracle starts to chain the blocks from this "first block".( max chain length 65535 blocks).
I expect to get the chain length for an account using:

select count(distinct dbms_rowid.rowid_block_number(rowid)) from cu where account_no=12

I would also expect no blocks with more than an account in it.(because of 8K size)

Is this right or I miss something ? I ask this because of ORA-02475 which I could not explain.

thank you.
Basil

Hi Tom,

known facts:
- I have reorganized the cluster (truncate cluster CU_CLU drop storage)
- I have inserted the records (no parallel DMLs)- one insert in fact.
- no deletes in this table

I would expect the free blocks smaller than freelist blocks=5 .
The distinct block count for one account brings back 33301 blocks. I have tried to insert a new row and I've got ORA-02475.

I came to the idea of "walking through" the chained blocks for an account by selecting the rows into "natural" order.
Here is the select :

select nr, prev_block
from
(
select nr, lag(block,1) over (order by nr) prev_block,block
from
(
select rownum nr,dbms_rowid.rowid_block_number(rowid) block from cu where account_no=47143978
)
)
where prev_block!=block

I've got indeed 65533 rows(chained blocks) - but there were only 33301 distinct blocks.

3)Any idea why Oracle is not fully using a block before allocating a second and how comes to decision to reuse it later on ?

Here is an example( 4 blocks):
NR ,PREV_BLOCK
====================
27312, 224171
51731, 200423
59732, 213132
87436, 207031
7619237, 207031
8062353, 213132
8278680, 200423
8286682, 224171

4)Is there any option which might be responsible for this(initrans) ?

thank you again.
Basil

Hi Tom,

this cluster is filled with real data. My problem was that I've got the error ORA-02475 for the mentioned account (so a have a chain which reached 65535 chained blocks) but when I have "measured" number of blocks with dbms_rowid package I get only 33301 blocks.
This means for me the rest are empty blocks (impossible as no deletes) or the same block is chained twice.

The output you see is from included statement(with lag) for the first 4 blocks. As you could see with this statement I measure the number of times I skip to a new block - so I come to 65533 blocks.

Another check I've done was to find the number of rows per block and I found a number which was double than the "first skip".
For eg. for the next statement
select rownum nr,dbms_rowid.rowid_block_number(rowid) block from cu where account_no=47143978

I get by row 132 a new block :

NR BLOCK
============
27180 224171
27181 224171
............
27311 224171
27312 124432
27313 124432

but the statement:
select count(*) from cu where account_no=47143978 and dbms_rowid.rowid_block_number(rowid)=224171

returns 262 rows.

My only doubt was if I "walk" the chain right.

thank you for your support.
Basil

set echo on
set linesize 1000
drop table cu;
drop cluster cu_clu;

CREATE CLUSTER CU_CLU(ACCOUNT_NO NUMBER(10))PCTUSED 0 PCTFREE 3 SIZE 8K INDEX;

CREATE INDEX CU_CLU_IDX ON CLUSTER CU_CLU;

CREATE TABLE CU(f1  DATE,f2 NUMBER(10),f3  NUMBER(3), ACCOUNT_NO NUMBER(10) ) CLUSTER CU_CLU(ACCOUNT_NO);

insert into cu select sysdate, 1, 2, object_id from all_objects where object_id > 99;
insert into cu select * from cu;
insert into cu select * from cu;
insert into cu select * from cu;
commit;

declare
    cluster_overload exception;
    pragma exception_init( cluster_overload, -2475 );
begin
    for i in 1 .. 1000000000
    loop
    begin
        insert into cu (f1, f2, f3, account_no ) values (sysdate,mod(i,3),mod(i,3),42);
        commit;
    exception
    when cluster_overload
    then
        dbms_output.put_line( 'cluster key filled at ' || i );
        exit;
    end;
    end loop;
end;
/
select count(distinct dbms_rowid.rowid_block_number(rowid)) from cu where account_no = 42;

ops$tkyte%ORA11GR2> select count(distinct dbms_rowid.rowid_block_number(rowid)) from cu where account_no = 42;

COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))
---------------------------------------------------
                                              65534


ops$tkyte%ORA11GR2> select count(*) from cu where account_no = 42;

  COUNT(*)
----------
  26410202

Hi Tom,

I have managed to reproduce the problem with generated data. Looks not very elegant but because I don't know why this happen I've tried to keep it as close to initial configuration as possibe. The number of "double chained" blocks is also not as big as into initial case but still exist.(there are 8160 double blocks)

v$version - BANNER
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit(HP-UX)
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production


CREATE CLUSTER CU_CLU(ACCOUNT_NO NUMBER(10))
PCTUSED 0
PCTFREE 3
INITRANS 3
SIZE 8K
INDEX
NOROWDEPENDENCIES
NOCACHE
NOPARALLEL;



CREATE INDEX CU_CLU_IDX ON CLUSTER CU_CLU
PCTFREE 50
INITRANS 5
MAXTRANS 255
NOPARALLEL;


CREATE TABLE CU
(
THE_DATA_PARTITION_KEY DATE,
KEY_ID NUMBER(10),
KEY_ID2 NUMBER(3),
ACT_ID_SERV NUMBER(3),
GRIFT_ROW_NUM NUMBER(3),
ACCOUNT_NO NUMBER(10),
ACTSCR_NO NUMBER(10),
ACTSCR_NO_RESETS NUMBER(6),
MESG_ID_USG NUMBER(10),
TRANS_DT DATE,
NAME_DT DATE
)
CLUSTER CU_CLU(ACCOUNT_NO)
NOCOMPRESS ;

insert into cu
select trunc(sysdate),242906497,3,2,1,47143978 as account_no,48613975,0,32478,sysdate,sysdate
from dual connect by rownum<=8582160
union all
select trunc(sysdate),242906497,3,2,1,47143948 as account_no,48613975,0,32478,sysdate,sysdate
from dual connect by rownum<=300000
order by account_no;


commit;

-- this should already raise the error ORA-02475
insert into arbor.cu
select trunc(sysdate),242906497,3,2,1,47143978 as account_no,48613975,0,32478,sysdate,sysdate
from dual connect by rownum<=10000

--next statement return 57353
select count(distinct dbms_rowid.rowid_block_number(rowid)) from arbor.cu a where account_no=47143978


--next statement return 65512 - very close to my 65534 blocks
select count(*) from
(
select nr, prev_block
from
(
select nr, lag(block,1) over (order by nr) prev_block,block
from
(
select rownum nr,dbms_rowid.rowid_block_number(rowid) block from arbor.cu where account_no=47143978
)
)
where prev_block!=block
)

Looking forward for your comments.

Hi Tom,
I am trying to understand the relationship between tablespace,datafile,segment,extent well.
As we know:
1> 1 tablespace can have many datafiles
i can see this select * from dba_data_files where tablespace='USER'
2> 1 segment can only exists inside 1 single tablespace
can understand this by the syntax below
create table ... tablespace USER;
create index ... tablespace INDTS;
3> so 1 segment can spread into more than one datafiles
i want to prove it by query some data dictionary, but can not figure out how
there is no fileno or something clue inside the dba/user_segments
please help?
4> 1 segment is composed of many extents, can understand this
5> 1 extent can only inside one datafile
again, there is no clue inside the dba/user_extents, please help?
BTW, i know dbms_rowid can find the relative FILENO for a row, then we can know above info.
I am just curious whether there is any dictionary can prove above concept? Or it is just desired so and only way is using dbms_rowid?

Tom said in the beginning of the article:

Followup March 30, 2008 - 8am UTC:
... or is it just unlikely that
there are more than 10000 rows in a block? ....

it is that.

But after little research and reading documentation I found that it is dangerous to use just big number.
Documentation says:

After simple visual test I found that:
row number is 3 last symbols in rowid
So max rowid could be '///' or 64*64*64-1, but I found that biggest row number for me was 'H//' or 32767 is decimal.
Range from 'I//' to '///' (32768 to 262143) generates invalid rowid error.
And now most interesting starting from 262144 there is overflow of 64*64*64 and Oracle just ignores it and starts to cycle row numbers. So row 262144 equals 0, 262145 = 1, ...
So if you just write some big number and did not get error, do not enjoy too much.
Maybe it depends on block sizes or something else, but just wanted to show principle.
In my case 'H//' or 64*64*8-1=32767 was the biggest possible row number in the block.

Rowids are stored as base 64 values that can contain the characters A-Z, a-z, 0-9, and the plus sign (+) and forward slash (/)