Block and row number
rowid_block_number and rowid_row_number show a record's block number and row number within this block:
create table tq84_rowid (
id number,
col_1 varchar2(20)
);
insert into tq84_rowid values(1, 'foo');
insert into tq84_rowid values(2, 'bar');
insert into tq84_rowid values(3, 'baz');
select
rowid,
dbms_rowid.rowid_block_number (rowid) block_no,
dbms_rowid.rowid_row_number (rowid) row_no,
dbms_rowid.rowid_relative_fno (rowid) rel_fno,
case dbms_rowid.rowid_type (rowid)
when 0 then 'restricted'
else 'extended' end type_,
r.*
from
tq84_rowid r;
drop table tq84_rowid;
Record's partition
create table tq84_part_interval_list (
id number,
txt varchar2(10),
cat varchar2( 3),
dt date
)
partition by range (dt) interval (numtoyminterval(1, 'month'))
subpartition by list (cat)
subpartition template (
subpartition foo values ('foo'),
subpartition bar values ('bar'),
subpartition baz values ('baz')
)
(
partition values less than ( date '2017-01-01')
);
insert into tq84_part_interval_list values (1, 'abc', 'foo', date '2017-03-01');
insert into tq84_part_interval_list values (2, 'def', 'bar', date '2017-03-02');
insert into tq84_part_interval_list values (3, 'ghi', 'baz', date '2017-03-03');
insert into tq84_part_interval_list values (4, 'jkl', 'foo', date '2017-12-01');
insert into tq84_part_interval_list values (5, 'mno', 'bar', date '2017-12-02');
insert into tq84_part_interval_list values (6, 'pqr', 'baz', date '2017-12-03');
select
o.subobject_name,
r.*
from
tq84_part_interval_list r join
user_objects o on dbms_rowid.rowid_object(r.rowid) =
o.data_object_id;
drop table tq84_part_interval_list purge;