snippetMinor
How to generate DDL of an object which a synonym points to?
Viewed 0 times
pointssynonymddlgeneratehowwhichobject
Problem
I want to generate the DDL of an object which a synonym points to. Consider the below example.
I have a synonym named USER which points to USER_1 table. I want to generate DDL of the USER_1 table using the synonym as input.
I need to generate for over 400 synonyms, hence searching for a method to retrieve DDL using synonyms.
Thanks in advance.
I have a synonym named USER which points to USER_1 table. I want to generate DDL of the USER_1 table using the synonym as input.
I need to generate for over 400 synonyms, hence searching for a method to retrieve DDL using synonyms.
Thanks in advance.
Solution
For tables:
For other object types as well:
select dbms_metadata.get_ddl('TABLE', table_name)
from user_synonyms
where synonym_name in ...;For other object types as well:
select dbms_metadata.get_ddl(o.object_type, s.table_name)
from user_synonyms s join user_objects o on (s.table_name = o.object_name)
where s.synonym_name in ...;Code Snippets
select dbms_metadata.get_ddl('TABLE', table_name)
from user_synonyms
where synonym_name in ...;select dbms_metadata.get_ddl(o.object_type, s.table_name)
from user_synonyms s join user_objects o on (s.table_name = o.object_name)
where s.synonym_name in ...;Context
StackExchange Database Administrators Q#177715, answer score: 2
Revisions (0)
No revisions yet.