How can I get the DDL of all tables in all tablespaces?
Posted on 2014-10-06
I have 90 + tables that are currently listed under a user we'll call 'user1'. All of the tables I need the DDL for are listed under this user but these same tables come from many different tablespaces.
So, I am essentially looking for a way to have the DDL returned for any table listed under 'user1' no matter what the tablespace name is for each table.
I have tried using the dbms_metadata.GET_DDL function in many different ways. I usually get an error like "table table-1 does not belong to schema 'user1'" or something along those lines.
Can anyone help? A higher-level way to put it would be, I need to extract the DDL for any table that begins with XX_ no matter which tablespace it belongs to (preferably owned or used by 'user1').
I am happy to answer any questions, should you have them.