Whole database EXPORT

Posted on 2007-08-01
Medium Priority
Last Modified: 2013-12-19
      I want to refresh a development database with production data. The database size is around 3GB.  As this is a full EXPORT and IMPORT of the database, how can i truncate the before data in development instance? I can directly IMPORT but that would leave some duplicate data. So, i would like to clean the development database first and then IMPORT with the new production data. Do i have to go and do a truncate at each user level or is there any way to get around this problem?

Also, do we have to do a FULL database export as SYS? or will the SYSTEM work?

Any information will be of great help.

Question by:harris2107
LVL 78

Accepted Solution

slightwv (䄆 Netminder) earned 1500 total points
ID: 19619296
FULL exports are fine.  There are those that will disagree with me but I would not do a FULL import.  FULL imports also try to import the SYSTEM user (or at least it used to.  I haven't done them in a while for this very reason).

SYSTEM should work fine for the export and import.

Depending on the number of schemas in the system, I would probably drop and recreate all the schemas then do a 'OWNER' specific import for the schemas in question.  This would be quicker than truncating all the tables ( again it depends on the setup).

Expert Comment

ID: 19626540
I would add:  Create a specific user that you will use for all your exports, with the following privileges as Sys:

create user backup_user identified by backup_user default tablespace users;
grant connect, exp_full_database, imp_full_database, select_catalog_role, unlimited tablespace, select any sequence, become user, backup any table, analyze any to backup_user;

grant select on sys.incexp to backup_user;
grant select on sys.incfil to backup_user;
grant select on sys.incvid to backup_user;            

Now use this to import as slightwv says.


Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Attention: This article will no longer be maintained. If you have any questions, please feel free to mail me. jgh@FreeBSD.org Please see http://www.freebsd.org/doc/en_US.ISO8859-1/articles/freebsd-update-server/ for the updated article. It is avail…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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.
Suggested Courses
Course of the Month14 days, 11 hours left to enroll

840 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