Solved

How can I get the DDL of all tables in all tablespaces?

Posted on 2014-10-06
2
460 Views
Last Modified: 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.
0
Comment
Question by:Erik Hauser
2 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40364837
Log in as user1 and:
select dbms_metadata.get_ddl('TABLE',table_name)
from user_tables
where table_name like 'XX\_%' ESCAPE '\'
/

or the DBA view:
select dbms_metadata.get_ddl('TABLE',table_name)
from dba_tables
where owner='USER1' and table_name like 'XX\_%' ESCAPE '\'
/

The reason for the escape is the '_' is a single character wildcard and would match 'XXTABLE'.

If this isn't an issue, just remove the escape clause and do:
table_name like 'XX%'
0
 

Author Closing Comment

by:Erik Hauser
ID: 40365031
Thank you very much! I will try this tomorrow!  Looks like it's just what I need.  Thank you!
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question