Solved

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

Posted on 2014-10-06
2
455 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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

911 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now