Solved

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

Posted on 2014-10-06
2
452 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
Comment Utility
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
Comment Utility
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.

Join & Write a Comment

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 …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

771 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

11 Experts available now in Live!

Get 1:1 Help Now