Solved

How to get a complete schema dump of an Oracle database?

Posted on 2014-11-03
4
319 Views
Last Modified: 2014-11-14
Hello,

I'm trying to export the schemas (Tables, Procedures, Packages, etc.) I have access to, into a .sql file.

I'm not the root user/or the person who owns the schemas, but I'd like to copy all the schemas I have access to; so that I can recreate the database in another location.

Is there a simple way to do this? Or do I need to go log in as each schema's ID and export the objects under that ID?

Appreciate any help on this!
0
Comment
Question by:Errang Genevre
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
ID: 40420722
Unless you have elevated privs I don't think you can.

There are some cool scripts out there that use dbms_metadata.get_ddl to grab all the DDL but you need the select_catalog_role role to be able to grab the DDL from a different schema.  Then you just go against all_objects to get all the DDL.  Examples are out there.

If you were granted the export privs, you could export using the indexfile or sqlfile (classic exp or datapump export).
0
 
LVL 16

Accepted Solution

by:
Wasim Akram Shaik earned 400 total points
ID: 40420923
In that case, if you don't have privileges,
this kind of approach should work..

select * from all_source where owner='USR1';

you should be able to view the source code, though in a table form.. substitute object names to get the object source code, this should bring you the basic ddl, however to get other things ddl like constraints, etc., you have to get it via all_dependencies or by via user_tab_cons
0
 

Author Closing Comment

by:Errang Genevre
ID: 40444036
Thanks!
0

Featured Post

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Displaying images within Access Form 6 76
Call to SQL server times out 5 55
Automate an Oracle update in Excel 7 70
Ranking Students in Ms Access based on given Criteria 5 60
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…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

738 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