Export and Importing data using Oracle 11
Background In the era of oracle 9-10, I usually does export and imports data using exp and imp utility. Using oracle 11g, things aren't as smooth as it used to be. Problem A - Exp doesn't export empty tables Exp no longer exports empty tables. The cause are, exp utility is processing data segments, creating export dump. The Oracle 11g has a new feature, called 'Deferred segment creation'. It means that tables with no data will occupy no segments. And these tables will be missing from the exp result. The symptom of this problem is simple, we have missing tables in the destination system whose row count in the source system are zero. Solution A-1 - Allocate segment for empty tables Alter table allocate extent; This is not so ideal, we must execute the SQL above for each empty table before doing exp command. Solution A-2 - Disable deferred segment creation Alter system set DEFERRED_SEGMENT_CREATION=FALSE; This need to be done before we even cre...