[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


How to backup and restore whole(complete) database using Import and Export Utilities using Oracle 9i

Posted on 2006-07-23
Medium Priority
Last Modified: 2008-02-01
How would I export the FULL database using Oracle 9i Export utilities to a dump file and restore the FULL database again using Import utility into another DB in a different machine.  Is there any way that I can restore the same/complete database state and I do not have to create the users one by one?  

Question by:angelsky
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
LVL 18

Assisted Solution

rbrooker earned 100 total points
ID: 17165241

sounds to me like you want to do a backup restore.  much easier than doing a complete export / import and much much more reliable.  this also has the advantage of taking everything, including users ( plus their passwords are maintained ).

there are plenty of examples of how to do this throughout experts exchange...

good luck.


Accepted Solution

hardikbeit earned 200 total points
ID: 17165347

Hi angelsky,

You can do full export with EXPORT utility of Oracle and restore with IMPORT utility.

Some export/import command for your reference:

With use of EXP command you can backup
-      Entire database
-      User wise database
EXP filename.dmp it creates dump file
Examples: (For entire backup use 'sys' userid with sysdba rights (sys/pass@string as sysdba)
Exp user/pass file = a.dmp rows= yes indexes = no;
Exp user/pass file = a.dmp full=yes
Exp user/pass file = a.dmp tables = working_lines query =\’where phone_no = 272721\’;

IMP command is used to import database from dump file.
      With use of IMP command you can restore
-      Entire database
-      Particular user
-      Particular table
IMP user/pass@domain filename
IMP user/pass file = a.dmp fromuser = fromusername touser= tousername tables = (working_lines, closed_lines);

Let me know in case of any query.

With regards,
LVL 16

Expert Comment

ID: 17165473
A full database export and import can be a good way to replicate or clean up a database. A full export does not export triggers owned by schema SYS. You must manually re-create SYS triggers either before the full import. A full import creates any undefined tablespaces using the same datafile names as the exported database.
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments


Assisted Solution

sathya_s earned 200 total points
ID: 17165504
For Exporting a full database:
exp <username1>/<password1> full=y file=<filename> statistics=NONE consistent=Y log=<log_filename>

Username1/password1 :  Username  & Password of the user who is going to do the export

Filename : Specifies the name of the export file.

Statistics : Specifies the type of database optimizer statistics to generate when the exported data is imported. Options are ESTIMATE, COMPUTE, and NONE.

In some cases, Export will place the calculated statistics in the export file as well as the ANALYZE statements to regenerate the statistics.
The calculated optimizer statistics are flagged as questionable at export time if:
•      There are row errors while exporting
•      The client character set or NCHAR character set does not match the server character set or NCHAR character set
•      A QUERY clause is specified
•      Only certain partitions or subpartitions are exported
It is always better to specify statistics = NONE, which will avoid the warnings generated.

Consistent : Specifies whether or not Export uses the SET TRANSACTION READ ONLY statement to ensure that the data seen by Export is consistent to a single point in time and does not change during the execution of the exp command. You should specify CONSISTENT=y when you anticipate that other applications will be updating the target data after an export has started.

Log: Error log

For Importing a full database:

imp <username1>/<password1> full=y file=<filename> log=<log_filename>

Username1/password1 :  Username  & Password of the user who is going to do the import.

Filename : Specifies the name of the import file

Log: Error log

Create a new database before importing.


Author Comment

ID: 17166422
Hi Guys,
I tried the export but it truncated my user names and schemas to only 30 characters, is there a way to configure it not to truncate the data but restore everything with its original length?


Expert Comment

ID: 17166510

Hi angelsky,

This is due to Oracle naming restriction, max length is 30 characters. I am thinking that How can you create your usernames with more than 30 characters. As per my point of view it's not possible to exceed 30 characters.

With regards, Hardik

Author Comment

ID: 17172733
Hi Hardik,
The database I am trying to backup is connected to our test management tool, TestDirector and it is the one that creates the users and schemas automatically when we use it.

Is there any way we can lengthen the names :-)

Many thanks to all of you!

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

656 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