Forum Stats

  • 3,781,549 Users
  • 2,254,529 Discussions
  • 7,879,752 Comments

Discussions

Query all-objects from rowid returns empty

mail8mz
mail8mz Member Posts: 178 Blue Ribbon
edited Nov 23, 2021 7:48PM in General Database Discussions

Hello,

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


"

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);

"

It should be not a permission issue, the last 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!

Best Answer

Answers

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

    What happens if you test on the tables where you are facing issues with something simple as:

    select count(*), min(dbms_rowid.rowid_object(rowid)), max(dbms_rowid.rowid_object(rowid)) from tb_test;
    


    Do all tables have rows? What ID is returned? NULL?

  • mail8mz
    mail8mz Member Posts: 178 Blue Ribbon
    Accepted Answer

    Data_object_id should be used in the query:


    select * from all_objects where data_object_id =

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

    L. Fernigrini