Forum Stats

  • 3,781,547 Users
  • 2,254,529 Discussions
  • 7,879,751 Comments

Discussions

Query all-objects from rowid returns empty

mail8mz
mail8mz Member Posts: 178 Blue Ribbon

Hello,

I am using Oracle 19, and have empty result when query all-objects based on the rowid.

Here is the query:

select * from all_objects where object_id = dbms_rowid.rowid_objects('rowidfromtable');

I got the "rowidfromtable" from via "select rowid from schema1.table1".

It should be not a permission issue, the query returns the table name for most tables under the same schema. However, i have no idea why the query returns empty for only several tables.

Thanks in advance!

Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,384 Red Diamond

    Hi, @mail8mz

    Whenever you have a problem, post a complete test script that the people who want to help you can run to re-create the problem and test their ideas. In this case, include CREATE TABLE and INSERT statements for some table, and the e axact code that fails for that table.

    However, i have no idea why the query returns empty for only several tables.

    Are you sure those "tables" aren't actually views? Views behave like tables in many ways, but they don't have ROWIDs.

  • User_H3J7U
    User_H3J7U Member Posts: 814 Gold Trophy

    Object_id is a logical object id. rowid is pointed to a data segment. Object_id equals to data_object_id on creation but later the segment can be moved. Also object on creation can use another segment. Also an object is not present in all_ when user have no object grants.

    mail8mz
  • mail8mz
    mail8mz Member Posts: 178 Blue Ribbon

    create table schem1.tb_test (id number(3), nm varchar2(20) );

    insert into schema1.tb_test values (1, 'first nm');

    commit;

    select * from all_objects where object_id =

    (select dbms_rowid.rowid_object(rowid) from schema1.tb_test where id =1);

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,384 Red Diamond

    Hi, @mail8mz

    create table schem1.tb_test (id number(3), nm varchar2(20) ); ...

    The CREATE TABLE statement is for a schema called schem1 (6 characters, no 'a'), but the other statements reference schema1 (7 characters, including 'a').

    If I use the same schema name everywhere, that code works fine for me. When you do that, what happens? Do you get "no rows selected"? What happens if you run the sub-query all by itself:

    select dbms_rowid.rowid_object(rowid) from schema1.tb_test where id =1;
    
    mail8mz
  • mail8mz
    mail8mz Member Posts: 178 Blue Ribbon
    edited Nov 23, 2021 8:32PM

    Just want to close the issue, I should use all_objects.data_object_id not all_objects.object_id.


    select * from all_objects where data_object_id =

    (select dbms_rowid.rowid_object(rowid) from schema1.tb_test where id =1);


    Thanks for everyone's help!

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,696 Silver Crown

    I answered on the other thread, do not to create more than 1 thread on different spaces for the same question, it is complex to follow up :-).