Query all-objects from rowid returns empty

mail8mz

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!

mail8mz

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

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

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