Solved

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

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Migrating an SQL 2008 database to Oracle 12c 3 100
oracle 11g 23 78
SQL Developer 6 48
Oracle - Query link database loop 8 37
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

786 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