Single update statement?
Greg, December 12, 2005 - 2:05 pm UTC
Tom, is this also an issue/concern with a single update statement?
For example:
Update some_table
set value = something
where rowid in ( select rowid from some_table where some_critieria);
??
Just a single statement? The Rollback/Undo is going to keep track of it all for us, correct? In this case, the rowid is "safe", isn't it?
December 12, 2005 - 2:26 pm UTC
in this case rowid is "safe" but perhaps not "efficient"
update t
set value = something
where some_criteria;
unless the subquery had analytics or something like that.....
What about page splits?
Alexander, December 12, 2005 - 2:21 pm UTC
Tom,
thank you for the most useful explanation.
What happened when I updated narrow rows, setting c to wide values? Weren't some pages split? If the pages were split, and ROWIDs did not change, then what is ROWID internally?
December 12, 2005 - 2:31 pm UTC
the row migrates.
search this site for
"row migration"
to read about it (or see Expert one on one Oracle or Expert Oracle: Database Architecture where I cover this in detail)
The rowid for the row stays the same, even when the row migrates.
Different row, same rowid
A reader, December 12, 2005 - 3:01 pm UTC
Rowid's are also unreliable in the following situation:
Session 1: retrieves a row with rowid X
Session 2: deletes the row with rowid X, commits
/* rowid X is now free for re-use */
Session 3: inserts a new row with rowid X, commits
Session 1: update .... where rowid = X
Session 1's update is not updating the same row that it had earlier retrieved.
December 12, 2005 - 4:13 pm UTC
that is why you need the primary key as I said above.
....
It is perfectly safe to use the rowid in ALL CASES however, assuming you combine
it with the primary key as well:
.....
Duplicate rowid's
Girish, December 12, 2005 - 11:33 pm UTC
But I have seen in Oracle 9i deplicate rowid's for duplicate records , but same is not true in Oracle 8i
Can throw some light on this
SQL> select rownum,rowid,deptno from mydept;
ROWNUM ROWID DEPTNO
---------- ------------------ ----------
1 AAAJWTAAWAAB+BiAAA 10
2 AAAJWTAAWAAB+BiAAB 20
3 AAAJWTAAWAAB+BiAAC 30
4 AAAJWTAAWAAB+BiAAD 40
5 AAAJWTAAWAAB+BjAAA 10
6 AAAJWTAAWAAB+BjAAB 20
7 AAAJWTAAWAAB+BjAAC 30
8 AAAJWTAAWAAB+BjAAD 40
December 13, 2005 - 9:00 am UTC
I've a feeling mydept is a union all view IF you really have seen dups - but Bi and Bj are different...
a rowid is unique within a table.
a rowid maybe be duplicated many times across tables in a database, but in a table - nope.
Look twice
Bart, December 13, 2005 - 2:14 am UTC
Look more carefully !!
If asktom.oracle.com didn't change them, all rowid's are slightly different!
Rowids and GTT
N.Venkatagiri, December 13, 2005 - 2:25 am UTC
There is no duplication found in the above rowids.
Tom
Kindly tell me about any issues on using rowids with Global temporary tables.
December 13, 2005 - 9:16 am UTC
no different than a real table.
Anonymous
A reader, November 26, 2008 - 6:17 pm UTC
Hi Tom,
If you have a single delete statement that deletes many records using rowids. Would there ever be a time when the rowid within this table change during the execution of this delete statement ?
For ex: delete from T where rowid in (<rowids>)
November 28, 2008 - 5:06 pm UTC
in order for a rowid to change you have to
enable row movement
first - so, if row movement is not enabled, then no.
If it is, then
flashback table could change a rowid, but that is DDL and would not happen concurrently with a delete (so it would not affect it)
alter table t shink space compact, that moves rows and would change rowids...
update of a partition key that causes a row to move. that moves rows and would change rowids
ROWID AND ORDER BY CLAUSE
Dhairyasheel Tawade, November 29, 2008 - 11:02 am UTC
Hi Tom,
just one question regarding your rowid + primary key combination suggestion.
if my table has no primary key and say 10 records in it &
if I do a "select * from mytab order by rowid", will it return the same order everytime?
Also if I fire two inserts in a table, whether the rowid of the 2nd record will be greater than rowid of the 1st record?
Thanks.
December 01, 2008 - 6:05 am UTC
if rowid is constant, yes it would return the same order each time.
However, in the year 2008, rowid is not constant - many things can cause rowids to change.
As for the last one - the answer is "of course not".
If you
insert A
insert B
and later
insert C
delete A
insert D
it is quite possible that D will be "first" in the table - as it took over A's place.
If rowids always "grew", than space would never be reused (that would be an implication of rowids growing always - we would never be able to reuse old space as the rowid is just a file.block.slot-on-block - a physical address)
Regarding SESSION_ID & SID in Oracle 9i R2
Rajeshwaran, Jeyabal, December 02, 2008 - 1:32 am UTC
Tom,
Just one question regarding the SESSION_ID & SID. I executed the below query in Oracle 9i R2.
1) SQL> SELECT SYS_CONTEXT('USERENV','SESSIONID') AS SESS_ID FROM DUAL;
SESS_ID
------------------------------------------------------------------------
29058
2) SQL> SELECT * FROM V$MYSTAT WHERE ROWNUM =1;
SID STATISTIC# VALUE
---------- ---------- ----------
10 0 1
So now My SESSION_ID is "29058" (This matches with AUDSID column of V$session) & SID is "10"
Then I Issued a Update statement on my Table 'T' and executed the below query.
3) SQL> SELECT xidusn,xidslot,xidsqn,object_id,session_id,locked_mode
2 FROM V$LOCKED_OBJECT;
XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID LOCKED_MODE
---------- ---------- ---------- ---------- ---------- -----------
8 21 61670 101115 10 3
Question 1) What is SESSION_ID and SID in oracle represents?
Question 2) From the output for the Query 3 why the "SESSION_ID" Column shows the value of 10 instead of "29058" ?
December 02, 2008 - 7:32 am UTC
sessionid maps to audsid and can be unreliable (eg: for sys recursive transactions, it'll be 0).
sid,serial# uniquely identifies your session
I would stick to using sid,serial#
SESSION_ID & SID IN Oracle 9i R2
Rajeshwaran, Jeyabal, December 02, 2008 - 11:05 am UTC
Tom,
1) Session_id & SID should be used together to identify a Session in oracle server. Or either can be used?
2) Is there is any specific expansion to SID?
December 02, 2008 - 11:23 am UTC
1) already answered.
"I would stick to using sid,serial# "
2) not at all sure what you mean.
Update Records using ROWID.
Rajeshwaran, Jeyabal, August 31, 2009 - 4:12 pm UTC
Hi Tom,
we have a table named STG_INBOUND. where Pipe Delimited data from flatfile is loaded into the table using SQL loader. Once the data is loaded completely application will start processing the data. once completed the PROCESSED_FLAG in the table is marked to 'Y'. if any error in the data then it will be marked to 'N' and start processing the next record.
the table STG_INBOUND is partitioned by the FILE_KEY Column for each new file loaded by Sql loader that has a reference to the FILE_KEY in STG_FILEMASTER table. once the file is completely processed without errors then partition is Dropped.
ALTER TABLE STG_INBOUND DROP PARTITION <partition_name>;
Question
1) we dont have any primary key in the STG_INBOUND table. we use ROWID to update the PROCESSED_FLAG = Y or PROCESSED_FLAG ='N', is this a right approch to follow? i need your inputs in this scenario.
Also we are not performing any updates on the PARTITION_KEY results in ROW_MOVEMENT & not even issuing ALTER TABLE ...SHRINK SPACE Command during this data processing (even no where in the database itself).
please suggest me is it safe to use ROWID to update records in STG_INBOUND table or need to have a running sequence as a primary key and make use of that in the Where clause?
August 31, 2009 - 4:32 pm UTC
as long as row movement is not enabled and you do nothing to reorganize the table in any fashion - rowid would be ok in this case. not highly recommended or encouraged, but it would work
Rowid in SYS.COL$
Manuel Vidigal, December 23, 2009 - 4:44 am UTC
Hi Tom,
I'm trying to write an application that automatically gives you the alter table scripts from one revision to the other. The problem I'm facing because of the drop and rename column commands, I need a unique identifier for the columns. I guessing since the row movement for this table is disabled, the rowid is safe to use. But I wanted your opinion on this one.
Thanks in advance.
December 31, 2009 - 11:52 am UTC
such tools already exist.
They typically just "diff" schemas (sql developer can do that for example)
rowids can and will be reused, in general, it would not be safe. I don't know what you are doing exactly, so that is all I can say...
A reader, January 12, 2010 - 10:07 am UTC
The purpose is getting the version changes within the same schema. For that I'm saving in a table the metadata for each revision. I'm also saving the user_tab_columns information.
I will try to describe my question.
For example:
Version 0.1:
create table TEST
(
COLUMN1 NUMBER,
COLUMN2 VARCHAR2(20)
)
Version 0.2:
create table TEST
(
COLUMN1 NUMBER,
COLUMN3 VARCHAR2(30)
)
Unless I save the rowid of SYS.COL$, I have no way of knowing if column2 and column3 are the same. There are at least 2 case scenarios. Column2 could be dropped and then Column3 was created, or Column2 could have been renamed and the size was changed.
My question is if the table is no dropped and then recreated, if the rowid for a given column will be always the same.
Hope this help you understand the purpose.
January 18, 2010 - 12:56 pm UTC
the rowids can and will be reused, yes, definitely, you cannot rely on them at all.
Now that said, if you need this level of detail - I would strongly suggest you read about source code control.
If you just want to bump schemas, you are going to have to *HAVE TO* live with the fact you are able to only create a diff - nothing more, nothing less.
If you want to know what really happened (and why, and more) you NEED source code control, no way around it.
Manuel Vidigal, January 19, 2010 - 9:44 am UTC
The purpose of the application I'm trying to develop is to automatically create the scripts we put on our version control software
I know the rowids can and will be reused. But my question is if for a given column that isn't dropped, only changed, will the rowid in sys.col$ change? For example:
create table mamv.teste(column1 number, column2 varchar2(30));
Using the following query to check the rowid:
SELECT c.rowid,
c.name
FROM sys.col$ c,
all_objects a
WHERE c.obj# = a.object_id
AND a.object_name = 'TESTE'
and a.owner = 'MAMV';
--Results
1 AAAAACAABAAATNWAAA COLUMN1
2 AAAAACAABAAATNWAAB COLUMN2
alter table mamv.teste modify (column1 number(5), column2 varchar2(50));
--Results
1 AAAAACAABAAATNWAAA COLUMN1
2 AAAAACAABAAATNWAAB COLUMN2
alter table mamv.teste drop column column2;
--Results
1 AAAAACAABAAATNWAAA COLUMN1
What I try to show in this example is that it seems that unless the column/table is dropped it will have the same rowid. Can you provide me an example where it doesn't?
Kind regards.
January 19, 2010 - 5:01 pm UTC
... The purpose of the application I'm trying to develop is to automatically create
the scripts we put on our version control software
...
but you cannot get there, you have NO way to tell the difference between
a) drop and create
b) rename
please do not rely on hacking the real dictionary, it will (not might, it will) only lead to heartburn later.
... But my question is if for a given
column that isn't dropped, only changed, will the rowid in sys.col$ change? ...
no one can honestly answer that. Not even the guys that write the kernel. Today the answer might be "yes", tomorrow it might well be "NO, absolutely not". So, it doesn't really matter what the answer is.
And to me, doing source code control AFTER THE FACT - after the ddl has been done is not doing source code control. The developers will not have told you what they are doing, they will not have told you why they are doing it, you do not have the change history - you have no clue "why" and that is what source control is about - maintaining the "why"
you are approaching this wrong, you are trying to put a band-aid on a system developed without any rigor or rules.
... Can you provide me an
example where it doesn't?
...
no, but that doesn't mean anything. And I know if I did - you would just say "oh, but we'll never do that"
Can you provide me with an assurance that Oracle will never change? Unless you can do that - you should not do what you are doing.
there are so many holes in this approach - the most worrisome is "you don't know WHY anything was done" - source code control would be so much chopped off at the legs without this information.
Manuel Vidigal, January 20, 2010 - 6:38 am UTC
Thanks for the information Tom.
Although the purpose is to automatically create the scripts, the goal is not to add them to the source control without developers eyes. The goal is to help us on the creation, of course the developer needs to check the scripts and validate if everything is ok.
--
please do not rely on hacking the real dictionary, it will (not might, it will) only lead to heartburn later.
--
I won't.
Thanks for your help.
January 20, 2010 - 11:38 am UTC
the developers must have already developed these scripts.
The developers must have already had these scripts code reviewed
the developers must have already added their commentary and documentation on this
This is not needed therefore, unless the developers just willy nilly do whatever they feel like on their own without any sort of review or consensus.
and if they do that, the probability they'll remember why a change was made 2 months later is nill.
Back to square one - you need to do it right - which means YOU do not do it, THEY (the developers) do it - from the beginning, from day one.
Update/delete using ROWID
Kavita, January 27, 2010 - 3:32 am UTC
Hi Tom,
The table I want to update doesn't have primary key. So is it safe to use ROWID to identify the row and perform the appropriate operation
January 29, 2010 - 3:07 pm UTC
it depends.
If you are stateful (eg: typically NOT web based) you can safely select rowid, .... for update - locking the row, work with the row, and then update it. Yes. Because you KNOW that rowid will not change, you know that rowid won't become the rowid of some other row.
If however, you
a) select the rowid out
b) commit or do not lock the row - eg: lose your state
c) then update the row by rowid.
There is nothing saying you will have updated the SAME row - it could be some other row, the row you are looking for might have another rowid, the row you are looking for might be gone.
primary keys, sort of an important thing.
The rowid will probably work 99% of the time or even more. That is the good news.
The bad news is - sometimes you will update the wrong row, or just lose the row altogether.
The bad news is pretty bad.
Two Tables indenpent
Arturo Caceres Sequeira, February 01, 2010 - 9:48 pm UTC
I have 2 tables, at beginin this f1 master and f2 detail, but when enduser insert rows he change restrictions 1 to 1, and confuse repeats no. of id, 1,1 type 1,
1,1 type 2, and i practical the conection of rows lost, but belive using rownum sequence of row1 of f1 is equal row1 of f2, i need put another column to establish relations again.
if not explain good sorry, i speak spanish.
muchas Thanks Tom
February 02, 2010 - 12:13 pm UTC
sorry, I did not follow this.
"Is it safe to use ROWID to locate a row?", version 9.2.0
sunny gambhir, April 30, 2010 - 7:28 am UTC
you said 'rowid may be duplicated for two different tables but not for same table'
But rowid id a physical address , so how can rows in different tables share same physical address
April 30, 2010 - 8:51 am UTC
a rowid is a physical address for a row in an object. Not for a row in just any object.
We have clustered tables whereby more than one tables data may be stored in a block.
ops$tkyte%ORA11GR2> create cluster emp_dept_cluster
2 ( deptno number(5) )
3 size 1024
4 /
Cluster created.
ops$tkyte%ORA11GR2> create index emp_dept_cluster_idx
2 on cluster emp_dept_cluster
3 /
Index created.
ops$tkyte%ORA11GR2> create table dept
2 ( deptno number(5) primary key,
3 dname varchar2(14),
4 loc varchar2(13)
5 )
6 cluster emp_dept_cluster(deptno)
7 /
Table created.
ops$tkyte%ORA11GR2> create table emp
2 ( empno number primary key,
3 ename varchar2(10), job varchar2(9),
4 mgr number, hiredate date,
5 sal number, comm number,
6 deptno number(5) references dept(deptno)
7 )
8 cluster emp_dept_cluster(deptno);
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into dept (deptno, dname, loc )
2 select rownum,
3 substr( object_name, 1, dbms_random.value( 5, 14 ) ),
4 substr( owner, 1, dbms_random.value( 8, 13 ) )
5 from all_objects
6 where rownum <= 10000
7 /
10000 rows created.
ops$tkyte%ORA11GR2> insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno )
2 select object_id empno,
3 substr(object_name,1,10) ename, substr(object_name,1,9) job,
4 object_id mgr, created hiredate, data_object_id sal,
5 data_object_id comm,
6 mod(rownum,10000)+1 deptno
7 from all_objects
8 /
72249 rows created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select emp.rowid, dept.rowid,
2 dbms_rowid.rowid_block_number( emp.rowid ) erid,
3 dbms_rowid.rowid_block_number( dept.rowid ) drid
4 from emp, dept
5 where emp.deptno = dept.deptno
6 and dept.deptno = 10
7 /
ROWID ROWID ERID DRID
------------------ ------------------ ---------- ----------
AAAYUzAAEAABIjLAAC AAAYUzAAEAABIjLAAC 297163 297163
AAAYUzAAEAABIjLAAJ AAAYUzAAEAABIjLAAC 297163 297163
AAAYUzAAEAABIjLAAQ AAAYUzAAEAABIjLAAC 297163 297163
AAAYUzAAEAABIjLAAX AAAYUzAAEAABIjLAAC 297163 297163
AAAYUzAAEAABIjLAAe AAAYUzAAEAABIjLAAC 297163 297163
AAAYUzAAEAABIjLAAl AAAYUzAAEAABIjLAAC 297163 297163
AAAYUzAAEAABIjLAAs AAAYUzAAEAABIjLAAC 297163 297163
AAAYUzAAEAABIjLAAz AAAYUzAAEAABIjLAAC 297163 297163
8 rows selected.
<b>they are all on the same block - that is the goal of the btree cluster, to pre-join data... Note the first two rowids - they are the same. The rowid is a file/block/row slot on block - GIVEN AN OBJECT to retrieve. </b>
ops$tkyte%ORA11GR2> select count(distinct rid) , count(*)
2 from (select rowid rid from dept
3 union all
4 select rowid rid from emp)
5 /
COUNT(DISTINCTRID) COUNT(*)
------------------ ----------
72249 82249
See how there are only 72,249 distinct rowids for 82,249 rows....
Anto, April 30, 2010 - 9:15 am UTC
And rows belonging to 2 different tables cannot have the same rowid(except for clustering as you have demonstrated above) - right ?
April 30, 2010 - 9:21 am UTC
you need to have two rows from two different tables be on the same block for that to happen, and the only way I know for that to be true would be in a cluster (hash or b*tree)
but the rule is - rowids are only unique within the context of an object.
A reader, April 30, 2010 - 9:19 am UTC
you said 'rowid may be duplicated for two different tables but not for same table'. ---> does not mean that 2 different tables can have the same rowid(except in the case of clustering) - otherwise one can never update based on rowids
April 30, 2010 - 9:22 am UTC
Just remember this:
rowids are not unique in a database, rowids are only unique within and object.
That is what you need to know - not the exceptions.
What I'm worried is, people are asking just so they can go back to pretending that rowids are unique in a database. They aren't and if you ever make that assumption, you will have a bug in your code just waiting to happen.
... otherwise one can never update based
on rowids
...
I don't know why you would jump to that conclusion. If you have a table_name PLUS rowid, you have a unique key.
A reader, April 30, 2010 - 11:53 am UTC
when does the column add or alter
Yao, July 11, 2010 - 7:49 pm UTC
Hi Tom
The first question: somebody ask me when does a column of a table add or alter but i don't know ...please tell me which data directory view can view the date ?
The second question: 11g tells me i can create a trigger with order,so how to write it ?
with kind regards.
July 19, 2010 - 8:58 am UTC
we do not track that minutiae. You would have to set up your own auditing if you wanted that. We usually use configuration management software for things like that (you know - documentation - so you cannot only see WHEN something took place but more importantly - infinitely more importantly - WHY something took place)
Yes you can order the triggering firing order in 11g - but you know what - if you feel the need to - I know for a fact you have a bad design. You should (in order)
a) not use a trigger at all.
b) if you ignore (a), put your code in a package, have the main procedure of the package call the routines in the right order and have your trigger be all of one line long - it just calls the main package procedure that does the right thing.
ordering of triggers:
http://docs.oracle.com/docs/cd/B28359_01/appdev.111/b28370/triggers.htm#g1041767
Using rowid with outer join
Srastanka, June 04, 2012 - 3:39 am UTC
Is there any risk in using rowid along with outer joins as in the query below? It is known that neither the table table1 nor table2 has a primary key.
It is also known that row movement is disabled and there is going to be only one session which is going to operate.
UPDATE (SELECT t1.col1, t2.col2
FROM table1 t1
LEFT OUTER JOIN
table2 t2
ON t1.some_column = t2.some_column) tt
SET tt.col1 = tt.col2
WHERE t1.rowid IN (<set of rowids>);
June 04, 2012 - 9:11 am UTC
where is the set of rowids coming from?
Using rowid with outer join
Srastanka, June 05, 2012 - 6:23 am UTC
Lets say the set of rowids come from a subquery like
UPDATE (SELECT t1.col1, t2.col2
FROM table1 t1
LEFT OUTER JOIN
table2 t2
ON t1.some_column = t2.some_column) tt
SET tt.col1 = tt.col2
WHERE table1.rowid IN (SELECT t3.rowid FROM table1 t3, table4 t4 WHERE t3.col3 = t4.col4 AND t3.col3 = '<condition_value>');
Please note that both the aliases t1 and t3 are for table1.
Assuming this query will execute fine, but will it update those records of table1 that we intend to? And is there any risk involved?
June 05, 2012 - 1:03 pm UTC
given that multiversioning is a basic tenent of the Oracle database - that'll work, the rowids will point to rows that exist and are the same rows you selected.
but you should move the where clause into the SELECT component, it shouldn't be "outside" like that.
why rowid+pk?
A reader, June 08, 2012 - 1:19 am UTC
Hi Tom,
I can not understand the point 'It is perfectly safe to use the rowid in ALL CASES however, assuming you combine
it with the primary key as well:
'.
I know use only rowid is not a good idea, when we did not get lock of it and concurrently some other session delete and insert new row which reuse this rowid. right?
But what's the point of 'rowid+primary key'?
Why not just use primary key? As primary key already uniqly identify the record. Even some other session delete and re-insert it, we are still considering it as the same/original record,right?
June 08, 2012 - 7:20 am UTC
the rowid+primary key is being used for updating a row in a "lost update" protection scheme (that is what we were discussing above in the original question)
If the row was modified in a way that caused it to move (the partition key was updated for example), then the update by rowid+pk will not update anything (that is the desired outcome - the row was already modified by someone - we don't want a 'lost update' so the update updates zero rows)
If the row was not modified in a way that caused it to move, then the rowid will find it faster than the primary key.
If the row was deleted and someone inserted a new row that just happened to reuse that rowid - then the rowid will 'find' a row but the primary key will "unfind it"
The primary key would suffice, however, it would require an unique unique scan of an index - so the rowid could offer a performance benefit.
A reader, September 26, 2012 - 2:49 pm UTC
RowID's and Data Migration
Nand Kishore Sagi, October 10, 2012 - 3:02 pm UTC
Hi Tom,
We are currently working on migrating data different two versions of same tool (Argus - Oracle Provided). For each table migration we have two different scripts
(1.) Migration script developed by Dev Engineers
(2.) Validation scripts developed by QA Engineers
For each table there is a common understanding between the development team and the QA team as to how to identify a single row in the table. We cannot alter the schemas and underlying tables to add or delete columns.
The major challenge is always with tables having composite keys. For example a common reoccurring primary key configuration at table level is EmpID and SeqNum (Employee table analogy) where SeqNum is generated at runtime. These SeqNum's always vary across the versions of the systems.
To overcome this challenge we always used a unused column or column containing NULL data to populate the OLD SEQ_NUM's. Once the validation process is done then all the columns used specifically for the migration process would be cleaned up.
I (Developer) have hit across a table that does not a primary key at table level. No combination of columns gives me a unique set of records. I was thinking along the lines of dumping the RowID in one of the varchar columns and clean it up after migration. When the migration process is going on there would not be any activity on the old system (So no DML's). Will this RowID help the QA team in identifying the row of data which should be validated. The QA team would be basically picking this RowID from the new system and then go to old system and pick up the row and validate ?
Thanks a lot for patiently reading this lengthy post and as always for sharing your wisdom.
Thanks and Regards
Nand Kishore Sagi
October 10, 2012 - 4:39 pm UTC
as long as you do not have row movement enabled on a table - the rowid assigned to a row when inserted will remain its rowid until it is deleted.
so yes, it would work - we ourselves use rowids in our online redefinition if you have no primary key.
just do not allow for row movement on the source table.
partition key
kiran, March 15, 2013 - 12:10 pm UTC
Hi Tom,
What is the partition key we are referring here? Let's suppose table t is partitioned by tab_id and primary key is created on tab_id, it is highly unlikely(for ex., none) that tab_id is updated in this table. In this scenario, is it ok to use rowid's to update this table?
Thank you
March 25, 2013 - 10:06 am UTC
I don't know what partition key you are thinking about - this is a big page, hasn't been updated in many months, the review was not about partitioning. So I really have no idea what you are referring to...
If row movement is not enabled, and therefore updating tab_id could never move a row, and since you say tab_id is never modified anything - then the rowid assigned to a row upon insert is the rowid the row will have *forever* (unless you rebuild the table - using DDL like alter table t move, or dbms_redefintion).
If you know that row movement is not enabled, and if you know that the table will never be online reorganized while you are using it, then using rowid by itself is "safe"
Row ID manipulation in a table
Boobal Ganesan, November 04, 2014 - 9:31 am UTC
Hi Tom,
I have a clarification with the rowid in a table.
If I have a rowid with AAABBBCCC for a row in a table and if i delete it followed by a commit, is there any possibility of the same rowid AAABBBCCC to appear again for some other row insertion of the same table or any other table?
rowid and replication
Scott Duncan, November 12, 2014 - 12:32 am UTC
Hi, I have written a mechanism that deletes unwanted old data from a large Oracle database, large table e.g. 750M rows and I need to delete 400M according to a large where clause condition to check whether the columns are les than an allowed tolerance, and I found that it performs best if I query the rows to be deleted by rowid, order by the rowid and use it for the deletion. This works well, however the whole database, including redo logs etc.. are block replicated to a Disaster Recovery system. I am wondering whether it is safe/ok in Oracle 10g and 11g to do this as I previously understood that the rowid's were only valid within the database that they are derived? If you can possibly clarify whether this additional consideration for using rowid is ok.
Many thanks in anticipation of any help/advice you can possibly offer about this.
Scott
Rowid for deleting millions of records using bul collect
Shefali, December 09, 2015 - 9:11 pm UTC
I used rowed as the key to delete around 19 million records in a table which already has a unique index using bulk collections . I am committing the delete after every 300000 rows.
Which approach will be faster.. ? Using Rowid for deleting the records or using Unique index key for deleting the records.
Please suggest.
Thanks.
December 10, 2015 - 2:07 pm UTC
How many rows do you have left ?
If 19million is a large percentage of your table, it might be better not to delete, but to create a new table just keeping the ones you want to retain.
What if my table doesnt have primary key ?
Suman Panigrahi, April 23, 2018 - 11:57 am UTC
I want to bulk update my table for all rows in a column, but it doesnt have primary key. How to do it ?
April 23, 2018 - 3:08 pm UTC
Why does that matter? And what's that got to do with the original question?
ORA-08006: specified row no longer exists
George, May 17, 2022 - 5:25 pm UTC
Dear all,
We migrate some databases from OCC version 12.1.2 to OCI version 19.14 and now we have the following situation.
It looks like a simple update on a table, for one or two columns which are not part of the primary key, it changes the ROWID of the record.
First it selects the ROWID with FOR UPDATE NOWAIT, at some point is updating a status column to "In work" value, it makes the processing and after the processing is finished it tries to update the STATUS column to "Processed" value, using the ROWID selected in the beginning.
But it returns "ORA-08006: specified row no longer exists" error.
I insert some debug mechanism and I compared the ROWID selected in the beginning with the ROWID of the record selected by the primary key columns and they are different.
No other update or delete on the record is done between.
Do you know anything about this?
Thank you!
George
May 18, 2022 - 2:48 am UTC
Does this table have ENABLE ROW MOVEMENT set?
See comments above
emaN, May 18, 2022 - 7:12 am UTC
at some point is updating a status column
If the table is partitioned by status column, the rowid will change.
May 18, 2022 - 1:18 pm UTC
Good point
Great!
George, May 19, 2022 - 4:55 am UTC
Hi Connor, Chris,
The tables are not partitioned.
Indeed, one difference was that somehow the ROW MOVEMENT was enabled.
After disabeling the row movemet it worked.
So, thank you very much!
Anyway, there are some other differences because I found one database that it is at release 12.2.0.1 and for one table it has the ROW_MOVEMENT to YES and the processing works correctly (the simple update on a non partitioned table and columns that are not part of the primary key does not change the ROWID).
The same update on the same table (non partitioned, non primary key columns) but with version 19.14 it changes the ROWID.
Why this could happen?
We will disable the row movement for these tables as they are not partitioned and we dont need the flash back for them.
Thank you!
George
May 23, 2022 - 3:40 am UTC
In general, we are recommending more strongly that people stay away from using ROWID in their application code.
Enable row movement
A reader, May 19, 2022 - 6:48 am UTC
If memory serves me right, row movement needs to be enabled if you want to perform SHRINK DDL on the table (even if it is not partitioned).
Cheers!
May 23, 2022 - 3:40 am UTC
Indeed
A reader, May 19, 2022 - 2:15 pm UTC
Hello,
We use a reorganization procedure which runs in the night, temporarly enables the row movement, do the alter table shrink and then disable the row movement back.
For us the solution was to disable the row movement.
Regards,
George
May 23, 2022 - 3:43 am UTC
nice work