DBA Blogs

The format of the ROWID

Hemant K Chitale - 14 hours 15 min ago

 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




Categories: DBA Blogs

Indexes with or without TRIM

Tom Kyte - Fri, 2022-08-05 15:46
If I have an index, for example, by CD_CLIENT, CD_COMMOD, CD_MERCAD, and there is a query that uses them with TRIM. So: ....... <code>WHERE TRIM( CUSTOMER_CD ) = ... AND TRIM(CD_COMMOD) = ... AND TRIM(CD_MARKET) = ...</code> Should I make another index that has the TRIM ? Or is that enough? Thank you very much!!
Categories: DBA Blogs

Your suggestions on OLAP technology

Tom Kyte - Fri, 2022-08-05 15:46
We are evaluating OLAP technology for implementing some analytics for our product. We are reviewing technologies from Oracle, Microsoft and other vendors. Correct my if I am wrong, OLAP technology with Oracle looks like an early stage feature. I got this assumption when comparing the features, ease of use etc with other vendors. Even Microsoft also well in advance in that area. I couldn?t see much customer references for Oracle OLAP. Please comment on the following issue as you have more expertise with the different technologies and industry? What are your suggestions on choosing OLAP technology? What are your suggestions on Oracle OLAP? Should we consider it? If yes, why?
Categories: DBA Blogs

NVARCHAR2 > 2000 - MAX_STRING_SIZE

Tom Kyte - Fri, 2022-08-05 15:46
Hello, I inherited from a schema that is using nvarchar2. I am trying to recreate that schema but I am struggling to create table with nvarchar2 that have a size of 4000 (> 2000). I have changed the max_string_size to "extended" but it does not works. I works varchar2 with e.g. 32676 bytes. Here are the operation I made. <code> sqlplus / as sysdba ALTER SESSION SET CONTAINER=CDB$ROOT; ALTER SYSTEM SET max_string_size=extended SCOPE=SPFILE; shutdown immediate; startup upgrade; ALTER PLUGGABLE DATABASE ALL OPEN UPGRADE; EXIT; cd $ORACLE_HOME/rdbms/admin mkdir -p /home/oracle/tmp/log_utl32k $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS --force_pdb_mode 'UPGRADE' -d $ORACLE_HOME/rdbms/admin -l '/home/oracle/tmp/log_utl32k' -b utl32k_cdb_pdbs_output utl32k.sql sqlplus / as sysdba shutdown immediate; startup; ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE; EXIT; mkdir -p /home/oracle/tmp/utlrp $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS --force_pdb_mode 'READ WRITE' -d $ORACLE_HOME/rdbms/admin -l '/home/oracle/tmp/utlrp' -b utlrp_cdb_pdbs_output utlrp.sql sqlplus / as sysdba create table testvc (name varchar2(32676)); >>Table created. create table testnvc (name nvarchar2(4000)); >>ORA-00910: specified length too long for its datatype </code> I have checked several resources but I cannot find a solution. Any suggestion are welcomed.
Categories: DBA Blogs

SQL Model

Tom Kyte - Thu, 2022-08-04 21:26
Tom, sorry about the silly question, but have been trying to do something with SQL Model without any success. I cannot understand how it works, could you do some simple sample? -- may be already exists. Thanks,
Categories: DBA Blogs

KVM with an OnPrem Exadata X9-2 M

Tom Kyte - Thu, 2022-08-04 21:26
I have a client that is trying to implement KVM on an Exadata X9-2 M (21.2.11.0.0), and he was told that in the Exadata he could have a single VM running in a single DB Node in the exadata, not a cluster VM, but just a single VM in a single DB Node. To my understanding, that is not possible in the Exadata as they had to be VM clusters, 2 DB nodes, instead of 1 DB node. I'm I incorrect in what I know?
Categories: DBA Blogs

Shrink space row movement

Tom Kyte - Thu, 2022-08-04 21:26
I use the process below to shrink datafiles and reclaim operating system space. It works fine. My question is there any downside to ALWAYS leaving row movement enabled instead of disabling it when the process is completed? Below is a sample of what I'm currently doing. <code>create table t as select rownum x, lpad('x', 500, 'x') xx from dual connect by level <= 10000; / -- ALTER TABLE t SHRINK SPACE CHECK; select bytes from user_segments where segment_name = 'T'; / delete t where x <= 9900; / select bytes from user_segments where segment_name = 'T'; / alter table t enable row movement; / alter table t shrink space CASCADE; / select bytes from user_segments where segment_name = 'T'; </code>
Categories: DBA Blogs

Outer Join with OR - Ansi vs Oracle

Tom Kyte - Thu, 2022-08-04 21:26
hello I'm curious how you would deal with the following. Table t1 has mixture of usernames, some with format of email address, some with format of first_name.last_name. Table t2 lookup table has username in email format. Requirement to return all 3 rows <code> create table t1 (taskid number, username varchar2(50)) insert into t1 values (1,'john.smith@12]3.com'); insert into t1 values (2,'dave.jones@123.com'); insert into t1 values (3,'dave.smith'); commit; create table t2 (username varchar2(50)); insert into t2 values ('john.smith@123.com'); insert into t2 values ('dave.smith@123.com'); commit; </code> Via ansi syntax, quite forward: <code> select * from t1 left outer join t2 on t1.username = t2.username or t1.username = substr(t2.username,1,instr(t2.username,'@')-1) order by 1 </code> Via oracle syntax - As can't use outer joins with OR, do you have any (easier) alternatives than this? <code> select t1.taskid, t1.username, t2.username from t1, t2 where t1.username = t2.username union all select t1.taskid, t1.username, t2.username from t1, t2 where t1.username = substr(t2.username,1,instr(t2.username,'@')-1) union all select t1.taskid, t1.username, to_char(null) from t1 where not exists (select 1 from t2 where t1.username = t2.username or t1.username = substr(t2.username,1,instr(t2.username,'@')-1)) order by 1 </code> thanks
Categories: DBA Blogs

analytical vs aggregate functions

Tom Kyte - Mon, 2022-08-01 20:06
Are analytical functions replacement for aggregate functions? Some of the developers here are hurrying in replacing the group by aggregate functions with analytical functions just because they learnt something new :-).. Are the analytical functions faster always(compared to aggregate)?
Categories: DBA Blogs

adding sysdate on the file name

Tom Kyte - Mon, 2022-08-01 20:06
Hi Tom When spooling the file on PL/SQL, i would like the file name to have sysdate attached to the filename and it should change dynamicaly.. e.g.PM-INT-20021129_FLASHNOTIFICATIONS.TXT Thanx Zoliswa
Categories: DBA Blogs

FORALL issues...

Tom Kyte - Mon, 2022-08-01 20:06
Hi Tom, I'm using the FORALL statement to update tables in my applications. When the update raises an error, it can be saved using the SAVE EXCEPTIONS clause and then continuing with the update, or the update can be aborted without using that clause. In both these situations however, I am unable to pin down which specific record caused the error, i.e. the id of the record. More specifically, is there a way to store user defined data upon encountering an error during a FORALL execution? Small example: declare type intArray is table of number index by binary_integer; type charArray is table of varchar2 (2000) index by binary_integer; type recType is record (idArray intArray, array2, charArray); l_tabrec recType; lt_id intArray; begin ... ... /* populating the l_tabrec record of arrays here. */ ... begin forall i in 1..l_tabrec.idArray.count update table2 set value = l_tabrec.array2(i) where id = l_tabrec.idArray(i); exception when others then /* here is where I'd like to log the id of the record which fails. */ raise; end; end; I know the SAVE EXCEPTIONS gives us information about the type of error and the iteration on which it encountered the error, but I'm not sure how I can use that information to find which record actually caused the error. Hope my question is clear. Thanks in advance. Kashif
Categories: DBA Blogs

dense_rank vs. rank

Tom Kyte - Fri, 2022-07-29 18:46
Hi, Tom. Could you please tell me more about how to determine when to use analyatic function dense_rank, and when to use rank? Thanks. Bo
Categories: DBA Blogs

How can I suppress the other error messages from PLSQL errors

Tom Kyte - Fri, 2022-07-29 18:46
When we use raise_application_error procedure in exception part, the procedure returns customized as well as other error message. How can I suppress the other error message? For example... ======================================= SQL> execute error_mesg(60); BEGIN error_mesg(60); END; * ERROR at line 1: ORA-20001: No Department exists! ORA-06512: at "BHAVESH.ERROR_MESG", line 18 ORA-06512: at line 1 ======================================= is the message I get in my procedure bhavesh.error_mesg. I don't want all other ORA_065512. All I want in my error message is ORA-20001 which we defined using raise_application_error package. Thanks for the help. Regards, Bhavesh
Categories: DBA Blogs

Faster way to Pivot the data and insert into another table

Tom Kyte - Fri, 2022-07-29 00:26
Hi All, I have a table to 1.8b records and need to pivot this table and insert it into another table. The performance of this SQL is very bad and it took around 8hr but no results. Please find the sql below. <code>v_sql1 := q'{ INSERT INTO SCPOMGR.FCSTWIDE ( dmdunit, dmdgroup, loc, startdate, TYPE, fcstid, model, dmdcal, period1, period2, period3, period4, period5, period6, period7, period8, period9, period10, period11, period12, period13, period14, period15, period16, period17, period18, period19, period20, period21, period22, period23, period24, period25,period26, period27, period28, period29, period30, period31, period32, period33, period34, period35, period36, period37, period38, period39, period40, period41, period42, period43, period44, period45, period46, period47, period48, period49, period50, period51, period52, period53, period54, period55, period56, period57, period58, period59, period60, period61, period62, period63, period64, period65, period66, period67, period68, period69, period70, period71, period72, period73, period74, period75, period76, period77, period78, period79, period80, period81, period82, period83, period84, period85, period86, period87, period88, period89, period90, period91, period92, period93, period94, period95, period96, period97, period98, period99, period100, period101, period102, period103, period104, period105, period106, period107, period108, period109, period110, period111, period112, period113, period114, period115, period116, period117, period118, period119, period120, period121, period122, period123, period124, period125, period126, period127, period128, period129, period130, period131, period132, period133, period134, period135, period136, period137, period138, period139, period140, period141, period142, period143, period144, period145, period146, period147, period148, period149, period150, period151, period152, period153, period154, period155, period156, period157, period158, period159, period160, period161, period162, period163, period164, period165, period166, period167, period168, period169, period170, period171, period172, period173, period174, period175, period176, period177, period178, period179, period180, period181, period182, period183, period184, period185, period186, period187, period188, period189, period190, period191, period192, period193, period194, period195, period196, period197, period198, period199, period200, period201, period202, period203, period204, period205, period206, period207, period208, period209, period210, period211, period212, period213, period214, period215, period216, period217, period218, period219, period220, period221, period222, period223, period224, period225, period226, period227, period228, period229, period230, period231, period232, period233, period234, period235, period236, period237, period238, period239, period240, period241, period242, period243, period244, period245, period246, period247, period248, period249, period250, period251, period252, period253, period254, period255, period256, period257, period258, period259, period260, period261, period262, period263, period264, period265, period266, period267, period268, period269, period270, period271, period272, period273, period274, period275, period276, period277, period278, period279, period280, period281, period282, period283, period284, period285, period286, period287, period288, period289, period290, period291, period292, period293, period294, period295, period296, period297, period298, period299, period300, period301, period302, period303, period304, period305, period306, period307, period308, period309, period310, period311, period312, period313, period314, period315, period316, period317, period318, period319, period320, period321, period322, period323, period324, period325, period326, period327, period328, period329, period330, ...
Categories: DBA Blogs

Costing Concatenated Indexes With Range Scan Predicates Part II (Coming Back To Life)

Richard Foote - Tue, 2022-07-26 23:22
In my previous Part I post, I discussed how the CBO basically stops the index leaf block access calculations after a non-equality predicate. This means that for an index with the leading indexed column being accessed via an unselective non-equality predicate, a large percentage of the index’s leaf blocks might need to be scanned, making […]
Categories: DBA Blogs

Oracle Raptor

Tom Kyte - Tue, 2022-07-26 17:26
Tom, Just curious about your impressions of Oracle Raptor. I've installed the Early Adoptor Release and it seems to be a slick tool for SQL and PL/SQL application development. What group is Oracle trying to target with this free tool? How is it better then other development tools already out there. What are the limitations of Raptor - how isn't it meant to be used? As always, thanks for your insight! Regards, Craig
Categories: DBA Blogs

print the multiplication tables in PLSQL

Tom Kyte - Tue, 2022-07-26 17:26
Hi , I want print the multiplication table, if i enter 2 then need to display 2 multiplication , if i enter 50 then want print 50th multiplication. Can you give both sql and plsql 2 examples ?
Categories: DBA Blogs

Quarter Date calculation

Tom Kyte - Tue, 2022-07-26 17:26
Hi Tom, I work in the insurance industry and I'm having trouble calculating the quarter. My insurance policy runs from 1-Feb-2022 to 31-Jan-2023, and I pay my premiums quarterly. Inst No Inst date 1 01-Feb-2022 ( Collect premium for Feb,Mar, Apr) 2 01-May-2022 ( Collect premium for May, Jun, Jul) 3 01-Aug-2022 ( Collect premium for Aug, Sep, Oct) 4 01-Nov-2022 ( Collect premium for Nov, Dec, Jan) Suppose in the above policy a new risk is added from 01-Jun-2022 to 31-Jan-2023, then the premium for this risk should be collected on the following dates Inst No Inst Date 2 01-Jun-2022 ( Collect premium for Jun, Jul) 3 01-Aug-2022 ( Collect premium for Aug, Sep, Oct) 4 01-Nov-2022 ( Collect premium for Nov, Dec, Jan) Using the above example, kindly guide me on how can I arrive the instalment dates for the newly added risk
Categories: DBA Blogs

TO_date function behaviour in select list and where clause

Tom Kyte - Tue, 2022-07-26 17:26
Dear Tom, I have column (C1) in varchar2 datatype, and it has around 10k records coming as 'dd-mon-yyyy'format (ex:'22-jun-2022'). when querying below is exucuted without error, select C1, CASE WHEN TO_DATE(C1,'dd-mon-yyyy') between to_date('01-Jun-2022') and to_date('30-Jun-2022') then 1 else 0 end ---for checking it is giving 1 or 0 from T; where it as,if I write like below getting error as 'ora-01858 a non-numeric character was found where numeric expected'. select C1, CASE WHEN TO_DATE(C1,'dd-mon-yyyy') between to_date() and to_date() then 1 else 0 end from T where TO_DATE(C1,'dd-mon-yyyy') between to_date('01-Jun-2022') and to_date('30-Jun-2022'); --also tried select C1, CASE WHEN TO_DATE(C1,'dd-mon-yyyy') between to_date() and to_date() then 1 else 0 end from T where TO_DATE(C1,'dd-mon-yyyy') between to_date('01-Jun-2022','dd-mon-yyyy') and to_date('30-Jun-2022','dd-mon-yyyy'); In the select list it is not getting any error,but in the where clause it is showing error. Please share if any clue/references?
Categories: DBA Blogs

Recursive query to calculate salary increases

Tom Kyte - Tue, 2022-07-26 17:26
Please tell me what condition of the problem can be thought up so that it can be solved only by recursion, not connect by. For example, you can do something like There is a list of employees, the coefficient of salary increase every year, his current salary For each of them, draw up a schedule for 20 years ahead of salaries, given that if the total salary of the entire department exceeds a million, then 10% is subtracted from each salary, and 10% is added to this million My table <code>create table scrubs5(name varchar2(100),cent number, salary number); insert into scrubs5 values ('John Dorian' ,10,50000); insert into scrubs5 values ('Eliot Reed',15 ,40000); insert into scrubs5 values ('Pesivald Cox',10, 35000); insert into scrubs5 values ('Bob Kelso' ,20,30000); insert into scrubs5 values ('Cris Terk',15, 25000);</code> Code <code>WITH data( sal, name, n, cent) AS ( SELECT salary as sal, name, 1 as n, cent FROM scrubs5 UNION ALL SELECT sal + sal*cent/100, name, n+1, cent FROM data WHERE n < 5 ), data1(sal, name, n, cent, t) as (SELECT sal, name, n, cent, 100000 as t FROM data union all select case when sum(sal) over (partition by n)> t then sal- sal*cent/100 else sal end, name,n+1, cent, case when sum(sal) over (partition by n)> t then t+t*10/100 else t end from data1 WHERE n < 5) select sal, name, n, cent, t from data1 order by n ;</code>
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs