Hello community,
I'm trying to compare the DDL of a table which resides in two databases with the same name and the same schema:
select my_files.schema_name || '.' || my_files.table_name as table_name
, case ( select dbms_lob.compare( dbms_metadata.get_ddl(one.object_type, one.object_name, one.owner)
, dbms_metadata.get_ddl(two.object_type, two.object_name, two.owner)
)
from all_objects one
, all_objects@db_link two
where one.object_type = 'TABLE'
and one.owner = my_files.schema_name
and one.object_name = my_files.table_name
and two.object_type = one.object_type
and two.owner = one.owner
and two.object_name = one.object_name
)
when 0 then 'Y'
else 'N'
end as ddl_equal_flag
from my_files;
When I execute the above query in SQL Developer I get results like:
| TABLE_NAME | DDL_EQUAL_FLAG |
|---|
| MY_SCHEMA.TABLE_1 | Y |
| MY_SCHEMA.TABLE_2 | Y |
| MY_SCHEMA.TABLE_3 | Y |
| etc. | etc. |
But I wonder whether this really works... I suspect DBMS_METADATA.GET_DDL returns the DDL of the database where I'm logged in, regardless of the database link I used. Is that correct?
And if so, is there another approach that gives me the desired results?
Your help is very much appreciated!
Kind regards, Yvo