Querying across a Database Link

 In this demonstration, assume that I have a local table called "TARGET_DATA" with a list of Countries and Products.  A new company is being acquired and that organisations's product list has been loaded into a table called TMP_DATA in a different database PDBTMP.

So, I run a MINUS query to find the local products that are not in the new company's product list -- but this query is run across a Database Link.

Here is how I create the Database Link, run the MINUS query and then obtain a tkprof of the trace file generated for the query.

SQL> create database link pdbtmp_data
  2  connect to data_load identified by data_load using 'pdbtmp';

Database link created.

SQL>
SQL> alter session set tracefile_identifier='MY_DBLINK_QRY';

Session altered.

SQL> alter session set statistics_level='ALL';

Session altered.

SQL>
SQL> exec dbms_session.session_trace_enable(waits=>TRUE,binds=>FALSE);

PL/SQL procedure successfully completed.

SQL>
SQL> select * from target_data
  2  minus
  3  select * from tmp_data@pdbtmp_data
  4  /

...
...

600 rows selected.

SQL> select 'x' from dual;

'
-
x

SQL> show array
arraysize 15
SQL>
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
oracle19c>


-- tkprof of the trace file

select * from target_data
minus
select * from tmp_data@pdbtmp_data

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.24          0          0          1           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       41      0.17       0.30        453        455          0         600
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       43      0.19       0.55        453        455          1         600

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 106
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
       600        600        600  MINUS  (cr=455 pr=453 pw=0 time=306868 us starts=1)
     71682      71682      71682   SORT UNIQUE (cr=455 pr=453 pw=0 time=142899 us starts=1 cost=872 size=2876679 card=73761)
     73761      73761      73761    TABLE ACCESS FULL TARGET_DATA (cr=455 pr=453 pw=0 time=80877 us starts=1 cost=130 size=2876679 card=73761)
     71143      71143      71143   SORT UNIQUE (cr=0 pr=0 pw=0 time=148559 us starts=1 cost=831 size=2927240 card=73181)
     73181      73181      73181    REMOTE  TMP_DATA (cr=0 pr=0 pw=0 time=96385 us starts=1 cost=80 size=2927240 card=73181)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  PGA memory operation                          135        0.00          0.00
  SQL*Net message to client                      41        0.00          0.00
  Disk file operations I/O                        1        0.00          0.00
  db file sequential read                         1        0.00          0.00
  db file scattered read                         21        0.00          0.04
  SQL*Net message to dblink                       4        0.00          0.00
  SQL*Net message from dblink                     4        0.00          0.00
  SQL*Net more data from dblink                 352        0.01          0.05
  SQL*Net message from client                    41        8.84          8.89
********************************************************************************
SQL ID: 04vfkrajpkrnj Plan Hash: 1388734953

select 'x'
from
 dual


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          0          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 106
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  FAST DUAL  (cr=0 pr=0 pw=0 time=1 us starts=1 cost=2 size=0 card=1)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2       11.31         11.31



********************************************************************************


OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.02       0.24          0          0          1           0
Execute      3      0.01       0.01          0        676          0           1
Fetch       43      0.17       0.30        453        455          0         601
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       48      0.20       0.56        453       1131          1         602

Misses in library cache during parse: 2
Misses in library cache during execute: 1

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  PGA memory operation                          137        0.00          0.00
  SQL*Net message to client                      44        0.00          0.00
  SQL*Net message from client                    44       16.85         37.06
  db file sequential read                         1        0.00          0.00
  single-task message                             1        0.02          0.02
  SQL*Net message from dblink                    15        0.05          0.15
  SQL*Net message to dblink                      14        0.00          0.00
  Disk file operations I/O                        1        0.00          0.00
  db file scattered read                         21        0.00          0.04
  SQL*Net more data from dblink                 352        0.01          0.05
  
  

The query returns 600 rows but with an ARRAYSIZE of 15 for the sqlplus session, it results in 40 round trips (SQL*Net message to/from client)  (plus 1 additional for the parse) resulting a total of 41 SQL*Net messages.

However, the 73,181 rows from the remote database (PDBTMP) appear as 4 DBLink messages (SQL*Net message to/from dblink) and 352 "more data from dblink" waits.  So, there were many more network packets from PDBTMP to my local database.

But the total count of dblink messages is 15 (from dblink) and 14 (to dblink) -- an excess of about 11 over the initial 4.  Which means that there were some additional "chatty" interaction between my local database session and the one created in PDBTMP.

I also trace the session that is created by the Database Link operation in PDBTMP.  The tkprof for the trace file of that session is below :

SQL ID: bv2zwsyu4hq3k Plan Hash: 0

SELECT /*+ FULL(P) +*/ *
FROM
 "TMP_DATA" P


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.04       0.05          4        101          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.04       0.05          4        101          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 109

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.14          0.14
********************************************************************************
SQL ID: 33v4pbtw6zjxt Plan Hash: 3633341221

SELECT "COUNTRY","PRODUCT_NAME"
FROM
 "TMP_DATA" "TMP_DATA"


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        4      0.05       0.08        450        455          0       73181
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.05       0.08        450        455          0       73181

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 109
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
     73181      73181      73181  TABLE ACCESS FULL TMP_DATA (cr=455 pr=450 pw=0 time=66315 us starts=1 cost=124 size=2927240 card=73181)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       4        0.00          0.00
  db file sequential read                         1        0.00          0.00
  db file scattered read                         19        0.00          0.03
  SQL*Net message from client                     4       20.26         20.31
  SQL*Net more data to client                   352        0.00          0.00



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ----------- ---------- ----------  ----------
Parse        2      0.04       0.05          4        101          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        4      0.05       0.08        450        455          0       73181
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        7      0.09       0.13        454        556          0       73181

Misses in library cache during parse: 2

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  PGA memory operation                            1        0.00          0.00
  log file sync                                   1        0.00          0.00
  SQL*Net message to client                      11        0.00          0.00
  SQL*Net message from client                    11       20.26         20.47
  db file sequential read                         1        0.00          0.00
  db file scattered read                         19        0.00          0.03
  SQL*Net more data to client                   352        0.00          0.00

Note how the "select * from tmp_data" portion passed to PDBTMP is rewritten as 

SELECT /*+ FULL(P) +*/ *
FROM
 "TMP_DATA" P
- and then as 
SELECT "COUNTRY","PRODUCT_NAME"
FROM
 "TMP_DATA" "TMP_DATA"

The remote database also shows that it sent 4 messages to/from "client" (in this case the client was my database session in my local database, not the sqlplus program) and 352 "more data" packets.  These numbers match what we see in the local trace file.

However, this also shows an additional 11 messages to/from "client".

There are some additional lines in the raw trace file which indicate what these overheads are.

One of them is for the logon trigger in PDBTMP that I have created to ensure that the DBLink connection generates a trace file.

The others are for recursive parse and other calls in PDBTMP (where parse success is sent to my "client" session in my local database).  Calls like :

select decode(upper(failover_method), NULL, 0 , 'BASIC', 1, 'PRECONNECT', 2 , 'PREPARSE', 4 , 0), decode(upper(failover_type), NULL, 1,  'NONE', 1, 'SESSION', 2, 'SELECT',  4, 'TRANSACTION',  8, 'AUTO' , 32,  1 ), failover_retries, failover_delay, flags, nvl(replay_initiation_timeout, 900), decode(upper(session_state_consistency), 'STATIC', 16, 0), failover_restore from sys.service$ where name = :1

insert into link_logons$(logon_time , source_id)  VALUES ( SYSTIMESTAMP AT TIME ZONE 'UTC', :srcid )

XCTEND rlbk=0, rd_only=0, tim=....

XCTEND rlbk=1, rd_only=1, tim=...

XCTEND rlbk=0, rd_only=1, tim=...

So, a Database Link query has overheads. Also, when running a query (just a SELECT statement) requires the two databases to synchronise SCN.  Whichever is the database with the lower SCN has to increment its SCN to the other database's (higher) SCN  

A distributed query is effectively a *transaction* which requires implicit COMMIT/ROLLBACK. To clarify : Oracle automatically re-synchronizes the SCN -- not that your distributed transaction is automatically commit.

(also notice the "insert into link_logon$"  which seems to be an audit implementation in the remote database PDBTMP).

Bonus : This is the ON LOGON TRIGGER in PDTMP which causes it to write to a Trace File :

SQL> select dbms_metadata.get_ddl('TRIGGER','DATA_LOAD_LOGON_TRIG') from dual;

DBMS_METADATA.GET_DDL('TRIGGER','DATA_LOAD_LOGON_TRIG')
--------------------------------------------------------------------------------

  CREATE OR REPLACE NONEDITIONABLE TRIGGER "SYS"."DATA_LOAD_LOGON_TRIG"
after logon on database
 WHEN (user = 'DATA_LOAD') begin
   execute immediate 'alter session set tracefile_identifier=''DATA_LOAD''';
   execute immediate 'alter session set statistics_level=''ALL''';
   dbms_session.session_trace_enable(waits=>TRUE,binds=>FALSE);
end;
ALTER TRIGGER "SYS"."DATA_LOAD_LOGON_TRIG" ENABLE

Such a trigger is useful when you want to trace every logon by a particular user but don't know when the logon will occur.

Direct Path Inserts and Locks

 In my previous post I had demonstrated how a Direct Path Insert in one session blocks another Direct Path Insert from another session, except when the two sessions explicitly name separate target Partitions.

Here, I will lock at how Oracle creates Locks for such operations.  In the output below, the SQLPrompt is set to either 'Sesn_1' or 'Sesn_2' or 'SYSTEM' (the DBA session) to indicate which session is executing the SQL statement.

First, running the Direct Path Insert without specifying the target Partition name.

SYSTEM>l
  1  select object_name, subobject_name, object_type, object_id, data_object_id
  2  from dba_objects
  3  where owner = 'HEMANT'
  4  and object_name in ('MY_PART_TABLE')
  5  and object_type in ('TABLE','TABLE PARTITION')
  6* order by 1, 2 nulls first, 4
SYSTEM>/

OBJECT_NAME      SUBOBJECT_NAME   OBJECT_TYPE              OBJECT_ID DATA_OBJECT_ID
---------------- ---------------- ----------------------- ---------- --------------
MY_PART_TABLE                     TABLE                        81817
MY_PART_TABLE    P_100            TABLE PARTITION              81818          81827
MY_PART_TABLE    P_200            TABLE PARTITION              81819          81828
MY_PART_TABLE    P_300            TABLE PARTITION              81820          81820

SYSTEM>
SYSTEM>truncate table hemant.my_part_table;

Table truncated.

SYSTEM>
SYSTEM>select count(*) from v$locked_object;

  COUNT(*)
----------
         0

SYSTEM>



Sesn_1>select distinct sid from v$mystat;

       SID
----------
       138

Sesn_1>
Sesn_1>l
  1  insert /*+ APPEND */ into my_part_table
  2  select rownum, dbms_random.string('X',12)
  3  from dual
  4* connect by rownum < 51
Sesn_1>/

50 rows created.

Sesn_1>



Sesn_2>select distinct sid from v$mystat;

       SID
----------
       384

Sesn_2>
Sesn_2>l
  1  insert /*+ APPEND */ into my_part_table
  2  select rownum+101, dbms_random.string('X',12)
  3  from dual
  4* connect by rownum < 51
Sesn_2>/
---------- Sesn_2 is now in a Wait



SYSTEM>select event from v$session where sid=384;

EVENT
----------------------------------------------------------------
enq: TM - contention

SYSTEM>
SYSTEM>select session_id, object_id, locked_mode
  2  from v$locked_object
  3  /

SESSION_ID  OBJECT_ID LOCKED_MODE
---------- ---------- -----------
       138      81817           6
       384      81817           0

SYSTEM>
---------- Object ID 81817 is the Table itself, locked by Session 138 -- Sesn_1
SYSTEM>select sid, type, id1, id2, lmode, request, block
  2  from v$lock
  3  where sid in (138,384)
  4  order by 1
  5  /

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       138 TM      81817          0          6          0          1
       138 AE        134 4057974068          4          0          0
       138 TX     393246       7880          6          0          0
       384 AE        134 4057974068          4          0          0
       384 TM      81817          0          0          6          0

SYSTEM>
---------- Session 138 has TM Lock Mode6 on Object 81817 -- the Table itself
---------- Session 384 (Sesn_2, Waiting) is requesting a TM Lock Mode6

So, it is clear that Session 1 (SID 138) had a TM Lock (Mode->6) on the Table, blocking Session 2 (384) (which is requesting the same Mode->6 lock) when the Direct Path Insert specified the Table name alone.

The TX lock by SID 138  is the Transaction Row Exclusive Lock.  What is blocking Session 2 (SID 384) is that it is requesting a Mode->6 lock on the same Object (the Table) as already held by Session 1 (SID 138)

Then, when I issue a Rollback (or Commit) from Sesn_1, the Insert by Sesn_2 goes through.

Before this next test, I rollback both the Inserts

Next, with the target Partition named

Sesn_1>l
  1  insert /*+ APPEND */ into my_part_table partition (p_100)
  2  select rownum, dbms_random.string('X',12)
  3  from dual
  4* connect by rownum < 51
Sesn_1>/

50 rows created.

Sesn_1>



Sesn_2>l
  1  insert /*+ APPEND */ into my_part_table partition (p_200)
  2  select rownum+101, dbms_random.string('X',12)
  3  from dual
  4* connect by rownum < 51
Sesn_2>/

50 rows created.

Sesn_2>



SYSTEM>select event from v$session where sid=384;

EVENT
----------------------------------------------------------------
SQL*Net message from client

SYSTEM>
SYSTEM>select session_id, object_id, locked_mode
  2  from v$locked_object
  3  /

SESSION_ID  OBJECT_ID LOCKED_MODE
---------- ---------- -----------
       138      81817           3
       138      81818           6
       384      81817           3
       384      81819           6

SYSTEM>
SYSTEM>l
  1  select sid, type, id1, id2, lmode, request, block
  2  from v$lock
  3  where sid in (138,384)
  4* order by 1,2,3
SYSTEM>/

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       138 AE        134 4057974068          4          0          0
       138 TM      81817          0          3          0          0
       138 TM      81818          0          6          0          0
       138 TX      65540       6296          6          0          0
       384 AE        134 4057974068          4          0          0
       384 TM      81817          0          3          0          0
       384 TM      81819          0          6          0          0
       384 TX     458781       6211          6          0          0

8 rows selected.

SYSTEM>
---------- Both sessions SIDs 138 (Sesn_1) and 384 (Sesn_2) have the Table Object (81817) locked in Mode3, not Mode6
---------- But the Partitions (Objects 81818 and 81819) are locked in Mode6
---------- Neither is SID 138 Blocking any other session nor is SID 384 being blocked

Now we see that the Table (object 81817) is locked in Mode->3 and not Mode->6  by by *both sessions* while the respective Partitions P_100 (object 81818) and P_200 (81819) are locked in Mode->6 without session 384 (Sesn_2) waiting for a block.

Thus, as the Table itself is not locked in Mode->6, Sesn_2 is allowed a Direct Path Insert into another Partition -- only so as long as it explicitly names the Target Partition. (If Sesn_2 attempts to do a Direct Path Insert without naming a Target Partition, it will, again begin waiting on Sesn_1 without the REQUEST being evident in v$lock)

Mode 6 is Lock Table in Exclusive Mode in the first case.

In the second case, the Table is locked in Mode 3 which is Row-Exclusive, not Table level.

The "TX" is a Transaction Enqueue 

The "AE" Lock type is "Edition Enqueue" --- which we are not concerned with in this test, as it relates to Editioning (which I am not currently using) and will appear even if Editioning is not being used.

Direct Path Insert into a Partitioned Table

 Normally a Direct Path Insert that is not committed blocks other concurrent Direct Path Inserts.  This behaviour also extends to Partitioned Tables.

UPDATE : Correction to 3 lines explaining how the two sessions see rows after commits.

Thus :

--------- From Session 1 : inserting only into the first Partition p_100
SQL> l
  1  create table my_part_table (id_col number, data_col varchar2(25))
  2  partition by range (id_col)
  3  (partition p_100 values less than (101),
  4   partition p_200 values less than (201),
  5   partition p_300 values less than (301)
  6* )
SQL>
SQL> /

Table created.

SQL>
SQL> l
  1  insert /*+ APPEND */ into my_part_table
  2  select rownum, dbms_random.string('X',12)
  3  from dual
  4* connect by rownum < 51
SQL> /

50 rows created.

SQL>

---------- From Session 2 :  where we know that the rows will actually be inserted into a different Partition p_200
SQL> l
  1  insert /*+ APPEND */ into my_part_table
  2  select rownum+101, dbms_random.string('X',12)
  3  from dual
  4* connect by rownum < 51
SQL> /

-- Yet, Session 2 is blocked and has to wait untill Session 1 does a COMMIT or ROLLBACK

Even though the 2 sessions will be inserting into separate Partitions (i.e. separate Segments). the first session blocks  the other Direct Path Insert.

However, this blocking can be avoided by explicitly naming the target Partition

Thus :

--------- From Session 1 : inserting only into the first Partition p_100 explicitly named 
SQL> l
  1  insert /*+ APPEND */ into my_part_table partition (p_100)
  2  select rownum, dbms_random.string('X',12)
  3  from dual
  4* connect by rownum < 51
SQL> /

50 rows created.

SQL>
---------- From Session 2 :  inserting into the second Parition p_200 explicitlly named
SQL> l
  1  insert /*+ APPEND */ into my_part_table partition (p_200)
  2  select rownum+101, dbms_random.string('X',12)
  3  from dual
  4* connect by rownum < 51
SQL> /

50 rows created.

SQL>
-------- Of course, both sessions can't requery until they COMMIT or ROLLBACK
--- note : Session 2 can query and see it's own rows after it does a COMMIT
---        Similarly, Session 1 can query and see it's own rows after it does a COMMIT
---        And they can see all the rows after both do a COMMIT
------------------------- the above 3 lines are correction to this blog post 
SQL> select count(*) from my_part_table;
select count(*) from my_part_table
                     *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


SQL> 
SQL> commit; -- issued by both sessions so as to be able to see their own rows as well

Commit complete.

SQL>
SQL> select count(*) from my_part_table;

  COUNT(*)
----------
       100

SQL>
SQL> select count(*) from my_part_table partition (p_100);

  COUNT(*)
----------
        50

SQL>  select count(*) from my_part_table partition (p_200);

  COUNT(*)
----------
        50

SQL>

Thus, although there is general advice not to explicitly name a target Partition, I find this method useful if I have multiple concurrent Direct Path Inserts.

The format of the ROWID

 A ROWID is a method of identifying the physical location of a row in an Oracle Database.  An Index on a Table captures the ROWIDs for the rows holding the index key values and these entries in the Index are how an Index lookup redirects a query to the row (i.e. physical location) in the table.

A ROWID (called an Extended ROWID) consists of 4 components :

    -    DataObject Number

    -    DataFile Number Relative to the Tablespace

    -    DataBlock Number (within the DataFile)

    -    RowNumber within the DataBlock

A Partitioned Table actually consists of multiple segments.  Each segment has a different DataObject Number.

Here is a quick demo of the difference between a normal (Non-Partitioned) Table and a Partitioned Table :

The Normal Table :

SQL> create table NONPARTITIONED (id_col number, data_col varchar2(1000)) pctfree 99 tablespace HEMANT_DATA;

Table created.

SQL>
SQL> insert into NONPARTITIONED
  2  values (1, dbms_random.string('X',999));

1 row created.

SQL> insert into NONPARTITIONED
  2  values (2,'This is the second row');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select rowid, id_col, substr(data_col,1,24)
  2  from NONPARTITIONED
  3  order by id_col
  4  /

ROWID                  ID_COL SUBSTR(DATA_COL,1,24)
------------------ ---------- ------------------------------------------------------------------------------------------------
AAAT70AAgAAAACTAAA          1 CBXBRIP5ZNQ9VPZNC4HHVJJH
AAAT70AAgAAAACXAAA          2 This is the second row

SQL>
SQL> l
    1  select id_col,
    2  dbms_rowid.rowid_object(rowid) ObjectNumber,
    3  dbms_rowid.rowid_relative_fno(rowid) RelativeFileNumber,
    4  dbms_rowid.rowid_block_number(rowid) BlockNumber
    5  from NONPARTITIONED
    6* order by id_col
SQL> /

    ID_COL OBJECTNUMBER RELATIVEFILENUMBER BLOCKNUMBER
---------- ------------ ------------------ -----------
         1        81652                 32         147
         2        81652                 32         151

SQL>

Because I created the Table with PCTFREE 99 and inserted a long string in the first row, the second row was created in a different block.  Both Blocks are in the same Relative File Number (32) and belong to the same Object (ObjectNumber 81652).  Is this really the Object ID ?

The Partitioned Table :

SQL> l
  1  create table PARTITIONED (id_col number, data_col varchar2(1000))
  2  partition by range (id_col)
  3  (
  4  partition P_1 values less than (2) tablespace HEMANT_DATA,
  5  partition P_2 values less than (3) tablespace HEMANT_DATA,
  6  partition P_3 values less than (4) tablespace HEMANT_DATA,
  7  partition P_MAX values less than (MAXVALUE) tablespace HEMANT_DATA
  8   )
  9* tablespace HEMANT_DATA
SQL> /

Table created.

SQL>
SQL> insert into PARTITIONED
  2  values (1, dbms_random.string('X',999));

1 row created.

SQL> insert into PARTITIONED
  2  values (2,'This is the second row');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select rowid, id_col, substr(data_col,1,24)
  2  from PARTITIONED
  3  order by id_col
  4  /

ROWID                  ID_COL SUBSTR(DATA_COL,1,24)
------------------ ---------- ------------------------------------------------------------------------------------------------
AAAT77AAfAAAAJ3AAA          1 RFU3DNMCD6GXL2ZNV9DDGBG2
AAAT78AAfAAAAZ3AAA          2 This is the second row

SQL>
SQL> l
  1  select id_col,
  2  dbms_rowid.rowid_object(rowid) ObjectNumber,
  3  dbms_rowid.rowid_relative_fno(rowid) RelativeFileNumber,
  4  dbms_rowid.rowid_block_number(rowid) BlockNumber
  5  from PARTITIONED
  6* order by id_col
SQL> /

    ID_COL OBJECTNUMBER RELATIVEFILENUMBER BLOCKNUMBER
---------- ------------ ------------------ -----------
         1        81659                 31         631
         2        81660                 31        1655

SQL>

In this case, the two rows are in different Blocks not because of the PCTFREE (which has defaulted to 10) but because they are in different Segments -- as you can see from the ObjectNumbers being different for the two rows.

(You might have also noticed that these were created in a separate datafile, FILENUMBER 31 instead of 32 {as was for the first table}, but that is because Oracle tries to allocate new segments across different datafiles)

In the ROWID format the ObjectNumber is actually the *Data Object Number* that identifies the  Segment, not the Object Number of the Table.

Thus, to verify the Segments of the two tables, I can query and check :

SQL> l
  1  select object_name, subobject_name, object_type, object_id, data_object_id
  2  from user_objects
  3  where object_name in ('NONPARTITIONED','PARTITIONED')
  4  and object_type in ('TABLE','TABLE PARTITION')
  5* order by 1, 2 nulls first, 4
SQL> /

OBJECT_NAME      SUBOBJECT_NAME   OBJECT_TYPE              OBJECT_ID DATA_OBJECT_ID
---------------- ---------------- ----------------------- ---------- --------------
NONPARTITIONED                    TABLE                        81652          81652
PARTITIONED                       TABLE                        81658
PARTITIONED      P_1              TABLE PARTITION              81659          81659
PARTITIONED      P_2              TABLE PARTITION              81660          81660
PARTITIONED      P_3              TABLE PARTITION              81661          81661
PARTITIONED      P_MAX            TABLE PARTITION              81662          81662

6 rows selected.

SQL>

Thus, for the NONPARTITIONED Table, the Object_ID and Data_Object_ID and that returned by DBMS_ROWID are all the same -- 81652.  

But the logical entry for the PARTITIONED Table has an Object_ID of 81658 but, without any segment and, therefore, without a Data_Object_ID.

The rows in this Partitioned Table are actually created in the two different Partition Segments with the corresponding Data_Object_ID  (81659 and 81660).

We know that when we rebuild a Table, the ROWID changes.  But this is actually because a new Segment is allocated.  

Thus, if I were to do a MOVE of the "Normal" Table :

SQL> alter table NONPARTITIONED move;

Table altered.

SQL> 
SQL> l
  1  select id_col,
  2  dbms_rowid.rowid_object(rowid) ObjectNumber,
  3  dbms_rowid.rowid_relative_fno(rowid) RelativeFileNumber,
  4  dbms_rowid.rowid_block_number(rowid) BlockNumber
  5  from NONPARTITIONED
  6*  order by id_col
SQL> /

    ID_COL OBJECTNUMBER RELATIVEFILENUMBER BLOCKNUMBER
---------- ------------ ------------------ -----------
         1        81663                 32         155
         2        81663                 32         156

SQL>
SQL> l
  1  select object_name, subobject_name, object_type, object_id, data_object_id
  2  from user_objects
  3  where object_name in ('NONPARTITIONED','PARTITIONED')
  4  and object_type in ('TABLE','TABLE PARTITION')
  5* order by 1, 2 nulls first, 4
SQL> /

OBJECT_NAME      SUBOBJECT_NAME   OBJECT_TYPE              OBJECT_ID DATA_OBJECT_ID
---------------- ---------------- ----------------------- ---------- --------------
NONPARTITIONED                    TABLE                        81652          81663
PARTITIONED                       TABLE                        81658
PARTITIONED      P_1              TABLE PARTITION              81659          81659
PARTITIONED      P_2              TABLE PARTITION              81660          81660
PARTITIONED      P_3              TABLE PARTITION              81661          81661
PARTITIONED      P_MAX            TABLE PARTITION              81662          81662

6 rows selected.

SQL>

Executing a MOVE of the Non-Partitioned Table resulted in a change of the *Data Object Number* (i.e DATA_OBJECT_ID) (from 81652 to 81663) without changing the OBJECT_ID.

For a couple of more interesting aspects of ROWIDs, see this YouTube video "Think you know how the ROWID works? Think again!" by Connor McDonald