Friday, April 27, 2012

Why should we perform consistent export?


As per TomKyte....

it makes all of the queries run by exp be "as of the same point in time-- consistent with
regards to eachother"

imagine if you started an export at 9am of the EMP and DEPT tables.

EMP started exporting at 9am and DEPT at 9:15am.

Now, the EMP data would be "as of 9am", but the DEPT data would be as of 9:15am.  What if
you import that data now -- will it work?  maybe, maybe not -- perhaps at 9:10am, someone
fired the last employee in department 50 and deleted department 50.  Your EMP export
would have them in this deptno, your DEPT export would not have this deptno.  The import
would fail.

If you use consistent=y, Oracle will export all of the data "as of 9am", so deptno=50
will be included in all tables and the import will succeed.