Using SYSDBA for datapump export and import

Here is a quote from MOS on the usage oof SYSDBA for export and import

*How to Connect AS SYSDBA when Using Export or Import

[ID 277237.1]*

SYSDBA is used internally in the Oracle database and has specialized functions. Its behavior is not the same as for generalized users. For example, the SYS user cannot do a transaction level consistent read (read-only transaction). Queries by SYS will return changes made during the

transaction even if SYS has set the transaction to be READ ONLY. 

Therefore export parameters like CONSISTENT, OBJECT_CONSISTENT, FLASHBACK_SCN, and FLASHBACK_TIME cannot be used. Starting with Oracle10g, the export shows a warning that the export is not

consistent when the export is started with CONSISTENT=Y and connects to the database with the user SYS (or as SYSDBA):

EXP-00105: parameter CONSISTENT is not supported for this user

Note that Oracle automatically provides read consistency to a query so that all the data that the query sees comes from a single point in time (statement-level read consistency). For export this means that the export of

table data is consistent. However, if a table contains nested tables, the outer table and each inner table are exported as separate transactions. And if a table is partitioned, each partition is exported as a separate transaction. If a nested table or a partitioned table was updated during the

export, the data that is exported while connected as the SYS schema could be inconsistent.

This could be reason when you see 'duplicate key on index' and related errors when you perform datapump import using impdp. For eg :- There were some duplicate value on index of some tables which were taken using datapump export as sysdba with flashback parameters. The table does not have duplicate values in production.

Typically, there is no need to invoke Export or Import as SYSDBA, except in the following situations:

- at the request of Oracle technical support;

- when exporting a transportable tablespace set with the old style export utility (Oracle9i and Oracle8i);

- when importing a transportable tablespace set with the old-style import utility (Oracle10g, Oracle9i, and Oracle8i)."