Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
Showing results for 
Search instead for 
Did you mean: 

Oracle export

Go to solution
Giada Bonfà
Frequent Advisor

Oracle export

I've to make a database full export. Before issuing :

exp system/passws file=EXP.DMP full=y buffer=300000

do I have to open the database in restrict mode?
What happens if some users are working on the db while I'm making the export?

Will my export be good?

Thank you.
Steve Steel
Honored Contributor

Re: Oracle export


I would try to have no users active when I export and even limit the access.


Set the BUFFER parameter to a high value (eg. 2800000)
Make sure all applications on the site has been closed
Make sure the export file is on a physical drive not used by oracle
DO NOT export to a NFS mounted filesystem. It will take forever.

Steve Steel
If you want truly to understand something, try to change it. (Kurt Lewin)
Ian Lochray
Respected Contributor

Re: Oracle export

Here is an extract from Oracle's documentation describing the "consistent" parameter of the "exp" command. (I hope the formatting is OK).
Default: N

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 export command. You should specify CONSISTENT=Y when you anticipate that other applications will be updating the target data after an export has started.

If you specify CONSISTENT=N (the default), each table is usually exported in a single transaction. However, if a table contains nested tables, the outer table and each inner table are exported as separate transactions. If a table is partitioned, each partition is exported as a separate transaction.

Therefore, if nested tables and partitioned tables are being updated by other applications, the data that is exported could be inconsistent. To minimize this possibility, export those tables at a time when updates are not being done.

The following chart shows a sequence of events by two users: USER1 exports partitions in a table and USER2 updates data in that table.

Time Sequence USER1 USER2
Begins export of TAB:P1


Updates TAB:P2
Updates TAB:P1
Commit transaction

Ends export of TAB:P1

Exports TAB:P2

If the export uses CONSISTENT=Y, none of the updates by USER2 are written to the export file.

If the export uses CONSISTENT=N, the updates to TAB:P1 are not written to the export file. However, the updates to TAB:P2 are written to the export file because the update transaction is committed before the export of TAB:P2 begins. As a result, USER2's transaction is only partially recorded in the export file, making it inconsistent
Steven E. Protter
Exalted Contributor

Re: Oracle export

Its always better to have no activity going on when you export, but you don't have to open the database in exlusive mode to get a good export.

Steven E Protter
Owner of ISN Corporation

Re: Oracle export

I've run into problems with export files on very busy db's. 100 + users at a time. If your activity is low you can get away with it. Just use consistent=y if this is your case.

Re: Oracle export

If you use CONSISTENT=Y,
be careful about Rollback (with database activity)
or you'll get a error
'Snapshot too old'
Honored Contributor

Re: Oracle export


hope that this export is NOT your way of backing up of the database!

The only real backup you can make is using RMAN.

RMAN is real easy to configure and takes care of any consitency issues as long as you are running in Archive log mode.

if you need any further help, let us know.

Hope this helps!
Best Regards
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Graham Cameron_1
Honored Contributor

Re: Oracle export

Agree with most that EXP/IMP is not an ideal way of backing up but it is certainly better than nothing, and I have seen it used in many businesses.
There are several other ways, of which RMAN is the best, but not the only. For example, where I currently work, we shutdown the database and do file copy at the O/S level. (Management still believe that this is a supposed 24x7 environment !!)

-- Graham
Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done.

Re: Oracle export

Export is not a "real" backup of the database bc
it's a program after all, you can have bugs...

Export is useful when you want to restore one table that a user has deleted for example

RMAN is nice because it doesn't backup empty database blocks... and it checks block corruption