0

Is there a way in Oracle 11g to dump database to sql script, that when run will perform database, users, tables and data creation?

In Microsoft SQL Server there's SSMS Toolpack that is capable of such thing. (Script all data from SQL Server database) I'm interested whether the same is possible in Oracle 11g.

Improve this question
3
  • Do you actually want a script where you can see all the individual statements, or just a mechanism to move the data? The Oracle server has tools to move everything (except the database creation part - but you might mean 'schema' in this context - and users), but in a proprietary format rather than a script. Client applications have tools to generate scripts. Commented Sep 11, 2013 at 8:29
  • do you want to import and export your DB? Commented Sep 11, 2013 at 9:03
  • have u refer this link?asktom.oracle.com/pls/asktom/… Commented Sep 11, 2013 at 9:11

1 Answer 1

Reset to default
4

To extract metadata and data you should look at data pump, specifically the export and import tools. This will be the simplest, fastest and most supported way to move everything.

You will need to already have created the database, but I'm not sure if you're confusing that with the Oracle schema. Which you will also have to create in advance by creating the user(s) that will own all the objects. You can extract a script to create the user/schema, e.g. from Toad or SQL Developer, or using the DBMS_METADATA package.

Most client applications also have options to export pretty much everything as scripts. In SQL Developer, for example, go to the Tools menu and there's an 'Export database' option where you can choose what you want to include, which will be pretty much everything in your case. You'll still need to pre-create the new database to run those scripts against.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Yes, I see - it's possible with SQL Developer -> Tools -> Export database. That creates .sql script as I need. Just being curious whether it's possible to run it from command line?
@AnatolyLushnikov - SQL Developer has a command line tool, which has been rewritten for version 4, but I haven't used it and I don't know what it can do really. You could look on their forum if no-one here knows. Or you could write your own scripts to extract the metadata and run those from a command line with SQL*Plus maybe. Data is more tricky and I'd go the data pump route if possible.
Update : creating the same sql script from command line is possible by using expdp first and then impdp (with sqlfile option).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.